Pivoting multiple columns with repetitions in Python

Question

I have data in a frame that looks like:

Region   Date   Drip Coffee  Espresso  Latte  Other
Central   1         5           1        2      3
East      1         3           3        1      4
North     1         5           1        3      2
Central   2         2           7        2      0
East      2         10          3        2      1
North     2         6           9        4      2
.
.
.

I want to pivot Drip Coffee, Espresso, Latte, and Other so that it lines up like so with repetitions on Date and Region like:

Region   Date      Type       Value
Central   1      Drip Coffee    5      
East      1      Drip Coffee    3             
North     1      Drip Coffee    5    
Central   1       Espresso      1      
East      1       Espresso      3       
North     1       Espresso      1       
.
.
.
Central   2      Drip Coffee    2      
East      2      Drip Coffee    10             
North     2      Drip Coffee    6    
.
.

I've tried a few methods like:

df_new = df_old.pivot(index='Date',columns=['Drip Coffee', 'Espresso', 'Latte', 'Other']).stack(0).rename_axis(['Date','Type']).reset_index()

But this gives me ValueError: all arrays must be same length

I understand that I'm missing a new column here in my test for Value but it's because I don't understand how to pivot such a series of values like this.

I'd like to see if there's a possible fix because this problem seems to be quite unique; and I cannot find such a multiple repetition solution out there.

user3483203 · Accepted Answer · 2018-10-25T15:34:07.173

Setup

d = {'id_vars': ['Region', 'Date'], 'var_name': 'Type', 'value_name': 'Value'}

IIUC, using melt and sort_values.

df.melt(**d).sort_values(by=['Date', 'Type'])

     Region  Date         Type  Value
0   Central     1  Drip Coffee      5
1      East     1  Drip Coffee      3
2     North     1  Drip Coffee      5
6   Central     1     Espresso      1
7      East     1     Espresso      3
8     North     1     Espresso      1
12  Central     1        Latte      2
13     East     1        Latte      1
14    North     1        Latte      3
18  Central     1        Other      3
19     East     1        Other      4
20    North     1        Other      2
3   Central     2  Drip Coffee      2
4      East     2  Drip Coffee     10
5     North     2  Drip Coffee      6
9   Central     2     Espresso      7
10     East     2     Espresso      3
11    North     2     Espresso      9
15  Central     2        Latte      2
16     East     2        Latte      2
17    North     2        Latte      4
21  Central     2        Other      0
22     East     2        Other      1
23    North     2        Other      2

Pivoting multiple columns with repetitions in Python

1 Answers1