Python如何从不同的文件在CSV文件中添加列？

Question

我有4个CSV文件。 我可以合并所有4个，但有一个问题

这是一个名为services.csv的文件

ServiceID   Service
1   General Practitioner
2   Pathology
3   Radiology
4   Psychiatry
5   Chiropratic

另一个文件（“我把它命名为test.csv”）

ClinicServiceID ClinicID    ServiceID   Name    Suburb  State   Postcode    Email   Lat Lon
1   1   1   Hurstville Clinic   Hurstville  NSW 1493    hurstville@myclinic.com.au  -33.975869  151.088939
2   1   2   Hurstville Clinic   Hurstville  NSW 1493    hurstville@myclinic.com.au  -33.975869  151.088939
3   2   1   Sydney Centre Clinic    Sydney  NSW 2000    sydney@myclinic.com.au  -33.867139  151.207114
4   2   2   Sydney Centre Clinic    Sydney  NSW 2000    sydney@myclinic.com.au  -33.867139  151.207114
5   2   3   Sydney Centre Clinic    Sydney  NSW 2000    sydney@myclinic.com.au  -33.867139  151.207114

现在我必须根据测试文件中的ServiceID从Service.csv文件中添加Service列

我可以合并所有的文件，但我不知道如何执行上述操作

Things that I Have to achieve:
1) Add Service column into the test.csv file
2) make the entries in the Service Column using the Service.csv file data.

谁能帮帮我吗。我不知道如何解决这个问题

Answer 1

我认为需要^{}来表示DataFrames和^{}以及^{}来表示ServiceID之后的新列：

df1 = pd.read_csv('services.csv') 
df2 = pd.read_csv('test.csv')

#get position of ServiceID column
pos = df2.columns.get_loc('ServiceID') + 1

df2.insert(pos, 'Service', df2['ServiceID'].map(df1.set_index('ServiceID')['Service']))
print (df2)
   ClinicServiceID  ClinicID  ServiceID               Service  \
0                1         1          1  General Practitioner   
1                2         1          2             Pathology   
2                3         2          1  General Practitioner   
3                4         2          2             Pathology   
4                5         2          3             Radiology   

                   Name      Suburb State  Postcode  \
0     Hurstville Clinic  Hurstville   NSW      1493   
1     Hurstville Clinic  Hurstville   NSW      1493   
2  Sydney Centre Clinic      Sydney   NSW      2000   
3  Sydney Centre Clinic      Sydney   NSW      2000   
4  Sydney Centre Clinic      Sydney   NSW      2000   

                        Email        Lat         Lon  
0  hurstville@myclinic.com.au -33.975869  151.088939  
1  hurstville@myclinic.com.au -33.975869  151.088939  
2      sydney@myclinic.com.au -33.867139  151.207114  
3      sydney@myclinic.com.au -33.867139  151.207114  
4      sydney@myclinic.com.au -33.867139  151.207114