Group dates by month in Pandas

user_14659593 0
Viewed 121

I'm currently trying to organize data of avocado prices that was used in Sentdex data analysis video: https://www.youtube.com/watch?v=DamIIzp41Jg&list=PLQVvvaa0QuDfSfqQuee6K8opKtZsh7sA9&index=2

Here is the dataset that I am using: https://www.kaggle.com/neuromusic/avocado-prices

I want to group the dates for the state of California by the month to ultimately graph month with average price.

I've currently written the following code:

import pandas as pd

df = pd.read_csv(avocado.csv")

cali = pd.DataFrame()

region_df = df.copy()[ df['region'] == "California" ]


cali = region_df[["Date","AveragePrice"]]

M=["Jan",'Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']

cali = region_df[["Date","AveragePrice"]]

cali["Month"] = "NA"


cali.loc[cali.Date.str.contains('2015-01'), 'Month'] = M[0]

cali.set_index("Date", inplace=True)

cali.sort_index(inplace=True)

This is the output for the table:

table1

To do this for every month from 2015 to 2018 would be messy and tedious, I was wondering if there is a more efficient method to group dates by month.

pandas data-analysis python
3 Answers

I think you need DataFrame.resample, with convert Date to DatetimeIndex in read_csv if need plot year with months:

df = pd.read_csv("avocado.csv", index_col=['Date'], parse_dates=['Date'])

region_df = df[df['region'] == "California" ]
s = region_df.resample('MS').AveragePrice.mean()
print (s)

s.plot()

If need plot months like Jan, Feb...:

df = pd.read_csv("avocado.csv", index_col=['Date'], parse_dates=['Date'])

region_df = df[df['region'] == "California" ].copy()
region_df = region_df.sort_index()

s = region_df.groupby(region_df.index.strftime('%b'), sort=False).AveragePrice.mean()
print (s)
Date
Jan    1.218333
Feb    1.191875
Mar    1.281765
Apr    1.306923
May    1.263929
Jun    1.360000
Jul    1.502857
Aug    1.583462
Sep    1.626250
Oct    1.723929
Nov    1.527308
Dec    1.298846
Name: AveragePrice, dtype: float64

d.plot()
user_29010020

If you don't mind creating new columns on your data frame you can use this code:

import pandas as pd
from datetime import datetime

data = [
    {'Date': '2015-12-27', 'AveragePrice': 1.33, 'TotalVolume': 64236.62},
    {'Date': '2015-08-26', 'AveragePrice': 2.66, 'TotalVolume': 900},
    {'Date': '2015-08-5', 'AveragePrice': 2.34, 'TotalVolume': 900}
]
df = pd.DataFrame(data)

df['Date'] = df['Date'].apply(lambda x: datetime.strptime(x, '%Y-%m-%d'))
df['Month'] = df['Date'].apply(lambda x: x.month)
df['Year'] = df['Date'].apply(lambda x: x.year)

df.groupby(['Month', 'Year']).sum(['AveragePrice', 'TotalVolume'])

photo of result

user_146536270

You parse the column 'Date' data to the month in name by converting the datatype of 'Date' values to datetime64.

df = pd.read_csv("avocado.csv")
cali = pd.DataFrame()
region_df = df.copy()[ df['region'] == "California" ]
cali = region_df[["Date","AveragePrice"]]

# Convert column 'Date' values data type to dtype='datetime64[ns]'
cali['Month'] = pd.DatetimeIndex(cali['Date']).month_name()
print(cali.head())

#  Group them by month with mean value of 'AveragePrice'
grp = cali.groupby(cali['Month']).AveragePrice.mean()
print('\nGrouped Data:')
print(grp)
user_135238100
Related