How to transform rows into columns for my case?

Question

I have the following pandas DataFrame df:

SIGN        TYPE    TIME                         ADDITIONAL
ABC5245     10      2017-01-01 01:52:25.000      2017-01-01 01:39:04.000
ABC5245     20      2017-01-01 01:53:22.000      2017-01-01 02:39:04.000
DEF1111     20      2017-01-01 01:57:00.000      2017-01-01 03:39:04.000
DEF1111     10      2017-01-01 01:55:15.000      2017-01-01 01:39:04.000
AAA2222     10      2017-01-01 01:57:00.000      2017-01-01 01:39:04.000

I need to group data by SIGN and create four new columns based on TYPE: TIME_10, TIME_20, ADDITIONAL_10 and ADDITIONAL_20.

This is the expected result:

SIGN        TIME_10                  TIME_20                  ADDITIONAL_10             ADDITIONAL_20
ABC5245     2017-01-01 01:52:25.000  2017-01-01 01:53:22.000  2017-01-01 01:39:04.000   2017-01-01 02:39:04.000         
DEF1111     2017-01-01 01:55:15.000  2017-01-01 01:57:00.000  2017-01-01 01:39:04.000   2017-01-01 03:39:04.000
AAA2222     2017-01-01 01:57:00.000  NaN                      2017-01-01 01:39:04.000   NaN

Possible duplicate of [How to pivot a dataframe](https://stackoverflow.com/questions/47152691/how-to-pivot-a-dataframe) — Quang Hoang, Aug 04 '19 at 23:38

score 4 · Accepted Answer · answered Aug 04 '19 at 22:10

Use reshaping and flattening column header multiindex

df_out = df.set_index(['SIGN','TYPE']).unstack('TYPE')
df_out.columns = [f'{i}_{j}' for i, j in df_out.columns]
print(df_out)

Output:

                         TIME_10                  TIME_20  \
SIGN                                                        
AAA2222  2017-01-01 01:57:00.000                      NaN   
ABC5245  2017-01-01 01:52:25.000  2017-01-01 01:53:22.000   
DEF1111  2017-01-01 01:55:15.000  2017-01-01 01:57:00.000   

                   ADDITIONAL_10            ADDITIONAL_20  
SIGN                                                       
AAA2222  2017-01-01 01:39:04.000                      NaN  
ABC5245  2017-01-01 01:39:04.000  2017-01-01 02:39:04.000  
DEF1111  2017-01-01 01:39:04.000  2017-01-01 03:39:04.000

I may have more columns that should remain unchanged. Where should I specify that I only want to apply thins operation to `TIME` and `ADDITIONAL`? — ScalaBoy, Aug 05 '19 at 07:24

brentertainer · Answer 2 · 2019-08-05T13:19:26.707

You can get the result with a pivot. If you are okay with the columns being a MultiIndex, the second line is not needed.

Thanks @ScottBoston for the tip on column formatting.

df = df.pivot('SIGN', 'TYPE', ['TIME', 'ADDITIONAL'])                          
df.columns = df.columns.map('{0[0]}_{0[1]}'.format)

EDIT

In context:

import pandas as pd
data = [
    ['ABC5245', 10, '2017-01-01 01:52:25.000', '2017-01-01 01:39:04.000'],
    ['ABC5245', 20, '2017-01-01 01:53:22.000', '2017-01-01 02:39:04.000'],
    ['DEF1111', 20, '2017-01-01 01:57:00.000', '2017-01-01 03:39:04.000'],
    ['DEF1111', 10, '2017-01-01 01:55:15.000', '2017-01-01 01:39:04.000'],
    ['AAA2222', 10, '2017-01-01 01:57:00.000', '2017-01-01 01:39:04.000'],
]
columns = ['SIGN', 'TYPE', 'TIME', 'ADDITIONAL']

df = pd.DataFrame(data=data, columns=columns)
print(df)

df = df.pivot('SIGN', 'TYPE', ['TIME', 'ADDITIONAL'])
df.columns = df.columns.map('{0[0]}_{0[1]}'.format)
print(df)

Output:

      SIGN  TYPE                     TIME               ADDITIONAL
0  ABC5245    10  2017-01-01 01:52:25.000  2017-01-01 01:39:04.000
1  ABC5245    20  2017-01-01 01:53:22.000  2017-01-01 02:39:04.000
2  DEF1111    20  2017-01-01 01:57:00.000  2017-01-01 03:39:04.000
3  DEF1111    10  2017-01-01 01:55:15.000  2017-01-01 01:39:04.000
4  AAA2222    10  2017-01-01 01:57:00.000  2017-01-01 01:39:04.000
                         TIME_10                  TIME_20            ADDITIONAL_10            ADDITIONAL_20
SIGN
AAA2222  2017-01-01 01:57:00.000                      NaN  2017-01-01 01:39:04.000                      NaN
ABC5245  2017-01-01 01:52:25.000  2017-01-01 01:53:22.000  2017-01-01 01:39:04.000  2017-01-01 02:39:04.000
DEF1111  2017-01-01 01:55:15.000  2017-01-01 01:57:00.000  2017-01-01 01:39:04.000  2017-01-01 03:39:04.000

Another way to flatten column `df.columns = df.columns.map('{0[0]}_{0[1]}'.format)` vs using multiple `map` functions. — Scott Boston, Aug 04 '19 at 22:41
I get the error `ValueError: Index contains duplicate entries, cannot reshape`. — ScalaBoy, Aug 05 '19 at 07:26
@ScalaBoy I edited my answer to show how it works in context. Is `'SIGN'` your index in the original data? — brentertainer, Aug 05 '19 at 13:22

How to transform rows into columns for my case?

2 Answers2