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

2019-05-23T19:45:37+05:30

It is unfortunately not 100% correctly. I receive sometimes week 1, but still with the previous year. It can occur when 31.12 is Monday. Then you have incorrect values for this particular row. I would suggest to use this approach:
https://stackoverflow.com/questions/34597926/converting-daily-stock-data-to-weekly-based-via-pandas-in-python

2020-12-21T12:10:34+05:30

Agree… You gave a very good point of special case.

2020-12-22T06:48:37+05:30

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

2020-02-09T08:13:52+05:30

It is Wonderful. It is Great! Excellent!

	################################################################################################
	# 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 *')

	################################################################################################
	# 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 *')

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