4

Hi I have a set of data(message with different size) in excel like (over 5000 entries):

message Name               MessageSize
message1                   0.5M
message2                   10.2M
message3                   2.1M
...
message n-1                40.52M
message n                  12.12M

I have successfully generated a frequency table and a bar chart with it.

e.g. Different range of size have how many message occurred ?

0 - 5M 4000
5 - 10M 454
5 - 15M 234
..
...
45-50M  2

Below are some statistics for this set of data:

Mean            Median          Mode            Skewness    Kurtosis        
2.085443743         0.884527206 9.762702942 5.067491934 37.95900794 

Standard deviation    min             max
3.642138843       0.006651878    49.12574959

But when I tried to draw a normal distribution via excel NORMDIST() it's has turned very ugly because it's probably not a Normal distribution ! As mean, median & mod are totally different !

How could I find out what kind of distribution the data belongs to ? or model a statistic model for it ?

Thanks a lot.

I think stats here is a great community, lots of useful answers in a short while just like stackoverflow :)

RoundPi
  • 145
  • 1
  • 1
  • 6

2 Answers2

7

Following up on Peter Flom's excellent answer, and its comments. If you have your data in a CSV file with column names at the top, in R you would do something like:

messages <- read.csv ("my_data_file", header=TRUE)

you could then look at your data to check that it did what you think it did with:

str (messages)

which will give one line for each column that was read in. If the column of data you're interested in is called "length", you could do:

plot (density (messages$length))
plot (density (log (messages$length)))

And similar things to look at your data.

Note that before you export from Excel, make sure that the numbers are formatted as just numbers. If you have it formatted to include comma separators ("12,300"), R will consider it to be a string not a number. (Actually, the read.csv will turn it into a factor by default, which will complicate your life.) If your column names have spaces in them, R will turn the spaces into periods by default, which will make your life simpler than if it kept spaces in the names.

If you don't have column names at the top of your CSV, don't add headers=TRUE, and the data will be read into messages with the columns named "V1", "V2", etc.

summary (messages)
qqnorm (messages$length)

To get help, put a question mark in front of the command:

?density

Or if you don't know density but want to find all commands that use the word "density" in their description, use two question marks (though you'll probably either get more or less than you want to know at this point):

??density

Good luck!

Wayne
  • 19,981
  • 4
  • 50
  • 99
5

Welcome to the site.

First, I wouldn't use Excel. Whatever its merits as a spreadsheet, it's not a statistics program. On this site, and generally, R is hugely popular (with good reason) but there are also SAS, SPSS, Matlab and other programs that are better suited to this sort of thing.

Second, I am not sure why you divided your continuous variable (or nearly continuous) of message size into categories.

Third, as to your question: it's clear that your variable has a long right tail. Since no messages can have size 0, a natural choice is the log transformation. Try that and then plot a density curve and maybe a QQ plot against a normal and see how it looks.

Peter Flom
  • 94,055
  • 35
  • 143
  • 276
  • 2
    +1. Binning into classes is an ancient method. While histograms can be useful, modern statistical software makes it easy as well as advisable to fit distributions to the raw data. Binning just throws away detail that is crucial in determining which distributions are plausible. In particular, about 2/3 of your values fall in your lowest bin. (Have to add a mention of my own personal choice of program, `Stata`.) – Nick Cox May 23 '13 at 10:54
  • @Peter: Thanks. I have heard of R, SAS and used Matlab before but I am in no position do follow your instruction straight away. Is there some good tutorial for me to follow so I can do qqplot etc with R ?I have some statistic background with a strong programming background(C++,python,C#, java etc). – RoundPi May 23 '13 at 10:58
  • Searches for `qqnorm` within or indeed outside R will yield numerous hits. – Nick Cox May 23 '13 at 11:08
  • Gob00st - if you're used to Matlab and Python, parts of R will seem familiar. You can read a 'flat' .csv file (which Excel can generate and open) directly into R with `read.csv`; it will take column headers as variable names if you have them. You can do a normal qqplot in R with `qqnorm(x)` for some vector `x`; also see `qqline`. – Glen_b May 23 '13 at 11:18
  • @Glen_b: thanks I will try R. Any nice tutorial or book you would recommend ? – RoundPi May 23 '13 at 11:43
  • @NickCox: thanks for the input I will try qqnorm ! – RoundPi May 23 '13 at 11:44
  • Depends on your needs, really. There's a ton of information available for R. If you're after a "R programming" style of book, Matloff's book is decent enough. If you're more after statistical things, there's a bunch of good books, depending on topics and level (I'd recommend Venables&Ripley's *Modern Applied Statistics with S* as one of the books you consider, if you have enough stats background to make use of it). – Glen_b May 28 '13 at 07:31