python - Transforming a CSV from wide to long format

Question

I have a csv like this:

col1,col2,col2_val,col3,col3_val
A,1,3,5,6
B,2,3,4,5

and i want to transfer this csv like this :

col1,col6,col7,col8
A,Col2,1,3
A,col3,5,6

there are col3 and col3_val so i want to keep col3 in col6 and values of col3 in col7 and col3_val's value in col8 in the same row where col3's value is stored.

Answer 1

I think what you're looking for is df.melt and df.groupby:

In [63]: df.rename(columns=lambda x: x.strip('_val')).melt('col1')
           .groupby(['col1', 'variable'], as_index=False)['value'].apply(lambda x: pd.Series(x.values))
           .add_prefix('value')
           .reset_index()
Out[63]: 
  col1 variable  value0  value1
0    A     col2       1       3
1    A     col3       5       6
2    B     col2       2       3
3    B     col3       4       5

_{Credit to John Galt for help with the second part.}

If you wish to rename columns, assign the whole expression above to df_out and then do:

df_out.columns = ['col1', 'col6', 'col7', 'col8']

Saving this should be straightforward with df.to_csv.