Find sum of each month Pandas

Question

df = pd.read_csv("wind_data.csv")
df = df[['SETTLEMENTDATE', 'wind']].copy()

dataset = df.set_index("SETTLEMENTDATE")
dataset.index = pd.to_datetime(dataset.index)
print(dataset.head())
print(dataset.shape)

Dataset

In this dataset I want to calculate wind data for each month. (I need only 12 rows of this data set instead 105350)

Can you please help me?

Answer 1

One way using a groupby:

df = pd.read_csv("wind_data.csv")
df = df[['SETTLEMENTDATE', 'wind']].copy()

dataset['SETTLEMENTMONTH'] = pd.to_datetime(dataset['SETTLEMENTDATE']).dt.floor('M')

dataset.groupby('SETTLEMENTMONTH')['wind'].sum()

Answer 2

Use DataFrame.resample:

dataset.resample('M')['wind'].sum()