Convert Daily data to Weekly data using Python Pandas

Photo by Hubble on Unsplash

Let’s jump straight to the point.

My manager gave me a bunch of files and asked me to convert all the daily data to weekly for data validation and modeling purpose. As usual, I said “Yes!! definitely”

The First Contact.

I downloaded all the files from the respective Google drive and I saw a bunch of huge files, which I was not able to open via Microsoft Excel. But no worries, I can use Python Pandas. Bingo! I was able to check all the files one by one and spent almost 3 to 4 hours for checking all the files individually ( including short and long breaks ). So far, so good.

The Mission.

My main focus was to identify the date column, rename/keep the name as “Date” and convert all the daily entries to weekly entries by aggregating all the metric values in that week to Wednesday of that particular week.

Let me give an example.

Sample file — Digital Marketing

I think the above image will give you an understanding of the file. We have a date ( daily data has entered ), channel, Impressions, Clicks and Spend. So the mission is to convert this data to weekly. But please note that, while converting into weekly, the values such as Impressions, Clicks and Spend should be aggregated.

Yeah!!! looks easy.

The Execution.

To keep it short, I tried different types of method and failed many times. Finally, my colleague told me to use the below method and I loved it. That’s why I decided to share it in a dramatic way.

Here is the solution :

#import required libraries
import pandas as pd
from datetime import datetime
#read the daily data file
paid_search = pd.read_csv("Digital_marketing.csv")
#convert date column into datetime object
paid_search['Day'] = paid_search['Day'].astype('datetime64[ns]')
#convert daily data to weekly
weekly_data = paid_search.groupby("Channel").resample('W-Wed', label='right', closed = 'right', on='Day').sum().reset_index().sort_values(by='Day')

In the last line in the code, you can see that I have represented the weekly date as Wednesday ( ‘W-Wed’) and aggregated the by adding all the 7 days ( including the Wednesday date) by label=’right’.

The cup of tea.

You will get more idea about the resample function by checking this page — https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.resample.html