Scenario

A CSV file contains daily stock price data for multiple companies that needs statistical analysis using rolling window calculations across different time periods.

Task

Write a Python script at /home/interview/calculate_rolling.py using pandas that reads /home/interview/stock_data.csv, calculates rolling statistics (mean, sum, standard deviation) for the close_price column using three different window sizes (7-day, 14-day, 30-day), rounds all calculated values to 2 decimal places, and saves the result to /home/interview/rolling_stats.csv.

Note: The dataset contains multiple stock tickers. Calculate rolling statistics separately for each ticker.

Example

Input (stock_data.csv):

date,ticker,close_price
2025-01-01,AAPL,180.50
2025-01-01,GOOGL,142.30
2025-01-02,AAPL,182.10
2025-01-02,GOOGL,143.20
...

Expected output (rolling_stats.csv):

date,ticker,close_price,rolling_mean_7d,rolling_sum_7d,rolling_std_7d,rolling_mean_14d,rolling_sum_14d,rolling_std_14d,rolling_mean_30d,rolling_sum_30d,rolling_std_30d
2025-01-01,AAPL,180.50,,,,,,,,,,
...
2025-01-07,AAPL,185.20,182.45,1277.15,2.34,,,,,,,
...
2025-01-14,AAPL,188.50,186.30,1304.10,2.51,184.20,2578.80,3.12,,,,
...

Step 1: Examine the input data

head -20 /home/interview/stock_data.csv

Review the structure with date, ticker, and close_price columns. Multiple tickers share the same dates.

Step 2: Create the Python script

nano /home/interview/calculate_rolling.py

Write a script using pandas groupby and rolling window functions:

import pandas as pd

# Read the CSV file
df = pd.read_csv('/home/interview/stock_data.csv')

# Group by ticker and calculate rolling statistics for each window size
for window in [7, 14, 30]:
    df[f'rolling_mean_{window}d'] = df.groupby('ticker')['close_price'].rolling(window=window).mean().reset_index(level=0, drop=True).round(2)
    df[f'rolling_sum_{window}d'] = df.groupby('ticker')['close_price'].rolling(window=window).sum().reset_index(level=0, drop=True).round(2)
    df[f'rolling_std_{window}d'] = df.groupby('ticker')['close_price'].rolling(window=window).std().reset_index(level=0, drop=True).round(2)

# Save to output CSV
df.to_csv('/home/interview/rolling_stats.csv', index=False)

print(f"Calculated rolling statistics for {len(df)} records across multiple tickers")

The groupby('ticker') ensures rolling calculations are done separately for each stock. The round(2) rounds all calculated values to 2 decimal places.

Step 3: Run the script

python3 /home/interview/calculate_rolling.py

Step 4: Verify the output

head -50 /home/interview/rolling_stats.csv

Each ticker will have its own rolling calculations with NaN values in early rows until the window is filled.