Combine Dataframes With Different Indexes in Pandas

Question

I have two dataframes:

df1 : here index is ip

            accountname      name
ip
192.168.1.1        aaaa  john doe
192.168.1.2        bbbb  jane doe

df2 : index is accountname

             gsm
accountname
aaaa         850
bbbb         860
cccc         870

I have to combine two dataframe and add gsm column to df1.

            ip accountname      name  gsm
0  192.168.1.1        aaaa  john doe  850
1  192.168.1.2        bbbb  jane doe  860

These dataframes has different indexes and I couldnt reach right data. any advice would be appreciated.

score 8 · Accepted Answer · answered Oct 15 '17 at 13:07

You could use merge with index as well.

In [2313]: df1.merge(df2, left_on='accountname', right_index=True).reset_index()
Out[2313]:
            ip accountname      name  gsm
0  192.168.1.1        aaaa  john doe  850
1  192.168.1.2        bbbb  jane doe  860

jezrael · Answer 2 · 2017-10-15T13:38:07.670

3

Use join:

df = df1.join(df2, on='accountname', how='inner').reset_index()
print (df)
            ip accountname      name  gsm
0  192.168.1.1        aaaa  john doe  850
1  192.168.1.2        bbbb  jane doe  860

edited Oct 15 '17 at 13:38

answered Oct 15 '17 at 12:45

jezrael

822,522
95
1,334
1,252

ValueError: columns overlap but no suffix specified: Index([u'accountname'], dtype='object') – jojo Oct 15 '17 at 12:51
First or second solution? – jezrael Oct 15 '17 at 12:52
first solution @jezrael – jojo Oct 15 '17 at 12:58
2

I think `df = pd.merge(df1.reset_index(), df2.reset_index(), on='accountname')` should help. – jezrael Oct 15 '17 at 12:59
Interesting, second solution does not work too? – jezrael Oct 15 '17 at 13:38
@jezrael comment refers to the best solution. Can you post it as an answer? Please, do tag me so that I may know and upvote your answer. The comment is: df = pd.merge(df1.reset_index(), df2.reset_index(), on='accountname') – Asif Mehmood Sep 01 '21 at 22:50

Combine Dataframes With Different Indexes in Pandas

2 Answers2