根据条件选择A或where函数

Question

我需要创建一个新列indicator，它的值为code。我不能使用if-else语句，因为code可以在second code列的其他地方有一个值和相同的值。任何帮助都将不胜感激

import pandas as pd

df = pd.DataFrame({
    'date': ['2019-04-19','2019-04-20','2019-05-03', '2019-05-04',
             '2019-10-01','2019-10-07','2019-10-11', '2019-11-20'],
    'category': ['ID F', 'ID F', 'ID F', 'ID F',
             'ID B', 'ID B', 'ID B', 'ID B'],
    'code': ['None', 'None', 'None', '8008',
             'None', 'None', '9001', 'None'],
    'second_code': ['None', 'None', '8008', 'None',
             'None', 'None', 'None', 'None']})

df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True)


date    category    code    second_code
0   2019-04-19  ID F    None    None
1   2019-04-20  ID F    None    None
2   2019-05-03  ID F    None    8008
3   2019-05-04  ID F    8008    None
4   2019-10-01  ID B    None    None
5   2019-10-07  ID B    None    None
6   2019-10-11  ID B    9001    None
7   2019-11-20  ID B    None    None

所需数据帧

date    category       code    second_code indicator
0   2019-04-19  ID F    None    None    None
1   2019-04-20  ID F    None    None    None
2   2019-05-03  ID F    None    8008    8008
3   2019-05-04  ID F    8008    None    None
4   2019-10-01  ID B    None    None    None
5   2019-10-07  ID B    None    None    None
6   2019-10-11  ID B    9001    None    9001
7   2019-11-20  ID B    None    None    None

编辑：

我试过：

df['indicator'] = np.where(df['new_code'].notnull(), df['new_code'], np.where(df['code']).notnull(), df['code'], np.nan)

但是我得到了错误 AttributeError: 'tuple' object has no attribute 'notna'

Answer 1

最简单的方法应该是将fillna函数与drop_duplicates一起使用，以确保第一次在列中显示特定代码

# First we need to replace the "None" with actual NaN values
df = df.replace("None", np.nan)

df["indicator"] = df["second_code"].fillna(df["code"]).drop_duplicates()

df
        date category  code second_code indicator
0 2019-04-19     ID F   NaN         NaN       NaN
1 2019-04-20     ID F   NaN         NaN       NaN
2 2019-05-03     ID F   NaN        8008      8008
3 2019-05-04     ID F  8008         NaN       NaN
4 2019-10-01     ID B   NaN         NaN       NaN
5 2019-10-07     ID B   NaN         NaN       NaN
6 2019-10-11     ID B  9001         NaN      9001
7 2019-11-20     ID B   NaN         NaN       NaN

本质上，我是在告诉熊猫：取“second_code”列，用“code”中的值填充“second_code”中缺少的值（NaN）。然后，从上述操作中删除任何潜在的重复条目，并将此结果分配给“indicator”列

Answer 2

试试这个

import pandas as pd

df = pd.DataFrame({
    'date': ['2019-04-19','2019-04-20','2019-05-03', '2019-05-04',
             '2019-10-01','2019-10-07','2019-10-11', '2019-11-20'],
    'category': ['ID F', 'ID F', 'ID F', 'ID F',
             'ID B', 'ID B', 'ID B', 'ID B'],
    'code': ['None', 'None', 'None', '8008',
             'None', 'None', '9001', 'None'],
    'second_code': ['None', 'None', '8008', 'None',
             'None', 'None', 'None', 'None']})

df['date'] = pd.to_datetime(df['date'], infer_datetime_format=True)

df['indicator'] = df['code']

print(df)

输出

        date category  code second_code indicator
0 2019-04-19     ID F  None        None      None
1 2019-04-20     ID F  None        None      None
2 2019-05-03     ID F  None        8008      None
3 2019-05-04     ID F  8008        None      8008
4 2019-10-01     ID B  None        None      None
5 2019-10-07     ID B  None        None      None
6 2019-10-11     ID B  9001        None      9001
7 2019-11-20     ID B  None        None      None

在这里阅读更多https://pandas.pydata.org/docs/getting_started/intro_tutorials/05_add_columns.html，文档中包含了所有内容