CODEWITHSUNDEEP
pythonpandas
Anjith
Anjith

Reputation: 2308

How can I combine series with dataframe

I have two series and one dataframe and I want to merge all of them together.

cola = pd.Series(['test','test1','test2','test3'], index=[0,0,1,1])

colb = pd.Series(['k','k1','k2','k3'], index=[0,1,1,2])

df = pd.DataFrame({'col1': ['z','z1','z2','z3']}, index=[0,0,0,1])

I've tried both merge and concat and it always threw an error. The index is the problem here. I just want to merge them and if one column doesn't have same index as another it should fill it with NaN. Is it possible?

This is my desired output:

     col1  colb  cola
0     z     k    test
0    z1   NaN   test1
0    z2   NaN     NaN 
1    z3    k1   test2
1   NaN    k2   test3
2   NaN    k3     NaN

Any Help? Thanks

Upvotes: 0

Views: 46

Answers (1)

BENY
BENY

Reputation: 323226

Need using cumcount create a helpkey for join 

df.set_index(df.groupby(df.index).cumcount(),append=True,inplace=True)
cola=cola.to_frame('cola').set_index(cola.groupby(cola.index).cumcount(),append=True)
colb=colb.to_frame('colb').set_index(colb.groupby(colb.index).cumcount(),append=True)
yourdf=df.join(cola,how='outer').join(colb,how='outer')
yourdf
Out[161]: 
    col1   cola colb
0 0    z   test    k
  1   z1  test1  NaN
  2   z2    NaN  NaN
1 0   z3  test2   k1
  1  NaN  test3   k2
2 0  NaN    NaN   k3

Upvotes: 2

Related Questions