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.
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