I have dataframe comprising of datetime and float column. To illustrate, I am creating a sample dataframe from scratch as follows:-
#Creating the dataframe
import pandas as pd
data = {'Year': [1978, 1978, 1978, 1978, 1978, 1978], 'Month': [11, 11, 10, 10, 9, 9], 'Extent': [10.5, 11.8, 12.4, 13.3, 15.5, 16.7]}
df = pd.DataFrame(data)
df.head(6)
# Create datetime column under "Year-Month" from the "Year" and "Month" columns
df['Year-Month'] = pd.to_datetime(df[['Year', 'Month']].assign(Day=1))
df.head(6)
# Drop the "Year" and "Month" columns
cols = [0, 1]
df.drop(df.columns[cols], axis=1, inplace=True)
# Rearrange columns
df=df.reindex(columns= ['Year-Month', 'Extent'])
df.head(6)
# finding the type of data
df.dtypes
# Average the extent data by same Year-Month
df.groupby('Year-Month')
df.mean()
df.reset_index()
What I'am trying to do is to merge the 'Year-Month' with the same date. As for the respective data in the 'Extent' column, the float are to be average amount. However, the output I got was this:
But I would like the output to be like this:
Please advise. Thank you.
