Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Python: Grouping by date and finding the average of a column inside a dataframe

Tags:

I have a data frame that has a 3 columns. Time represents every day of the month for various months. what I am trying to do is get the 'Count' value per day and average it per each month, and do this for each country. The output must be in the form of a data frame.

Curent data:

    Time    Country Count
 2017-01-01    us   7827
 2017-01-02    us   7748
 2017-01-03    us   7653
 ..
 ..
 2017-01-30    us   5432
 2017-01-31    us   2942
 2017-01-01    us   5829
 2017-01-02    ca   9843
 2017-01-03    ca   7845
 ..
 ..
 2017-01-30    ca   8654
 2017-01-31    ca   8534

Desire output (dummy data, numbers are not representative of the DF above):

    Time       Country   Monthly Average
 Jan 2017      us          6873
 Feb 2017      us          8875
 ..
 .. 
 Nov 2017      us          9614
 Dec 2017      us          2475
 Jan 2017      ca          1878
 Feb 2017      ca          4775
 ..
 .. 
 Nov 2017      ca          7643
 Dec 2017      ca          9441
like image 526
David Avatar asked Nov 12 '17 00:11

David


1 Answers

I'd organize it like this:

df.groupby(
    [df.Time.dt.strftime('%b %Y'), 'Country']
)['Count'].mean().reset_index(name='Monthly Average')

       Time Country  Monthly Average
0  Feb 2017      ca             88.0
1  Feb 2017      us            105.0
2  Jan 2017      ca             85.0
3  Jan 2017      us             24.6
4  Mar 2017      ca             86.0
5  Mar 2017      us             54.0

If your 'Time' column wasn't already a datetime column, I'd do this:

df.groupby(
    [pd.to_datetime(df.Time).dt.strftime('%b %Y'), 'Country']
)['Count'].mean().reset_index(name='Monthly Average')

       Time Country  Monthly Average
0  Feb 2017      ca             88.0
1  Feb 2017      us            105.0
2  Jan 2017      ca             85.0
3  Jan 2017      us             24.6
4  Mar 2017      ca             86.0
5  Mar 2017      us             54.0
like image 75
piRSquared Avatar answered Sep 23 '22 12:09

piRSquared