Pair Programming #5: Values Relative To Previous Monday – Pandas Dates Fun

Youtube live link

Github Code

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]:
DateRevenue
02020-01-01175
12020-01-02225
22020-01-03267
32020-01-04337
42020-01-05384
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]:
DateRevenuelast_mondayincrease
02020-01-01175<NA><NA>
12020-01-02225<NA><NA>
22020-01-03267<NA><NA>
32020-01-04337<NA><NA>
42020-01-05384<NA><NA>
52020-01-0690901
62020-01-07144901.6
72020-01-08155901.72222
82020-01-09208902.31111
92020-01-10303903.36667