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 stackoverflow.com/questions/41861846/…. It can help. — LOrD_ARaGOrN
– LOrD_ARaGOrN, Commented Nov 12, 2018 at 9:17

Space Impact · Accepted Answer · 2018-11-12 09:47:40Z

2

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

edited Nov 12, 2018 at 9:47

answered Nov 12, 2018 at 9:33

Space Impact

13.3k26 silver badges51 bronze badges

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

Comments

cs95 · Accepted Answer · 2018-11-12 09:41:37Z

1

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

answered Nov 12, 2018 at 9:41

cs95

406k106 gold badges744 silver badges797 bronze badges

Comments

Edgar Ramírez Mondragón · Accepted Answer · 2018-11-12 09:58:22Z

1

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

answered Nov 12, 2018 at 9:58

Edgar Ramírez Mondragón

3,0613 gold badges23 silver badges36 bronze badges

Collectives™ on Stack Overflow

Pandas: dataframe transformation using pivot

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related