python - 添加列并根据 Pandas 中的其他列填充缺失值

Question

对于下面的输入数据，我需要填充缺失的office_number，并创建一列来区分office_number是原始的还是后来填充的。

这是示例数据:

df = pd.DataFrame({'id':['1010084420','1010084420','1010084420','1010084421','1010084421','1010084421','1010084425'],
                   'building_name': ['A', 'A', 'A', 'East Tower', 'East Tower', 'West Tower', 'T1'],
                   'floor': ['1', '1', '2', '10', '10', '11','11'],
                   'office_number':['', '','205','','','', '1101-1105'],
                   'company_name': ['Ariel Resources Ltd.', 'A.O. Tatneft', '', 'Agrium Inc.', 'Creo Products Inc.', 'Cott Corp.', 'Creo Products Inc.']})
print(df)

输出:

           id building_name floor office_number          company_name
0  1010084420             A     1                Ariel Resources Ltd.
1  1010084420             A     1                        A.O. Tatneft
2  1010084420             A     2           205                      
3  1010084421    East Tower    10                         Agrium Inc.
4  1010084421    East Tower    10                  Creo Products Inc.
5  1010084421    West Tower    11                          Cott Corp.
6  1010084425            T1    11     1101-1105    Creo Products Inc.

对于具有相同id、building_name 和floor 的办公室，当它为空时，我需要填写office_number , 具有以下规则:楼层值 + F + 001, 002, 003, etc.;并创建一列office_num_status，当它不为空时，插入original，否则filled。

这是最终的预期结果:

           id building_name floor office_num_status office_number  \
0  1010084420             A     1            filled         1F001   
1  1010084420             A     1            filled         1F002   
2  1010084420             A     2          original           205   
3  1010084421    East Tower    10            filled        10F001   
4  1010084421    East Tower    10            filled        10F002   
5  1010084421    West Tower    11            filled        11F001   
6  1010084425            T1    11          original     1101-1105   

           company_name  
0  Ariel Resources Ltd.  
1          A.O. Tatneft  
2                        
3           Agrium Inc.  
4    Creo Products Inc.  
5            Cott Corp.  
6    Creo Products Inc. 

我到目前为止所做的是创建列 office_num_status 但所有值都是 original:

# method 1
df['office_num_status'] = np.where(df['office_number'].isnull(), 'filled', 'original')

# method 2
df['office_num_status'] = ['filled' if x is None else 'original' for x in df['office_number']]

# method 3
df['office_num_status'] = 'filled'
df.loc[df['office_number'] is not None, 'office_num_status'] = 'original'

有人可以帮我完成这个吗？非常感谢。

Answer 1

比较丢失的字符串而不是丢失的值，通过GroupBy.cumcount添加计数器并填充不存在的值:

mask = df['office_number'] == ''
df.insert(3, 'office_num_status', np.where(mask, 'filled', 'original'))
s = df.groupby(['id','building_name','floor']).cumcount().add(1).astype(str).str.zfill(3)
df.loc[mask, 'office_number'] = df['floor'].astype(str) + 'F' + s
print (df)
           id building_name floor office_num_status office_number  \
0  1010084420             A     1            filled         1F001   
1  1010084420             A     1            filled         1F002   
2  1010084420             A     2          original           205   
3  1010084421    East Tower    10            filled        10F001   
4  1010084421    East Tower    10            filled        10F002   
5  1010084421    West Tower    11            filled        11F001   
6  1010084425            T1    11          original     1101-1105   

           company_name  
0  Ariel Resources Ltd.  
1          A.O. Tatneft  
2                        
3           Agrium Inc.  
4    Creo Products Inc.  
5            Cott Corp.  
6    Creo Products Inc.