Inner join a table on 2 primary key columns

Question

I have two table with 2 primary keys on id and group_id

id      group_id      name
01       333           Ken
05       123           Tom
03       231           Mary
05       94            Brandy
.... more rows

id      group_id      name
05       123           Tom
03       231           Mary
05       94            Brandy
07       384           Jane
.... more rows

expected multilevel dataframe

        df1    df2          df1    df2 
         group_id             name
id      
05       123   123         Tom     Tom
03       231   231        Mary     Mary
05       94    94        Brandy    Brandy

I want to merge 2 tables with inner join on the primary key columns (id and group_id), but so far I can only do it for one column and it's complaining Shape of passed values is (5, 3012), indices imply (5, 2915) because I have duplicate ids.

pd.concat([df1.set_index('id'), df2.set_index('id')], axis='columns', keys=['df1', 'df2'],join='inner')

did you try `merge`? `pd.merge(df1,df2,how='inner',on=['id','group_id'])` — David Erickson, Oct 15 '20 at 22:16
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — David Erickson, Oct 15 '20 at 22:28
`pd.concat([d.set_index(['id','group_id']) for d in (df1,df2)], axis=1, keys=['df1', 'df2'])`? — Quang Hoang, Oct 15 '20 at 22:58

score 0 · Answer 1 · answered Oct 15 '20 at 23:38

0

try;

newdf = pd.merge(df1, df2, how='inner', left_on=['id,'group_id'], right_on=['id','group_id])

answered Oct 15 '20 at 23:38

Taylrl

3,601
6
33
44

Inner join a table on 2 primary key columns

1 Answers1