python - 尝试使用 catch block 在 Pandas 中创建汇总计数

Question

我想创建一个汇总数据框，反射(reflect)跟踪和未跟踪框的数量。简单:

                       School - Exams Tracked    School - Exams Not Tracked
All Box Tracked Sites                    5820                             2

我们将在投递期间使用此报告，因此有时不会有跟踪的箱子，一段时间后所有箱子都会被跟踪。

现在，我的代码 (.get_loc(key)) 可能会收到一个关键错误，因为有时它会查找目前尚不存在的“TRACKED”。

这是我想出的最好的解决方案，但我觉得它很难看:

BoxTrackingSummary_df = pd.DataFrame()
BoxTrackingSummary_df_columns = ['School - Exams Tracked', 'School - Exams Not Tracked']

summary_group = pd.DataFrame(BoxTrackingReport_df.groupby('Tracked At A Site?').agg('count')['All Box Tracked Sites'])

# group.loc can only count groups that exist. plan for when there are no 'TRACKED' or no 'NO's, or receive a .get_loc(key) error
try:
    BoxTrackingSummary_df['School - Exams Tracked'] = summary_group.loc['TRACKED']
except:
    BoxTrackingSummary_df['School - Exams Tracked'] = 0
    print('No Tracked yet.')

try:
    BoxTrackingSummary_df['School - Exams Not Tracked'] = summary_group.loc['NO']
except:
    BoxTrackingSummary_df['School - Exams Not Tracked'] = 0
    print('All Tracked.')

这就是报告列“在站点跟踪？”的内容看起来像:

>>> BoxTrackingReport_df['Tracked At A Site?']
...
0       TRACKED
1       TRACKED
2       TRACKED
3       TRACKED
4       TRACKED

Answer 1

无需try/except或初始化空数据帧并从单独的groupby数据帧分配列。考虑直接从在站点跟踪？列(即系列)进行工作:

BoxTrackingSummary_df = (BoxTrackingReport_df['Tracked At A Site?'] 
                             .rename('All Box Tracked Sites')
                             .value_counts()
                             .to_frame()
                             .transpose()
                             .reindex(columns=['TRACKED', 'NO'])  
                             .fillna(0)
                             .set_axis(['School - Exams Tracked', 'School - Exams Not Tracked'], 
                                        axis='columns', inplace=False)
                        )

用随机种子数据进行演示

import numpy as np
import pandas as pd

np.random.seed(882019)
BoxTrackingReport_df = pd.DataFrame({'Tracked At A Site?': np.random.choice(['TRACKED', 'NO'], 500)})
...

print(BoxTrackingSummary_df)

#                        School - Exams Tracked  School - Exams Not Tracked
# All Box Tracked Sites                     251                         249

<小时/>

使用上面的reindex，代码始终确保两列都出现，无论它们是否在数据中(添加.fillna(0)

BoxTrackingReport_df = pd.DataFrame({'Tracked At A Site?': np.repeat(['TRACKED'], 500)})
...

print(BoxTrackingSummary_df)

#                        School - Exams Tracked  School - Exams Not Tracked
# All Box Tracked Sites                     500                         0.0