根据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：value of floor + F + 001, 002, 003, etc.；并创建一列office_num_status，当不为空时插入original，否则<代码>d13。

这是最终的预期结果：

           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，但是所有的值都是originals：

# 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.