import pandas as pd

from google.cloud import bigquery

def create$\_$time$\_$dimension$($df$)$:

$"""$Creates a time dimension table."""

$$ df$[\text{'}$created$\_$date'$]=$ pd$.$to$\_$datetime$($df$[\text{'}$created$\_$date'$],$ format$=\text{'}\%$Y$-\%$m$-\%$dT$\%$H:$\%$M:$\%$S$.\%$f$\text{'})$

$$ time$\_$dim $=$ df$[[\text{'}$created$\_$date'$]].$copy$()$

$$ time$\_$dim$[\text{'}$hour$\text{'}]=$ time$\_$dim$[\text{'}$created$\_$date'$].$dt$.$hour

$$ time$\_$dim$[\text{'}$minute$\text{'}]=$ time$\_$dim$[\text{'}$created$\_$date'$].$dt$.$minute

$$ time$\_$dim$[\text{'}$id$\text{'}]=$ time$\_$dim$[\text{'}$created$\_$date'$].$dt$.$strftime$(\text{'}\%$H$\%$M$\text{'}).$astype$($int$)$# Create unique ID from hour and minute

$$ time$\_$dim $=$ time$\_$dim$[[\text{'}$id$\text{'},$ 'hour', 'minute'$]].$drop$\_$duplicates$()$

$$ return time$\_$dim

def create$\_$date$\_$dimension$($df$)$:

$"""$Creates a date dimension table."""

$$ df$[\text{'}$created$\_$date'$]=$ pd$.$to$\_$datetime$($df$[\text{'}$created$\_$date'$],$ format$=\text{'}\%$Y$-\%$m$-\%$dT$\%$H:$\%$M:$\%$S$.\%$f$\text{'})$

$$ date$\_$dim $=$ df$[[\text{'}$created$\_$date'$]].$copy$()$

$$ date$\_$dim$[\text{'}$date$\text{'}]=$ date$\_$dim$[\text{'}$created$\_$date'$].$dt$.$date

$$ date$\_$dim$[\text{'}$year$\text{'}]=$ date$\_$dim$[\text{'}$created$\_$date'$].$dt$.$year

$$ date$\_$dim$[\text{'}$month$\text{'}]=$ date$\_$dim$[\text{'}$created$\_$date'$].$dt$.$month

$$ date$\_$dim$[\text{'}$day$\text{'}]=$ date$\_$dim$[\text{'}$created$\_$date'$].$dt$.$day

$$ date$\_$dim$[\text{'}$id$\text{'}]=$ date$\_$dim$[\text{'}$created$\_$date'$].$dt$.$strftime$(\text{'}\%$Y$\%$m$\%$d$\text{'}).$astype$($int$)$# Create unique ID from year, month, day

$$ date$\_$dim $=$ date$\_$dim$[[\text{'}$id$\text{'},$ 'date', 'year', 'month', 'day'$]].$drop$\_$duplicates$()$

$$ return date$\_$dim

def create$\_$complaint$\_$type$\_$dim$($df$)$:

$"""$Creates a complaint type dimension table."""

$$ complaint$\_$types $=$ df$[[\text{'}$complaint$\_$type', 'descriptor'$]].$drop$\_$duplicates$()$

$$ complaint$\_$types.reset$\_$index$($drop$=$True, inplace$=$True$)$

$$ complaint$\_$types.insert$(0,\text{'}$id$\text{'},$ complaint$\_$types.index $+1)$# Adding an ID column starting from $1$

$$ return complaint$\_$types

def create$\_$location$\_$type$\_$dim$($df$)$:

$"""$Creates a location type dimension table."""

$$ location$\_$types $=$ df$[[\text{'}$location$\_$type', 'borough'$]].$drop$\_$duplicates$()$

$$ location$\_$types.reset$\_$index$($drop$=$True, inplace$=$True$)$

$$ location$\_$types.insert$(0,\text{'}$id$\text{'},$ location$\_$types.index $+1)$# Adding an ID column starting from $1$

$$ return location$\_$types

def create$\_$agency$\_$dim$($df$)$:

$"""$Creates an agency dimension table."""

$$ agencies $=$ df$[[\text{'}$agency$\text{'},$ 'agency$\_$name'$]].$drop$\_$duplicates$()$

$$ agencies.reset$\_$index$($drop$=$True, inplace$=$True$)$

$$ agencies.insert$(0,\text{'}$id$\text{'},$ agencies.index $+1)$# Adding an ID column starting from $1$

$$ return agencies

def create$\_$complaint$\_$status$\_$dim$($df$)$:

$"""$Creates a complaint status dimension table."""

$$ statuses $=$ df$[[\text{'}$status$\text{'}]].$drop$\_$duplicates$()$

$$ statuses.reset$\_$index$($drop$=$True, inplace$=$True$)$

$$ statuses.insert$(0,\text{'}$id$\text{'},$ statuses.index $+1)$# Adding an ID column starting from $1$

$$ return statuses

def create$\_$complaint$\_$fact$\_$table$($df$,$ bq$\_$client, project$\_$id$,$ dataset$\_$id$)$:

$"""$Creates a complaint fact table and loads it to BigQuery.

$$ Args:

$$ df $($pandas$.$DataFrame$)$: The raw complaint data DataFrame.

$$ bq$\_$client $($bigquery$.$Client$)$: A BigQuery client object.

$$ project$\_$id $($str$)$: The Google Cloud Project ID$.$

$$ dataset$\_$id $($str$)$: The BigQuery dataset ID$.$

$"""$

$$ # Create dimension tables

$$ complaint$\_$type$\_$dim $=$ create$\_$complaint$\_$type$\_$dim$($df$)$

$$ location$\_$type$\_$dim $=$ create$\_$location$\_$type$\_$dim$($df$)$

$$ time$\_$dim $=$ create$\_$time$\_$dim$($df$)$

$$ agency$\_$dim $=$ create$\_$agency$\_$dim$($df$)$

$$ complaint$\_$status$\_$dim $=$ create$\_$complaint$\_$status$\_$dim$($df$)$

$$ # Create the fact table

$$ fact$\_$table $=$ df$[[\text{'}$Time$\text{'},$ 'date', 'complaint$\_$type', 'location$\_$type', 'agency', 'complaint$\_$status'$]]$

$$ fact$\_$table $=$ fact$\_$table.merge$($complaint$\_$type$\_$dim, on$=$'complaint$\_$type'$)$

$$ fact$\_$table $=$ fact$\_$table.merge$($location$\_$type$\_$dim, on$=$'location$\_$type'$)$

$$ fact$\_$table $=$ fact$\_$table.merge$($time$\_$dim, on$=$'created$\_$date'$)$

$$ fact$\_$table $=$ fact$\_$table.merge$($agency$\_$dim, on$=$'agency'$)$

$$ fact$\_$table $=$ fact$\_$table.merge$($complaint$\_$status$\_$dim, on$=$'complaint$\_$status'$)$

$$ # Load dimension tables and fact table to BigQuery

$$ for table$\_$name, table$\_$data in $[$

$(\text{'}$complaint$\_$type$\_$dim', complaint$\_$type$\_$dim$),$

$(\text{'}$location$\_$type$\_$dim', location$\_$type$\_$dim$),$

$(\text{'}$time$\_$dim', time$\_$dim$),$

$(\text{'}$agency$\_$dim', agency$\_$dim$),$

$(\text{'}$complaint$\_$status$\_$dim', complaint$\_$status$\_$dim$),$

$(\text{'}$complaint$\_$fact', fact$\_$table$)$

$]$:

$$ table$\_$id $=$ f$"\{$project$\_$id$\}.\{$dataset$\_$id$\}.\{$table$\_$name$\}"$

$$ job$\_$config $=$ bigquery.LoadJobConfig$($

$$ write$\_$disposition$=$"WRITE$\_$TRUNCATE"

$)$

$$ job $=$ bq$\_$client.load$\_$table$\_$from$\_$dataframe$($table$\_$data, table$\_$id$,$ job$\_$config$=$job$\_$config$)$

$$ job.result$()$# Wait for the job to complete

# Example usage

if $\_\_$name$\_\_=="\_\_$main$\_\_"$:

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Programming Questions!

Q:

*PYTHON COURSE* Can someone please help me answer this question for my class. My professor mentioned it using pandas and I honestly dont know what that is. There are a lot of pictures that I believe...

Q:

*PYTHON COURSE* Can someone please help me answer this question for my class. My professor mentioned it using pandas and I honestly dont know what that is. There are a lot of pictures that I believe...

Q:

Total Number of Wins by Average Points Scored 70 60 50 Total Number of Wins 40 30 20 10 85 90 95 100 105 110 Average Points Scored Correlation between Average Points Scored and the Total Number of...

Q:

Use the link in the Jupyter Notebook activity to access your Python script. Once you have made your calculations, complete this discussion. The script will output answers to the questions given...

Q:

Control Theory allows us to find various useful properties of a system such as stability. Draw a picture of a generic control system, explaining the functions of feedback and the design goals for the...

Q:

1:57 7 OneDrive Done Module Six Discussion Module Six Discussion: Multiple Regression This notebook contains the step-by-step directions for your Module Six discussion. It is very important to run...

Q:

Python Script : To complete the tasks listed below, open the Project Three Jupyter Notebook link in the Assignment Information module.This notebook contains your data set and the Python scripts for...

Q:

Python Script : To complete the tasks listed below, open the Project Three Jupyter Notebook link in the Assignment Information module.This notebook contains your data set and the Python scripts for...

Q:

import pandas as pd import os DATA_DIR = 'data' # -- covered this - this week - symbol_to_path() # -- provided - symbol to path --- # -- sample function ------------- # keep directory paths portable...

Q:

hello everyone I want the time complexity for this pseudocode and how we got it step by step with explanations also the best case and worst case of the algorithm in details """ Created on Wed Feb 1...

Q:

A small grocery' store needs the following number of cashiers during each day of a normal week for day (8 a.m. to 4 p.m.) and evening (4 p.m. to midnight) shifts. The store prefers to employ...

Q:

A cylindrical piece of steel 38 mm (1in.) in diameter is to be austenitized and quenched such that a microstructure consisting of at least 80% martensite will be produced throughout the entire piece....

Q:

describe the ways in which word choices affect meaning?

Q:

discuss S.M.A.R.T. framework

Previous Question Next Question