2
$\begingroup$

Explanation:

I would like to analyse the data from an experiment, which investigates the performance of a mechanical component using sensors, that has generated 2000 CSV files. Each file contains 513 Rows x 1220411 Cols, and they are in spectrogram format (columns are time and rows are frequency):

| Time (s)| 0.0000 |0.000164|... 
|:--------|--------|:-------|:-------
| 1.52kHz |  2747  |  350   |...
| 3.05kHz |  2996  |  420   |...
| 4.57kHz |  4078  |  300   |...
|   ...   |  ...   |  ...   |...

I have plotted my 3D chart of the first 100 rows using persp3D():

persp3D(x,y,z, theta=45, phi=5, xlab="Frequency (kHz)", ylab="Time (s)", axes=TRUE, expand=0.5, shade=0.2)

enter image description here I would like to extract 1-4 column (1 sample of time for all frequencies) from each file to get a table of data with the total of 2000-8000 columns (for 2000 files with all the frequencies) and plot this to get the 3D plot of the experiment.

Question: best data reducing method

  • I would like to know what is the best method to make sure that those 1-4 columns represent the total data set in each file?
  • Is averaging simply a good method in this case? What are the alternatives?
$\endgroup$
3
$\begingroup$

So, first of all, CSV seems to me the least suitable format imaginable for this amount of data. It needs to be parsed, is memory hungry, and wastes precision, and isn't linearly addressable (ie. to get to the 99999. element, you need to parse the preceeding 99998 elements).

So I'd recommend keeping the structure from these files, but converting them to binary files, eg. numpy arrays, HDF5, whatever, but keep it binary, parse-free. Convert once and keep that data as what you load – it will reduce your file loading times immensely, and your software (whatever you're using) possibly doesn't even need to load all the file at once into memory, because when the data format has a fixed bit width per element (e.g. always 4-Byte integers and 8-Byte floating point numbers), it's trivial for the software to just load the part of the file into memory that you need. In fact, operating systems do that for you ("mmaped files").

Reducing your text file format to e.g. an array of single-precision floats or 32bit integers, your total data size would be a mere 8GB – and that fits multiple times in the RAM of every modern workstation, so I'd argue it's not really "big data" anymore! (Just to take the fear of handling so many numbers away)

I've ranted about this before, and people have answered that "CSV files are human-readable". I dare anyone who says so to read a 100k-column, 500 row CSV file and justify their impression of the data as being representable for the whole set.


I would like to know what is the best method to make sure that those 1-4 columns represent the total data set in each file?

As always in engineering, there's no "best" without you defining a measure for good. If you want to highlight the differences among different frequencies, pick the column e.g. that has the highest sample variance. If you want to represent the whole measurement "fair", don't pick a single column, but average all columns. It's impossible to answer that question without being you – it's you that wants to show something. What you have is data – meaning is something that you humanly attribute (and, yes, you must be aware that the choice of data reduction method is your biased decision, and you should definitely communicate that).

Is averaging simply a good method in this case?

It's the first thing I'd try to see if I can still see something interesting. That's a pure coincidence – I might as well just pick a random one!

What are the alternatives?

Impossible to tell, again – picking one column based on some metric, calculating variance, third statistical moment, postulate a certain functional shape would be handy and fit that shape to your data set … The sky is the limit, and what you want to find out is your only guideline.

$\endgroup$

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.