I would like to apply a type of pivot on a pandas dataframe but I can't find the proper way to do it.
What I want to do:
input_df:
p_id p_name prod_t1 prod_t2 prod_t3
-----------------------------------
1 foo 3 2 4
2 bar 0 1 0
expected_output_df:
p_id p_name prod_time quantity
-----------------------------------
1 foo prod_t1 3
1 foo prod_t2 2
1 foo prod_t3 4
2 bar prod_t1 0
2 bar prod_t2 1
2 bar prod_t3 0
This is melt:
df.melt(id_vars=['p_id','p_name'],
var_name='prod_time',
value_name='quantity')
Output:
p_id p_name prod_time quantity
0 1 foo prod_t1 3
1 2 bar prod_t1 0
2 1 foo prod_t2 2
3 2 bar prod_t2 1
4 1 foo prod_t3 4
5 2 bar prod_t3 0
Or this can be stack:
(df.set_index(['p_id','p_name'])
.stack().reset_index()
.rename(columns={'level_2':'prod_time', 0:'quantity'})
)
Output:
p_id p_name prod_time quantity
0 1 foo prod_t1 3
1 1 foo prod_t2 2
2 1 foo prod_t3 4
3 2 bar prod_t1 0
4 2 bar prod_t2 1
5 2 bar prod_t3 0
df.melt(id_vars = ['p_name'], value_vars= ['prod_t1', 'prod_t2', 'prod_t3'])
>>>
p_name variable value
0 foo prod_t1 3
1 bar prod_t1 0
2 foo prod_t2 2
3 bar prod_t2 1
4 foo prod_t3 4
5 bar prod_t3 0
I would like to do wide_to_long, notice the order here
df=pd.wide_to_long(df,['prod'],
i=['p_id','p_name'],
j='Number',
suffix='\w+',sep='_').reset_index()
p_id p_name Number prod
0 1 foo t1 3
1 1 foo t2 2
2 1 foo t3 4
3 2 bar t1 0
4 2 bar t2 1
5 2 bar t3 0
Just for completion, this is solution using stack
(df.set_index(['p_id', 'p_name']).rename_axis('prod_time', axis=1)
.stack().reset_index(name='quantity'))
Out[1103]:
p_id p_name prod_time quantity
0 1 foo prod_t1 3
1 1 foo prod_t2 2
2 1 foo prod_t3 4
3 2 bar prod_t1 0
4 2 bar prod_t2 1
5 2 bar prod_t3 0
