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
  Unexpected Output - Python Dataframes: Filtering based on Overlapping Dates Xensor 5 654 Nov-15-2023, 06:54 PM
Last Post: deanhystad
  Color a table cell based on specific text Creepy 11 1,822 Jul-27-2023, 02:48 PM
Last Post: deanhystad
  create new column based on condition arvin 12 2,130 Dec-13-2022, 04:53 PM
Last Post: jefsummers
  How to assign a value to pandas dataframe column rows based on a condition klllmmm 0 797 Sep-08-2022, 06:32 AM
Last Post: klllmmm
  Creating a numpy array from specific values of a spreadsheet column JulianZ 0 1,074 Apr-19-2022, 07:36 AM
Last Post: JulianZ
  Openpyxl-change value of cells in column based on value that currently occupies cells phillipaj1391 5 9,568 Mar-30-2022, 11:05 PM
Last Post: Pedroski55
  Extracting Specific Lines from text file based on content. jokerfmj 8 2,856 Mar-28-2022, 03:38 PM
Last Post: snippsat
Photo Creating column in python based on the other colums value count Bartek635 2 2,889 Apr-15-2021, 03:47 PM
Last Post: Bartek635
  Annotating plot bar from values of other a specific column celinafregoso99 0 1,923 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 7,445 Feb-11-2021, 09:31 PM
Last Post: nilamo

Forum Jump:

User Panel Messages

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