# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# +
# Includes 5 tables of which 4 are domains and 1 is performance
# Instead of creation 4 data frames we use dictionary of dataframes using comprehensions
# See below an example of comprehensions from https://towardsdatascience.com/dictionary-comprehensions-in-python-912a453da512

num_list = [1,1,7,3,5,3,2,9,5,1,3,2,2,2,2,2,9]
count_dict = {}
for num in num_list:
    count_dict[num] = num_list.count(num) # Create a key value pair
print(count_dict)
# {1: 3, 7: 1, 3: 3, 5: 2, 2: 6, 9: 2}

# -

# # Ok now back to healthcare dataset with HVBP 
#
# ![HVBP.jpeg](attachment:HVBP.jpeg)

# +
import pandas as pd
# df = pd.read_csv('./Datasets/Input2/ESRD_QIP_-_Complete_QIP_Data_-_Payment_Year_2018.csv',header=0)
#D:\\Stackroute\\Phillips\\Delivery Phillips\\Track 3B_Unit_F_ML in Healthcare\\Track 3B_Unit_F_Notebooks\\Datasets\\Input2
pathname = 'D:\\Stackroute\\Phillips\\Delivery Phillips\\Track 3B_Unit_F_ML in Healthcare\\Track 3B_Unit_F_Notebooks\\Datasets\\Input2\\'

files_of_interest = [
    'hvbp_tps_11_07_2017.csv', 
    'hvbp_clinical_care_11_07_2017.csv',
    'hvbp_safety_11_07_2017.csv',
    'hvbp_efficiency_11_07_2017.csv',
    'hvbp_hcahps_11_07_2017.csv'
]

dfs = {
    foi: pd.read_csv(pathname + foi, header=0) for foi in files_of_interest
}


# +
#Let us now see the dataframe
# -

dfs

# +

# This is the combined dictionary of dataframes - Uncomment to run the cell above.
# Note all 5 files from Hvbp
# -

#Demo of items() function used below - it returns list of tuple pairs
# Recall: A tuple is a sequence of immutable Python objects. Tuples are sequences, just like lists. 
#The differences between tuples and lists are, the tuples cannot be changed unlike lists and tuples use parentheses,
# whereas lists use square brackets.
dict = {'Name': 'Zara', 'Age': 7}
print ("Value : %s" %  dict.items())

for k, v in dfs.items():
    print(
        k + ' -  Number of rows: ' + str(v.shape[0]) + 
        ', Number of columns: ' + str(v.shape[1]) 
    )

for v in dfs.values(): # We loop to find the common column ID just like SQL JOIN
    for column in v.columns:
        print(column)
    print('\n')
        

# # Observation: Looking at the 5 tables above carefully we can see that 'Provider Number' is common to all tables

# +
df_master = dfs[files_of_interest[0]].merge(
    dfs[files_of_interest[1]], 
    on='Provider Number', 
    how='left',
    copy=False
)

print(df_master.shape)

# +
# See initial output of cell 3
#hvbp_tps_11_07_2017.csv -  Number of rows: 2808, Number of columns: 16
#hvbp_clinical_care_11_07_2017.csv -  Number of rows: 2808, Number of columns: 28

# If the merge is a success we should have 16+ 28 Minus 1 rows = 44 -1 = 43 rows
# -

# # Observation: The merge of tps & clinical care table is now correct

print(df_master.columns) # Columns of the newly joined data frames

# # Now Let us join the remaining 3 tables + rename Provider_Number to Provider Number for a clean JOIN

# +
for df in dfs.values():
    df.columns = [col if col not in ['Provider_Number'] else 'Provider Number' for col in df.columns]

for num in [2,3,4]:
    df_master = df_master.merge(
        dfs[files_of_interest[num]],
        on='Provider Number',
        how='left',
        copy=False
    )
        
print(df_master.shape)
# -

# # Observation: We can again check that sum of 5 tables minus 4 rows matches

for column in df_master.columns:
    print(column)

# # We can continue to do same kind of analysis now as we did for Dialysis center example. Some are shown

# Check for MSPB-1 Measure Score 
print(df_master.groupby('MSPB-1 Measure Score').size())

#Weighted Normalized Clinical Care Domain Score
print(df_master.groupby('Weighted Normalized Clinical Care Domain Score').size())

print(df_master.groupby('Weighted Normalized Clinical Care Domain Score').size().sort_values(ascending=False))

print(df_master.head(n=1))

print(df_master.groupby('MSPB-1 Measure Score').size().sort_values(ascending=False))


StarcoderdataJupyterScriptsDedupFiltered