In python, how to reshape a dataframe so that some datetime columns become rows-CodePudding

In a pandas dataframe, I want to transpose and agrupate datetime columns into rows.

Like this (there are about 12 date columns):

 Category   Type    11/2021 12/2021
0   A        1       0.0     20
1   A        2       NaN     13
2   B        1       5.0      7
3   B        2       20.0     4

to one like this:

    Date    Category    Type1   Type2
0   2021-11    A         0       NaN
1   2021-11    B         5       20.0
2   2021-12    A         20      13.0
3   2021-12    B         7       4.0

I tought about using pivot tables, but I wasnt able to do so.

CodePudding user response：

Here's a link to the pandas transpose feature, which I think should help a little bit, not sure how it would effect the category column though, -https://note.nkmk.me/en/python-pandas-t-transpose/

CodePudding user response：

You could do:

(df.melt(['Category', 'Type'], var_name = 'Date').
   pivot(['Date', 'Category'],'Type').reset_index())

         Date Category value      
Type                       1     2
0     11/2021        A   0.0   NaN
1     11/2021        B   5.0  20.0
2     12/2021        A  20.0  13.0
3     12/2021        B   7.0   4.0

To be alitle cleaner you could use janitor:

import janitor

(df.pivot_longer(['Category', 'Type'], names_to = 'Date', values_to = 'type').
  pivot_wider(['Date', 'Category'], names_from = 'Type', names_sep = ''))

      Date Category   type1   type2
0  11/2021        A     0.0     NaN
1  11/2021        B     5.0    20.0
2  12/2021        A    20.0    13.0
3  12/2021        B     7.0     4.0