CODEWITHSUNDEEP
pythonpandasdataframe
user96564
user96564

Reputation: 1607

Pandas, Converting multiple rows into multiple columns

I have a sample data

Index, Column1,Value1
1,  1,6
1,  2,7
1,  3,8
Index,  Column1,Value1
2,  11,16
2,  12,17
2,  13,18
2,  14,19
2,  15,20
Index,  Column1,Value1
3,  21,24
3,  22,25

i want to reshape in this way

Index Column1, Value1, Column2, Value2, Column3, Value3, Column4, Value4
1   1,  6,  2,  7,  3,  8
2   11, 16, 12, 17, 13, 18, 14, 19
3   21, 24, 22, 25

I am using this

print(df.groupby('Index')[['Column1', 'Value1']].apply(','.join).reset_index())

but i got weird output. What I am doing wrong here ? Any suggestions ? 

Index               0
    1  Column1,Value1
    2  Column1,Value1
    3  Column1,Value1
Index  Column1,Value1

Upvotes: 1

Views: 1837

Answers (1)

jezrael
jezrael

Reputation: 863556

EDIT:

Solution for 3 columns input DataFrame:

df = df[df['Column1'] != 'Column1']
s =  df.groupby('Index').cumcount().add(1)
df = (df.set_index(['Index',s])
        .unstack()
        .sort_index(axis=1, level=1)
       )
df.columns = ['{}_{}'.format(a, b) for a,b in df.columns]

df = df.reset_index()
print (df)
  Index Column1_1 Value1_1 Column1_2 Value1_2 Column1_3 Value1_3 Column1_4  \
0     1         1        6         2        7         3        8       NaN   
1     2        11       16        12       17        13       18        14   
2     3        21       24        22       25       NaN      NaN       NaN   

  Value1_4 Column1_5 Value1_5  
0      NaN       NaN      NaN  
1       19        15       20  
2      NaN       NaN      NaN

Upvotes: 2

Related Questions