Creating varaibles based on calculation outputs in pandas data frame

Question

I have a dataframe that looks like this:

df = pd.DataFrame( {'ID':range(1,366), 'No_of_Visits': np.random.randint(1,100, 365), 'Date':pd.date_range(pd.datetime.today(), periods=365).tolist() })

I want to count the number of visits for each three months, and return the result as a column to look like this:

df = ID, No_of_Visits, Date, Count_first _3_Month, Count_Second_3_Months, Count_third_3_Months, Count_forth_3_Months

Here is what I tried:

My idea is to extract the months so I did this

df['Month'] = df['Date'].dt.month

and then groupby month and sum:

df['monthly_count'] = df.groupby(['Month'])['No_of_Visits'].transform('sum')

I now got stuck becuase:

1- I wanted to have a parameter that controls for how many month can I count the number of visits (e.g 3 month, 6 month, etc)

2- how to return these counts and store them in new columns?

Any hints?

score 0 · Answer 1 · answered Jun 19 '17 at 16:06

Would something like this work for you?

df = df.set_index('Date')
df_3m = df.groupby(pd.TimeGrouper('3M', closed='left'))['No_of_Visits'].sum().reset_index().T
df_3m.columns = ['First 3 Months', 'Second 3 Months', 'Third 3 Months', 'Fourth 3 Months', 'Fifth 3 Months']

Output:

                   First 3 Months      Second 3 Months       Third 3 Months  \
Date          2017-08-31 00:00:00  2017-11-30 00:00:00  2018-02-28 00:00:00   
No_of_Visits                 3410                 4443                 4296   

                  Fourth 3 Months       Fifth 3 Months  
Date          2018-05-31 00:00:00  2018-08-31 00:00:00  
No_of_Visits                 4754                  996

Creating varaibles based on calculation outputs in pandas data frame

1 Answers1