0

I have two CSV files, doctors.csv, and clinics.csv, in clinics.csv there is cols Clinic_ID and Clinic_Name, I then need to add a new column in doctors.csv to add the Clinic_ID where the Clinic_Name from doctors.csv matches Clinic_Name in clinics.csv.

clinics.csv
Clinic_ID   Clinic_Name
1           Clinic 1
2           Clinic 2
3           Clinic 3
4           Clinic 4

doctors.csv
Clinic_ID   Clinic_Name
add ID      Clinic 1
add ID      Clinic 2
add ID      Clinic 3
add ID      Clinic 4

If you could point me in the right direction on where to go from here much appreciated, as I am not sure if this involves mapping df1 to df2 based on the Clinic Names and then populate the ID based on that, or not.

Coplan Consulting
  • 19
  • 8

2 Answers2

2

Try using merge() function.

This is the code that fixed your issue:

clinics = pd.DataFrame({'Clinic_ID': [1, 2, 3, 4], 'Clinic_Name':['Clinic 1','Clinic 2','Clinic 3','Clinic 4'] })    
doctors = pd.DataFrame({'Clinic_Name': ['Clinic 1','Clinic 2','Clinic 3','Clinic 4']})
  

clinics_final=clinics.merge(doctors, on='Clinic_Name')

OUTPUT:

   Clinic_ID Clinic_Name
0          1    Clinic 1
1          2    Clinic 2
2          3    Clinic 3
3          4    Clinic 4

You can read more about merge() function on pandas.DataFrame.merge

Youness Saadna
  • 792
  • 2
  • 8
  • 25
1

Try assign

print(df_doctor.assign(Clinic_ID=lambda x: [df_clinic['Clinic_ID'][key] for key, value in df_clinic['Clinic_Name'].items() if value == df_doctor['Clinic_Name'][key]]))

or you could directly assign, which is faster.

df_doctor['Clinic_ID'] = [df_clinic['Clinic_ID'][key] for key, value in df_clinic['Clinic_Name'].items() if value == df_doctor['Clinic_Name'][key]]
print(df_doctor)

Output:

  Clinic_Name  Clinic_ID
0    Clinic 1          1
1    Clinic 2          2
2    Clinic 3          3
3    Clinic 4          4
Ahmet
  • 7,527
  • 3
  • 23
  • 47