Masking a Pandas DataFrame rows based on the whole row
pandas, python
Solution
The process of clarifying my question has lead me, in a roundabout way, to finding the answer. This question also helped point me in the right direction. Here's what I figured out:
import pandas as pd
# Set up my fake test data again. My actual data is described
# in the question.
cols = ['band1','band2','band3','band4','band5','band6','band7','band8']
rdf = pd.DataFrame(np.random.randint(0,10,80).reshape(10,8),columns=cols)
zdf = pd.DataFrame(np.zeros( (3,8) ),columns=cols)
df = pd.concat((zdf,rdf)).reset_index(drop=True)
# View the dataframe. (sorry about the alignment, I don't
# want to spend the time putting in all the spaces)
df
band1 band2 band3 band4 band5 band6 band7 band8
0 0 0 0 0 0 0 0 0
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
3 6 3 7 0 1 7 1 8
4 9 2 6 8 7 1 4 3
5 4 2 1 1 3 2 1 9
6 5 3 8 7 3 7 5 2
7 8 2 6 0 7 2 0 7
8 1 3 5 0 7 3 3 5
9 1 8 6 0 1 5 7 7
10 4 2 6 2 2 2 4 9
11 8 7 8 0 9 3 3 0
12 6 1 6 8 2 0 2 5
13 rows × 8 columns
# This is essentially the same as item #2 under Fails
# in my question. It gives me the indexes of the rows
# I want unmasked as True and those I want masked as
# False. However, the result is not the right shape to
# use as a mask.
df.apply( lambda row: any([i<>0 for i in row]),axis=1 )
0 False
1 False
2 False
3 True
4 True
5 True
6 True
7 True
8 True
9 True
10 True
11 True
12 True
dtype: bool
# This is what actually works. By setting broadcast to
# True, I get a result that's the right shape to use.
land_rows = df.apply( lambda row: any([i<>0 for i in row]),axis=1,
broadcast=True )
land_rows
Out[92]:
band1 band2 band3 band4 band5 band6 band7 band8
0 0 0 0 0 0 0 0 0
1 0 0 0 0 0 0 0 0
2 0 0 0 0 0 0 0 0
3 1 1 1 1 1 1 1 1
4 1 1 1 1 1 1 1 1
5 1 1 1 1 1 1 1 1
6 1 1 1 1 1 1 1 1
7 1 1 1 1 1 1 1 1
8 1 1 1 1 1 1 1 1
9 1 1 1 1 1 1 1 1
10 1 1 1 1 1 1 1 1
11 1 1 1 1 1 1 1 1
12 1 1 1 1 1 1 1 1
13 rows × 8 columns
# This produces the result I was looking for:
df.where(land_rows)
Out[93]:
band1 band2 band3 band4 band5 band6 band7 band8
0 NaN NaN NaN NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN NaN NaN NaN
3 6 3 7 0 1 7 1 8
4 9 2 6 8 7 1 4 3
5 4 2 1 1 3 2 1 9
6 5 3 8 7 3 7 5 2
7 8 2 6 0 7 2 0 7
8 1 3 5 0 7 3 3 5
9 1 8 6 0 1 5 7 7
10 4 2 6 2 2 2 4 9
11 8 7 8 0 9 3 3 0
12 6 1 6 8 2 0 2 5
13 rows × 8 columns
Thanks again to those who helped. Hopefully the solution I found will be of use to somebody at some point.
I found another way to do the same thing. There are more steps involved but, according to %timeit, it is about 9 times faster. Here it is:
def mask_all_zero_rows_numpy(df):
"""
Take a dataframe, find all the rows that contain only zeros
and mask them. Return a dataframe of the same shape with all
Nan rows in place of the all zero rows.
"""
no_data = -99
arr = df.as_matrix().astype(int16)
# make a row full of the 'no data' value
replacement_row = np.array([no_data for x in range(arr.shape[1])], dtype=int16)
# find out what rows are all zeros
mask_rows = ~arr.any(axis=1)
# replace those all zero rows with all 'no_data' rows
arr[mask_rows] = replacement_row
# create a masked array with the no_data value masked
marr = np.ma.masked_where(arr==no_data,arr)
# turn masked array into a data frame
mdf = pd.DataFrame(marr,columns=df.columns)
return mdf
The result of `mask_all_zero_rows_numpy(df)` should be the same as `Out[93]:` above.
Problem
Background: I'm working with 8 band multispectral satellite imagery and estimating water depth from reflectance values. Using statsmodels, I've come up with an OLS model that will predict depth for each pixel based on the 8 reflectance values of that pixel. In order to work easily with the OLS model, I've stuck all the pixel reflectance values into a pandas dataframe formated like the one in the example below; where each row represents a pixel and each column is a spectral band of the multispectral image. Due to some pre-processing steps, all the on-shore pixels have been transformed to all zeros. I don't want to try and predict the 'depth' of those pixels so I want to restrict my OLS model predictions to the rows that are NOT all zero values. I will need to reshape my results back to the row x col dimensions of the original image so I can't just drop the all zero rows. Specific Question: I've got a Pandas dataframe. Some rows contain all zeros. I would like to mask those rows for some calculations but I need to keep the rows. I can't figure out how to mask all the entries for rows that are all zero. For example: ``` In [1]: import pandas as pd In [2]: import numpy as np # my actual data has about 16 million rows so # I'll simulate some data for the example. In [3]: cols = ['band1','band2','band3','band4','band5','band6','band7','band8'] In [4]: rdf = pd.DataFrame(np.random.randint(0,10,80).reshape(10,8),columns=cols) In [5]: zdf = pd.DataFrame(np.zeros( (3,8) ),columns=cols) In [6]: df = pd.concat((rdf,zdf)).reset_index(drop=True) In [7]: df Out[7]: band1 band2 band3 band4 band5 band6 band7 band8 0 9 9 8 7 2 7 5 6 1 7 7 5 6 3 0 9 8 2 5 4 3 6 0 3 8 8 3 6 4 5 0 5 7 4 5 4 8 3 2 4 1 3 2 5 5 9 7 6 3 8 7 8 4 6 6 2 8 2 2 6 9 8 7 9 4 0 2 7 6 4 8 8 1 3 5 3 3 3 0 1 9 4 2 9 7 3 5 5 0 10 0 0 0 0 0 0 0 0 11 0 0 0 0 0 0 0 0 12 0 0 0 0 0 0 0 0 [13 rows x 8 columns] ``` I know I can get just the rows I'm interested in by doing this: ``` In [8]: df[df.any(axis=1)==True] Out[8]: band1 band2 band3 band4 band5 band6 band7 band8 0 9 9 8 7 2 7 5 6 1 7 7 5 6 3 0 9 8 2 5 4 3 6 0 3 8 8 3 6 4 5 0 5 7 4 5 4 8 3 2 4 1 3 2 5 5 9 7 6 3 8 7 8 4 6 6 2 8 2 2 6 9 8 7 9 4 0 2 7 6 4 8 8 1 3 5 3 3 3 0 1 9 4 2 9 7 3 5 5 0 [10 rows x 8 columns] ``` But I need to reshape the data again later so I'll need those rows to be in the right place. I've tried all sorts of things including `df.where(df.any(axis=1)==True)` but I can't find anything that works. Fails: `df.any(axis=1)==True` gives me `True` for the rows I'm interested in and `False` for rows I'd like to mask but when I try `df.where(df.any(axis=1)==True)` I just get back the whole data frame complete with all the zeros. I want the whole data frame but with all the values in those zero rows masked so, as I understand it, they should show up as Nan, right? I tried getting the indexes of the rows with all zeros and masking by row: ``` mskidxs = df[df.any(axis=1)==False].index df.mask(df.index.isin(mskidxs)) ``` That didn't work either that gave me: ``` ValueError: Array conditional must be same shape as self ``` The `.index` is just giving an `Int64Index` back. I need a boolean array the same dimensions as my data frame and I just can't figure out how to get one. Thanks in advance for your help. -Jared