How to rename columns while reading multiple files using pandas

I have two data frames (to excel files) with the below columns

File 1- columns

person_ID   Test_CODE   REGISTRATION_DATE   subject_CD   subject_DESCRIPTION    subject_TYPE

File 2- columns

person_ID   Test_CODE   REGISTRATION_DATE   subject_Code subject_DESCRIPTION    subject_Indicator

However, the columns subject_CD and subject_Code mean the same. Similarly, subject_TYPE and subject_Indicator mean the same. So, I would like to rename them when I read the excel file

I tried the below but it doesn't work

dfs = []       
for f in files:
    df = pd.read_excel(f, sep=",",low_memory=False)
    df1 = df[df.columns.intersection(['person_ID','Test_CODE','REGISTRATION_DATE','subject_CD','subject_DESCRIPTION','subject_TYPE'])].rename(columns={'subject_TYPE':'subject_Indicator','subject_CD':'subject_Code'})

Since, I would like to append/merge both the files, I expect the column names in my final data frame to be like as shown below

person_ID   Test_CODE   REGISTRATION_DATE   subject_Code subject_DESCRIPTION subject_Indicator

Can help me with this?

Submitted March 28th 2021 by Admin


Rename 2 columns from particular df:

 df.rename({"subject_CD": "subject_Code", "subject_TYPE": "subject_Indicator"}, axis='columns', inplace =True) 

Also You can connect df1 and df2 - on the same columns:

enter image description here

frames = [df1, df2]
result = pd.concat(frames)

Admin | 6 months ago

Relevant Questions