Transform pandas dataframe with binary columns to a more compact representation

Question

I have the following pandas dataframe:

import pandas as pd
import numpy as np

np.random.seed(0)
daytime = pd.date_range('2015-02-24', periods=6, freq='d')
df = pd.DataFrame({'DATE': ['2015-02-24', '2015-02-24', '2015-02-25', '2015-02-25', '2015-02-26', '2015-02-26'],  
                   'HappyCustomer': ['True', 'False','True', 'False','True', 'False'],
                   'HappyCustomerCount': [2, 4,1, 6, 2, 3] }) 

df.set_index('DATE', inplace=True)

df.head(6)


    HappyCustomer   HappyCustomerCount
DATE        
2015-02-24  True    2
2015-02-24  False   4
2015-02-25  True    1
2015-02-25  False   6
2015-02-26  True    2
2015-02-26  False   3

I think the column HappyCustomer is redundant, since it is always 'True' and 'False'. I would like to transform the column to sth. like this:

Solution:

        HappyCustomerCount_True  HappyCustomerCount_False
DATE        
2015-02-24      2                 4 
2015-02-25      1                 6
2015-02-26      2                 3

Optional:

In best case scenario I can do this transformation with (SQL related) functions like groupby since I have to do the same job later in SQL database.

How can I do this?

Shubham Sharma · Accepted Answer · 2020-07-05 07:59:31Z

3

Use, DataFrame.set_index along with DataFrame.unstack to reshape the dataframe finally use map with join to flatten the MultiIndex columns:

df1 = df.set_index('HappyCustomer', append=True).unstack()
df1.columns = df1.columns.map('_'.join)

Result:

# print(df1)

            HappyCustomerCount_False  HappyCustomerCount_True
DATE                                                         
2015-02-24                         4                        2
2015-02-25                         6                        1
2015-02-26                         3                        2

edited Jul 5, 2020 at 7:59

answered Jul 5, 2020 at 7:52

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Transform pandas dataframe with binary columns to a more compact representation

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related