Weekly Average CSV Conversion

2025-04-04 09:39 UTC o3-mini-high Open in ChatGPT ↗

You

I have a csv in python containing hourly data. I want to keep the structure unchanged, but use weekly averages instead of hourly data. then i save it in the same directory appending _WEEKLY

ChatGPT

Below is an example Python snippet using pandas that reads your hourly CSV, computes weekly averages, and then saves a new CSV file with the same structure (i.e. same columns) but with one row per week. In this example, it assumes you have a datetime column (named "datetime") that’s used to group the data; adjust the column name if needed.

import pandas as pd
import os

# Define the input CSV file path
file_path = 'your_file.csv'  # Replace with your actual CSV file path

# Read the CSV file and parse the datetime column
df = pd.read_csv(file_path, parse_dates=['datetime'])  # Adjust 'datetime' to your timestamp column name

# Set the datetime column as the index to facilitate resampling
df.set_index('datetime', inplace=True)

# Resample to weekly frequency and calculate the mean for each week
# Note: 'W' uses weeks ending on Sunday. You can adjust this if needed.
df_weekly = df.resample('W').mean()

# If you wish to have the datetime as a column again, reset the index
df_weekly.reset_index(inplace=True)

# Build the output filename by appending '_WEEKLY' before the file extension
base, ext = os.path.splitext(file_path)
output_path = base + '_WEEKLY' + ext

# Save the weekly averaged data to a new CSV file
df_weekly.to_csv(output_path, index=False)

print(f'Weekly averaged data saved to {output_path}')

How It Works:

• Reading and Parsing: The CSV is read with pd.read_csv and the datetime column is parsed.
• Indexing for Resampling: The datetime column is set as the DataFrame index so that pandas can easily group data by time periods.
• Weekly Averaging: The .resample('W').mean() function computes the average of all numeric columns for each week.
• Saving the File: The output file is saved with the original filename appended with _WEEKLY in the same directory.

Feel free to modify the code according to your CSV’s specific structure or your resampling needs.

You

I want options for daily average, bi-daily and tri-daily

ChatGPT

Below is an example Python snippet using pandas that gives you the option to resample your data on a daily, bi-daily (every 2 days), or tri-daily (every 3 days) basis. Just set the frequency_option variable to 'daily', 'bi-daily', or 'tri-daily' as needed.

import pandas as pd
import os

# Define the input CSV file path
file_path = 'your_file.csv'  # Replace with your actual CSV file path

# Read the CSV file and parse the datetime column
df = pd.read_csv(file_path, parse_dates=['datetime'])  # Adjust 'datetime' to your timestamp column name

# Set the datetime column as the index to facilitate resampling
df.set_index('datetime', inplace=True)

# Choose the frequency option: 'daily', 'bi-daily', or 'tri-daily'
frequency_option = 'daily'  # Change this value as needed

# Map the frequency option to a pandas resample rule
if frequency_option == 'daily':
    freq_rule = 'D'
elif frequency_option == 'bi-daily':
    freq_rule = '2D'
elif frequency_option == 'tri-daily':
    freq_rule = '3D'
else:
    raise ValueError("Invalid frequency option. Choose 'daily', 'bi-daily', or 'tri-daily'.")

# Resample to the chosen frequency and compute the mean for each period
df_resampled = df.resample(freq_rule).mean()

# If you want the datetime as a column again, reset the index
df_resampled.reset_index(inplace=True)

# Build the output filename by appending an indicator for the frequency option before the file extension
base, ext = os.path.splitext(file_path)
output_path = f"{base}_{frequency_option.upper()}{ext}"

# Save the resampled data to a new CSV file
df_resampled.to_csv(output_path, index=False)

print(f"Data resampled to {frequency_option} averages saved to {output_path}")

Explanation:

• Reading and Parsing: The CSV is read with pd.read_csv and the datetime column is parsed.
• Setting the Index: The datetime column is set as the DataFrame index so that pandas can easily resample the data.
• Choosing Frequency: Based on the value of frequency_option, a corresponding pandas resample rule is assigned:
  • Daily: 'D'
  • Bi-daily (every 2 days): '2D'
  • Tri-daily (every 3 days): '3D'
• Resampling: The .resample(freq_rule).mean() function computes the average of all numeric columns for each period.
• Saving the File: The output file is saved with the original filename appended with the frequency indicator (e.g., _DAILY) in the same directory.

This snippet keeps the original structure of your data (i.e. same columns) while aggregating the data into the desired time intervals.

You

reduce the file size further by rounding to 2 decimals

ChatGPT

Below is the updated code snippet that rounds the resampled data to 2 decimal places before saving:

import pandas as pd
import os

# Define the input CSV file path
file_path = 'your_file.csv'  # Replace with your actual CSV file path

# Read the CSV file and parse the datetime column
df = pd.read_csv(file_path, parse_dates=['datetime'])  # Adjust 'datetime' to your timestamp column name

# Set the datetime column as the index to facilitate resampling
df.set_index('datetime', inplace=True)

# Choose the frequency option: 'daily', 'bi-daily', or 'tri-daily'
frequency_option = 'daily'  # Change this value as needed

# Map the frequency option to a pandas resample rule
if frequency_option == 'daily':
    freq_rule = 'D'
elif frequency_option == 'bi-daily':
    freq_rule = '2D'
elif frequency_option == 'tri-daily':
    freq_rule = '3D'
else:
    raise ValueError("Invalid frequency option. Choose 'daily', 'bi-daily', or 'tri-daily'.")

# Resample to the chosen frequency, compute the mean for each period, and round to 2 decimals
df_resampled = df.resample(freq_rule).mean().round(2)

# Reset the index to include the datetime as a column again
df_resampled.reset_index(inplace=True)

# Build the output filename by appending an indicator for the frequency option before the file extension
base, ext = os.path.splitext(file_path)
output_path = f"{base}_{frequency_option.upper()}{ext}"

# Save the resampled and rounded data to a new CSV file
df_resampled.to_csv(output_path, index=False)

print(f"Data resampled to {frequency_option} averages and rounded to 2 decimals saved to {output_path}")

Explanation:

• Resampling and Averaging: The code groups the data by the specified frequency (daily, bi-daily, or tri-daily) and computes the mean for each group.
• Rounding: The .round(2) method rounds all numeric columns to 2 decimal places to help reduce the file size.
• Saving: The new CSV file is saved in the same directory with an updated filename indicating the frequency used.