CODEWITHSUNDEEP
pythonpandasdataframepandas-groupby
CodingNinja
CodingNinja

Reputation: 109

Merge Different Dataframe which is already grouped on column

First dataframe:

Subject     Branch     Test1        Test2       Test3
DS           IT          45          43           44
             CE          40          45           39
JAVA         MCA         43          34           39
             IT          38          43           44
             CE          34          43           37
Python       MCA         35          49           43

Second dataframe:

Subject     Final_Marks    Faculty
DS            45           Dr. Kumar
              40           Dr. Mamtha
JAVA          43           Prof. Sagar
              38           Dr. Chethana
              34           Prof. Smitha
              43           Dr. Romin
Python        35           Dr. Rakesh
              46           Prof.Chethan

I want to merge this two dataframe on "subject".

Expected output:

Subject     Branch     Test1        Test2       Test3    Final_Marks      Faculty
DS           IT          45          43           44        45             Dr.Kumar
             CE          40          45           39        40             Dr.Mamtha
JAVA         MCA         43          34           39        43             Prof.Sagar
             IT          38          43           44        38             Dr.Chethana
             CE          34          43           37        34             Prof.Smitha
                                                            43             Dr.Romin
Python       MCA         35          49           43        35             Dr.Rakesh
                                                            46             Prof.Chethan

I have tried:

f = pd.merge(df1,df2,on='Subject', how='left')

But second dataframe is repeating for each row of 1st dataframe. It is not giving expected output.

Upvotes: 1

Views: 56

Answers (1)

jezrael
jezrael

Reputation: 862511

If you are sure first, second, third... rows by Subject are same for both DataFrames is possible use GroupBy.cumcount for counter and use it for merging:

df1['g'] = df1.groupby('Subject').cumcount()
df2['g'] = df2.groupby('Subject').cumcount() 

f = pd.merge(df1,df2,on=['Subject', 'g'], how='left')

If need all values need outer join:

f = pd.merge(df1,df2,on=['Subject', 'g'], how='outer')

Upvotes: 2

Related Questions