Pandas Melt Columns to Rows

Question

I have data that looks like this:

Date	Country	Channel	App-Purchase-Laptop	App-Purchase-Cell	App-Sell-Laptop	App-Sell-Cell	Web-Purchase-Laptop	Web-Purchase-Cell	Web-Sell-Laptop	Web-Sell-Cell
03/25	US	Email	6	10	30	40	5	7	9	11
03/25	US	Direct	1	30	10	20	6	8	10	12
03/25	India	Email	4	60	29	37	40	21	43	53
03/25	India	Direct	11	16	32	22	39	11	7	45
03/26	US	Email	6	10	30	40	5	7	9	11
03/26	US	Direct	1	30	10	20	6	8	10	12
03/26	India	Email	4	60	29	37	40	21	43	53
03/26	India	Direct	11	16	32	22	39	11	7	45

Output I am looking for:

Date	Country	Channel	Category	Source	Purchase	Sell
03/25	US	Email	App	Laptop	6	30
03/25	US	Email	App	Cell	10	40
03/25	US	Email	Web	Laptop	5	9
03/25	US	Email	Web	Cell	7	11
03/25	US	Direct	App	Laptop	1	10
03/25	US	Direct	App	Cell	30	20
03/25	US	Direct	Web	Laptop	6	10
03/25	US	Direct	Web	Cell	8	12
03/25	India	Email	App	Laptop	4	30
03/25	India	Email	App	Cell	60	29
03/25	India	Email	Web	Laptop	40	43
03/25	India	Email	Web	Cell	21	53
03/25	India	Direct	App	Laptop	11	32
03/25	India	Direct	App	Cell	16	22
03/25	India	Direct	Web	Laptop	39	7
03/25	India	Direct	Web	Cell	11	45
03/26	US	Email	App	Laptop	6	30
03/26	US	Email	App	Cell	10	40
03/26	US	Email	Web	Laptop	5	9
03/26	US	Email	Web	Cell	7	11
03/26	US	Direct	App	Laptop	1	10
03/26	US	Direct	App	Cell	30	20
03/26	US	Direct	Web	Laptop	6	10
03/26	US	Direct	Web	Cell	8	12
03/26	India	Email	App	Laptop	4	30
03/26	India	Email	App	Cell	60	29
03/26	India	Email	Web	Laptop	40	43
03/26	India	Email	Web	Cell	21	53
03/26	India	Direct	App	Laptop	11	32
03/26	India	Direct	App	Cell	16	22
03/26	India	Direct	Web	Laptop	39	7
03/26	India	Direct	Web	Cell	11	45

I tried different ways to solve this using melt, but I have been unable to solve this since I am new to python. Your help will be appreciated. Thanks!

user_6361531 · Answer

Try this:

dfi = df.set_index(['Date','Country','Channel'])

dfi.columns = pd.MultiIndex.from_tuples(dfi.columns.str.split('-', expand=True), 
                                        names=['Category', None, 'Source'])

df_out = dfi.stack([0,2]).reset_index()

df_out

Output:

     Date Country Channel Category  Source  Purchase  Sell
0   03/25      US   Email      App    Cell        10    40
1   03/25      US   Email      App  Laptop         6    30
2   03/25      US   Email      Web    Cell         7    11
3   03/25      US   Email      Web  Laptop         5     9
4   03/25      US  Direct      App    Cell        30    20
5   03/25      US  Direct      App  Laptop         1    10
6   03/25      US  Direct      Web    Cell         8    12
7   03/25      US  Direct      Web  Laptop         6    10
8   03/25   India   Email      App    Cell        60    37
9   03/25   India   Email      App  Laptop         4    29
10  03/25   India   Email      Web    Cell        21    53
11  03/25   India   Email      Web  Laptop        40    43
12  03/25   India  Direct      App    Cell        16    22
13  03/25   India  Direct      App  Laptop        11    32
14  03/25   India  Direct      Web    Cell        11    45
15  03/25   India  Direct      Web  Laptop        39     7
16  03/26      US   Email      App    Cell        10    40
17  03/26      US   Email      App  Laptop         6    30
18  03/26      US   Email      Web    Cell         7    11
19  03/26      US   Email      Web  Laptop         5     9
20  03/26      US  Direct      App    Cell        30    20
21  03/26      US  Direct      App  Laptop         1    10
22  03/26      US  Direct      Web    Cell         8    12
23  03/26      US  Direct      Web  Laptop         6    10
24  03/26   India   Email      App    Cell        60    37
25  03/26   India   Email      App  Laptop         4    29
26  03/26   India   Email      Web    Cell        21    53
27  03/26   India   Email      Web  Laptop        40    43
28  03/26   India  Direct      App    Cell        16    22
29  03/26   India  Direct      App  Laptop        11    32
30  03/26   India  Direct      Web    Cell        11    45
31  03/26   India  Direct      Web  Laptop        39     7

Details:

Let's move Date, Country, and Channel into the index, now use string manipulation on the remaining columns to create a multiindex column header. Finally, stack the Category and Source levels into index and reset index.

user_7175713 · Answer

You could use the pivot_longer function from pyjanitor to abstract the reshaping process (it is just a wrapper around melt and some pandas string methods):

df.pivot_longer(index = ['Date','Country','Channel'], 
                names_to=['Category', '.value', 'Source'], 
                names_sep="-", 
                sort_by_appearance = True)
 
     Date Country Channel Category  Source  Purchase  Sell
0   03/25      US   Email      App  Laptop         6    30
1   03/25      US   Email      App    Cell        10    40
2   03/25      US   Email      Web  Laptop         5     9
3   03/25      US   Email      Web    Cell         7    11
4   03/25      US  Direct      App  Laptop         1    10
5   03/25      US  Direct      App    Cell        30    20
6   03/25      US  Direct      Web  Laptop         6    10
7   03/25      US  Direct      Web    Cell         8    12
8   03/25   India   Email      App  Laptop         4    29
9   03/25   India   Email      App    Cell        60    37
10  03/25   India   Email      Web  Laptop        40    43
11  03/25   India   Email      Web    Cell        21    53
12  03/25   India  Direct      App  Laptop        11    32
13  03/25   India  Direct      App    Cell        16    22
14  03/25   India  Direct      Web  Laptop        39     7
15  03/25   India  Direct      Web    Cell        11    45
16  03/26      US   Email      App  Laptop         6    30
17  03/26      US   Email      App    Cell        10    40
18  03/26      US   Email      Web  Laptop         5     9
19  03/26      US   Email      Web    Cell         7    11
20  03/26      US  Direct      App  Laptop         1    10
21  03/26      US  Direct      App    Cell        30    20
22  03/26      US  Direct      Web  Laptop         6    10
23  03/26      US  Direct      Web    Cell         8    12
24  03/26   India   Email      App  Laptop         4    29
25  03/26   India   Email      App    Cell        60    37
26  03/26   India   Email      Web  Laptop        40    43
27  03/26   India   Email      Web    Cell        21    53
28  03/26   India  Direct      App  Laptop        11    32
29  03/26   India  Direct      App    Cell        16    22
30  03/26   India  Direct      Web  Laptop        39     7
31  03/26   India  Direct      Web    Cell        11    45

Your data has a pattern : category, followed by either Purchase or Sell and finally followed by Source. the separator here is -. the pattern in names_to (Category, '.value', 'Source') keeps values paired with .value as column names, while the others are lumped into either Category or Source, depending on position.

Pandas Melt Columns to Rows

2 Answers