Pandas: dataframe transformation using pivot

Question

I have a data frame in the below format:

Date        Id       A         B         C          D        E
2018-01-28 5937.0 11.000000 11.000000 10.000000 10.000000 10.000000

2018-01-21 5937.0 10.000000 10.000000 10.000000 10.000000 10.000000

I want to change the data into the below format:

             Id       2018-01-28         2018-01-21
A           5937.0   11.000000          10.000000
B           5937.0   11.000000          10.000000
C           5937.0   10.000000          10.000000
D           5937.0   10.000000          10.000000
E           5937.0   10.000000          10.000000

What is the best method to carry out following transformation. I have been using pivot but its not working(I am not very good with pivot)

You can check this https://stackoverflow.com/questions/41861846/convert-row-to-column-in-python-pandas. It can help. — LOrD_ARaGOrN, Nov 12 '18 at 09:17

Space Impact · Accepted Answer · 2018-11-12T09:47:40.880

Use set_index followed by stack and unstack with reset_index:

df1 = df.set_index(['Date','Id']).stack().unstack(0).reset_index(0)

print(df1)
Date      Id  2018-01-21  2018-01-28
A     5937.0        10.0        11.0
B     5937.0        10.0        11.0
C     5937.0        10.0        10.0
D     5937.0        10.0        10.0
E     5937.0        10.0        10.0

df1=df.set_index(['Date','Id']).stack().unstack(0).reset_index(0).rename_axis(None,1)

print(df1)
       Id  2018-01-21  2018-01-28
A  5937.0        10.0        11.0
B  5937.0        10.0        11.0
C  5937.0        10.0        10.0
D  5937.0        10.0        10.0
E  5937.0        10.0        10.0

score 1 · Answer 2 · answered Nov 12 '18 at 09:41

I would do this using melt and pivot_table:

(df.melt(['Date', 'Id'])
   .pivot_table(index=['variable', 'Id'], columns='Date', values='value')
   .reset_index())


Date variable      Id  2018-01-21  2018-01-28
0           A  5937.0        10.0        11.0
1           B  5937.0        10.0        11.0
2           C  5937.0        10.0        10.0
3           D  5937.0        10.0        10.0
4           E  5937.0        10.0        10.0

score 1 · Answer 3 · answered Nov 12 '18 at 09:58

Using pivot:

(df.pivot_table(values=["A", "B", "C", "D", "E"], columns=["Id", "Date"])
    .unstack()
    .reset_index(1) # Multi-index level 1 = Id
    .rename_axis(None, 1)) # Set columns name to None (not Date)

Output:

Date      Id  2018-01-21  2018-01-28
A     5937.0        10.0        11.0
B     5937.0        10.0        11.0
C     5937.0        10.0        10.0
D     5937.0        10.0        10.0
E     5937.0        10.0        10.0

Pandas: dataframe transformation using pivot

3 Answers3