Pandas: calculate the std of total column value per "year"

Question

I have a data frame representing the customers checkins (visits) of restaurants. year is simply the year when a checkin in a restaurant happened .

• What i want to do is to add a column std_checkin to my initial Dataframe df that represents the standard deviation of visits per year. So, I need to calculate the standard deviation for the total visits per year.

data = {
        'restaurant_id':  ['--1UhMGODdWsrMastO9DZw', '--1UhMGODdWsrMastO9DZw','--1UhMGODdWsrMastO9DZw','--1UhMGODdWsrMastO9DZw','--1UhMGODdWsrMastO9DZw','--1UhMGODdWsrMastO9DZw','--6MefnULPED_I942VcFNA','--6MefnULPED_I942VcFNA','--6MefnULPED_I942VcFNA','--6MefnULPED_I942VcFNA'],
        'year': ['2016','2016','2016','2016','2017','2017','2011','2011','2012','2012'],
        }
df = pd.DataFrame (data, columns = ['restaurant_id','year'])

# total number of checkins per restaurant
d = df.groupby('restaurant_id')['year'].count().to_dict()
df['nb_checkin'] = df['restaurant_id'].map(d)


grouped = df.groupby(["restaurant_id"])
avg_annual_visits = grouped["year"].count() / grouped["year"].nunique()
avg_annual_visits = avg_annual_visits.rename("avg_annual_visits")
df = df.merge(avg_annual_visits, left_on="restaurant_id", right_index=True)

df.head(10)

From here, I'm not sure how to write what i want with pandas. If any clarifications needed, please ask.

Thank you!

Answer 1

I think you want to do:

counts = df.groupby('restaurant_id')['year'].value_counts()
counts.std(level='restaurant_id')

Output for counts, which is total visit per restaurant per year:

restaurant_id           year
--1UhMGODdWsrMastO9DZw  2016    4
                        2017    2
--6MefnULPED_I942VcFNA  2011    2
                        2012    2
Name: year, dtype: int64

And output for std

restaurant_id
--1UhMGODdWsrMastO9DZw    1.414214
--6MefnULPED_I942VcFNA    0.000000
Name: year, dtype: float64