Pivoting dataframe for multiple targets/columns

user_5368122 0
Viewed 32

I am using Dask to read and save csvs.

import dask.dataframe as dd

I have a dataframe with 6 columns

date    col1    col2    t1    t2    t3    col3
12      23.2     23.2   100   200   300    1
13      34.2     12.2   10    223   122    2
14      32        12    10     20   100    3

I want to conver this dataframe to:

date    col1    col2    t_day    t_val    col3
12       23.2   23.2     1        100      1
12       23.2   23.2     2        200      1
12       23.2   23.2     3        300      1
13       34.2   12.2     1        10       2
13       34.2   12.2     2        223      2
13       34.2   12.2     3        122      2
14       32     12       1        10       3
14       32     12       1        20       3
14       32     12       1        100      3

Need to pivot on all columns that match 't*' so that all columns t1, t2, t3, .. can be pivoted. Can you please help?

I have tried using:

pivoted_df = (data.melt(id_vars=data.columns.difference(target_features),var_name='t_day', value_name='t_val')
                .assign(t_day=lambda d: d['t_day'].astype('str').str.extractall('(\d+)').unstack().fillna('').sum(axis=1).astype(int))
            )

But because dask doesnt have unstack operation:

AttributeError: 'DataFrame' object has no attribute 'unstack'

pandas dataframe python
1 Answers

IIUC, you need wide_to_long (thanks @sammywemmy for reminding me of the obvious!):

out = (pd.wide_to_long(df, stubnames='t', i='col3', j='t_day')
         .rename(columns={'t': 't_val'}).reset_index()
       )

or a custom melt:

cols = df.filter(regex='^t').columns.to_list()
# ['t1', 't2', 't3']

out = (df.melt(id_vars=df.columns.difference(cols),
               var_name='t_day', value_name='t_val')
         .assign(t_day=lambda d: d['t_day'].str[1:])
      )

output:

   col1  col2  col3  date t_day  t_val
0  23.2  23.2     1    12     1    100
1  34.2  12.2     2    13     1     10
2  32.0  12.0     3    14     1     10
3  23.2  23.2     1    12     2    200
4  34.2  12.2     2    13     2    223
5  32.0  12.0     3    14     2     20
6  23.2  23.2     1    12     3    300
7  34.2  12.2     2    13     3    122
8  32.0  12.0     3    14     3    100

Variant with stack for a different order of the rows:

cols = df.filter(regex='^t').columns.to_list()
# ['t1', 't2', 't3']

out = (df.set_index(df.columns.difference(cols).to_list())
   .rename_axis('t_day', axis=1)
   .stack()
   .reset_index(name='t_val')
   .assign(t_day=lambda d: d['t_day'].str[1:])
)

output:

   col1  col2  col3  date t_day  t_val
0  23.2  23.2     1    12     1    100
1  23.2  23.2     1    12     2    200
2  23.2  23.2     1    12     3    300
3  34.2  12.2     2    13     1     10
4  34.2  12.2     2    13     2    223
5  34.2  12.2     2    13     3    122
6  32.0  12.0     3    14     1     10
7  32.0  12.0     3    14     2     20
8  32.0  12.0     3    14     3    100

variants with date as index:

option #1

cols = df.filter(regex='^t').columns.to_list()
# ['t1', 't2', 't3']

out = (df.reset_index()
         .melt(id_vars=['date']+df.columns.difference(cols).to_list(),
               var_name='t_day', value_name='t_val')
         .assign(t_day=lambda d: d['t_day'].str[1:])
         .set_index('date')
      )

option #2

cols = df.filter(regex='^t').columns.to_list()
# ['t1', 't2', 't3']

out = (df
   .set_index(df.columns.difference(cols).to_list(), append=True)
   .rename_axis('t_day', axis=1)
   .stack()
   .reset_index(name='t_val')
   .assign(t_day=lambda d: d['t_day'].str[1:])
   .set_index('date')
)
user_163434640
Related