Jump to content

Pandas DataFrame Manipulation Issue: Calculating Monthly Average from Daily Data


Recommended Posts

I'm working on a data analysis project using Python and Pandas, and I'm facing an issue with manipulating a DataFrame that contains daily data. I have a DataFrame with two columns: date and value. I want to calculate the monthly average of the value column based on the daily data.

Here's a simplified version of my code:

import pandas as pd

# Sample data
data = {'date': ['2023-01-01', '2023-01-02', '2023-01-03', '2023-02-01', '2023-02-02'],
        'value': [10, 15, 20, 5, 8]}

df = pd.DataFrame(data)
df['date'] = pd.to_datetime(df['date'])

# Calculate monthly average
monthly_avg = df.resample('M', on='date').mean()

When I run this code, the monthly_avg DataFrame seems to have NaN values for all the rows. I suspect this is because I don't have data for every day in a month. Is there a way to calculate the monthly average even if I have missing days within a month? Or do I need to preprocess the data differently before calculating the monthly average? I have looked on other websites like this, but I was unable to find the answer. I would appreciate any advice on how to correctly compute the monthly average using Pandas from this daily data. I appreciate your help in advance.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...