Python Pandas: Count Quarterly Occurrence From Start And End Date Range

I have a dataframe of jobs for different people with star and end time for each job. I'd like to count, every four months, how many jobs each person is responsible for. I figured o

Solution 1:

This answer assumes that each job-person combination is unique. It creates a series for every row with the value equal to the job an index that expands the dates. Then it resamples every 4th month (which is not quarterly but what your solution describes) and counts the unique non-na occurrences.

defmake_date_range(x):
    return pd.Series(index=pd.date_range(x.start.values[0], x.end.values[0], freq='M'), data=x.job.values[0])

# Iterate through each job person combo and make an entry for each month with the job as the value
df1 = df.groupby(['job', 'person']).apply(make_date_range).unstack('person')

# remove outer level from index
df1.index = df1.index.droplevel('job')

# resample each month counting only unique values
df1.resample('4MS').agg(lambda x: len(x[x.notnull()].unique()))

Output

personp1p22015-01-01   112015-05-01   212015-09-01   112016-01-01   022016-05-01   012016-09-01   01

And here is a long one line solution that iterates over every rows and creates a new dataframe and stacks all of them together via pd.concat and then resamples.

pd.concat([pd.DataFrame(index = pd.date_range(tup.start, tup.end, freq='4MS'), 
                        data=[[tup.job]], 
                        columns=[tup.person])  for tup in df.itertuples()])\
  .resample('4MS').count()

And another one that is faster

df1 = pd.melt(df, id_vars=['job', 'person'], value_name='date').set_index('date')

g = df1.groupby([pd.TimeGrouper('4MS'), 'person'])['job']

g.agg('nunique').unstack('person', fill_value=0)