Removing rows with missing values

Share icon

Hello All, so I have a data file with 100 columns and 2000 rows. In some rows some of the cells don't have values and are blank. Is there an easy way to delete these rows?

Thanks

To remove NaNs from 1D numeric waves you can do

WaveTransform zapnans yourWave

It could be that this task is a bit more complicated though - can't tell from your question. E.g. if it's one 2D wave or if you want to evaluate if the whole row is blank before deleting...

December 27, 2018 at 12:28 pm - Permalink

OK so at the end the columns need to be the same length. The way I would approach this is to make a 2D wave with all the columns in. Then do

// matrix is called matA
MatrixOp/O NaNLocWave = SumRows(matA)

This will give a 1D wave that has NaNs for every row where there was a NaN in the matrix (because summing with a NaN gives a NaN). You can then use this to set all points each row where there is a NaN to NaN. You can test for NaN with Numtype == 2.

MatA[][] = (NumType(NaNLocWave[p]) == 2) ? NaN : MatA[p][q]

Next you can use SplitWave to break the matrix into columns. Run WaveTransform ZapNans on each 1D wave (do this in a loop) and then concatenate it back together.

December 27, 2018 at 01:30 pm - Permalink

The OP also asked this question of support@wavemetrics.com, sending in an example file.

I assumed he loaded the file as separate waves (all the same length) using Load Delimited Text into a table.

This code was presented as a solution:

#pragma TextEncoding = "UTF-8"
#pragma rtGlobals=3     // Use modern global access method and strict wave access.

Macro RemoveRowsWithNansInTopTable()

    String tableName= WinName(0,2)
   
    if( strlen(tableName) == 0 )
        DoAlert 0, "Expected table"
    endif
    fRemoveRowsInTableWithNans(tableName)
End

Function fRemoveRowsInTableWithNans(tableName)
    String tableName
   
    // put references to all of the table column waves into one WAVE/WAVE
    String info= TableInfo(tableName,-2)
    Variable maxRows = NumberByKey("ROWS", info)
    Variable columns = NumberByKey("COLUMNS", info) // includes "Points" column
    Make/O/N=(columns-1)/WAVE/FREE waveRefs= WaveRefIndexed(tableName, p, 1)
   
    // make a "mask wave" as long as the longest wave in the table
    // rowHasBlank[row] = 0 means no columns are blank (NaN)
    // rowHasBlank[row] = 1 means one or more columns are blank
    Make/O/N=(maxRows)/FREE rowHasBlank = AnyColumnIsBlank(waveRefs,p) // p is also table row number

    // remove rows from all the waves if any row has a NaN
    RemoveRowsWithBlanks(waveRefs, rowHasBlank)
End

// return 1 if one (or more table column) is blank

Function AnyColumnIsBlank(waveRefs,row)
    WAVE/WAVE waveRefs
    Variable row
   
    Variable i, nWaves= numpnts(waveRefs)
    for(i=0; i < nWaves; i+=1 )
        WAVE w = waveRefs[i]
        Variable type = numtype(w[row])
        if( type == 2 )     // NaN
            return 1            // at least one "column" is NaN
        endif
    endfor
   
    return 0 // none were blank if we got here
End

    // remove rows from all the waves if any row has a NaN
Function RemoveRowsWithBlanks(waveRefs, rowHasBlank)
    WAVE/WAVE waveRefs
    Wave rowHasBlank
   
    // work from the end to the start, because DeletePoint changes the effective row Number.
    Variable nRows = numpnts(rowHasBlank)
    Variable row= nRows-1
    do
        Variable hasBlank = rowHasBlank[row]
        if( hasBlank )
            Variable i, nWaves= numpnts(waveRefs)
            for(i=0; i < nWaves; i+=1 )
                WAVE w = waveRefs[i]
                DeletePoints/M=0 row, 1, w
            endfor
        endif
        row = row-1
    while( row >= 0 )
End

December 31, 2018 at 09:58 am - Permalink