Split compare dataframe based on a column

user_12947035 0
Viewed 62

I have a concatenated dataframe with the columns as b,c,d and index values as 0,1,2,0,1,2. Index values are repeating because its concatenated.

         b     c    d
   0     x     bh   sj
   1     y     ss   as
   2     z     ws   ad
   0     x     sf   ak
   1     y     ks   kq
   2     z     sl   la

I want it to be like this. I'm trying to compare the values and its a big dataset. I've tried it a lot but was unable to split the dataframe in this manner. Can someone help me out?

  
         b     c    d      e     f  
   0     x     bh   sj     sf    ak     
   1     y     ss   as     ks    kq
   2     z     ws   ad     sl    la

Here's the code to create the dataframe

cols1 = ['b', 'c', 'd']
df1 = pd.DataFrame(data=[['x','bh','sj'],['y','ss','as'],['z','ws','ad'],['x','sf','ak'],['y','ks','kq'],['z','sl','la']], columns=cols1,  index=['0','1','2','0','1','2'])
df1.head()
pandas dataframe python
1 Answers

You can do this:

In [1414]: g = df1.groupby(df1.index).cumcount().add(1)
In [1419]: df = df1.set_index(['b', g]).unstack(fill_value=0).sort_index(axis=1, level=1)

In [1422]: df.columns = ["{}{}".format(a, b) for a, b in df.columns]
In [1424]: df = df.reset_index()

In [1425]: df
Out[1425]: 
   b  c1  d1  c2  d2
0  x  bh  sj  sf  ak
1  y  ss  as  ks  kq
2  z  ws  ad  sl  la
user_58208140
Related