How to use columns in a dataframe to create rows in a new dataframe?

Question

I have a dataframe and want to create a new dataframe using some columns to create rows.

Here is my dataframe:

import pandas as pd
df=pd.DataFrame(columns=["TeamNumber", "C", "C1", "W", "W1", "W2","D","D1","G", "UTIL", "DATE", "TOP" ])
df=df.append({"TeamNumber": 1, "C": "PAUL", "C1": "BOB", "W": "NIC", "W1":"ED", "W2": "ROB","D": "ERIK","D1": "JOE","G": "RED", "UTIL": "JIM", "DATE": "2017-10-12", "TOP": 0}, ignore_index= True)
df=df.append({"TeamNumber": 2, "C": "PETE", "C1": "BOBBY", "W": "FRED", "W1":"TED", "W2": "ROBY","D": "DERIK","D1": "JULIAN","G": "CARL", "UTIL": "JIMMY", "DATE": "2017-10-15", "TOP": 1}, ignore_index= True)


TeamNumber   C    C1     W  W1   W2    D    D1    G  UTIL   DATE       TOP
0   1       PAUL BOB   NIC  ED  ROB  ERIK  JOE    RED  JIM  2017-10-12  0
1   2       PETE BOBBY FRED TED ROBY DERIK JULIAN CARL JIMMY 2017-10-15 1

I expect the output to be:

   TOP TeamNumber  DATE Name       
0   0   1   2017-10-12  PAUL
1   0   1   2017-10-12  BOB
2   0   1   2017-10-12  NIC
3   0   1   2017-10-12  ED
4   0   1   2017-10-12  ROB
5   0   1   2017-10-12  ERIK
6   0   1   2017-10-12  JOE
7   0   1   2017-10-12  RED
8   0   1   2017-10-12  JIM
9   1   2   2017-10-15  PETE
10  1   2   2017-10-15  BOBBY
11  1   2   2017-10-15  FRED
12  1   2   2017-10-15  TED
13  1   2   2017-10-15  ROBY
14  1   2   2017-10-15  DERIK
15  1   2   2017-10-15  JULIAN
16  1   2   2017-10-15  CARL
17  1   2   2017-10-15  JIMMY

Possible duplicate of [pandas convert some columns into rows](https://stackoverflow.com/questions/28654047/pandas-convert-some-columns-into-rows) — Jan Carlo Once, Apr 01 '19 at 16:47

score 3 · Answer 1 · answered Apr 01 '19 at 17:05

3

Check DataFrame.melt() in Pandas

df.melt(id_vars=['TOP','DATE' ,'TeamNumber'])[['TOP','DATE','TeamNumber','value']]

answered Apr 01 '19 at 17:05

function

1,298
1
14
41

score 2 · Accepted Answer · answered Apr 01 '19 at 17:20

df = df.set_index(['TOP','TeamNumber',"DATE"])
df = df.stack().reset_index().drop(['level_3'],axis=1).rename(columns = {0:'Name'})

output df

    TOP TeamNumber  DATE    Name
0   0   1   2017-10-12  PAUL
1   0   1   2017-10-12  BOB
2   0   1   2017-10-12  NIC
3   0   1   2017-10-12  ED
4   0   1   2017-10-12  ROB
5   0   1   2017-10-12  ERIK
6   0   1   2017-10-12  JOE
7   0   1   2017-10-12  RED
8   0   1   2017-10-12  JIM
9   1   2   2017-10-15  PETE
10  1   2   2017-10-15  BOBBY
11  1   2   2017-10-15  FRED
12  1   2   2017-10-15  TED
13  1   2   2017-10-15  ROBY
14  1   2   2017-10-15  DERIK
15  1   2   2017-10-15  JULIAN
16  1   2   2017-10-15  CARL
17  1   2   2017-10-15  JIMMY

Where does `level_3`come from and how does it work ? Just for the next time I want to do something similar. Thanks — Jagr, Apr 01 '19 at 18:00
@Jagr in by using `set_index` making `multiindexing` till `level_3`, `stack` is changing `shape` , lets assume if `set_index` is `4 element list` then it will be `level_4` — Akhilesh_IN, Apr 01 '19 at 18:06

How to use columns in a dataframe to create rows in a new dataframe?

2 Answers2