inserting missing categories and dates in pandas dataframe

user_15924357 0
Viewed 90

I have the following data frame. I want to add in all score levels (high, mid, low), for each group (a, b, c, d), for all dates (there are two dates - 2020-06-01 and 2020-06-02)

x = pd.DataFrame(data={ 'date'  : ['2020-06-01','2020-06-01','2020-06-02','2020-06-01','2020-06-02','2020-06-01','2020-06-02','2020-06-02','2020-06-02'],
                        'group' : ['a','a','a','b','b','c','c','c','d'],
                        'score' : ['high','low','mid','low','high','high','high','mid','high'],
                        'count' : [12,13,2,19,22,3,4,49,12]})

I can add in the score categories for all subjects with the following, but i am having trouble adding date in as well

cats = ['high', 'mid','low'] 
x_re = pd.DataFrame(list(product(x['group'].unique(), cats)),columns=['group', 'score'])
x_re.merge(x, how='left').fillna(0)

the expected output would be this : so there are 6 rows per subject, 3 rows for each date, and one row for each score category. The count is then filled in with np.nan (or zero is fine) where the data points are missing

pd.DataFrame(data={ 'date'  : ['2020-06-01','2020-06-01','2020-06-01','2020-06-02','2020-06-02','2020-06-02','2020-06-01','2020-06-01','2020-06-01','2020-06-02','2020-06-02','2020-06-02','2020-06-01','2020-06-01','2020-06-01','2020-06-02','2020-06-02','2020-06-02','2020-06-01','2020-06-01','2020-06-01','2020-06-02','2020-06-02','2020-06-02'],                        
                        'group' : ['a','a','a','a','a','a','b','b','b','b','b','b','c','c','c','c','c','c','d','d','d','d','d','d'],
                        'score' : ['high','low','mid','high','low','mid','high','low','mid','high','low','mid','high','low','mid','high','low','mid','high','low','mid','high','low','mid'],
                        'count' : [12, 13, np.nan, np.nan, np.nan, 2, np.nan, 22, np.nan, 19, np.nan, np.nan, 3, np.nan, np.nan, 4, np.nan, np.nan, np.nan, np.nan, np.nan, 12, np.nan, 49]})

any advice would be great, thank you

pandas grouping dataframe python
3 Answers

Your solution is possible modify with add date columns by unique values, this solution working if not unique triples date, group, score in input data:

cats = ['high', 'mid','low'] 
x_re = pd.DataFrame(list(product(x['date'].unique(), 
                                 x['group'].unique(), 
                                 cats)),columns=['date','group', 'score'])
x = x_re.merge(x, how='left').fillna(0)

Solution with reindex by 3 level MultiIndex is similar:

cats = ['high', 'mid','low'] 
x_re = pd.MultiIndex.from_product([x['date'].unique(), 
                                   x['group'].unique(),
                                   cats],names=['date','group', 'score'])

x = x.set_index(['date','group','score']).reindex(x_re).reset_index()
print (x)
          date group score  count
0   2020-06-01     a  high   12.0
1   2020-06-01     a   mid    NaN
2   2020-06-01     a   low   13.0
3   2020-06-01     b  high    NaN
4   2020-06-01     b   mid    NaN
5   2020-06-01     b   low   19.0
6   2020-06-01     c  high    3.0
7   2020-06-01     c   mid    NaN
8   2020-06-01     c   low    NaN
9   2020-06-01     d  high    NaN
10  2020-06-01     d   mid    NaN
11  2020-06-01     d   low    NaN
12  2020-06-02     a  high    NaN
13  2020-06-02     a   mid    2.0
14  2020-06-02     a   low    NaN
15  2020-06-02     b  high   22.0
16  2020-06-02     b   mid    NaN
17  2020-06-02     b   low    NaN
18  2020-06-02     c  high    4.0
19  2020-06-02     c   mid   49.0
20  2020-06-02     c   low    NaN
21  2020-06-02     d  high   12.0
22  2020-06-02     d   mid    NaN
23  2020-06-02     d   low    NaN

With one call unstack and one call stack is possible use, but is necessary all unique values cats have to exist in input data:

x = (x.set_index(['date', 'group', 'score'])
      .unstack(['group','score'])
      .stack([1, 2], dropna=False)
      .reset_index())
print (x)
          date group score  count
0   2020-06-01     a  high   12.0
1   2020-06-01     a   low   13.0
2   2020-06-01     a   mid    NaN
3   2020-06-01     b  high    NaN
4   2020-06-01     b   low   19.0
5   2020-06-01     b   mid    NaN
6   2020-06-01     c  high    3.0
7   2020-06-01     c   low    NaN
8   2020-06-01     c   mid    NaN
9   2020-06-01     d  high    NaN
10  2020-06-01     d   low    NaN
11  2020-06-01     d   mid    NaN
12  2020-06-02     a  high    NaN
13  2020-06-02     a   low    NaN
14  2020-06-02     a   mid    2.0
15  2020-06-02     b  high   22.0
16  2020-06-02     b   low    NaN
17  2020-06-02     b   mid    NaN
18  2020-06-02     c  high    4.0
19  2020-06-02     c   low    NaN
20  2020-06-02     c   mid   49.0
21  2020-06-02     d  high   12.0
22  2020-06-02     d   low    NaN
23  2020-06-02     d   mid    NaN
user_29010020

An easy way when you don't have too many levels is to unstack/stack:

(x.set_index(['date', 'group', 'score'])
  .unstack('group').stack(dropna=False)
  .unstack('score').stack(dropna=False)
  .reset_index()
)

output:

          date group score  count
0   2020-06-01     a  high   12.0
1   2020-06-01     a   low   13.0
2   2020-06-01     a   mid    NaN
3   2020-06-01     b  high    NaN
4   2020-06-01     b   low   19.0
5   2020-06-01     b   mid    NaN
6   2020-06-01     c  high    3.0
7   2020-06-01     c   low    NaN
8   2020-06-01     c   mid    NaN
9   2020-06-01     d  high    NaN
10  2020-06-01     d   low    NaN
11  2020-06-01     d   mid    NaN
12  2020-06-02     a  high    NaN
13  2020-06-02     a   low    NaN
14  2020-06-02     a   mid    2.0
15  2020-06-02     b  high   22.0
16  2020-06-02     b   low    NaN
17  2020-06-02     b   mid    NaN
18  2020-06-02     c  high    4.0
19  2020-06-02     c   low    NaN
20  2020-06-02     c   mid   49.0
21  2020-06-02     d  high   12.0
22  2020-06-02     d   low    NaN
23  2020-06-02     d   mid    NaN
user_163434640

If I understand you correctly, you could abstract this with the complete function from pyjanitor:

# pip install pyjanitor
import pandas as pd
import janitor
x.complete(['date', 'group', 'score'])

          date group score  count
0   2020-06-01     a  high   12.0
1   2020-06-01     a   low   13.0
2   2020-06-01     a   mid    NaN
3   2020-06-01     b  high    NaN
4   2020-06-01     b   low   19.0
5   2020-06-01     b   mid    NaN
6   2020-06-01     c  high    3.0
7   2020-06-01     c   low    NaN
8   2020-06-01     c   mid    NaN
9   2020-06-01     d  high    NaN
10  2020-06-01     d   low    NaN
11  2020-06-01     d   mid    NaN
12  2020-06-02     a  high    NaN
13  2020-06-02     a   low    NaN
14  2020-06-02     a   mid    2.0
15  2020-06-02     b  high   22.0
16  2020-06-02     b   low    NaN
17  2020-06-02     b   mid    NaN
18  2020-06-02     c  high    4.0
19  2020-06-02     c   low    NaN
20  2020-06-02     c   mid   49.0
21  2020-06-02     d  high   12.0
22  2020-06-02     d   low    NaN
23  2020-06-02     d   mid    NaN
user_71757130
Related