Skip to main content
 首页 » 编程设计

python之根据整行屏蔽 Pandas DataFrame 行

2024年11月01日9TianFang

背景:

我正在处理 8 波段多光谱卫星图像,并根据反射率值估算水深。使用 statsmodels,我提出了一个 OLS 模型,该模型将根据每个像素的 8 个反射率值预测每个像素的深度。为了轻松地使用 OLS 模型,我将所有像素反射率值粘贴到格式如下例所示的 pandas 数据框中;其中每一行代表一个像素,每一列是多光谱图像的光谱带。

由于一些预处理步骤,所有岸上像素都已转换为全零。我不想尝试预测这些像素的“深度”,所以我想将我的 OLS 模型预测限制为不全为零值的行。

我需要将我的结果 reshape 为原始图像的行 x 列尺寸,这样我就不能只删除所有零行。

具体问题:

我有一个 Pandas 数据框。有些行包含全零。我想为某些计算屏蔽这些行,但我需要保留这些行。我不知道如何屏蔽全为零的行的所有条目。

例如:

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] 

我知道这样做可以得到我感兴趣的行:

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] 

但我需要稍后再次 reshape 数据,所以我需要将这些行放在正确的位置。我已经尝试了各种方法,包括 df.where(df.any(axis=1)==True) 但我找不到任何有效的方法。

失败:

  1. df.any(axis=1)==True 为我感兴趣的行提供 True 和 False 对于我想屏蔽的行,但是当我尝试 df.where(df.any(axis=1)==True) 时,我只是取回了包含所有零的整个数据框。我想要整个数据框,但那些零行中的所有值都被屏蔽了,所以据我所知,它们应该显示为 Nan,对吧?

  2. 我尝试获取全为零的行的索引并按行屏蔽:

    mskidxs = df[df.any(axis=1)==False].index 
    df.mask(df.index.isin(mskidxs)) 
    

    那也不管用,它给了我:

    ValueError: Array conditional must be same shape as self 
    

    .index 只是返回一个 Int64Index。我需要一个与我的数据框尺寸相同的 bool 数组,但我不知道如何获得一个。

预先感谢您的帮助。

-贾里德

请您参考如下方法:

澄清我的问题的过程以迂回的方式引导我找到答案。 This question也帮助我指明了正确的方向。这是我想出来的:

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 

再次感谢那些提供帮助的人。希望我找到的解决方案在某个时候对某些人有用。

我找到了另一种方法来做同样的事情。涉及的步骤更多,但根据 %timeit,它快了大约 9 倍。在这里:

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 

mask_all_zero_rows_numpy(df)的结果应该和上面的Out[93]:一样。