How to convert daily time series data into weekly and monthly using pandas and python

While working with stock market data, sometime we would like to change our time window of reference. Generally daily prices are available at stock exchenges. Let us see how to conert daily prices into weekly and monthly prices.

You can download daily prices from NSE from this link. We will downoad daily prices for last 24 months. Here is the sample file with which we will work

15-06-2016-TO-14-06-2018HDFCBANKALLN

Please refer to below program to convert daily prices into weekly. Comments in the program will help you understand the logic behind each line.

 

################################################################################################
# name: convert_daily_to_weekly.py
# desc: takes inout as daily prices and convert into weekly data
# date: 2018-06-15
# Author: conquistadorjd
################################################################################################
import pandas as pd
import numpy as np
print('*** Program Started ***')
df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')
# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']
# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Getting week number
df['Week_Number'] = df['Date'].dt.week
# Getting year. Weeknum is common across years to we need to create unique index by using year and weeknum
df['Year'] = df['Date'].dt.year
# Grouping based on required values
df2 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})
# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})
df2.to_csv('Weekly_OHLC.csv')
print('*** Program ended ***')

Once you understand daily to weekly, only small modification is needed to convert this into monthly OHLC data. Here is the script

 

################################################################################################
# name: convert_daily_to_monthly.py
# desc: takes inout as daily prices and convert into monthly data
# date: 2018-06-15
# Author: conquistadorjd
################################################################################################
import pandas as pd
import numpy as np
print('*** Program Started ***')
df = pd.read_csv('15-06-2016-TO-14-06-2018HDFCBANKALLN.csv')
# ensuring only equity series is considered
df = df.loc[df['Series'] == 'EQ']
# Converting date to pandas datetime format
df['Date'] = pd.to_datetime(df['Date'])
# Getting month number
df['Month_Number'] = df['Date'].dt.month
# Getting year. month is common across years (as if you dont know 🙂 )to we need to create unique index by using year and month
df['Year'] = df['Date'].dt.year
# Grouping based on required values
df2 = df.groupby(['Year','Month_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum'})
# df3 = df.groupby(['Year','Week_Number']).agg({'Open Price':'first', 'High Price':'max', 'Low Price':'min', 'Close Price':'last','Total Traded Quantity':'sum','Average Price':'avg'})
df2.to_csv('Monthly_OHLC.csv')
print('*** Program ended ***')

Here are the output files for your reference.

Monthly_OHLC Weekly_OHLC

Weekly_OHLC

I wasted some time to find ‘Open Price’ for weekly and monthly data. I tried some complex pandas queries and then realized same can be achieved by simply using aggregate function and Open Price:first.

Please do let me know your feedback.

4 Replies to “How to convert daily time series data into weekly and monthly using pandas and python”

    1. To resolve this issue, we can directly handle this specific special case like:

      date_range_frame[‘week’] = date_range_frame[‘date’].dt.week

      date_range_frame[‘year’] = date_range_frame[‘date’].dt.year

      date_range_frame[‘month’] = date_range_frame[‘date’].dt.month

      date_range_frame[‘quarter’] = date_range_frame[‘date’].dt.quarter

      initialize week-year to year first

      date_range_frame[‘week-year’] = date_range_frame[‘year’]

      incease the week-year by 1 for the special case

      date_range_frame.loc[(date_range_frame[‘month’] == 12) & (date_range_frame[‘week’] == 1), ‘week-year’] = date_range_frame[‘year’] + 1

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.