Melt a Pandas Dataframe with multiple columns

Question

I wanted to know if there's a way to melt a DataFrame with multiple column names.

I have this Pandas Data Frame:

Edad       2000    2001    2002    2003  ...   2017    2018    2019    2020
                                         ...                               
[15-25]  126675  158246  171958  188389  ...  78707   70246   65661   52209
(25-35]   65823   85059   92841   95394  ...  88479  157492  149862  122067
(35-45]   37474   48605   54593   56279  ...  65870   65798   64587   51502
(45-55]   20624   22067   25860   27601  ...  39476   40725   40566   33979
(55-65]   30240    9047   10500   10972  ...  20135   21095   21173   17242

And would like to have something like this:

Edad    Year  Value
[15-25] 2000  126675
[15-25] 2001  158246
[15-25] 2002  171958
[15-25] 2003  188389

I've used Melt before but I always address a value column, this time I have my values as cells and I'm having a very hard time figuring out how to address them.

Please share an example part of your dataframe large enough for us to reproduce the problem. NOT an image. The output of `df.head()` or `df.head().to_dict()` is often enough. — BeRT2me, Sep 07 '22 at 22:26
dont use an image. Post your original df table (just like your desired result). — adhg, Sep 07 '22 at 22:26
I just tried @Umar.H's suggestion and it worked. Soryy. Hadn't thought about using it without arguments. Thanks!! — Santiago Fernndez del Castillo, Sep 07 '22 at 23:11

score 0 · Accepted Answer · answered Sep 07 '22 at 23:16

You can use melt with groupby and sort like this:

df.melt(id_vars='Edad', var_name='Year').groupby(['Edad','Year']).agg({'value':'first'}).reset_index().sort_values(by=['Edad','Year'], ascending=[False,True])

Desired results:

    Edad    Year    value
32  [15-25] 2000    126675
33  [15-25] 2001    158246
34  [15-25] 2002    171958
35  [15-25] 2003    188389
36  [15-25] 2017    78707
37  [15-25] 2018    70246
38  [15-25] 2019    65661
39  [15-25] 2020    52209
24  (55-65] 2000    30240
25  (55-65] 2001    9047
26  (55-65] 2002    10500
27  (55-65] 2003    10972
28  (55-65] 2017    20135
29  (55-65] 2018    21095
30  (55-65] 2019    21173
31  (55-65] 2020    17242
16  (45-55] 2000    20624
17  (45-55] 2001    22067
18  (45-55] 2002    25860
19  (45-55] 2003    27601
20  (45-55] 2017    39476
21  (45-55] 2018    40725
22  (45-55] 2019    40566
23  (45-55] 2020    33979
8   (35-45] 2000    37474
9   (35-45] 2001    48605
10  (35-45] 2002    54593
11  (35-45] 2003    56279
12  (35-45] 2017    65870
13  (35-45] 2018    65798
14  (35-45] 2019    64587
15  (35-45] 2020    51502
0   (25-35] 2000    65823
1   (25-35] 2001    85059
2   (25-35] 2002    92841
3   (25-35] 2003    95394
4   (25-35] 2017    88479
5   (25-35] 2018    157492
6   (25-35] 2019    149862
7   (25-35] 2020    122067

Melt a Pandas Dataframe with multiple columns

1 Answers1