Python Forum
How to merge three DataFrames based on specific column
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
How to merge three DataFrames based on specific column
#1
Hi,
I have three Data Frames with a common column (ID) and I want to merge them into one.


df1:

ID	Pop	RANK
A1	T1	1
A1	T1	3
B1	T1	4
B2	T1	2
K1	T1	9
K1	T1	1
K2	T1	5
df2:

ID	Pop	TEMP RANK
A1	T1	20	 1
A1	T1	56	 3
B1	T1	72	 4
B2	T1	45	 2
K1	T1	0	 9
K1	T1	20	 1
K2	T1	43	 5
KN	T2	56	 1
KP	T2	23	 5
NH6	T3	56	 23
KL5	T4	19	 16
df3:

ID	Status	RANK
A1	NOR	    1
A1	ABN	    3
B1	HOT	    4
B2	COOL	2
K1	HOT	    9
K1	ER	    1
K2	RAMP	5
KN	BAK	    1
KP	HOT	    5
NH6	COOL	23
KL5	RAMP	16
I want to merge based on ID:

result:
ID	Pop	RANK	TEMP   Status
A1	T1	1	    20	   NOR
A1	T1	3	    56	   ABN
B1	T1	4	    72	   HOT
B2	T1	2	    45	   COOL
K1	T1	9	    0	   HOT
K1	T1	1	    20	   ER
K2	T1	5	    43	   RAMP
when merge, merge TEMP column from df2, and Status column from df3.

I use below code, but it got error:

df1.set_index('ID',inplace=True)
df2.set_index('ID',inplace=True)
df3.set_index('ID',inplace=True)
df = pd.concat([df1,df2,df3],axis=1).reset_index()
df.rename(columns = {'index':'ID'})
ValueError: Shape of passed values is (7, 11), indices imply (7, 9)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Data extraction from a table based on column and row names tgottsc1 1 95 Jan-09-2021, 10:04 PM
Last Post: buran
  Copying column values up based on other column values codelines 1 107 Jan-03-2021, 05:55 PM
Last Post: codelines
  How to generate rows based on values in a column to fill missing values codesmatter 1 252 Oct-31-2020, 12:05 AM
Last Post: Larz60+
  merge dataframes imatiasmb 1 218 Oct-17-2020, 06:44 PM
Last Post: klllmmm
  Pandas, Assign a value in a row, to another column based on a condition klllmmm 6 633 Oct-16-2020, 04:43 PM
Last Post: klllmmm
  Extracting data based on specific patterns in a text file K11 1 403 Aug-28-2020, 09:00 AM
Last Post: Gribouillis
  How to sort rows based on specific order Mekala 3 315 Jul-31-2020, 01:01 AM
Last Post: bowlofred
  Assigning Column nunique values to another DataFrame column Pythonito 1 406 Jun-26-2020, 06:52 AM
Last Post: hussainmujtaba
  Process Data from one csv file and write to another CSV file specific column ajin9581 1 431 Jun-17-2020, 06:00 PM
Last Post: buran
  Merge 2 dataframes but avoid double summing of rows? vinaysalian17 0 353 Jun-03-2020, 01:48 AM
Last Post: vinaysalian17

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020