python - Pandas 在 iloc Nulls 上设置值

Question

我在下面有一个示例数据框和函数。我创建了一个函数，它将获取“单元格”的坐标并将其放入一个元组中，以及将其放在那里的原因。我希望此函数也可以更改特定列的值。

import pandas as pd
import numpy as np
df1 = pd.DataFrame({'A' : [np.NaN,np.NaN,3,4,5,5,3,1,5,np.NaN], 
                    'B' : [1,0,3,5,0,0,np.NaN,9,0,0], 
                    'C' : [10,0,30,50,0,0,4,10,1,0], 
                    'D' : [1,0,3,4,0,0,7,8,0,1],
                    'E' : [np.nan,'Unassign','Assign','Ugly','Appreciate',
                          'Undo','Assign','Unicycle','Assign','Unicorn',]})
print(df1)
highlights = []
def find_nan(list_col):
    for c in list_col:
        # if column is one of the dataframe's columns, go
        if c in df1.columns:
            # for each index x where column c of the dataframe is null, go
            for x in df1.loc[df1[c].isnull()].index: #appends to the list of tuples
                highlights.append(
                    tuple([x + 2, df1.columns.get_loc(c) + 1, f'{c} is Null in row {x + 2}']))

                df1.iloc[x, df1.columns.get_loc('E')] = f'{c} is blank in row {x + 2}'
find_nan(['A','B']) 
    # using the function above, checks for all nulls in A and B
    # Also places the coordinates and reason in a tuple and changes values of column 'E'

    #output:
    A   B   C   D   E
0   NaN 1.0 10  1   A is blank in row 2
1   NaN 0.0 0   0   A is blank in row 3
2   3.0 3.0 30  3   Assign
3   4.0 5.0 50  4   Ugly
4   5.0 0.0 0   0   Appreciate
5   5.0 0.0 0   0   Undo
6   3.0 NaN 4   7   Assign
7   1.0 9.0 10  8   Unicycle
8   5.0 0.0 1   0   Assign
9   NaN 0.0 0   1   A is blank in row 11

我想做的是添加逻辑，如果 E 已经填充，则将原因加在一起，或者如果为 null，则简单地更改 E 的值。这是我的问题:使用 df1.iloc 我似乎无法检查空值。

df1.iloc[0]['E'].isnull() 返回 AttributeError: 'float' object has no attribute 'isnull'(显然)

解决这个问题:我可以使用 if np.isnan(df1.iloc[0]['E'])，它的计算结果为 True，但如果有是 E 中已有的值 我将得到一个 TypeError。

基本上我想要的是我的函数中的这种逻辑:

if df1.iloc[x]['E'] is null:
    df1.iloc[x, df1.columns.get_loc('E')] = 'PREVIOUS_VALUE' + f'{c} is blank in row {x + 2}'
else:
    df1.iloc[x, df1.columns.get_loc('E')] = f'{c} is blank in row {x + 2}

我的函数在原始数据帧上的预期输出:

find_nan(['A','B'])

    A   B   C   D   E
0   NaN 1.0 10  1   A is blank in row 2
1   NaN 0.0 0   0   Unassign and A is blank in row 3
2   3.0 3.0 30  3   Assign
3   4.0 5.0 50  4   Ugly
4   5.0 0.0 0   0   Appreciate
5   5.0 0.0 0   0   Undo
6   3.0 NaN 4   7   Assign and B is blank in row 8
7   1.0 9.0 10  8   Unicycle
8   5.0 0.0 1   0   Assign
9   NaN 0.0 0   1   Unicorn and A is blank in row 11

使用 Python 3.6。这是具有更多功能的更大项目的一部分，因此“添加原因”和“无明显原因”向索引添加 2

Answer 1

请注意，这是使用 Python 2 测试的，但我没有注意到任何会防止它在 Python 3 中工作。

def find_nan(df, cols):
    if isinstance(cols, (str, unicode)):
        cols = [cols]  # Turn a single column into an list.
    nulls = df[cols].isnull()  # Find all null values in requested columns.
    df['E'] = df['E'].replace(np.nan, "")  # Turn NaN values into an empty string.
    for col in cols:
        if col not in df:
            continue
        # If null value in the column an existing value in column `E`, add " and ".
        df.loc[(nulls[col] & df['E'].str.len().astype(bool)), 'E'] += ' and '
        # For null column values, add to column `E`: "[Column name] is blank in row ".
        df.loc[nulls[col], 'E'] += '{} is blank in row '.format(col)
        # For null column values, add to column `E` the index location + 2.
        df.loc[nulls[col], 'E'] += (df['E'][nulls[col]].index + 2).astype(str)
    return df

>>> find_nan(df1, ['A', 'B'])
    A   B   C  D                                 E
0 NaN   1  10  1               A is blank in row 2
1 NaN   0   0  0  Unassign and A is blank in row 3
2   3   3  30  3                            Assign
3   4   5  50  4                              Ugly
4   5   0   0  0                        Appreciate
5   5   0   0  0                              Undo
6   3 NaN   4  7    Assign and B is blank in row 8
7   1   9  10  8                          Unicycle
8   5   0   1  0                            Assign
9 NaN   0   0  1  Unicorn and A is blank in row 11