New data frame composed of unique values of different dataframes

Admin 1
Viewed 53

I have two data frames:

df1 = 
a   b
1   555
2   555
4   555
4   555
5   555
6   555
7   555
8   555
15  555
1   555

And

df2 = 
a   b
2   666
2   666
3   666
4   666
5   666
2   666
3   666
9   666

I want to find same values of column a between two data frames and put them into new data frame (also with values of other columns. Here is b) Desired output is:

df3 = 
    a   b
    2   555
    2   666
    2   666
    4   555
    4   555
    4   666
    …

And so on.

I have tried:

df3= pd.merge(df1, df2, on=['a'], how='inner')

but it gives me something different

pandas numpy python
3 Answers

Use:

df3= pd.concat([df1, df2], ignore_index=True)
df3 = df3[df3['a'].isin(np.intersect1d(df1['a'], df2['a']))]

Or:

idx = np.intersect1d(df1['a'], df2['a'])
df3 = pd.concat([df1[df1.a.isin(idx)], df2[df2.a.isin(idx)]], ignore_index=True)

print (df3)
   a    b
1  2  555
2  4  555
3  4  555
4  5  555
0  2  666
1  2  666
3  4  666
4  5  666
5  2  666
user_29010020

Roughly, this will solve your issue:

(pd.concat((df1[df1.a.isin(df2.a)],
            df2[df2.a.isin(df1.a)]),
           ignore_index=True)
    .sort_values('a'))

#   a    b
#0  2  555
#4  2  666
#5  2  666
#8  2  666
#1  4  555
#2  4  555
#6  4  666
#3  5  555
#7  5  666
user_58110780

This is not much different from others solution

set1 = set(df1["a"].tolist())
set2 = set(df2["a"].tolist())
inter = list(set1.intersection(set2))

df3 = pd.concat([df1[df1["a"].isin(inter)],
                 df2[df2["a"].isin(inter)]],
                  ignore_index=True)
user_48193760
Related