Skip to main content
 首页 » 编程设计

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

2024年11月24日8落叶无声

对于下面的输入数据,我需要填充缺失的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' 

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

请您参考如下方法:

比较丢失的字符串而不是丢失的值,通过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.