Comparing the specific month from a datetime object in a dataframe using strftime()

Question

I am trying to use the strftime method to compare only the month from a row of a datetime object in a dataframe.

import datetime as dt

# Copy all months from quarters into new dataframe
new_df = pd.DataFrame(columns=['year','quarter','Value'])
df['Date'] = pd.to_datetime(df['Date'], errors='coerce')

cr=0

for i, rows in df.iterrows():
    if df.at[i,'Date'].strftime('%m') == '04':
        new_df.at[cr,'year'] = df.at[i, 'Date'].dt.strftime("%Y")
        new_df.at[cr,'quarter'] = 1
        new_df.at[cr,'Value'] = df.at[i, 'Value']
        cr = cr + 1
    elif df.at[i,'Date'].strftime('%m') == '07':
        new_df.at[cr,'year'] = df.at[i, 'Date'].strftime("%Y")
        new_df.at[cr,'quarter'] = 2
        new_df.at[cr,'Value'] = df.at[i, 'Value']
        cr = cr + 1
    elif df.at[i,'Date'].strftime('%m') == '10':
        new_df.at[cr,'year'] = df.at[i, 'Date'].strftime("%Y")
        new_df.at[cr,'quarter'] = 3
        new_df.at[cr,'Value'] = df.at[i, 'Value']        
        cr = cr + 1 
    elif df.at[i,'Date'].strftime('%m') == '01': 
        new_df.at[cr,'year'] = df.at[i, 'Date'].strftime("%Y")
        new_df.at[cr,'year'] = new_df.at[cr,'year'].asint() -1
        new_df.at[cr,'quarter'] = 4
        new_df.at[cr,'Value'] = df.at[i, 'Value']
        cr = cr + 1

I currently get the error: AttributeError: 'Timestamp' object has no attribute 'dt'

and if i remove the 'dt' i get the error: AttributeError: 'Series' object has no attribute 'strftime'

This is my sample data:

and this is a sample of my desired output:

Answer 1

I think what you are trying to do - based on the output - is to have the average value per quarter and year.

If that's the case, I think I reproduced your output - creating a new df where we assign the desired values and then 'groupby/transform' the 'value' column so it represents the mean per 'quarter' and 'year', and then drop all duplicates on the resulting newdf. Please let me know if that works:

newdf=pd.DataFrame()
newdf['year']=df['Date'].dt.year
newdf['quarter']=df['Date'].dt.quarter
newdf['value']=df['Value']
newdf['value']=newdf.groupby(['year','quarter'])['value'].transform(lambda x:x.mean())
newdf.drop_duplicates(subset=['year','quarter'],inplace=True)
newdf