I have two data frames (first_df and second_df) and I want to merge them in the way that the new data frame put the first_df´s ID in one column and the second_df´s ID in the second column putting equal IDs in the same row and for no-common ID putting in their respective column next to a Na or None value.
first = pd.DataFrame({"id": ["K0", "K1" ], "v": [1, 2]})
id v
0 K0 1
1 K1 2
second = pd.DataFrame({"id": ["K0", "K3", "K2"], "v": [4, 6,3]})
id v
0 K0 4
1 K3 6
2 K2 3
Desirable output
id_1 id_2
0 K0 K0
1 K1 Na
2 Na K2
CodePudding user response:
IIUC use outer join with create new id
columns:
df1 = first.assign(id_1 = first.id)[['id', 'id_1']]
df2 = second.assign(id_2 = second.id)[['id', 'id_2']]
df = df1.merge(df2, on='id', how='outer').drop('id', axis=1)
Or:
df1 = first.set_index('id', drop=False)[['id']].add_suffix('_1')
df2 = second.set_index('id', drop=False)[['id']].add_suffix('_2')
df = pd.concat([df1, df2], axis=1).reset_index(drop=True)
print (df)
id_1 id_2
0 K0 K0
1 K1 NaN
2 NaN K3
3 NaN K2
CodePudding user response:
import pandas as pd
data1 = {'Name':['Jai', 'Princi', 'Gaurav', 'Anuj'],
'Age':[27, 24, 22, 32],
'Address':['Nagpur', 'Kanpur', 'Allahabad', 'Kannuaj'],
'Qualification':['Msc', 'MA', 'MCA', 'Phd']}
data2 = {'Name':['Abhi', 'Ayushi', 'Dhiraj', 'Hitesh'],
'Age':[17, 14, 12, 52],
'Address':['Nagpur', 'Kanpur', 'Allahabad', 'Kannuaj'],
'Qualification':['Btech', 'B.A', 'Bcom', 'B.hons']}
df = pd.DataFrame(data1,index=[0, 1, 2, 3])
df1 = pd.DataFrame(data2, index=[4, 5, 6, 7])
print(df, "\n\n", df1)