Pandas Group by reoccurring dates

user_9513316 0
Viewed 60

I am analysing a data set with pandas and I am trying to group Broadway shows together based on a date condition. Here is a simplified dataset of the problem

+-------------------+-------------+---------+-------+
|       Show        | Week_ending | Theatre | gross |
+-------------------+-------------+---------+-------+
| Mamma Mia         | 05/01/2020  | T1      |   100 |
| Mamma Mia         | 12/01/2020  | T1      |   200 |
| Mamma Mia         | 19/01/2020  | T1      |   150 |
| Shrek The Musical | 19/01/2020  | T2      |   100 |
| Mamma Mia         | 11/08/2019  | T3      |   100 |
| Mamma Mia         | 18/08/2019  | T3      |   100 |
| Mamma Mia         | 27/12/2009  | T1      |   100 |
| Mamma Mia         | 03/10/2010  | T1      |   100 |
| Mamma Mia         | 10/01/2010  | T1      |   100 |
+-------------------+-------------+---------+-------+

What I want to do is to group the shows together for further comparison. shows of the same name that are on in different theatres should be separated and I did this using

df2 = df.groupby(['show', 'theatre']).mean()

however this groups the items as so

+-------------------+---------+------------+
|       Show        | Theatre | mean_gross |
+-------------------+---------+------------+
| Mamma Mia         | T1      |        100 |
| Shrek The Musical | T2      |        100 |
| Mamma Mia         | T3      |        100 |
+-------------------+---------+------------+

This is a problem as it groups both Mamma Mia shows that happened in same theatre (T1) together even though they took place 10 years apart. The desired out come would be something like this (the (2009) is not necessarily essential as long as they are sperate)

+-------------------+---------+------------+
|       Show        | Theatre | mean_gross |
+-------------------+---------+------------+
| Mamma Mia         | T1      |        100 |
| Shrek The Musical | T2      |        100 |
| Mamma Mia         | T3      |        100 |
| Mamma Mia (2009)  | T1      |        100 |
+-------------------+---------+------------+

Essential if a show is in the same theatre but there is a week gap between them where no info is recorded they can be considered separate shows. just separating by year will not work as a show may run from November through March and splitting this into two separate shows does not make sense.

pandas python
1 Answers

Essentially what you need is to create another column that flags the cases where you have more than 7 days changes in the date.

import pandas as pd

df = pd.DataFrame({'Show':['Mamma Mia', 'Mamma Mia', 'Mamma Mia', 'Shrek The Musical',
                   'Mamma Mia', 'Mamma Mia', 'Mamma Mia', 'Mamma Mia', 'Mamma Mia'] , 
                   'Week_ending':['05/01/2020', '12/01/2020', '19/01/2020', '19/01/2020', '11/08/2019', '18/08/2019', '27/12/2009', '03/10/2010', '10/01/2010'],
                   'Theatre':['T1', 'T1', 'T1', 'T2', 'T3', 'T3', 'T1', 'T1','T1'],
                  'gross':[100, 200, 150, 100, 100, 100, 100, 100, 100]})

# Change `Week_ending` to datetime
df['Week_ending'] = pd.to_datetime(df['Week_ending'], format="%d/%m/%Y")

# sort
df = df.sort_values(['Show', 'Theatre', 'Week_ending'])

# find the difference in days between two consequitive dates within the same group
df['days_diff'] = df.groupby(['Show', 'Theatre'])['Week_ending'].diff().dt.days.fillna(0)
# Check if the days difference is more than 7 (days)
df['days_diff'] = df['days_diff'] > 7 

# create a key column that increment by 1 everytime the days are more than 7
df['key'] = df.groupby(['Show', 'Theatre'])['days_diff'].cumsum()

# resulting dataframe
df_final = df.groupby(['Show', 'Theatre', 'key'])[['gross']].mean().reset_index().drop(columns=['key'])

df_final

+-------------------+---------+------------+
|       Show        | Theatre | mean_gross |
+-------------------+---------+------------+
| Mamma Mia         | T1      |        100 |
| Mamma Mia         | T1      |        100 |
| Mamma Mia         | T1      |        100 |
| Mamma Mia         | T1      |        150 |
| Mamma Mia         | T3      |        100 |
| Shrek The Musical | T1      |        100 |
+-------------------+---------+------------+
user_19305430
Related