Pandas merge create duplicate rows

Question

I want to merge two similar dataframes row by row

My code:

d5=pd.DataFrame([["Id1",5313.0],["Id1",414542.0]])
d5.columns=["Id","Random"]
d6=pd.DataFrame([["Id1",15616.0],["Id1",168449.0]])
d6.columns=["Id","Random"]

What I get is:

Id1 5313.0 15616.0
Id1 5313.0 168449.0
Id1 414542.0 15616.0
Id1 414542.0 168449.0

What I want is:

Id1 5313.0 15616.0
Id1 414542.0 168449.0

I don't understand why he's trying to create two separate rows..

Also If I want one more column in my first dataframe I want to indicate a Nan value in the second like this:

Id1 5313.0 15616.0
Id1 414542.0 168449.0
Id1 113.0 NaN

Is it because I have the same ID ?

Answer 1

df = pd.concat([d5,d6] , join='outer',axis=1).reset_index(drop=True) #this will join the dataframe right side

df.T.drop_duplicates().T # this will drop the duplicate column

Answer 2

Let's consider data with not only one Id, e. g.

>>> d5
    Id    Random
0  Id1    5313.0
1  Id1  414542.0
2  Id1     113.0
>>> d6
    Id    Random
0  Id1   15616.0
1  Id1  168449.0
2  Id2       1.0

With such data, loving_guy's approach pairs different Id values, giving:

    Id  Random   Id  Random
0  Id1    5313  Id1   15616
1  Id1  414542  Id1  168449
2  Id1     113  Id2       1

If this is not desired, another approach can be used:

import pandas as pd
d5 = pd.DataFrame([["Id1",5313.0], ["Id1",414542.0],["Id1",113.0]], columns=["Id","Random"])
d6 = pd.DataFrame([["Id1",15616.0],["Id1",168449.0],["Id2",1]],     columns=["Id","Random"])
d56 = pd.DataFrame()
for id in {*d5.groupby('Id').groups, *d6.groupby('Id').groups}:
    d56 = d56.append(pd.DataFrame({'Id':id, 'Random5':d5[d5['Id']==id]['Random'],
                                            'Random6':d6[d6['Id']==id]['Random']}))
print(d56)

That can give:

    Id   Random5   Random6
0  Id1    5313.0   15616.0
1  Id1  414542.0  168449.0
2  Id1     113.0       NaN
2  Id2       NaN       1.0