Pandas Python如何使用一个数据帧中的公共数据写入不同的数据帧

Question

我正在尝试使用df4的LineNum列，通过匹配LineNumbers并写入df1中相应的GeneralDescription列单元格来识别df1中的GeneralDescription。我正在寻找一种可扩展的解决方案，以处理具有数千行和其他几个无关紧要列的数据帧。如果不是绝对必要的话，我宁愿不合并。我只想写入df1的TrueDepartment列，并保持2个数据帧的原始结构不变。谢谢——

df1
LineNum Warehouse           GeneralDescription
0   2       Empty               Empty
1   3       Empty               Empty
2   4       PBS                 Empty
3   5       Empty               Empty
4   6       Empty               Empty
5   7       General Liability   Empty
6   8       Empty               Empty
7   9       Empty               Empty    
df4
LineNum GeneralDescription
0   4       TRUCKING
1   6       TRUCKING-GREENVILLE,TN
2   7       Human Resources 
Desired result
LineNum Warehouse           GeneralDescription
0   2       Empty               Empty
1   3       Empty               Empty
2   4       PBS                 TRUCKING
3   5       Empty               Empty
4   6       Empty               TRUCKING-GREENVILLE,TN
5   7       General Liability   Human Resources
6   8       Empty               Empty
7   9       Empty               Empty      

这是我迄今为止使用的可能有帮助的软件包的代码。事实上，我得到了一个错误，上面写着KeyError："标签[LineNum]不在[index]'中">

import pandas as pd
import openpyxl
import numpy as np
data= [[2,'Empty','Empty'],[3,'Empty','Empty'],[4,'PBS','Empty'],[5,'Empty','Empty'],[6,'Empty','Empty'],[7,'General Liability','Empty'],[8,'Empty','Empty'],[9,'Empty','Empty']]
df1=pd.DataFrame(data,columns=['LineNum','Warehouse','GeneralDescription'])
data4 = [[4,'TRUCKING'],[6,'TRUCKING-GREENVILLE,TN'],[7,'Human Resources']]
df4=pd.DataFrame(data4,columns=['LineNum','GeneralDescription'])

for i in range(len(df1.index)):
if df1.loc[i,'LineNum']==df4.loc['LineNum']:
df1.loc[i,'GeneralDescription']=df4.loc['GeneralDescription']

Answer 1

使用map和Series，由df4和fillna按原始列值创建：

s = df4.set_index('LineNum')['TrueDepartment']
df1['TrueDepartment'] = df1['LineNum'].map(s).fillna(df1['TrueDepartment'])
print (df1)
LineNum         Department          TrueDepartment
0        2              Empty                   Empty
1        3              Empty                   Empty
2        4                GBS                TRUCKING
3        5              Empty                   Empty
4        6              Empty  TRUCKING-GREENVILLE,TN
5        7  General Liability         Human Resources
6        8              Empty                   Empty
7        9              Empty                   Empty

---

DataFrame.merge:溶液

df = df1.merge(df4,how='left', on='LineNum', suffixes=('','_'))
df['TrueDepartment'] = df['TrueDepartment_'].combine_first(df['TrueDepartment'])
df = df.drop('TrueDepartment_', axis=1)
print (df)
LineNum         Department          TrueDepartment
0        2              Empty                   Empty
1        3              Empty                   Empty
2        4                GBS                TRUCKING
3        5              Empty                   Empty
4        6              Empty  TRUCKING-GREENVILLE,TN
5        7  General Liability         Human Resources
6        8              Empty                   Empty
7        9              Empty                   Empty