Pandas: Groupby dataframe and create dicts with missing data

Question

Below is my df:

   In [78]: df
Out[78]: 
   site        date     race  count
0     1  1999-01-31    Asian    100
1     1  1999-01-31  African     25
2     2  1999-01-31    Asian    200
3     1  2001-01-21    Asian     95
4     2  2001-01-21    Asian    130
5     1  2003-01-12    Asian     80
6     2  2003-01-12  Mexican     35

I want to group the above on race and date and create an output like below:

Expected:

{
    "dates":[
    "1999-01-31",
    "2001-01-21",
    "2003-01-12"
    ]
},
{
    "race": "Asian"
    "data": [
    300,
    225,
    80
    ]
},
{
    "race": "African"
    "data": [
    25,
    0,
    0
    ]
},
{
    "race": "Mexican"
    "data": [
    0,
    0,
    35
    ]
}

My attempt:

In [77]: df.groupby(['race', 'date'])['count'].sum().reset_index(level=1)
Out[77]: 
               date  count
race                      
African  1999-01-31     25
Asian    1999-01-31    300
Asian    2001-01-21    225
Asian    2003-01-12     80
Mexican  2003-01-12     35

I could groupby to get the above, but not sure how to create my expected output.

jezrael · Accepted Answer · 2021-03-18 09:46:27Z

2

Here are processing dates different like another values, so first is used pivoting by DataFrame.pivot_table and then list comprehension with custom format:

df = df.pivot_table(index='date',columns='race',values='count',fill_value=0, aggfunc='sum')

L = [{"dates": list(df.index)}] + [dict(race=k, data=list(v)) for k, v in df.items()]
print (L)
[{'dates': ['1999-01-31', '2001-01-21', '2003-01-12']}, 
 {'race': 'African', 'data': [25, 0, 0]}, 
 {'race': 'Asian', 'data': [300, 225, 80]},
 {'race': 'Mexican', 'data': [0, 0, 35]}]

answered Mar 18, 2021 at 9:46

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

Collectives™ on Stack Overflow

Pandas: Groupby dataframe and create dicts with missing data

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