Modifying Rows & columns of a dataset-CodePudding

I have the data like this

category | Sub-category | Variable | 1990 | 1991 | 1992
C1        sc1               v1         1      2      3
                            v2         4      5      6
                            v3         7      8      9
          sc2               v1         10     11     12
                            v2         13     14     15
                            v3         16     17     18

I want to convert it into this

    category | Sub-category | Variable | year | vals
       C1        sc1            v1       1990    1
                                         1991    2
                                         1992    3
                                V2       1990    4
                                         1991    5
                                         1992    6
                                V3       1990    7
                                         1991    8
                                        `1992`   9
and so on

How do I do this in python [&/or excel]?

CodePudding user response：

In Python you can use melt function (from pandas library).

Pandas.melt() unpivots a DataFrame from wide format to long format.

melt() function is useful to massage a DataFrame into a format where one or more columns are identifier variables, while all other columns, considered measured variables, are unpivoted to the row axis, leaving just two non-identifier columns, variable and value.

Usage:

pd.melt(x, id_vars=['category', 'Sub-category','Variable'], var_name='year', value_name='vals')