KeyError: 'Requested level (date) does not match index name (None)'

user_10040873 0
Viewed 11884

I got error while reshaping my dataframe data.

KeyError: 'Requested level (date) does not match index name (None)'

More details are as below:

# dataframe

# print(df.head(3))
       
    
...
account_id    entity     ae     is_pc   is_new_customer agency  related_entity  type    medium   our_side_entity      settlement_title  settlement_short_title  settlement_type  system_value   account_status  date    sale
12323         entity1   ae1     PC        yes            MB                     EC     TWITTER   our_side_entity1    settlement_title   settlement_short_title      1                0.2          active    2020-07-01     jimmy 
12323         entity1   ae1     PC        yes            MB                     EC     GOOGLE    our_side_entity2    settlement_title   settlement_short_title      1                0.5          active    2020-07-02    jimmy
1037093       Bentity1  ae1     PC        yes            MB                     APP    Google    our_side_entity3    settlement_title   settlement_short_title      2                0            disable   2020-07-03     jimmy
1037093       Bentity1  ae1     PC        yes            MB                     APP    Google    our_side_entity3    settlement_title   settlement_short_title      2                                      2020-07-04     jimmy
1037093       Bentity1  ae1     PC        yes            MB                     APP    Google    our_side_entity3    settlement_title   settlement_short_title      2                                      2020-07-05      jimmy
...

Then I want group by account, date and sum the total system_value of the account. I tried with below codes but failed:


            indices = OrderedDict([
                ('account_id', 'ID'),
                ('entity', 'entity'),
                ('ae', 'AE'),
                ('is_pc', 'PC'),
                ('is_new_customer', 'new_customer'),
                ('agency', 'agency'),
                ('related_entity', 'related_entity'),
                ('type', 'type'),
                ('medium', 'medium'),
                ('our_side_entity', 'our_side_entity'),
                ('settlement_title', 'settlement_title'),
                ('settlement_short_title', 'settlement_short_title'),
                ('settlement_type', 'settlement_type'),
                ('account_status', 'account_status'),
                ('sale', 'sale'),
                ('date', 'date'),

            ])


            df = df.groupby(list(indices.keys())).system_value.sum() \
                .unstack('date', fill_value=None) \
                .assign(total=lambda x: x.sum(1)) \
                .reset_index()
            print(df)
            df = df.rename(columns=indices). \
                set_index(indices['account_id'])

error like below:

KeyError: 'Requested level (date) does not match index name (None)'

Could you please tell me what's wrong with my trial?

Thanks.

Update more details of my trial

Below codes can reproduce the error all the time

import pandas as pd
from collections import OrderedDict


s = [
    {'account_id': '123123213',
     'entity': 'entity2',
     'ae': 'ae1',
     'is_pc': 'PC',
     'is_new_customer': 'yes',
     'agency': 'BV',
     'related_entity': None,
     'type': 'EC',
     'medium': 'Facebook',
     'our_side_entity': 'our_side_entity',
     'settlement_title': 'settlement_title',
     'settlement_short_title': 'SS',
     'settlement_type': 'unknown',
     'system_value': None,
     'account_status': None,
     'date': '2020-07-22',
     'sale': 'sale1'},
]


indices = OrderedDict([
    ('account_id', 'ID'),
    ('entity', 'Entity'),
    ('ae', 'AE'),
    ('is_pc', 'PC'),
    ('is_new_customer', 'NEW_CUSTOMER'),
    ('agency', 'agency'),
    ('related_entity', 'related_entity'),
    ('type', 'type'),
    ('medium', 'medium'),
    ('our_side_entity', 'our_side_entity'),
    ('settlement_title', 'settlement_title'),
    ('settlement_short_title', 'settlement_short_title'),
    ('settlement_type', 'settlement_type'),
    ('sale', 'sale'),
    ('date', 'date'),
])

df = pd.DataFrame.from_records(s)
# print df.to_dict()

{'account_id': {0: '123123213'}, 'entity': {0: 'entity2'}, 'ae': {0: 'ae1'}, 'is_pc': {0: 'PC'}, 'is_new_customer': {0: 'yes'}, 'agency': {0: 'BV'}, 'related_entity': {0: None}, 'type': {0: 'EC'}, 'medium': {0: 'Facebook'}, 'our_side_entity': {0: 'our_side_entity'}, 'settlement_title': {0: 'settlement_title'}, 'settlement_short_title': {0: 'SS'}, 'settlement_type': {0: 'unknown'}, 'system_value': {0: None}, 'account_status': {0: None}, 'date': {0: '2020-07-22'}, 'sale': {0: 'sale1'}}

df = df.groupby(list(indices.keys())).system_value.sum() \
    .unstack('date', fill_value=None) \
    .assign(total=lambda x: x.sum(1)) \
    .reset_index()
indices["account_status"] = "status"
df = df.rename(columns=indices). \
    set_index(indices['account_id'])
print(df)
pandas python
1 Answers

You are grouping by columns with all none values. In your example, the value for related_entity is None, which leads to an empty dataframe:

In [7]: df.groupby(list(indices.keys())).sum()                                                                                                                                                                       
Out[7]: 
Empty DataFrame
Columns: []
Index: []

I suggest that you remove this column from the groupby clause

[EDIT]: to set the value of related_entity to the value of entity, you can simply do:

df['related_entity'] = df['entity']

Or assuming you have some values in it that you don't want to replace:

df['related_entity'] = df['related_entity'].fillna(df['entity'])
user_75716730
Related