diff options
Diffstat (limited to 'src/fact-purchase-table.py')
| -rw-r--r-- | src/fact-purchase-table.py | 66 |
1 files changed, 48 insertions, 18 deletions
diff --git a/src/fact-purchase-table.py b/src/fact-purchase-table.py index 91f5077..597f104 100644 --- a/src/fact-purchase-table.py +++ b/src/fact-purchase-table.py @@ -4,38 +4,68 @@ import json import boto3 import re import pandas as pd +from datetime import datetime as dt +import requests +from bs4 import BeautifulSoup -# iterates through each dataframe in the list of dataframes and assigns them to a variable -def get_dfs_from_dict(tables,dictionary=dict_of_df): - for table in tables: - df_staff = dict_of_df['staff'] ##no change - df_currency = dict_of_df['currency'] ##scraping API - df_counterparty = dict_of_df['counterparty'] - df_address = dict_of_df['address'] - df_department = dict_of_df['department'] - df_purchase_order = dict_of_df['purchase_order'] - ## dim_staff table is the same across the schemas (no change) ## dim_location from address --> drops 2 columns def create_dim_location(dict_of_df): - dim_location = dict_of_df['address'].drop(labels=['created_at', 'last_updated'], axis=1).rename(columns={'address_id': 'location_id'}) - return dim_location + df_loc = dict_of_df['address'].drop(labels=['created_at', 'last_updated'], axis=1).rename(columns={'address_id': 'location_id'}).set_index('location_id') + return df_loc ## dim_counterparty from address and counterparty def create_dim_counterparty(dict_of_df): df_prefixed_address = dict_of_df['address'].add_prefix('counterparty_legal_', axis=1) - pd.merge(dict_of_df['counterparty'], + df_cp = pd.merge(dict_of_df['counterparty'], df_prefixed_address, left_on="legal_address_id", right_on="address_id", - how="outer") + how="outer").set_index('counterparty_id') + return df_cp +## fact_purchase_order from purchase_order def create_fact_purchase_order(dict_of_df): df_po = dict_of_df['purchase_order'] df_po.index.name = 'purchase_record_id' - #df_po['create_date'] = df_po['create_at'].date() - #df_po['create_time'] = df_po['create_at'].time() - df_po['agreed_delivery_date'] = - df_po['agreed_payment_date']
\ No newline at end of file + df_po['created_date'] = df_po['created_at'].date() + df_po['created_time'] = df_po['created_at'].dt.time + df_po['last_updated_date'] = df_po['last_updated_at'].date() + df_po['last_updated_time'] = df_po['last_updated_at'].dt.time + df_po['agreed_delivery_date'] = pd.to_datetime(df_po['agreed_delivery_date'],format="%Y-%m-%d") + df_po['agreed_payment_date'] = pd.to_datetime(df_po['agreed_payment_date'],format="%Y-%m-%d") + df_po.drop(labels=['created_at','last_updated_at'],axis=1,inplace=True) + return df_po + +## dim_date from purchase_order +def create_dim_date(dict_of_df): + sr_date = pd.concat([df['created_date'],df['last_updated_date'],df['agreed_delivery_date'],df['agreed_payment_date']]).sort() + df_date = pd.DataFrame(sr_date,columns='date_id') + df_date['year'] = df_date['date_id'].dt.year + df_date['month'] = df_date['date_id'].dt.month + df_date['day'] = df_date['date_id'].dt.day + df_date['day_of_week'] = df_date['date_id'].dt.dayofweek + df_date['day_name'] = df_date['date_id'].dt.day_name + df_date['month_name'] = df_date['date_id'].dt.month_name + df_date['quarter'] = df_date['date_id'].dt.quarter + df_date.set_index('date_id') + +def scrape_currency_names(): + response = requests.get('https://www.xe.com/currency/').content + soup = BeautifulSoup(response,'html.parser') + currency = [item.text for item in soup.findAll('a', attrs={'class' : "sc-299dec64-6 fZPTSw"})] + sr = pd.Series(currency) + df_cur = sr.str.split(pat=" - ",expand=True).rename({0:'currency_code',1:'currency_name'},axis=1) + return df_cur + +def create_dim_currency(dict_of_df,names=scrape_currency_names()): + df_cur = dict_of_df['currency'].drop(labels=['created_at', 'last_updated'], axis=1) + dim_cur = pd.merge(df_cur,names,left_on='currency_code',right_on='currency_code',how='inner').set_index('currency_id') + return dim_cur + + + + + |