Melt pandas dataframe multiple times

Question

I have a dataframe df:

Item   Name  Location  Jan-Units  Feb-Units  Mar-Units  Jan-Rev  Feb-Rev  Mar-Rev  Jan-Trl  Feb-Trl  Mar-Trl
123    ABC   CA        20         30        40          100      150      200      1.00     1.50     2.00
456    DEF   AZ        25         35        45          125      175      225      1.25     1.75     2.25

I need to melt this df multiple times, keep the first 3 columns the same and add a date column based on the first 3 characters of the melted columns. Output should be:

Item    Name  Location  Date        Units  Rev   Trl
123     ABC   CA        01/01/2020  20     100   1.00
123     ABC   CA        02/01/2020  30     150   1.50
123     ABC   CA        03/01/2020  40     200   2.00
456     DEF   AZ        01/01/2020  25     125   1.25
456     DEF   AZ        02/01/2020  35     175   1.75
456     DEF   AZ        03/01/2020  45     225   2.25

(The actual df has all the months for 2020 and hundreds of rows)

score 6 · Accepted Answer · answered Aug 13 '20 at 17:26

First reshape your columns and then use pd.wide_to_long:

df.columns = ["-".join(i.split("-")[::-1]) for i in df.columns]

print (pd.wide_to_long(df, stubnames=["Units","Rev","Trl"],
                       i=["Item","Name"], j="Date",
                       sep="-", suffix="\w+")
       .reset_index())

   Item Name Date Location  Units  Rev   Trl
0   123  ABC  Jan       CA     20  100  1.00
1   123  ABC  Feb       CA     30  150  1.50
2   123  ABC  Mar       CA     40  200  2.00
3   456  DEF  Jan       AZ     25  125  1.25
4   456  DEF  Feb       AZ     35  175  1.75
5   456  DEF  Mar       AZ     45  225  2.25

Melt pandas dataframe multiple times

1 Answers1