How to count number of occurrences per day over a large data set?

Question

I have a dataset that looks something like this but much larger, over 1000 unique products:

|   Hour   |  Date    || Pallet ID| PRODUCT  || Move Type|
| -------- | -------- || -------- | -------- || -------- |
| 1 PM     | 10/01    || 101      | Shoes    || Storage  |
| 1 PM     | 10/01    || 202      | Pants    || Load     |
| 1 PM     | 10/01    || 101      | Shoes    || Storage  |
| 1 PM     | 10/01    || 101      | Shoes    || Load     |
| 1 PM     | 10/01    || 202      | Pants    || Storage  |
| 3 PM     | 10/01    || 202      | Pants    || Storage  |
| 3 PM     | 10/01    || 101      | Shoes    || Load     |
| 3 PM     | 10/01    || 202      | Pants    || Storage  |`

What I want to do is create a new table that looks like this:


|   Hour   |  Date    || Pallet ID| PRODUCT  || Move Type| Total Moves |
| -------- | -------- || -------- | -------- || -------- | -------- |
| 1 PM     | 10/01    || 101      | Shoes    || Storage  |    2     |
| 1 PM     | 10/01    || 101      | Shoes    || Load     |    1     |
| 1 PM     | 10/01    || 202      | Pants    || Load     |    1     |
| 1 PM     | 10/01    || 202      | Pants    || Storage  |    1     |
| 3 PM     | 10/01    || 101      | Shoes    || Load     |    1     |
| 3 PM     | 10/01    || 202      | Pants    || Storage  |    2     |

Here is my attempt at doing this. This cannot be the correct way as this takes hours to fully run completely. Is there any way of doing this better than I am currently?


listy = df['PROD_CODE'].unique().tolist()
calc_df = pd.DataFrame()
count = 0
for x in listy:
    new_df = df.loc[df['PROD_CODE'] == x]
    dates = new_df['Date'].unique().tolist()
    count = count + 1
    print(f'{count} / {len(listy)} loops have been completed')
    for z in dates:
        dates_df = new_df[new_df['Date'] == z]
        hours = new_df['Hour'].unique().tolist()
        for h in hours:
            hours_df = dates_df.loc[new_df['Hour'] == h]
            hours_df[['Hour','Date','PALLET_ID','PROD_CODE','CASE_QTY','Move Type']]
            hours_df['Total Moves'] = hours_df.groupby('Move Type')['Move Type'].transform('count')
            calc_df = calc_df.append(hours_df,ignore_index=False)

score 0 · Answer 1 · answered Dec 02 '22 at 23:06

You should be able to use df.groupby() with .size() to get the counts for moves of the same date/time/pallet id/product/move type.

df.groupby(['Hour','Date','PALLET_ID','PROD_CODE','CASE_QTY','Move Type']).size().reset_index(name='Total Moves')

Source: Get statistics for each group (such as count, mean, etc) using pandas GroupBy?

How to count number of occurrences per day over a large data set?

1 Answers1