use meshgrid for rows with common values in column

user_8560167 0
Viewed 164

my dataframes:

df1 = pd.DataFrame(np.array([[1, 2, 3], [4, 2, 3], [7, 8, 8]]),columns=['a', 'b', 'c'])
df2 = pd.DataFrame(np.array([[1, 2, 3], [4, 2, 3], [5, 8, 8]]),columns=['a', 'b', 'c'])

df1,df2:
   a  b  c
0  1  2  3
1  4  2  3
2  7  8  8

   a  b  c
0  1  2  3
1  4  2  3
2  5  8  8

I want to combine rows from columns a from both df's in all sequences but only where values in column b and c are equal. Right now I have only solution for all in general with this code:

x = np.array(np.meshgrid(df1.a.values, 
  df2.a.values)).T.reshape(-1,2)
df = pd.DataFrame(x)
    print(df)
   0  1
0  1  1
1  1  4
2  1  5
3  4  1
4  4  4
5  4  5
6  7  1
7  7  4
8  7  5

expected output for df1.a and df2.a only for rows where df1.b==df2.b and df1.c==df2.c:

   0  1
0  1  1
1  1  4
2  4  1
3  4  4
4  7  5

so basically i need to group by common rows in selected columns band c

pandas python
1 Answers

You should try DataFrame.merge using inner merge:

df1.merge(df2, on=['b', 'c'])[['a_x', 'a_y']]

   a_x a_y
0   1   1
1   1   4
2   4   1
3   4   4
4   7   5
user_87273390
Related