anbento0490

#1. Install and load packages 

install.packages("rsdmx")
library(rsdmx)
library(dplyr)
library(ggplot2)

----------------------------------------------
#2. Get ECB EUR/GBP exchange rates

anbento0490

import pandas as pd
import yfinance as yf
orders = pd.read_csv('/Users/anbento/Documents/Data_Sets/Medium/customer_orders.csv', 
                     parse_dates = ['order_date'], index_col = ['order_date'] )

orders.insert(0, 'order_month', pd.DatetimeIndex(orders.index).to_period('M') )
orders.reset_index(inplace = True)
orders.columns = ['Order Date', 'Order Month' ,'Order ID', 'Customer ID', 'Item ID', 'Item Price', 
                  'Quantity', 'Amount Paid (£)'] 
print(orders.shape)

anbento0490

print(orders.groupby('Customer ID')['Order ID'].count())
orders.groupby('Customer ID')['Amount Paid (£)'].sum()

anbento0490

#1. ROW NUMBER() --> .RANK(method='first')
# Ranks orders over time by Custoner ID
orders['Row Num'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='first') 

# Sorts values by Customer ID and Order Date in ascending order
orders.sort_values(by= ['Customer ID','Order Date'], inplace = True)
orders = orders.reset_index(drop = True)
orders.head(20)

anbento0490

#2. RANK() --> RANK(method='min')
orders['Rank'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='min')
orders.head(10)

anbento0490

#3. DENSE_RANK() --> RANK(method='dense')
orders['Dense Rank'] = orders.groupby(['Customer ID'])['Order Date'].rank(method='dense')
orders.head(10)

anbento0490

#4.SUM(...) over(partition by ... order by .. rows unbounded preceding) --> CUMSUM()
orders["Run Tot (£)"] = orders.groupby(['Customer ID', 'Order Month'])['Amount Paid (£)'].cumsum()
orders.head(20)

anbento0490

orders["Run Avg (£)"] = orders.groupby(['Customer ID', 'Order Month'])['Amount Paid (£)'].transform('mean').round(1)
orders.head(20)

anbento0490

stocks['Lag1'] = stocks.groupby('Symbol')['Adj Close'].shift(-1)
stocks['Lag7'] = stocks.groupby('Symbol')['Adj Close'].shift(-7)
stocks.head(20)

anbento0490

stocks['Lag1 pct'] = stocks.groupby('Symbol')['Adj Close'].pct_change(-1).round(2)
stocks['Lag7 pct'] = stocks.groupby('Symbol')['Adj Close'].pct_change(-7).round(2)