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 exchanges. Let us see how to convert daily prices into weekly and monthly prices. You can download daily prices from NSE from [this link](https://www.nseindia.com/products/content/equities/equities/eq_security.htm). We will downoad daily prices for last 24 months. Here is the sample file with which we will work Please refer to below program to convert daily prices into weekly. Comments in the program will help you understand the logic behind each line. ```python ############################################################################################### # 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 ```python ################################################################################################ # 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 ***') ``` 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. Please do let me know your feedback.

This post is written by Pravin

Tags :

Share it :      

Leave a comment

captcha

by Sanjay Shirke on 2022-12-16

Thanks for the Python Code. It made my work easy


by Abhishek Banerjee on 2022-11-19

Does this automatically take care of the missing values? For eg: if Monday is not a trading day does this automatically use tuesday's opening price for Open?


by Naviin on 2022-07-17

Thanks Pravin for this, helps in a huge way! have a few more questions for you would love to connect. Let me know how that would be possible Grateful



Tags

Elsewhere

  1. GitHub
Ad below