value_counts unstack with additional categories

user_5120812 0
Viewed 109

I have this DataFrame:

data = [{'name':'John', 'date':20210801, 'work':False}, {'name':'John', 'date':20210802, 'work':True}, {'name':'Lucy', 'date':20210801, 'work':False}]
df = pd.DataFrame(data)
df.groupby(['name'])['work'].value_counts(normalize=True).unstack(fill_value=0).stack().reset_index()

So the input df and output df is like:

   name      date   work
0  John  20210801  False
1  John  20210802   True
2  Lucy  20210801  False

   name   work    0
0  John  False  0.5
1  John   True  0.5
2  Lucy  False  1.0
3  Lucy   True  0.0

Now if I change all of John's work to be False, and run the same code, the "True" row no longer shows up because no entry has "True" value.

data = [{'name':'John', 'date':20210801, 'work':False}, {'name':'John', 'date':20210802, 'work':False}, {'name':'Lucy', 'date':20210801, 'work':False}]
df = pd.DataFrame(data)
df.groupby(['name'])['work'].value_counts(normalize=True).unstack(fill_value=0).stack().reset_index()


   name      date   work
0  John  20210801  False
1  John  20210802  False
2  Lucy  20210801  False

   name   work    0
0  John  False  1.0
1  Lucy  False  1.0

How can I make the output df keep the original structure, i.e. still have the "True" row with 0.0 value? I was able to achieve this by adding a "dummy" row but it looks ugly. Is there a better way?

df = pd.DataFrame([*data, {'name':'dummy', 'work':True}])
pandas dataframe python
1 Answers

We can use normalized crosstab and reindex to ensure that there are both True and False columns then stack:

output_df = (
    pd.crosstab(df['name'], df['work'], normalize='index')
        .reindex(columns=[True, False], fill_value=0)
        .stack()
        .reset_index()
)

Or more in-line with the initial approach reindex after unstack:

output_df = (
    df.groupby(['name'])['work'].value_counts(normalize=True)
        .unstack(-1)
        .reindex(columns=[True, False], fill_value=0)
        .stack()
        .reset_index()
)

output_df:

   name   work    0
0  John   True  0.0
1  John  False  1.0
2  Lucy   True  0.0
3  Lucy  False  1.0

Initial DataFrame and imports:

import pandas as pd

df = pd.DataFrame({
    'name': ['John', 'John', 'Lucy'],
    'date': [20210801, 20210802, 20210801],
    'work': [False, False, False]
})
user_154978880
Related