Counting total occurences and occurences by category based on date ranges

user_15330859 0
Viewed 71

In the table below, the Start and End dates represent a period of time in which a unique identifyer is counted. My goal is count the number of times the unique identifyer falls between the date, but show it on a monthly basis. I'd also like to count the number of times that a category falls in that date range.

I'm new at working with table data and pandas so I'm a bit lost. Thanks a lot in advance for the help.

Example input data:

Start Date End Date Unique Identifyer Category
2019-04-17 2020-04-17 ID 1234 A
2019-05-20 2021-04-03 ID 3492 B
2019-05-20 2021-04-03 ID 7376 C
2019-04-18 2021-04-03 ID 9813 A
2019-06-20 2021-04-03 ID 6342 A
2019-06-20 2021-04-03 ID 6455 B
2019-07-20 2021-04-03 ID 6342 A
2019-06-20 2021-04-03 ID 6455 B
etc... etc... etc... etc...

example of output:

Date Total_Vol count_A count_B count_c
Apr-2019 2 2 0 0
May-2019 4 2 1 1
Jun-2019 7 3 3 1
Jul-2019 8 4 3 1
pandas datetime python
1 Answers

First i'd recommend to split the date column in two distincts columns year and month so that you can group by them.

df = (pd.DataFrame(records, columns=['start', 'end', 'id', 'cat'])
         .astype({'start':'datetime64', 'end':'datetime64'})
         .assign(year=lambda x: x['start'].dt.year)
         .assign(month=lambda x: x['start'].dt.month))

enter image description here

Then you can explode the cat column to facilitate the computation

df_cats = (pd
 .get_dummies(df['cat'], prefix='count')
 .assign(total = lambda r: r['count_A']+r['count_B']+r['count_C']))

You'll get

enter image description here

Now you just have to merge both dfs and to use groupby.sum() to get the result

pd.merge(df, df_cats, left_index=True, right_index=True).groupby(['year', 'month'].sum()

You'll end up with

enter image description here

user_37464180
Related