pandas reshaping/transform the dataframe

Question

I have dataframe df:

col_name    col_month   col_value
abc         2021-01-31  233
abc         2021-02-28  784
abc         2021-03-31  7868
def         2021-02-28  3652
def         2021-03-31  344
def         2021-04-30  87

I need the result as:

col_name    NAME        col1          col2            col3  
abc         col_month   2021-01-31    2021-02-28      2021-03-31
abc         col_value   233           784             7868
def         col_month   2021-02-28    2021-03-31      2021-04-30
def         col_value   3652          344             87

what i have tried so far using melt:

pd.melt(df, id_vars=['col_name'], var_name = 'NAME', value_name = 'VALUE')

this give the result:

col_name    NAME        VALUE
abc     col_month   2021-01-31
abc     col_month   2021-02-28
abc     col_month   2021-03-31
def     col_month   2021-02-28
def     col_month   2021-03-31
def     col_month   2021-04-30
abc     col_value   233
abc     col_value   784
abc     col_value   7868
def     col_value   3652
def     col_value   344
def     col_value   87

but i am not still able to get the desired result

score 3 · Answer 1 · answered Sep 07 '21 at 02:21

Here's another way:

df.assign(cols=df.groupby('col_name').cumcount()+1)\
  .set_index(['col_name','cols'])\
  .rename_axis('NAME', axis=1)\
  .stack().unstack(1)\
  .add_prefix('col')\
  .reset_index()

Output:

cols col_name       NAME        col1        col2        col3
0         abc  col_month  2021-01-31  2021-02-28  2021-03-31
1         abc  col_value         233         784        7868
2         def  col_month  2021-02-28  2021-03-31  2021-04-30
3         def  col_value        3652         344          87

Or with, melt:

df.assign(cols=df.groupby('col_name').cumcount()+1)\
  .melt(['col_name', 'cols'], var_name='NAME')\
  .set_index(['col_name', 'NAME', 'cols'])\
  .unstack()['value'].add_prefix('col')\
  .reset_index()

Great work as always~! You could avoid the explicit assign in the first option with `.set_index(['col_name', df.groupby('col_name').cumcount() + 1])` — Henry Ecker, Sep 07 '21 at 02:39
@HenryEcker Thanks, man. I appreciate your suggested improvements as always. — Scott Boston, Sep 07 '21 at 02:44

U13-Forward · Answer 2 · 2021-09-07T02:22:18.410

Try using groupby and T:

>>> df.groupby('col_name').apply(lambda x: x.drop('col_name', axis=1).reset_index(drop=True).T.rename_axis(index='NAME')).rename(lambda x: f'col{x + 1}', axis=1).reset_index()
  col_name       NAME        col1        col2        col3
0      abc  col_month  2021-01-31  2021-02-28  2021-03-31
1      abc  col_value         233         784        7868
2      def  col_month  2021-02-28  2021-03-31  2021-04-30
3      def  col_value        3652         344          87
>>>

Or try melt with pivot_table:

>>> x = df.melt('col_name')
>>> d = {'': ['col_name', 'NAME']}
>>> x.assign(idx=x.groupby(['col_name', 'variable']).cumcount()).pivot_table(index=['col_name', 'variable'], columns='idx', aggfunc=sum).reset_index().droplevel(0, axis=1).rename_axis(columns=None).rename(lambda x: f'col{x + 1}' if isinstance(x, int) else x, axis=1).rename(columns=lambda x: d.get(x, [x]).pop(0))
  col_name       NAME        col1        col2        col3
0      abc  col_month  2021-01-31  2021-02-28  2021-03-31
1      abc  col_value         233         784        7868
2      def  col_month  2021-02-28  2021-03-31  2021-04-30
3      def  col_value        3652         344          87
>>>

score 0 · Answer 3 · answered Sep 07 '21 at 03:42

Another approach to use melt, groupby and apply:

(
    df.melt('col_name',var_name='NAME')
    .groupby(['col_name','NAME'])
    .value.apply(list)
    .apply(pd.Series)
    .rename(columns = lambda x: f'col{x+1}')
    .reset_index()
)

If the dataset is large, use this version as pd.Series can be slow on large dataset.

(
    df.melt('col_name',var_name='NAME')
    .groupby(['col_name','NAME'])
    .value.apply(list)
    .pipe(lambda x: pd.DataFrame(index=x.index, data=x.tolist()))
    .rename(columns = lambda x: f'col{x+1}')
    .reset_index()
)

pandas reshaping/transform the dataframe

3 Answers3