Mukul Mukul - 3 months ago 14
Python Question

Columns to row in python

Do we have sas equivalent of proc transpose. i have the following variables

a b c d e f g
1 3 4 5 3 2 2
2 3 3 3 2 4 2


i want to transpose, across columns a and b , dummy out put below:

a b tranposed_columns transposed_value
1 3 c 4
1 3 d 5
1 3 e 3
1 3 f 2
1 3 g 2
2 3 c 3
2 3 d 3
2 3 e 2
2 3 f 4
2 3 g 2


How can i do this in python, in sas i would have written

proc transpose data=Dummy_data
out=ouput_data (rename=(col1=transposed_value _name_=tranposed_columns));
by a b;
run;


i tried dummy_data.T , but it tranposes all the data, also tried hands with pivot data, but its not repeating the rows.

dummy_data.pivot_table(index=['a','b'],values=['c','d','e','f','g'], aggfunc='sum', fill_value=0)

Answer

Use pd.melt to coalesce columns into a single column. Use the id_vars parameter to "protect" certain columns from participating in the melt:

In [11]: pd.melt(df, id_vars=['a','b'], var_name='transposed_columns', value_name='transposed_value')
Out[11]: 
   a  b transposed_columns  transposed_value
0  1  3                  c                 4
1  2  3                  c                 3
2  1  3                  d                 5
3  2  3                  d                 3
4  1  3                  e                 3
5  2  3                  e                 2
6  1  3                  f                 2
7  2  3                  f                 4
8  1  3                  g                 2
9  2  3                  g                 2