Multi Window Turn in Pandas

Question

Consider the following data file:

item_id hour when date quantity 110 0YrKNYeEoa 1 before 2015-01-26 247286 111 0UMNiXI7op 1 before 2015-01-26 602001 112 0QBtIMN3AH 1 before 2015-01-26 981630 113 0GuKXLiWyV 1 after 2015-01-26 2203913 114 0SoFbjvXTs 1 after 2015-01-26 660183 115 0UkT257SXj 1 before 2015-01-26 689332 116 0RPjXnkiGx 1 after 2015-01-26 283090 117 0FhJ9RGsLT 1 before 2015-01-26 2024256 118 0FhGJ4MFlg 1 before 2015-01-26 74524 119 0FQhHZRXhB 1 before 2015-01-26 0 120 0FsSdJQlTB 1 before 2015-01-26 0 121 0FrrAzTFHE 1 before 2015-01-26 0 122 0FfkgBdMHi 1 before 2015-01-26 0 123 0FOnJNexRn 1 before 2015-01-26 0 124 0FcWhIdBds 1 before 2015-01-26 0 125 0F2lr0cL9t 1 before 2015-01-26 1787659 

I would like to rotate it to arrange the table as:

 Index before after (item_id, hour, date) quantityB quantityA 

When I try:

 df.pivot(index=['item_id', 'hour', 'date'], columns='when', values='quanty') 

I get:

 ValueError: Wrong number of items passed 8143, placement implies 3 

Why?

Answer 1

If I understand what you are asking, I think you want pandas.pivot_table(...) , which you can use like this:

 table = pd.pivot_table(df, index=['item_id', 'hour', 'date'], columns='when', values='quantity') 

which with a sample data frame

  item_id hour when date quantity 0 a 1 before 2015-01-26 25 1 b 1 before 2015-01-26 14 2 a 1 after 2015-01-26 4 3 d 1 before 2015-01-26 43 4 b 1 after 2015-01-26 30 5 d 1 after 2015-01-26 12 

produces

 when after before item_id hour date a 1 2015-01-26 4 25 b 1 2015-01-26 30 14 d 1 2015-01-26 12 43