I would like to merge two dataframes which have only one column say df1 and df2 as below. Expected output dataframe as df3.
<code>import pandas as pd
data1 = [
'A',
'B',
'C']
df1 = pd.DataFrame(data1,columns=['name'])
data2 = [
'B',
'C',
'D']
df2 = pd.DataFrame(data2,columns=['name'])
data3 = [
['A',],
['B','B'],
['C','C'],
[None,'D']]
df3 = pd.DataFrame(data3,columns=['name_x','name_y'])
print(df1)
print(df2)
print(df3)
</code>
<code>import pandas as pd
data1 = [
'A',
'B',
'C']
df1 = pd.DataFrame(data1,columns=['name'])
data2 = [
'B',
'C',
'D']
df2 = pd.DataFrame(data2,columns=['name'])
data3 = [
['A',],
['B','B'],
['C','C'],
[None,'D']]
df3 = pd.DataFrame(data3,columns=['name_x','name_y'])
print(df1)
print(df2)
print(df3)
</code>
import pandas as pd
data1 = [
'A',
'B',
'C']
df1 = pd.DataFrame(data1,columns=['name'])
data2 = [
'B',
'C',
'D']
df2 = pd.DataFrame(data2,columns=['name'])
data3 = [
['A',],
['B','B'],
['C','C'],
[None,'D']]
df3 = pd.DataFrame(data3,columns=['name_x','name_y'])
print(df1)
print(df2)
print(df3)
Output:
<code> name
0 A
1 B
2 C
name
0 B
1 C
2 D
name_x name_y
0 A None
1 B B
2 C C
3 None D
</code>
<code> name
0 A
1 B
2 C
name
0 B
1 C
2 D
name_x name_y
0 A None
1 B B
2 C C
3 None D
</code>
name
0 A
1 B
2 C
name
0 B
1 C
2 D
name_x name_y
0 A None
1 B B
2 C C
3 None D
Should I use merge to do it or any other way?
6
Quick hack, you could merge passing one of the keys as Series:
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer').drop(columns='name')
</code>
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer').drop(columns='name')
</code>
df1.merge(df2, left_on='name', right_on=df2['name'], how='outer').drop(columns='name')
Output:
<code> name_x name_y
0 A NaN
1 B B
2 C C
3 NaN D
</code>
<code> name_x name_y
0 A NaN
1 B B
2 C C
3 NaN D
</code>
name_x name_y
0 A NaN
1 B B
2 C C
3 NaN D
If you don’t drop you’ll also get a column with the merged values:
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x name_y
0 A A NaN
1 B B B
2 C C C
3 D NaN D
</code>
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x name_y
0 A A NaN
1 B B B
2 C C C
3 D NaN D
</code>
df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x name_y
0 A A NaN
1 B B B
2 C C C
3 D NaN D
If you had extra columns in the input, this would give you:
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x col1 name_y col2
0 A A x NaN NaN
1 B B x B x
2 C C x C x
3 D NaN NaN D x
</code>
<code>df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x col1 name_y col2
0 A A x NaN NaN
1 B B x B x
2 C C x C x
3 D NaN NaN D x
</code>
df1.merge(df2, left_on='name', right_on=df2['name'], how='outer')
name name_x col1 name_y col2
0 A A x NaN NaN
1 B B x B x
2 C C x C x
3 D NaN NaN D x
generic method
If you have more than two inputs to combine, you can generalize with concat:
<code>dfs = [df1, df2, df1, df2]
out = pd.concat(
[
d.set_axis(d['name']).add_suffix(f'_{i}')
for i, d in enumerate(dfs, start=1)
],
axis=1,
).reset_index(drop=True)
</code>
<code>dfs = [df1, df2, df1, df2]
out = pd.concat(
[
d.set_axis(d['name']).add_suffix(f'_{i}')
for i, d in enumerate(dfs, start=1)
],
axis=1,
).reset_index(drop=True)
</code>
dfs = [df1, df2, df1, df2]
out = pd.concat(
[
d.set_axis(d['name']).add_suffix(f'_{i}')
for i, d in enumerate(dfs, start=1)
],
axis=1,
).reset_index(drop=True)
Output:
<code> name_1 name_2 name_3 name_4
0 A NaN A NaN
1 B B B B
2 C C C C
3 NaN D NaN D
</code>
<code> name_1 name_2 name_3 name_4
0 A NaN A NaN
1 B B B B
2 C C C C
3 NaN D NaN D
</code>
name_1 name_2 name_3 name_4
0 A NaN A NaN
1 B B B B
2 C C C C
3 NaN D NaN D
<code>df1.index = df1.name
df2.index = df2.name
df1.join(df2, how='outer', lsuffix='_x', rsuffix='_y')
</code>
<code>df1.index = df1.name
df2.index = df2.name
df1.join(df2, how='outer', lsuffix='_x', rsuffix='_y')
</code>
df1.index = df1.name
df2.index = df2.name
df1.join(df2, how='outer', lsuffix='_x', rsuffix='_y')
You can add .reset_index(drop=True) if you don’t want to have names in the index
Also, be careful with duplicates in df1 and df2, i.e. if you have n B’s if df1 and m B’s in df2, you will have n * m B’s in df3.