Pandas, Converting multiple rows into multiple columns

Question

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

jezrael · Accepted Answer · 2018-06-11 10:27:10Z

2

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

edited Jun 11, 2018 at 10:27

answered Jun 11, 2018 at 8:14

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

user96564 Over a year ago

I think I made a typo mistake there , when i convert rows to columns, I want it to be like Column1, value1, Column2, Value2 etc. so that it will be easier to read. I will edit that typo mistake now

jezrael Over a year ago

@user3280146 - What is df.index ?

user96564 Over a year ago

Index just an ID of that column which i am using to groupby

jezrael Over a year ago

@user3280146 - Added 2 solutions, because not sure if need split columns or not. Please check it.

user96564 Over a year ago

thats perfect. Thank you :)

|

Collectives™ on Stack Overflow

Pandas, Converting multiple rows into multiple columns

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related