I have a spreadsheet file with around 150,000 pairs (x,y). I wish to work with a smaller file, i.e., with less data, so my exploratory analysis is faster.

What is the appropriate way of resampling, or working (one time only) with this data, so I can obtain a smaller amount of pairs, loosing the "least possible" amount of information or reliability, statistically speaking.

  • 2
    $\begingroup$ The right way to do this is to load it into software like R or Python and work with the entire data set. It sounds like a big number, but 300,000 points is not that many. Does something prevent you from using either of those (free!) pieces of software? $\endgroup$ – Dave Aug 20 at 23:10
  • $\begingroup$ I understand this is desirable, in fact I plan to do this later (learn R). Nonetheless, me and my team don't have that specific knowledge so far, and we have relied on "office" to do our exploratory analysis. In any case, I still wish to know what would be the way to compress any large data (say then 1,000,000,000 pairs), so as not to loose that much reliability and so as to obtain the same statistics (mean, etc.). Today we just cluster rounded data, without knowing this is the best way. $\endgroup$ – Francisco Aug 20 at 23:28
  • 1
    $\begingroup$ There’s no way to assure yourself of getting the same statistics that you would get from the entire data set. The way you (probably) get close is by taking a large random sample. $\endgroup$ – Dave Aug 20 at 23:34
  • $\begingroup$ Currently, all we can advise is simple random sampling. If you could elaborate what kind of analyses / estimates you are interested in, and what kind of data this is, then a more detailed answer might be possible. Even then, likely the answer will be "take as much as you can, as randomly as you can" $\endgroup$ – juod Aug 21 at 3:42

For whatever it's worth, SAS defaults to 2,000 as a sample size in it's Graph Explore nodes. I know that this often produces graphs that are not representative in different ways. (SAS is top-of-the line data mining software.)

If you want a random sample in Excel, add a new column, put =rand() in the first cell, copy the cell down to the bottom; select all the data (all rows/columns), sort on the new column (direction doesn't matter), and select how ever many rows you want from the top going down.

I can't imagine trying to do exploratory analysis with Excel. Maybe there are functions I'm not aware of. How is it easier to find the standard deviation of 1,000 rows rather than the whole set? It's the same number of key strokes.

I would work with the whole data set.

| cite | improve this answer | |

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.