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
Photo Creating column in python based on the other colums value count Bartek635 2 1,284 Apr-15-2021, 03:47 PM
Last Post: Bartek635
  Annotating plot bar from values of other a specific column celinafregoso99 0 853 Mar-10-2021, 03:19 PM
Last Post: celinafregoso99
  How can I iterate through all cells in a column (with merge cells) with openpyxl? aquerci 1 3,171 Feb-11-2021, 09:31 PM
Last Post: nilamo
  merge two dataframes with different number of rows dawid294 5 1,361 Feb-05-2021, 03:21 PM
Last Post: perfringo
  Data extraction from a table based on column and row names tgottsc1 1 1,200 Jan-09-2021, 10:04 PM
Last Post: buran
  Copying column values up based on other column values codelines 1 929 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 1,015 Oct-31-2020, 12:05 AM
Last Post: Larz60+
  merge dataframes imatiasmb 1 683 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 2,462 Oct-16-2020, 04:43 PM
Last Post: klllmmm
  Extracting data based on specific patterns in a text file K11 1 1,080 Aug-28-2020, 09:00 AM
Last Post: Gribouillis

Forum Jump:

User Panel Messages

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