# Can not generate graph using smooth kernel distribution

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.

• Without data, it's hard to say anything. Commented Aug 24, 2015 at 13:05
• @Guess i think a dataset is included to generate data as a link to an external file. Commented Aug 24, 2015 at 13:12
• 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}}]
– user9660
Commented Aug 24, 2015 at 14:34
• @Guesswhoitis.I have uploaded data to my dropbox, here is the link: dropbox.com/s/fmhhpqfhjf01362/test.xlsx?dl=0 Commented Aug 24, 2015 at 14:34
• @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. Commented Aug 24, 2015 at 15:04

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


• 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.] Commented Aug 29, 2015 at 7:51
• 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. Commented Aug 29, 2015 at 7:55
• 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 Commented Aug 29, 2015 at 12:29
• @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. Commented Aug 29, 2015 at 13:25
• 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. Commented Aug 29, 2015 at 14:04

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}}]


How to | Import a Spreadsheet

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

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"}

• Bingo, thanks. I will go through the documentation page. Thanks anyway~:) Commented Aug 24, 2015 at 14:57
• @LeiPeng You are welcome
– user9660
Commented Aug 24, 2015 at 14:58
• 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 Commented Aug 29, 2015 at 7:08
• 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? Commented Aug 29, 2015 at 8:01
• But anyway, your answer really helps. Commented Aug 29, 2015 at 8:14