CODEWITHSUNDEEP
pythonpandasdataframedata-science
Jalal Hasain
Jalal Hasain

Reputation: 41

Combining certain columns from three excel files in one pandas DF based on the column number-Using python pandas

I have three excel files that was read to pandas DFs, the tables are below : DF1:

Column A Column B
F G
M K

DF2:

Column A Column B
J D
N L

DF3:

Column A Column B Column C
J E Y
N Q V
J D B
E B F

What I want to do is creating a fourth DF containing joined certain columns from the dataframes (Based on the column index) from the dataframes. for DF4, what I want it to be : 1)(Column A from DF1) with (Column C from Df3) with (Column A from DF2)
2)(Column B from DF1) with (Column A from Df3) with (Column B from DF2)

The expected result of DF4 :

Char 1 CHAR2
F G
M K
Y J
V N
B J
F E
J D
N L

what I wrote for now : `

import pandas as pd
NUR = pd.read_excel(r'C:\Users\jalal.hasain\Desktop\Copy of NUR Data 20-12.xlsx', 
                   sheet_name=['2G','3G','4G'])

DF1=NUR.get('2G')
DF2=NUR.get('3G')
DF3=NUR.get('4G')

`

Thanks in advance.

Upvotes: 1

Views: 55

Answers (1)

jezrael
jezrael

Reputation: 862781

Use concat with get columns in expected order in list for DF3 with set columns names by DataFrame.set_axis:

df = pd.concat([DF1, 
                DF3[['Column C','Column A']].set_axis(['Column A','Column B'], axis=1), 
                DF2], ignore_index=True)
print (df)
  Column A Column B
0        F        G
1        M        K
2        Y        J
3        V        N
4        B        J
5        F        E
6        J        D
7        N        L

Upvotes: 2

Related Questions