hha-data-ingestion-alice

HHA507 Data Science / Assignment 1

This repo aims to:

1. Find or create an excel (.xls) file that contains at least two tabs. Bring in the first tab as a data frame; label that dataset as ‘tab1’, and a second data frame that represents the 2nd tab of the excel file, name this 'tab2'

• hha-data-ingestion-alice/ingestion.py

  Lines 10 to 21 in 1e29573

  	## SECTION 1: Reading Excel Datasets from Kaggle using pandas
  	## consolidated 2 kaggle datasets into 1 excel file
  	## defining variables
  	## fixed unicode error in path using r and double quotes
  	xls = xlrd.open_workbook(r"C:\Users\Lcw62\hha-data-ingestion-alice\data\kaggle_data.xls", on_demand=True)
  	xls = pd.ExcelFile(r"C:\Users\Lcw62\hha-data-ingestion-alice\data\kaggle_data.xls") ##to read multiple excel sheets
  	##defining dataframes for visualization
  	tab1 = pd.read_excel(r"C:\Users\Lcw62\hha-data-ingestion-alice\data\kaggle_data.xls", sheet_name='tab1') #for sheet1
  	print (tab1) ##view output
  	tab2 = pd.read_excel(r"C:\Users\Lcw62\hha-data-ingestion-alice\data\kaggle_data.xls", sheet_name='tab2') #for sheet2
  	print (tab2) ##view output

2. Find 1 open source json API via CMS, and retrieve it using requests package ; call the dataset ‘apiDataset’

• data = requests.get('https://data.cms.gov/data-api/v1/dataset/ad73e4d3-925b-4055-ad9b-7f0015e906c8/data')
  data = data.json() 
  

• hha-data-ingestion-alice/ingestion.py

  Lines 24 to 31 in 1e29573

  	## SECTION 2: Retrieve open source json API from CMS website
  	## use requests to retrieve json API from CMS
  	apiDataset=requests.get('https://data.cms.gov/data-api/v1/dataset/b35c77ac-26e2-4320-91e1-ba71c4d7ff2c/data')
  	## load as json
  	apiDataset=apiDataset.json()
  	## load into dataframe for visualization
  	df = pd.DataFrame.from_records(apiDataset)
  	print(df) ## view output

3. Brings in 2 open source bigquery datasets

• Limit your query to get the first 100 rows from each, as either a dataframe or dictionary; please call the first dataset ‘bigquery1’ and the second dataset ‘bigquery2’;

• hha-data-ingestion-alice/ingestion.py

  Lines 34 to 46 in 1e29573

  	## SECTION 3: GCP BigQuery using google-cloud-bigquery
  	## create json key on GCP service account
  	## add json key to .gitignore
  	## connect to big query
  	client = bigquery.Client.from_service_account_json(r"C:\Users\Lcw62\hha-data-ingestion-alice\big query\section-3-360719-c5f4ca6c60ec.json")
  	## query public dataset
  	query_job = client.query("SELECT * FROM `bigquery-public-data.chicago_crime.crime` LIMIT 100") ## query public dataset
  	## get results
  	results = query_job.result()
  	## put results into dataframe for visualization
  	bigquery1 = pd.DataFrame(results.to_dataframe())
  	print(bigquery1) ## view output

Packages Used:

• import requests 
  import json
  import pandas as pd ## import pandas for general file types 
  from google.cloud import bigquery ## import bigquery for bigquery files
  import xlrd ## import xlrd for excel files, tab names 
  import openpyxl ## import openpyxl to read Excel 2010 .xlsx files
  import db_dtypes #import db_btypes to resolve section3 compatibility issues
  

Resources:

• Kaggle
• Healthdata.gov
• CMS
• Instructions for connecting to bigquery via a client (e.g., python)
• Helper YouTube Video that walks through how to create the special .json file to connect