How To Pass To Rows Multiple Groups Of Columns?

This is the continuation of my previous question. I have the data set: df = ID GROUP_1 GROUP_2 GROUP_3 COUNT NAME_1 NAME_2 1 AAA AAA CCC 5

Solution 1:

You can use melt twice using the same method as the previous answer

First melt the NAME columns into one column

df1 = pd.melt(
    df, ['ID', 'COUNT', 'GROUP_1', 'GROUP_2', 'GROUP_3'],
    ['NAME_1', 'NAME_2'],
    value_name='NAME').drop('variable', axis=1)

Output

   ID  COUNT GROUP_1 GROUP_2 GROUP_3 NAME
0   1      5     AAA     AAA     CCC  xxx
1   2      6     BBB     CCC     AAA  yyy
2   1      5     AAA     AAA     CCC  yyy
3   2      6     BBB     CCC     AAA  zzz

Then melt this one again by the GROUP columns

df2 = pd.melt(
    df1, ['ID', 'COUNT', 'NAME'],
    ['GROUP_1', 'GROUP_2', 'GROUP_3'],
    value_name='GROUP').drop('variable', axis=1).drop_duplicates()

Output

    ID  COUNT NAME GROUP
0    1      5  xxx   AAA
1    2      6  yyy   BBB
2    1      5  yyy   AAA
3    2      6  zzz   BBB
5    2      6  yyy   CCC
7    2      6  zzz   CCC
8    1      5  xxx   CCC
9    2      6  yyy   AAA
10   1      5  yyy   CCC
11   2      6  zzz   AAA