<jupyter_start><jupyter_text>In this notebook, we will provide instructions for launching a data wrangler job for preparing the data for Machine Learning The datasets required for completing the analysis of the entire source datasets are large. In order to demonstrate the use of Data Wrangler to prepare the data set in an expedited manner, we download and process sample data sets from the sources in CMS. You can use the same flow to process the entire datasets that you manually download from CMS and upload into S3 1. Import Libraries<jupyter_code>import numpy as np 
import pandas as pd
import boto3
import os
import sagemaker
import seaborn as sns
import matplotlib.pyplot as plt
import io
import sklearn
import string
import json
from sagemaker import get_execution_role<jupyter_output><empty_output><jupyter_text>2. Setup environment variables - store variables for use in the fraud identification step<jupyter_code>s3_client = boto3.client("s3")
session = sagemaker.Session()
role = get_execution_role()
bucket = session.default_bucket()
prefix = 'fraud-detect-demo'<jupyter_output><empty_output><jupyter_text>3. Ensure you upgrade your data wrangler application by following the instructions belowhttps://docs.aws.amazon.com/sagemaker/latest/dg/data-wrangler-update.html, 4. Download data files needed from CMS.  Download sample medicare part B files using the code below - It provides information on services and procedures provided to Medicare beneficiaries by physicians and other healthcare professionals.For downloading the entire dataset, go to https://data.cms.gov/provider-summary-by-type-of-service/medicare-physician-other-practitioners/medicare-physician-other-practitioners-by-provider-and-service, manually download the data set and upload into S3<jupyter_code>dict={
"2019": "5fccd951-9538-48a7-9075-6f02b9867868",
"2018":	"02c0692d-e2d9-4714-80c7-a1d16d72ec66",
"2017": "7ebc578d-c2c7-46fd-8cc8-1b035eba7218",
"2016": "5055d307-4fb3-4474-adbb-a11f4182ee35",
"2015": "0ccba18d-b821-47c6-bb55-269b78921637",
"2014":	"e6aacd22-1b89-4914-855c-f8dacbd2ec60",
"2013":	"ebaf67d7-1572-4419-a053-c8631cc1cc9b"
}
df = pd.DataFrame()
for key, value in dict.items():
     command = '-cO - https://data.cms.gov/data-api/v1/dataset/' + value + '/data' + '>' +  'medicareB_' + key + '.json'
     !wget $command
     file = 'medicareB_' + key + '.json'
     data = pd.read_json(f'{file}')
     df = df.append(data,  ignore_index=True)
df.to_csv('medicareB.csv',index = False, header = True)<jupyter_output><empty_output><jupyter_text>Download medicare part D files using the code below - It provides information on prescription drugs prescribed by individual physicians and other health care providers and paid for under the Medicare Part D Prescription Drug ProgramFor downloading the entire dataset, go to https://data.cms.gov/provider-summary-by-type-of-service/medicare-part-d-prescribers/medicare-part-d-prescribers-by-provider-and-drug, manually download the data set and upload into S3<jupyter_code>dict={
"2019": "5a27f7a8-c7af-434f-a26c-54db03e22cd1",
"2018":	"4861ecfc-a656-4dcd-accb-b9c3b840dfcb",
"2017": "04b93a42-c533-4e5c-8df9-a8f254886cde",
"2016": "0015c60c-af38-4d06-98bd-f058c0abb778",
"2015": "5da1b683-99ea-4734-8216-66ffdcd5e443",
"2014":	"2af61f9c-327c-4a23-8b7f-15e38b56e25a",
"2013":	"92d814bd-e2fb-48c2-95e7-a4b388a2c4be"
}
df = pd.DataFrame()
for key, value in dict.items():
    file = 'medicareD_' + key + '.json'
    command = '-cO - https://data.cms.gov/data-api/v1/dataset/' + value + '/data' + '>' +  file 
    !wget $command
    data = pd.read_json(f'{file}')
    df = df.append(data,  ignore_index=True)
df.to_csv('medicareD.csv',index = False, header = True)<jupyter_output><empty_output><jupyter_text>Download sample DMEPOS files using the code below - It provides information on DMEPOS products and services provided to Medicare beneficiaries ordered by physicians and other healthcare professionals.  For downloading the entire dataset, go to https://data.cms.gov/provider-summary-by-type-of-service/medicare-durable-medical-equipment-devices-supplies/medicare-durable-medical-equipment-devices-supplies-by-referring-provider-and-service, manually download the data set and upload into S3Note: DMEPOS files have columns that have been added over the years - When you download the main data set, make sure to append your data sets to the latest data set in order to maintain consistency. For this Data Wrangler flow, the input for DMEPOS is based on the 2019 file format, which is the latest available at this time.<jupyter_code>dict={
"2019": "d6d3de93-0579-408a-bcfe-c319f04069e7",
"2018":	"1c41ada1-2cb5-4808-95ef-757f5c237f4c",
"2017": "4ca364a5-89a3-4d9b-9a62-a4817f6c1464",
"2016": "3795f3a1-fa7a-4e7f-b4e4-0a3ba221311d",
"2015": "8f8fcdce-0924-4f5c-ab49-88e34d581f4c",
"2014":	"3f53a9ab-cc41-46d0-b37d-8799799425a7",
"2013":	"631d1c71-8af2-4d6e-912a-73f7954178ca"
}
df = pd.DataFrame()
for key, value in dict.items():
    file = 'dmepos_' + key + '.json'
    command = '-cO - https://data.cms.gov/data-api/v1/dataset/' + value + '/data' + '>' +  file 
    !wget $command
    data = pd.read_json(f'{file}')
    df = df.append(data,  ignore_index=True)
df.drop(columns = 'HCPCS_Cd', axis=1, inplace=True)
df.to_csv('dmepos.csv',index = False, header = True)<jupyter_output><empty_output><jupyter_text>Download the LEIE file using the code below. It provides information about all medical professionals or entities that are excluded from receiving Medicare reimbursement due to violations of sections 1128 or 1156 of the Social Security Act.  We use this dataset to identify fraudulent providers that have submitted for repayment in the above 3 datasets. You can also go to https://oig.hhs.gov/exclusions/exclusions_list.asp to manually download the latest file<jupyter_code>!wget -cO - 'https://oig.hhs.gov/exclusions/downloadables/UPDATED.csv' > leie.csv<jupyter_output><empty_output><jupyter_text>Upload files to S3 and note S3Uri for each file set<jupyter_code>s3_client.upload_file(
    Filename="medicareB.csv", Bucket=bucket, Key=f"{prefix}/data/raw/medicareB.csv"
)

print(f's3://{bucket}/{prefix}/data/raw/medicareB.csv')

s3_client.upload_file(
    Filename="medicareD.csv", Bucket=bucket, Key=f"{prefix}/data/raw/medicareD.csv"
)

print(f's3://{bucket}/{prefix}/data/raw/medicareD.csv')

s3_client.upload_file(
    Filename="dmepos.csv", Bucket=bucket, Key=f"{prefix}/data/raw/dmepos.csv"
)

print(f's3://{bucket}/{prefix}/data/raw/dmepos.csv')


s3_client.upload_file(
    Filename="leie.csv", Bucket=bucket, Key=f"{prefix}/data/raw/leie.csv"
)

print(f's3://{bucket}/{prefix}/data/raw/leie.csv')<jupyter_output><empty_output><jupyter_text>5. Remove intermediate files<jupyter_code>!rm medicare*.json
!rm dmepos*.json
!rm medicare*.csv
!rm dmepos.csv
!rm leie.csv<jupyter_output><empty_output><jupyter_text>6. Once you have the paths to the source files, open the data wrangler flow file "data_processing.flow" in "editor" mode by right clicking on the file and choosing Open With --> editor. Replace all S3Uri in the JSON with the S3Uri from above. Modify the type": "DESTINATION" output_path to the prefix in S3 where you want to store your output data. 7. Once that is done, save the flow by File --> Save and then open the file using Open With --> Flow. Data Wrangler will validate that all the changes you made are correct and you should have no errors shown. 8. Click the "Create Job" button on the top right and configure the job with the number and type of instances to execute the processing. Once you start the job, you can track the progress in the Sagemaker console9. After the job is complete, you can download the output data from S3 into this notebook and load the data into a dataframe for preview<jupyter_code>## Replace the fiile name with what you provided in the S3 DESTINATION in the flow
output_file = "fraud-detect-demo/data/data-processing-2022-02-10T10-07-34/part-00000-1653e8d0-26bf-46bf-b75d-9a16306ff5d2-c000.csv"
s3_client.download_file(Bucket=bucket, Key=output_file, Filename="sample_data.csv")
df_sample = pd.read_csv('sample_data.csv')
df_sample.head()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11127415015896307, 'fraction_numerical': 0.07263389581804842, 'mean_word_length': 5.177492447129909, 'pattern_counts': {'":': 22, '<': 37, '<?xml version=': 0, '>': 44, 'https://': 9, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 18, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '9', 'dir': 'jupyter-structured-clean-dedup', 'id': '371861', 'license': 'MIT-0', 'n_tokens_mistral': 3192, 'n_tokens_neox': 2844, 'n_words': 783, 'path': 'Data Preparation/data_load.ipynb', 'repo_name': 'SherryXDing/wwps-payment-integrity-solutions', 'size': '8178'}