In [1]:
import pandas as pd
In [2]:
# Get your data. Here I'm creating dummy data from a list
df = pd.DataFrame(data=[['1/1/2020', 175],
['1/2/2020', 225],
['1/3/2020', 267],
['1/4/2020', 337],
['1/5/2020', 384],
['1/6/2020', 90],
['1/7/2020', 144],
['1/8/2020', 155],
['1/9/2020', 208],
['1/10/2020', 303],
['1/11/2020', 285],
['1/12/2020', 342],
['1/13/2020', 91],
['1/14/2020', 136],
['1/15/2020', 176],
['1/16/2020', 229],
['1/17/2020', 230],
['1/18/2020', 284],
['1/19/2020', 312],
['1/20/2020', 101],
['1/21/2020', 148],
['1/22/2020', 173]], columns=['Date', 'Revenue'])
df['Date'] = pd.to_datetime(df['Date'])
In [3]:
df.head(5)
Out[3]:
In [4]:
# Holder list to store your values
monday_values = []
# Iterate through each row so you can find the last monday and store it's value
for i, row in df.iterrows():
# Get the day of the week you're row is
dayofweek = row['Date'].dayofweek
# Get the last monday. It's convicent that we can subtract the day of the week
# from the index and get the last monday.
last_monday = i-dayofweek
# If your last monday isn't on the dataset, then just append NA
if last_monday < 0:
monday_values.append(pd.NA)
else:
# If it is on the dataset, then take the revenue and store it on your holder list
monday_values.append(df.loc[last_monday]['Revenue'])
# Create a new column from your holder list
df['last_monday'] = monday_values
In [5]:
# Calculate your increase with your holder column
df['increase'] = df['Revenue'] / df['last_monday']
In [6]:
# Check out your results
df.head(10)
Out[6]:
