1

I am trying to analyze some signal data collected at 120 Hz. I have Excel and SPSS at my disposal (as well as MATLAB, but I'm not really familiar with it). I've been searching online for about and hour and half and can't seem to find what I'm looking for although I know it should be relatively simple.

I have a list of 0's and 1's. I would like to calculate the duration of each signal then graph the frequency distribution of signals (ie., a histogram depicting percent of signals @ 1/10th sec, percent of signals @ 2/10ths sec, @ 3/10ths sec... @ 1 sec, and so on). I believe it is an inverse cumulative distribution I'm looking for but before I can get to that, I need to process the data in my spreadsheets.

I'm having trouble figuring out how to sum each "group" of ones to get the duration of each signal. It would do something like this: for a cell containing a 1, if the cell above contains a 0, that starts a new sum and continues summing the 1's until a 0 appears. By the end of the spreadsheet, I would have a list of signal durations (expressed in terms of 1/120th of a sec -- ie., 30 would be 0.25 seconds)

I've got over 100 files to process, so I'm looking for a formula or macro to do this quickly for each file.

I have data for 4 different devices and I'm looking to compare these signal distributions by device. Would I better off doing this in excel, spps or matlab?

Thank you!

The data is formatted in columns - I have frame count in column A and signal in column B (0 or 1). Each frame is 1/120th sec (I have thousands of frames per file). I'm not interested in the waveform, per se - I'm doing this for statistical purposes to detect if the distribution of long vs short signals is significantly different between conditions. Ultimately I'd like a list of the signal lengths that I could represent in a histogram of Frequency (and by frequency, I don't mean wave freq but rather the number of occurrences.

MsHF
  • 115
  • 7
  • Can you add code for what you have tried so far? And what specific problems are you having? – triggerNZ Sep 25 '14 at 23:12
  • Locate a block of **1's** and record its center-point. Locate the next block of **1's** and also records its center-point. These are two successive peaks. From the time difference you can calculate the period, frequency, wavelength, etc. – Gary's Student Sep 25 '14 at 23:18
  • I can think several ideas how this could be done in SPSS, but those ideas depend on how your data is structured in the first way, and which structure you need or don't need in the end. Can you give us a small example of your data? And would it be a problem to store the lenght of the runs of "1" of one messurement inside an extra data sheet? – mirirai Sep 26 '14 at 12:20
  • @mirirai - I edited the post to elaborate on my data format. Can I attach a file? – MsHF Sep 26 '14 at 15:38
  • @mirirai I have no issues with storing the signal lengths in a separate data sheet – MsHF Sep 26 '14 at 15:46
  • > "Can I attach a file?" No, not directly. See: http://meta.stackexchange.com/questions/47689/how-can-i-attach-a-file-to-a-stack-overflow-post – mirirai Sep 29 '14 at 16:59

2 Answers2

0

"to sum each "group" of ones to get the duration of each signal" apply Subtotal and At each change in: select your list, Use function: Sum and Add subtotal to: whichever column you wish to add.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • I'm not sure I understand how to use the Excel SUBTOTAL function in this case. I don't want a single value for all the 1's in the column, I want a value for each "chunk" of 1's occurring. The sum would be conditional (IF cell = 1, add to cell above. If cell = 0, start new subtotal. The problem with this approach is it gives me another column of numbers that increment by 1 - I ONLY want the totals) – MsHF Sep 26 '14 at 16:02
  • Ah! OK, I understand now. Thanks. This does give me the totals for each run of ones - unfortunately, it's presented in a very cumbersome format (it inserts rows & text). I would love to get just the totals data into a separate column if possible. – MsHF Sep 26 '14 at 16:42
  • A really stinky feature of Subtotals (I'm using excel 2011 for mac) is that it requires a column heading. Really unfortunate since there is a lot of other data on the sheet. – MsHF Sep 26 '14 at 17:19
  • A strange thing happens when I filter for "1 Total" - the subtotal values (in the column to the right) all read 0, even though they were all larger numbers before filtering. – MsHF Sep 26 '14 at 18:09
  • 1
    Finally got it to work with Paste Special Values. Thank you! For some reason, I can't record a macro once I filter the rows (I recorded all the steps through to pasting just the >0 subtotals into their own column but when I run it the macro stops doing anything after filter). This means manually copying the filtered subtotal column, clearing the filter, then pasting the subtotals into a new column. Not sure why I can't get a macro to do that. – MsHF Sep 26 '14 at 19:03
0

This is one possibility how this can be done in SPSS.

   * create some test data.
    DATA LIST FREE / frame (F8) signal (F1).
    BEGIN DATA
    1  1
    2  1
    3  0
    4  0
    5  1
    6  0
    7  1
    8  1
    9  1
    10 0
    11 0
    12 0
    13 1
    14 0
    15 1
    16 0
    17 1
    18 0
    19 1
    20 1
    END DATA.
    DATASET NAME data_orig.

    * use a work copy because data will be reduced in the process.
    DATASET COPY data_workcopy.
    DATASET ACTIVATE data_workcopy.

    * count the length of each run of "1".
    COMPUTE signallength = 0.
    IF (signal=1 AND (LAG(signal)=0 OR frame=1)) signallength = 1.
    IF (signal=1 AND LAG(signal)>0) signallength = LAG(signallength)+1.
    EXECUTE.

    * get the last/highest number of each run.
    * It's somewhat complicated, but I haven't found a much better idea yet.
    IF (signallength=1) signalbegin = 1.

    SORT CASES BY frame (D).
    IF (signal=1 AND LAG(signal)>0) signallength = LAG(signallength).
    EXECUTE.

    SELECT IF signalbegin=1.

    FREQUENCIES signallength /HISTOGRAM.

In this example there is not really a need for creating a work copy data sheet, because you could run the SELECT IF command with a TEMPORARY before it and no data would be lost. However working with a data set copy might be useful if you want to do some further analysis.

mirirai
  • 1,365
  • 9
  • 25
  • If someone knows a nice way how to mark the highest/last instance of each run please leave a note. – mirirai Oct 01 '14 at 00:05