partially transpose a dataframe (exchange subset of the columns and rows)

Question

I have the dataframe below:

station      date  A  B  C
     NY  2019-1-1  0  1  4
     NY  2019-1-2  3  9  3
     WN  2019-1-1  3  7  6
     WN  2019-1-2  0  1  2

How can I change it to the following format?

station METRIC  2019-1-1  2019-1-2
     NY      A         0         3
     NY      B         1         9
     NY      C         4         3
     WN      A         3         0
     WN      B         7         1
     WN      C         6         2

I am not sure whether I should use stack/unstack/melt/pivot/pivot_table...

Hi and welcome on SO. It will be great if you can have a look at [how-to-ask](/help/how-to-ask) and then try to produce a [mcve](/help/mcve). In particular share text/code not pictures. — rpanai, Sep 02 '21 at 04:12
Does this answer your question? [how to unstack (or pivot?) in pandas](https://stackoverflow.com/questions/24640399/how-to-unstack-or-pivot-in-pandas) — rpanai, Sep 02 '21 at 04:31

mozway · Accepted Answer · 2021-09-02T04:37:50.210

You can use stack/unstack:

(df.set_index(['station', 'date'])
   .rename_axis('METRIC', axis=1)  # set name for future A/B/C column
   .stack()                        # move A/B/C as rows
   .unstack('date')                # move date as columns
   .rename_axis(None, axis=1)      # remove "date" name from index
   .reset_index()
)

or T+stack:

(df.set_index(['station', 'date'])
   .T
   .rename_axis('METRIC', axis=0)
   .stack('station')
   .rename_axis(None, axis=1)
   .swaplevel()
   .reset_index()
)

input:

  station      date  A  B  C
0      NY  2019-1-1  0  1  4
1      NY  2019-1-2  3  9  3
2      WN  2019-1-1  3  7  6
3      WN  2019-1-2  0  1  2

output:

  station METRIC  2019-1-1  2019-1-2
0      NY      A         0         3
1      NY      B         1         9
2      NY      C         4         3
3      WN      A         3         0
4      WN      B         7         1
5      WN      C         6         2

partially transpose a dataframe (exchange subset of the columns and rows)

1 Answers1