dataframe how pivot table based on substring of the column

I have a dataframe:

df = 
time id ser1 ser2 ... ser20 N0ch0 N1ch0 N2ch0 N0ch1 N1ch1 N2ch1 N0ch2 N1ch2 N2ch2 N0ch3 N1ch3 N2ch3
  1   2  4    5         3     8     7     8    5     1      4    6     2      7    9    8      6

And I want to pivot it based on the channel ('ch' substring), such that it will become a column, so new dataframe will be:

time id channel ser1 ser2 ... ser20 N0 N1 N2
  1   2   0      4    5         3   8  7  8
  1   2   1      4    5         3   5  1  4
  1   2   2      4    5         3   6  2  7
  1   2   3      4    5         3   9  8  6

What is the best way to do so?

Solution 1:

You can start by using melt with parameter id_vars set to your 'ser' like columns and 'time' + 'id'.

You can then split the 'variable' column into 2, where one of the columns will be used as an index column when using pivot_table, and the other will be a column:

# Columns to be used as index in melt & pivot
id_cols = ['time','id'] + list(df.filter(like='ser'))

# Melt and split a column
m = df.melt(id_vars = id_cols)
m[['N','channel']] = m.variable.str.split('ch', 1 ,expand=True)

# Pivot the melted dataframe
out = m.pivot_table(index = id_cols + ['channel'],  columns='N', values='value').reset_index()

prints:

   time  id channel  ser1  ser2  ser20  N0  N1  N2
0     1   2       0     4     5      3   8   7   8
1     1   2       1     4     5      3   5   1   4
2     1   2       2     4     5      3   6   2   7
3     1   2       3     4     5      3   9   8   6