2
$\begingroup$

I am a newbie to Mathematica so this question might looks silly.

I want to generate a smooth kernel distribution graph for this set of data. The data I uploaded has two columns, the first column is the price of a specific day, and the second column is the weighted volume (which I calculated myself in excel using the volume of that specific price divided by total volumn,if anyone wants to view the raw data, can be found here). So these two columns of data comes with pairs, and I want to do a kernel density estimation for the first column of variables, using the second column as analytic weights.

By following the"SmoothKernelDistribution" documentation(I don't have enough reputation to post another link here), here is my code:

data = Import["c:\\Users\\****\\results]\\test.xlsx"][[1]];
K = SmoothKernelDistribution[data];
Table[Plot[f[K, x], {x, -4, 4}, PlotLabel -> f], {f, {PDF, CDF}}]

Instead of generating a graph with curves, it is end up with showing only x-axis and y-axis with no curves.

I could generate pretty good graph by using Stata and gnuplot with this set of data, so I think the format of data was OK.

Is there anybody could point out what I did wrong with my codes? Thanks.


I just realized by using the data I provided beforehand, there isn't much differences if the second weighted column were taked into consideration or not. However, for other data, the differences are huge and fundamental. Such as using this set of data,the differences between the answer provided by Lou and the graph generated by Stata(which I used the second column as weights), could be huge: https://www.dropbox.com/s/wt7j3ku6ha9wuhb/66xlsx.png?dl=0

Anyone could provide some further help? Thanks.

$\endgroup$
  • 2
    $\begingroup$ Without data, it's hard to say anything. $\endgroup$ – J. M. will be back soon Aug 24 '15 at 13:05
  • $\begingroup$ @Guess i think a dataset is included to generate data as a link to an external file. $\endgroup$ – MarcoB Aug 24 '15 at 13:12
  • $\begingroup$ Try with: data = Import["/Users/xxx/Desktop/test.xlsx", {"Data", 1, All, 1}]; Table[Plot[f[K, x], {x, -1000, 4000}, PlotLabel -> f], {f, {PDF, CDF}}] $\endgroup$ – user9660 Aug 24 '15 at 14:34
  • $\begingroup$ @Guesswhoitis.I have uploaded data to my dropbox, here is the link: dropbox.com/s/fmhhpqfhjf01362/test.xlsx?dl=0 $\endgroup$ – Lei Peng Aug 24 '15 at 14:34
  • $\begingroup$ @LeiPeng Your data file contains two columns; should we understand them to be independent, or pairs of (x, y) data? The answer you accepted below seems to suggest that you are OK with treating them independently, but is this what you actually want? If so, I think you might want to specify your question more clearly. $\endgroup$ – MarcoB Aug 24 '15 at 15:04
6
$\begingroup$
data = Import["/Users/roberthanlon/Downloads/test.xlsx"][[1]];

Dimensions[data]

{6039, 2}

Since the data consists of pairs of values, the distribution given by SmoothKernelDistribution[data] is for a bivariate distribution.

K = SmoothKernelDistribution[data];

{xmin, xmax} = MinMax[data[[All, 1]]];

{ymin, ymax} = MinMax[data[[All, 2]]];

Plot3D[#[K, {x, y}],
    {x, xmin, xmax},
    {y, ymin, ymax},
    PlotLabel -> #,
    ClippingStyle -> None,
    ImageSize -> 300] & /@
  {PDF, CDF} //
 Column

enter image description here

$\endgroup$
  • $\begingroup$ Thanks Bob, that's really a neat graph. I had specified my question to make it more clearly. The first column is the price, and the second column is the weight. My raw data was uploaded as well. Can you provide me a 2D graph with a kernel density estimation on the first column using the second column as weight.(If you use the raw data, then the code might need to do an analytics weights for the second column) .I had uploaded another set of data(dropbox.com/s/jbvk6p83m5j12cg/66.xlsx?dl=0), [cont.] $\endgroup$ – Lei Peng Aug 29 '15 at 7:51
  • $\begingroup$ There isn't much differences if the test.xlsx were weighted or not,that's why I firstly accepted Lou's answer, but there are really big differences among 66.xlsx. (unweighted raw data for 66 is here:dropbox.com/s/8so3fvh9t6meken/66-raw.xlsx?dl=0 ) Thanks for your help. $\endgroup$ – Lei Peng Aug 29 '15 at 7:55
  • $\begingroup$ By the way, can you show me how to draw a 2d graph using your code?Like the one I generated in stata: dropbox.com/s/wt7j3ku6ha9wuhb/66xlsx.png?dl=0 $\endgroup$ – Lei Peng Aug 29 '15 at 12:29
  • $\begingroup$ @LeiPeng - The number of data pairs are different between 66.xlsx and 66-raw.xlsx and you have not shown what you have attempted to do given the various responses that you have received. $\endgroup$ – Bob Hanlon Aug 29 '15 at 13:25
  • $\begingroup$ The 66-raw.xlsx is the original raw data. If you use every specific cell in the second column of 66-raw.xlsx divided by total of the second column of 66-raw xlsx, it ends up as 66.xlsx. In Excel, it is done like, let’s say (B1/Sum(B:B),B2/Sum(B:B) and etc.). The reason I am uploading the original raw data is I’m afraid someone will need the original raw data to make the code functioning. cont. $\endgroup$ – Lei Peng Aug 29 '15 at 14:04
3
$\begingroup$

You can even read the following How to | Import a Spreadsheet

data = Import["/Users/xxx/Desktop/test.xlsx", {"Data", 1, All, 1}];
K = SmoothKernelDistribution[data];
Table[Plot[f[K, x], {x, -1000, 4000}, PlotLabel -> f], {f, {PDF, CDF}}]

enter image description here

How to | Import a Spreadsheet

The spreadsheet is included in the Wolfram Language documentation folder ExampleData:

enter image description here

You can import the second column like so:

Import["ExampleData/population.xls", {"Data", 1, All, 2}]

{"China", "India", "United States", "Indonesia", "Brazil", "Pakistan", "Bangladesh", "Russia", "Nigeria", "Japan"}

$\endgroup$
  • $\begingroup$ Bingo, thanks. I will go through the documentation page. Thanks anyway~:) $\endgroup$ – Lei Peng Aug 24 '15 at 14:57
  • $\begingroup$ @LeiPeng You are welcome $\endgroup$ – user9660 Aug 24 '15 at 14:58
  • $\begingroup$ Hey Lou, I just found a loophole in your code. You seems treated data independently while actually they come with pairs. The second column of test.xlsx is the weights, and I actually would like to do a kernel density estimation on the first column while using the second column as weights. There isn't much differences using your code to generate a graph with test.xlsx, but among other data, such as the one I uploaded under Bob's answer, the differences between using weights or not is huge. I had revised my question to be more specific and clearly. Can you provide an alternative? Thanks $\endgroup$ – Lei Peng Aug 29 '15 at 7:08
  • $\begingroup$ I had uploaded another set of data:(dropbox.com/s/jbvk6p83m5j12cg/66.xlsx?dl=0 ) I had used your code to generate a graph comparing the one generated by stata. Here is the differences: (dropbox.com/s/wt7j3ku6ha9wuhb/66xlsx.png?dl=0) So, I think the code need to take the second column, the weights, into consideration. I made some elaboration under Bob's answer as well ,can you give some hints please? $\endgroup$ – Lei Peng Aug 29 '15 at 8:01
  • $\begingroup$ But anyway, your answer really helps. $\endgroup$ – Lei Peng Aug 29 '15 at 8:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.