How can I unpivot data with multiple columns and multiple variables in pandas?

Question

my input: input

And desire output:

Please see https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples. Providing images makes it very difficult for any of us to reproduce your problem. — ALollz, May 09 '20 at 23:50
Please [provide a reproducible copy of the DataFrame with `to_clipboard`](https://stackoverflow.com/questions/52413246/provide-a-reproducible-copy-of-the-dataframe-with-to-clipboard/52413247#52413247) — Trenton McKinney, May 10 '20 at 00:15

score 0 · Accepted Answer · answered May 24 '20 at 05:34

Remove the Na, add a column name, and 'append()' the value to an empty 'DataFrame'.

    product ene ene_total   feb feb_total   mar mar_total
0   A   NaN NaN 2.0 218.75  NaN NaN
1   B   NaN NaN 1.0 27.40   NaN NaN
2   C   NaN NaN NaN NaN 24.0    1530.00
3   D   NaN NaN NaN NaN 24.0    1102.50
4   E   NaN NaN NaN NaN 12.0    206.79
5   F   NaN NaN NaN NaN 24.0    317.14
6   G   6.0 98.89   NaN NaN NaN NaN
7   H   NaN NaN NaN NaN 24.0    385.29
8   I   NaN NaN NaN NaN 25.0    895.98

new_df = pd.DataFrame(index=[], columns=[0,1,2,3])

for i in range(len(df)):
    tmp = df.iloc[i].dropna()
    new_df = new_df.append(pd.Series([tmp.index[1],tmp[0],tmp[1],tmp[2]]), ignore_index=True)

new_df.rename(columns={0:'period', 2:'unit', 3:'total'}).set_index(1)

    period  unit    total
1           
A   feb 2.0     218.75
B   feb 1.0     27.40
C   mar 24.0    1530.00
D   mar 24.0    1102.50
E   mar 12.0    206.79
F   mar 24.0    317.14
G   ene 6.0     98.89
H   mar 24.0    385.29
I   mar 25.0    895.98

How can I unpivot data with multiple columns and multiple variables in pandas?

1 Answers1