In [44]:
#Import Libraries

import pandas as pd
import numpy as np
pd.set_option('display.max_columns', None)
import matplotlib.pyplot as plt
In [54]:
#Connect python to MS Access for 2010 - 2013 data

import pyodbc 

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\user1\Documents\muriyo\gmu\hap725\week5\q4\data\Hospital_20150122.mdb;')
cursor = conn.cursor()
#cursor.execute('select * from HQI_HOSP_AMI_Payment')

query = ('SELECT * FROM HQI_HOSP_AMI_Payment')

df = pd.read_sql(query, conn)
conn.close()

#Now we replace any empty strings in the Denominator column with np.nan 
df['Denominator'].replace('', np.nan, inplace=True)  

df.head(5)
Out[54]:
Provider ID Hospital Name Measure Name Measure ID Compared to National Denominator Payment Lower Estimate Higher Estimate Footnote Measure Start Date Measure End Date
0 010001 SOUTHEAST ALABAMA MEDICAL CENTER Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 642 21423 20362 22606 None 07/01/2010 06/30/2013
1 010005 MARSHALL MEDICAL CENTER SOUTH Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 40 20614 17854 23776 None 07/01/2010 06/30/2013
2 010006 ELIZA COFFEE MEMORIAL HOSPITAL Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 290 20297 18894 21837 None 07/01/2010 06/30/2013
3 010007 MIZELL MEMORIAL HOSPITAL Payment for heart attack patients PAYM_30_AMI Number of Cases Too Small Not Available Not Available Not Available Not Available 1 07/01/2010 06/30/2013
4 010008 CRENSHAW COMMUNITY HOSPITAL Payment for heart attack patients PAYM_30_AMI Number of Cases Too Small Not Available Not Available Not Available Not Available 1 07/01/2010 06/30/2013
In [55]:
#List unique values in the df['Denominator'] column
# Note that you see 'Not Available' and 'None' in the printout.  These need to be excluded

df.Denominator.unique()
Out[55]:
array(['642', '40', '290', 'Not Available', '234', '107', '210', '45',
       '283', '243', '263', '242', '95', '60', '1035', '204', '97', '25',
       '38', '329', '255', '56', '389', '80', '73', '31', '65', '208',
       '384', '32', '171', '28', '292', '287', '249', '92', '163', '170',
       '273', '121', '86', None, '33', '49', '279', '74', '94', '103',
       '176', '366', '130', '150', '174', '305', '451', '88', '162',
       '129', '214', '47', '169', '143', '164', '383', '69', '201', '260',
       '146', '41', '188', '183', '190', '385', '125', '71', '105', '70',
       '27', '101', '151', '36', '295', '158', '219', '109', '59', '364',
       '346', '218', '324', '202', '303', '136', '438', '147', '72',
       '116', '149', '466', '175', '253', '436', '187', '185', '99',
       '369', '68', '186', '205', '44', '66', '115', '52', '178', '82',
       '135', '206', '43', '680', '46', '198', '401', '145', '137', '148',
       '84', '83', '328', '154', '497', '266', '35', '216', '349', '98',
       '26', '67', '51', '141', '61', '180', '357', '229', '119', '387',
       '167', '370', '144', '192', '259', '482', '235', '194', '37',
       '117', '140', '285', '152', '50', '81', '245', '173', '34', '215',
       '168', '63', '29', '177', '281', '231', '138', '297', '113', '87',
       '110', '217', '118', '307', '207', '120', '76', '296', '58', '123',
       '182', '90', '77', '111', '252', '228', '226', '55', '232', '241',
       '193', '122', '112', '127', '361', '233', '211', '457', '108',
       '222', '161', '552', '62', '78', '197', '57', '91', '209', '238',
       '75', '96', '79', '132', '331', '213', '333', '472', '203', '257',
       '317', '39', '134', '514', '735', '334', '93', '425', '362', '763',
       '440', '289', '53', '927', '316', '524', '978', '411', '528',
       '804', '485', '230', '445', '341', '126', '359', '423', '155',
       '85', '166', '336', '602', '594', '270', '372', '455', '491',
       '104', '685', '394', '462', '416', '236', '224', '48', '456',
       '430', '556', '179', '465', '354', '265', '30', '246', '564',
       '376', '449', '268', '332', '298', '181', '348', '323', '300',
       '433', '712', '42', '114', '191', '247', '371', '223', '641', '89',
       '487', '165', '420', '353', '382', '311', '54', '142', '506',
       '327', '256', '306', '128', '304', '212', '274', '554', '662',
       '100', '555', '444', '172', '496', '133', '396', '293', '574',
       '319', '577', '325', '131', '512', '299', '184', '580', '240',
       '271', '454', '288', '622', '262', '250', '345', '378', '291',
       '408', '853', '417', '225', '251', '227', '395', '404', '627',
       '282', '244', '195', '380', '676', '529', '536', '520', '613',
       '503', '682', '836', '381', '459', '277', '220', '338', '343',
       '160', '490', '313', '254', '876', '542', '537', '837', '744',
       '732', '314', '340', '410', '566', '413', '267', '609', '315',
       '543', '1011', '767', '414', '261', '189', '630', '412', '356',
       '717', '390', '432', '335', '572', '590', '624', '437', '374',
       '854', '286', '655', '1021', '560', '358', '221', '647', '418',
       '196', '442', '351', '492', '501', '576', '470', '640', '275',
       '426', '159', '658', '452', '302', '443', '276', '475', '157',
       '102', '308', '469', '474', '755', '153', '237', '386', '636',
       '347', '844', '752', '441', '628', '424', '891', '397', '518',
       '398', '633', '320', '272', '352', '363', '124', '858', '587',
       '399', '801', '309', '495', '427', '626', '852', '64', '301',
       '674', '570', '1330', '460', '595', '548', '483', '1039', '1194',
       '619', '561', '727', '407', '342', '326', '393', '368', '499',
       '310', '318', '258', '421', '106', '447', '575', '450', '525',
       '813', '337', '874', '473', '156', '264', '461', '805', '677',
       '350', '544', '367', '706', '403', '1049', '510', '585', '547',
       '533', '321', '365', '471', '1397', '391', '379', '776', '987',
       '239', '569', '668', '1105', '808', '344', '446', '553', '428',
       '200', '600', '780', '405', '578', '406', '515', '477', '1136',
       '373', '431', '478', '634', '360', '588', '996', '486', '269',
       '786'], dtype=object)
In [61]:
#2010 - 2013 Data File
#Create seperate dataframes with limited number of selected columns for each time period

df2=pd.DataFrame(df, columns =['Provider ID', 'Hospital Name', 'Compared to National', 'Measure ID',
                                                     'Denominator','Measure Start Date', 'Measure End Date'] )
df2=df2[df2['Measure ID'].isin(['PAYM_30_AMI'])]
df2=df2[df2['Denominator'] != 'Not Available'] 

#drop the null denominator values
df2.dropna(subset=['Denominator'], inplace = True)

hosp_count=int(df2['Provider ID'].count())
print("\033[1m" +'2010 to 2013 Hosp Count' + "\033[0m")
print(hosp_count)

2010 to 2013 Hosp Count
2429
In [62]:
#List unique values in the df['Denominator'] column
# Note that you see 'Not Available' and 'None' are excluded

df2.Denominator.unique()
Out[62]:
array(['642', '40', '290', '234', '107', '210', '45', '283', '243', '263',
       '242', '95', '60', '1035', '204', '97', '25', '38', '329', '255',
       '56', '389', '80', '73', '31', '65', '208', '384', '32', '171',
       '28', '292', '287', '249', '92', '163', '170', '273', '121', '86',
       '33', '49', '279', '74', '94', '103', '176', '366', '130', '150',
       '174', '305', '451', '88', '162', '129', '214', '47', '169', '143',
       '164', '383', '69', '201', '260', '146', '41', '188', '183', '190',
       '385', '125', '71', '105', '70', '27', '101', '151', '36', '295',
       '158', '219', '109', '59', '364', '346', '218', '324', '202',
       '303', '136', '438', '147', '72', '116', '149', '466', '175',
       '253', '436', '187', '185', '99', '369', '68', '186', '205', '44',
       '66', '115', '52', '178', '82', '135', '206', '43', '680', '46',
       '198', '401', '145', '137', '148', '84', '83', '328', '154', '497',
       '266', '35', '216', '349', '98', '26', '67', '51', '141', '61',
       '180', '357', '229', '119', '387', '167', '370', '144', '192',
       '259', '482', '235', '194', '37', '117', '140', '285', '152', '50',
       '81', '245', '173', '34', '215', '168', '63', '29', '177', '281',
       '231', '138', '297', '113', '87', '110', '217', '118', '307',
       '207', '120', '76', '296', '58', '123', '182', '90', '77', '111',
       '252', '228', '226', '55', '232', '241', '193', '122', '112',
       '127', '361', '233', '211', '457', '108', '222', '161', '552',
       '62', '78', '197', '57', '91', '209', '238', '75', '96', '79',
       '132', '331', '213', '333', '472', '203', '257', '317', '39',
       '134', '514', '735', '334', '93', '425', '362', '763', '440',
       '289', '53', '927', '316', '524', '978', '411', '528', '804',
       '485', '230', '445', '341', '126', '359', '423', '155', '85',
       '166', '336', '602', '594', '270', '372', '455', '491', '104',
       '685', '394', '462', '416', '236', '224', '48', '456', '430',
       '556', '179', '465', '354', '265', '30', '246', '564', '376',
       '449', '268', '332', '298', '181', '348', '323', '300', '433',
       '712', '42', '114', '191', '247', '371', '223', '641', '89', '487',
       '165', '420', '353', '382', '311', '54', '142', '506', '327',
       '256', '306', '128', '304', '212', '274', '554', '662', '100',
       '555', '444', '172', '496', '133', '396', '293', '574', '319',
       '577', '325', '131', '512', '299', '184', '580', '240', '271',
       '454', '288', '622', '262', '250', '345', '378', '291', '408',
       '853', '417', '225', '251', '227', '395', '404', '627', '282',
       '244', '195', '380', '676', '529', '536', '520', '613', '503',
       '682', '836', '381', '459', '277', '220', '338', '343', '160',
       '490', '313', '254', '876', '542', '537', '837', '744', '732',
       '314', '340', '410', '566', '413', '267', '609', '315', '543',
       '1011', '767', '414', '261', '189', '630', '412', '356', '717',
       '390', '432', '335', '572', '590', '624', '437', '374', '854',
       '286', '655', '1021', '560', '358', '221', '647', '418', '196',
       '442', '351', '492', '501', '576', '470', '640', '275', '426',
       '159', '658', '452', '302', '443', '276', '475', '157', '102',
       '308', '469', '474', '755', '153', '237', '386', '636', '347',
       '844', '752', '441', '628', '424', '891', '397', '518', '398',
       '633', '320', '272', '352', '363', '124', '858', '587', '399',
       '801', '309', '495', '427', '626', '852', '64', '301', '674',
       '570', '1330', '460', '595', '548', '483', '1039', '1194', '619',
       '561', '727', '407', '342', '326', '393', '368', '499', '310',
       '318', '258', '421', '106', '447', '575', '450', '525', '813',
       '337', '874', '473', '156', '264', '461', '805', '677', '350',
       '544', '367', '706', '403', '1049', '510', '585', '547', '533',
       '321', '365', '471', '1397', '391', '379', '776', '987', '239',
       '569', '668', '1105', '808', '344', '446', '553', '428', '200',
       '600', '780', '405', '578', '406', '515', '477', '1136', '373',
       '431', '478', '634', '360', '588', '996', '486', '269', '786'],
      dtype=object)
In [113]:
#Get count of hospitals containing the word 'Greater' from the 'Compared to National' column

df2=df2[df2['Compared to National'].str.contains("Greater", na=False)]
df2.head(3)

hosp_greater=int(df2['Provider ID'].count())
print('Count of Hospital with Greater....')
print(hosp_greater)

Count of Hospital with Greater....
382
In [68]:
#Find probability or observed rate

prob = hosp_greater/hosp_count

print('Observed Rate 2010 to 2013')
prob

Observed Rate 2010 to 2013
Out[68]:
0.15726636475916014
In [76]:
#Connect python to MS Access for 2011 to 2014 data

import pyodbc 

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\user1\Documents\muriyo\gmu\hap725\week5\q4\data\Hospital_20150716.mdb;')
cursor = conn.cursor()

query2 = ('SELECT * FROM HQI_HOSP_Payment')

hhp = pd.read_sql(query2, conn)
conn.close()

#Now we replace any empty strings in the Denominator column with np.nan  
hhp['Denominator'].replace('', np.nan, inplace=True)  

hhp.head(5)
Out[76]:
Provider ID Hospital name Measure name Measure ID Category Denominator Payment Lower estimate Higher estimate Footnote Measure start date Measure end date
0 360195 SUMMA WADSWORTH-RITTMAN HOSPITAL Payment for pneumonia patients PAYM_30_PN No Different than the National Average Payment 206 $15,415 $14,158 $16,709 None 07/01/2011 06/30/2014
1 360197 MARY RUTAN HOSPITAL Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 58 $22,988 $20,182 $26,223 None 07/01/2011 06/30/2014
2 360197 MARY RUTAN HOSPITAL Payment for heart failure patients PAYM_30_HF No Different than the National Average Payment 74 $14,224 $12,517 $16,083 None 07/01/2011 06/30/2014
3 360197 MARY RUTAN HOSPITAL Payment for pneumonia patients PAYM_30_PN No Different than the National Average Payment 140 $14,413 $13,058 $15,842 None 07/01/2011 06/30/2014
4 360203 SOUTHEASTERN OHIO REGIONAL MEDICAL CENTER Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 59 $22,496 $19,874 $25,482 None 07/01/2011 06/30/2014
In [72]:
#List unique values in the 'Denominator' column
# Note that you see 'Not Available'in the printout.  This needs to be excluded

hhp.Denominator.unique()
Out[72]:
array(['206', '58', '74', '140', '59', '192', '294', 'Not Available',
       '102', '110', '248', '497', '382', '69', '234', '202', '132',
       '220', '337', '309', '754', '581', '83', '215', '187', '55', '151',
       '258', '31', '143', '125', '681', '733', '367', '49', '326', '631',
       '303', '675', '99', '156', '32', '56', '237', '271', '474', '104',
       '136', '213', '207', '313', '317', '29', '311', '344', '70', '124',
       '108', '270', '408', '243', '459', '347', '540', '302', '39', '90',
       '466', '417', '92', '94', '400', '383', '139', '231', '181',
       '1,067', '1,406', '906', '184', '399', '527', '28', '131', '86',
       '154', '89', '307', '272', '105', '41', '27', '96', '128', '332',
       '452', '411', '276', '422', '515', '62', '52', '119', '273', '71',
       '177', '176', '165', '78', '107', '61', '112', '374', '499', '380',
       '158', '253', '87', '240', '298', '84', '462', '219', '72', '203',
       '263', '235', '495', '81', '60', '445', '856', '1,029', '33',
       '160', '172', '224', '25', '95', '267', '395', '246', '249', '26',
       '48', '103', '113', '642', '64', '162', '195', '149', '260', '167',
       '147', '82', '358', '63', '85', '44', '42', '79', '97', '233',
       '180', '323', '321', '512', '338', '218', '35', '116', '73', '277',
       '251', '80', '186', '47', '252', '98', '287', '318', '281', '197',
       '101', '67', '34', '68', '193', '254', '145', '335', '437', '390',
       '152', '185', '137', '183', '159', '198', '238', '361', '345',
       '483', '511', '880', '242', '285', '166', '66', '241', '329', '46',
       '339', '356', '216', '175', '288', '493', '360', '486', '545',
       '264', '279', '301', '229', '308', '114', '123', '153', '236',
       '463', '573', '43', '663', '201', '290', '366', '388', '456',
       '109', '189', '442', '296', '418', '510', '120', '118', '161',
       '211', '168', '30', '200', '282', '134', '126', '50', '77', '141',
       '54', '174', '292', '478', '315', '536', '299', '261', '40', '196',
       '353', '45', '53', '266', '117', '164', '291', '163', '327', '312',
       '226', '205', '619', '413', '232', '204', '148', '57', '598',
       '343', '352', '208', '590', '239', '381', '469', '569', '378',
       '37', '244', '209', '221', '217', '304', '319', '696', '100',
       '275', '397', '351', '420', '444', '405', '322', '766', '412',
       '583', '414', '289', '453', '610', '306', '210', '127', '503',
       '603', '465', '362', '389', '130', '111', '324', '230', '274',
       '133', '423', '770', '708', '472', '430', '91', '106', '115', '51',
       '36', '65', '75', '228', '190', '157', '385', '371', '649', '138',
       '410', '257', '300', '280', '182', '424', '627', '886', '155',
       '387', '392', '368', '171', '516', '460', '393', '365', '476',
       '1,061', '992', '409', '426', '144', '305', '179', '333', '283',
       '376', '473', '517', '227', '278', '293', '648', '490', '122',
       '191', '310', '314', '438', '369', '524', '336', '225', '121',
       '416', '377', '525', '247', '364', '178', '526', '76', '269',
       '340', '214', '188', '432', '349', '38', '262', '461', '439',
       '419', '370', '297', '359', '447', '150', '129', '88', '508',
       '328', '630', '415', '355', '596', '903', '799', '883', '2,739',
       '2,132', '403', '712', '553', '142', '530', '502', '994', '448',
       '433', '782', '697', '622', '929', '604', '255', '1,178', '967',
       '450', '316', '222', '471', '446', '320', '295', '404', '449',
       '431', '641', '908', '212', '173', '538', '1,371', '1,041', '199',
       '529', '544', '93', '146', '250', '245', '547', '434', '541',
       '135', '500', '1,082', '794', '494', '256', '599', '492', '436',
       '435', '363', '440', '699', '650', '636', '1,507', '1,182', '791',
       '1,200', '788', '563', '829', '835', '528', '820', '800', '2,006',
       '1,745', '480', '764', '398', '268', '568', '611', '923', '1,127',
       '372', '223', '585', '877', '758', '1,312', '1,219', '832', '728',
       '660', '406', '491', '860', '520', '194', '348', '783', '451',
       '827', '970', '551', '722', '574', '979', '982', '521', '384',
       '522', '454', '539', '690', '637', '868', '566', '793', '550',
       '1,040', '803', '956', '909', '330', '718', '589', '421', '470',
       '331', '334', '386', '1,002', '354', '1,046', '874', '286', '618',
       '481', '169', '350', '554', '1,194', '781', '379', '484', '498',
       '588', '653', '736', '549', '485', '325', '542', '477', '615',
       '531', '265', '957', '778', '496', '533', '893', '170', '407',
       '752', '580', '849', '629', '673', '582', '655', '391', '658',
       '776', '674', '570', '468', '743', '709', '745', '792', '402',
       '341', '428', '346', '969', '639', '744', '1,848', '1,582', '373',
       '682', '558', '625', '1,099', '1,214', '623', '458', '561',
       '1,001', '475', '429', '646', '784', '702', '396', '756', '689',
       '489', '606', '1,103', '513', '814', '284', '884', '707', '715',
       '507', '730', '532', '700', '739', '805', '875', '634', '595',
       '725', '518', '504', '724', '556', '716', '342', '633', '911',
       '892', '895', '427', '1,232', '740', '559', '501', '259', '771',
       '645', '984', '882', '602', '643', '640', '862', '609', '997',
       '1,748', '537', '1,012', '786', '688', '597', '910', '670', '714',
       '669', '1,028', '881', '425', '375', '1,517', '1,443', '1,304',
       '546', '635', '443', '1,344', '1,025', '479', '505', '750', '780',
       '523', '965', '723', '605', '686', '691', '971', '577', '935',
       '757', '662', '600', '672', '506', '457', '872', '720', '855',
       '467', '1,278', '844', '999', '1,765', '1,788', '796', '1,329',
       '1,162', '991', '514', '703', '664', '899', '575', '1,216', '717',
       '677', '734', '785', '1,131', '812', '828', '665', '920', '751',
       '608', '576', '394', '773', '787', '842', '614', '616', '1,016',
       '1,045', '401', '1,259', '759', '845', '772', '735', '620',
       '1,113', '654', '1,026', '808', '607', '659', '548', '357', '801',
       '644', '638', '854', '613', '713', '564', '441', '742', '601',
       '731', '1,006', '834', '737', '693', '578', '912', '907', '684',
       '651', '747', '464', '790', '487', '797', '1,320', '694', '1,083',
       '1,153', '617', '652', '815', '1,492', '1,205', '1,229', '940',
       '1,282', '488', '1,152', '726', '839', '1,630', '656', '535',
       '705', '939', '1,130', '571', '1,090', '926', '825', '761', '584',
       '840', '753', '1,853', '1,438', '1,267', '948', '749', '680',
       '802', '1,100', '1,262', '579', '915', '1,164', '1,348', '863',
       '1,303', '552', '626', '706', '1,382', '1,350', '887', '1,035',
       '695', '897', '1,144', '1,185', '661', '1,138', '1,252', '519',
       '683', '1,240', '1,189', '628', '837', '1,441', '1,064', '789',
       '593', '698', '509', '841', '851', '624', '916', '482', '1,122',
       '555', '1,111', '455', '859', '1,337', '741', '729', '765', '888',
       '1,172', '1,831', '931', '621', '1,295', '1,378', '1,376', '1,015',
       '1,769', '1,048', '1,070', '1,018', '567', '572', '685', '738',
       '960', '966', '813', '1,234', '671', '995', '833', '1,117', '885',
       '587', '1,204', '817', '952', '962', '914', '843', '1,383'],
      dtype=object)
In [73]:
#Create seperate dataframes with limited number of selected columns for each time period

hhp2=pd.DataFrame(hhp, columns =['Provider ID', 'Hospital Name', 'Category', 'Measure ID',
                                                     'Denominator','Measure Start Date', 'Measure End Date'] )
hhp2=hhp2[hhp2['Measure ID'].isin(['PAYM_30_AMI'])]

#Dropping "not available" entries
hhp2=hhp2[hhp2['Denominator'] != 'Not Available'] 

#drop the null denominator values
hhp2.dropna(subset=['Denominator'], inplace = True)

hosp_count2=int(hhp2['Provider ID'].count())
print("\033[1m" +'2011 to 2014 Hosp Count' + "\033[0m")
print(hosp_count2)

2011 to 2014 Hosp Count
2369
In [74]:
#Get count of hospitals containing the word 'Greater' from the 'Category' column

hhp2=hhp2[hhp2['Category'].str.contains("Greater", na=False)]
hhp2.head(5)

hosp_greater2=int(hhp2['Provider ID'].count())
print('Count Hospital Greater in 2011 to 2014')
print(hosp_greater2)

Count Hospital Greater in 2011 to 2014
365
In [75]:
#Get Observed rate or probability

prob2 = hosp_greater2/hosp_count2

print('Observed Rate 2011 to 2014')
prob2

Observed Rate 2011 to 2014
Out[75]:
0.15407344871253695
In [79]:
#Connect python to MS Access for 2012 to 2015 data

import pyodbc 

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\user1\Documents\muriyo\gmu\hap725\week5\q4\data\Hospital_20161110.mdb;')
cursor = conn.cursor()

query3 = ('SELECT * from HQI_HOSP_PaymentAndValueOfCare')

hhpv = pd.read_sql(query3, conn)
conn.close()

#Now we replace any empty strings in the Denominator column with np.nan objects
hhpv['Denominator'].replace('', np.nan, inplace=True)  

hhpv.head(3)
Out[79]:
Provider ID Hospital name Payment measure name Payment measure ID Payment category Denominator Payment Lower estimate Higher estimate Payment footnote Value of care display name Value of care display ID Value of care category Value of care footnote Measure start date Measure end date
0 010001 SOUTHEAST ALABAMA MEDICAL CENTER Payment for heart attack patients PAYM_30_AMI No Different than the National Average Payment 707 $21,804 $20,744 $22,932 None Value of Care Heart Attack measure MORT_PAYM_30_AMI Average mortality and average payment None 07/01/2012 06/30/2015
1 010001 SOUTHEAST ALABAMA MEDICAL CENTER Payment for heart failure patients PAYM_30_HF No Different than the National Average Payment 754 $15,659 $14,884 $16,446 None Value of Care Heart Failure measure MORT_PAYM_30_HF Average mortality and average payment None 07/01/2012 06/30/2015
2 010001 SOUTHEAST ALABAMA MEDICAL CENTER Payment for pneumonia patients PAYM_30_PN No Different than the National Average Payment 404 $15,089 $14,133 $16,018 None Value of Care Pneumonia measure MORT_PAYM_30_PN Average mortality and average payment None 07/01/2012 06/30/2015
In [80]:
#List unique values in the 'Denominator' column 
#Need to exclude "Not Available"

hhpv.Denominator.unique()
Out[80]:
array(['707', '754', '404', '53', '347', '646', '353', '670', '660',
       'Not Available', '93', '170', '25', '61', '240', '304', '454',
       '143', '145', '193', '198', '368', '308', '33', '344', '329', '83',
       '126', '32', '103', '290', '414', '160', '249', '455', '421',
       '259', '582', '343', '45', '70', '330', '530', '409', '98', '101',
       '432', '363', '157', '68', '140', '1,031', '1,431', '960', '195',
       '443', '549', '117', '79', '161', '75', '262', '239', '104', '112',
       '42', '26', '97', '338', '456', '416', '250', '423', '499', '77',
       '52', '116', '128', '291', '171', '62', '205', '191', '76', '100',
       '58', '510', '378', '169', '285', '269', '111', '517', '537', '86',
       '223', '78', '197', '265', '224', '484', '72', '55', '888',
       '1,150', '57', '65', '179', '155', '280', '252', '95', '87', '47',
       '241', '384', '263', '220', '190', '29', '130', '184', '39', '91',
       '31', '105', '232', '605', '516', '66', '144', '185', '261', '85',
       '141', '433', '69', '84', '301', '46', '357', '434', '442', '394',
       '346', '376', '63', '121', '306', '230', '200', '71', '168', '225',
       '309', '398', '391', '632', '830', '27', '110', '107', '106',
       '108', '152', '40', '73', '30', '94', '132', '43', '115', '133',
       '233', '399', '264', '38', '51', '44', '186', '348', '298', '479',
       '392', '272', '274', '81', '36', '176', '49', '242', '158', '90',
       '318', '321', '271', '135', '206', '67', '82', '118', '59', '28',
       '181', '254', '300', '386', '125', '154', '180', '229', '234',
       '214', '373', '527', '542', '974', '109', '228', '187', '96',
       '119', '258', '183', '173', '142', '247', '327', '37', '323',
       '377', '151', '199', '312', '470', '475', '533', '114', '201',
       '221', '255', '281', '99', '227', '339', '331', '131', '35', '54',
       '283', '430', '244', '486', '581', '315', '736', '211', '246',
       '354', '194', '370', '372', '453', '217', '287', '208', '472',
       '277', '490', '519', '120', '129', '149', '172', '296', '203',
       '238', '153', '210', '88', '64', '536', '514', '192', '260', '477',
       '680', '92', '320', '159', '188', '548', '760', '401', '597',
       '461', '651', '235', '213', '236', '122', '138', '575', '653',
       '494', '294', '452', '382', '136', '219', '113', '342', '164',
       '700', '689', '406', '521', '518', '369', '196', '48', '60', '156',
       '273', '364', '317', '218', '150', '80', '397', '163', '297',
       '352', '573', '415', '422', '667', '237', '278', '174', '367',
       '245', '686', '911', '403', '314', '405', '207', '202', '341',
       '316', '487', '395', '425', '459', '446', '991', '903', '402',
       '381', '139', '360', '361', '408', '288', '307', '524', '148',
       '268', '257', '102', '74', '624', '127', '511', '340', '359',
       '303', '293', '282', '324', '437', '383', '544', '407', '322',
       '212', '123', '124', '147', '292', '436', '248', '295', '540',
       '178', '424', '471', '426', '319', '276', '175', '189', '345',
       '89', '418', '299', '444', '256', '34', '451', '468', '362', '411',
       '420', '134', '137', '267', '431', '182', '305', '270', '41',
       '251', '350', '336', '162', '222', '412', '482', '374', '685',
       '284', '325', '328', '469', '253', '505', '417', '1,402', '966',
       '204', '366', '56', '562', '167', '419', '335', '478', '464',
       '853', '699', '642', '983', '630', '215', '358', '166', '289',
       '429', '279', '389', '547', '243', '440', '509', '50', '503',
       '1,100', '823', '626', '497', '385', '177', '664', '656', '791',
       '1,798', '1,294', '474', '811', '1,256', '620', '310', '556',
       '439', '865', '555', '615', '351', '719', '818', '2,078', '1,743',
       '379', '854', '313', '681', '619', '895', '1,101', '617', '693',
       '504', '953', '706', '866', '2,871', '2,113', '740', '481',
       '1,021', '914', '326', '746', '1,381', '1,235', '915', '726',
       '639', '460', '449', '863', '568', '311', '526', '275', '483',
       '146', '793', '489', '894', '1,129', '428', '552', '789', '692',
       '1,034', '998', '447', '375', '501', '637', '752', '666', '462',
       '413', '826', '560', '557', '804', '609', '1,105', '883', '1,040',
       '948', '673', '684', '528', '593', '371', '734', '563', '496',
       '349', '365', '558', '1,025', '821', '334', '1,106', '978', '495',
       '355', '231', '1,172', '802', '506', '541', '492', '602', '458',
       '286', '216', '515', '396', '659', '603', '393', '165', '450',
       '961', '751', '525', '465', '849', '916', '209', '463', '1,247',
       '1,023', '799', '473', '633', '625', '694', '613', '266', '388',
       '226', '445', '649', '956', '982', '669', '790', '2,027', '1,756',
       '448', '688', '586', '302', '655', '1,161', '1,248', '683', '730',
       '606', '598', '663', '808', '690', '676', '534', '1,099', '594',
       '819', '877', '546', '570', '765', '720', '485', '1,043', '747',
       '776', '886', '567', '585', '577', '493', '565', '531', '590',
       '774', '785', '601', '441', '753', '488', '491', '410', '400',
       '1,004', '869', '1,186', '712', '728', '725', '698', '628', '792',
       '435', '769', '1,032', '884', '337', '668', '648', '987', '861',
       '561', '427', '333', '658', '872', '968', '629', '779', '727',
       '843', '702', '587', '1,567', '1,518', '1,217', '1,255', '564',
       '529', '522', '675', '438', '674', '1,283', '831', '817', '906',
       '788', '910', '803', '665', '551', '457', '735', '608', '1,457',
       '952', '1,001', '1,775', '1,741', '847', '1,479', '678', '535',
       '1,244', '1,030', '559', '777', '599', '571', '662', '864', '985',
       '714', '764', '566', '591', '1,213', '661', '1,057', '786',
       '1,162', '708', '833', '739', '814', '717', '908', '387', '759',
       '857', '902', '641', '588', '1,791', '1,015', '650', '595',
       '1,108', '748', '1,014', '1,201', '733', '848', '731', '657',
       '1,165', '900', '986', '892', '723', '772', '949', '737', '634',
       '390', '959', '807', '761', '583', '860', '943', '627', '904',
       '691', '946', '873', '711', '704', '604', '1,007', '963', '356',
       '600', '773', '912', '621', '703', '332', '508', '500', '638',
       '1,147', '576', '623', '695', '1,522', '1,074', '794', '1,311',
       '580', '645', '893', '1,305', '810', '502', '1,136', '722', '592',
       '827', '1,574', '466', '954', '1,052', '724', '640', '380', '545',
       '1,118', '890', '1,133', '652', '815', '553', '512', '467', '858',
       '775', '1,312', '896', '1,854', '1,491', '1,284', '721', '1,084',
       '1,151', '927', '852', '1,070', '1,407', '859', '1,319', '839',
       '813', '710', '875', '569', '636', '715', '679', '498', '844',
       '1,447', '1,418', '1,238', '539', '816', '1,155', '1,267', '611',
       '589', '1,047', '1,215', '1,366', '1,093', '1,110', '798', '596',
       '801', '1,495', '1,097', '574', '607', '513', '687', '612', '856',
       '579', '572', '610', '745', '957', '795', '1,063', '1,126', '480',
       '1,416', '891', '1,368', '846', '520', '635', '962', '768', '622',
       '654', '578', '984', '647', '550', '762', '1,246', '1,341', '618',
       '1,465', '975', '1,643', '918', '976', '742', '901', '744',
       '1,010', '1,354', '1,205', '944', '732', '476', '898', '1,196',
       '1,838', '1,398', '643', '758', '1,149', '809', '1,286', '806',
       '1,053', '532', '812', '716', '631', '992', '614', '1,329'],
      dtype=object)
In [84]:
#Create seperate dataframes with limited number of selected columns for each time period

hhpv2=pd.DataFrame(hhpv, columns =['Provider ID', 'Hospital name', 'Payment category', 'Payment measure ID',
                                                     'Denominator','Measure start date', 'Measure end date'] )
hhpv2=hhpv2[hhpv2['Payment measure ID'].isin(['PAYM_30_AMI'])]
hhpv2=hhpv2[hhpv2['Denominator'] != 'Not Available'] 

#drop the null denominator values
hhpv2.dropna(subset=['Denominator'], inplace = True)

hosp_count3=int(hhpv2['Provider ID'].count())
print("\033[1m" +'2012 to 2015 Hosp Count' + "\033[0m")
print(hosp_count3)

2012 to 2015 Hosp Count
2343
In [86]:
#Find Count of hospitals containing 'Greater' from the 'Payment category column' for the 2012 to 2015 year
hhpv2=hhpv2[hhpv2['Payment category'].str.contains("Greater", na=False)]
hhpv2.head(5)

hosp_greater3=int(hhpv2['Provider ID'].count())
print('Count Hospital Greater in 2012 to 2015')
print(hosp_greater3)

Count Hospital Greater in 2012 to 2015
254
In [116]:
#Get Observed rate or probability

prob3 = hosp_greater3/hosp_count3

print('Observed Rate 2012 to 2015')
prob3

Observed Rate 2012 to 2015
Out[116]:
0.10840802390098164
In [117]:
#Recreating measure start dates and measure end dates 

msd = '07/01/2010'
med = '06/30/2013'
msd1 = '07/01/2011'
med1 = '06/30/2014'
msd2 = '07/01/2012'
med2 = '06/30/2015'
In [118]:
#Build dataset of counts, and rates and measure start and end dates
df_all = [['Hospitals 2010-2013', hosp_count, prob, msd, med],
       ['Hospitals 2011-2014', hosp_count2, prob2, msd1, med1],
       ['Hospitals 2012-2015', hosp_count3, prob3, msd2, med2]]
       
df_all
Out[118]:
[['Hospitals 2010-2013',
  2429,
  0.15726636475916014,
  '07/01/2010',
  '06/30/2013'],
 ['Hospitals 2011-2014',
  2369,
  0.15407344871253695,
  '07/01/2011',
  '06/30/2014'],
 ['Hospitals 2012-2015',
  2343,
  0.10840802390098164,
  '07/01/2012',
  '06/30/2015']]
In [119]:
#Create new dataframe from df_all and add column headings

df_all2 = pd.DataFrame(df_all, columns = ['TimePeriod', 'NumberOfHospitals','ObservedRange', 'MeasureStartDate','MeasureEndDate'])
 
df_all2
Out[119]:
TimePeriod NumberOfHospitals ObservedRange MeasureStartDate MeasureEndDate
0 Hospitals 2010-2013 2429 0.157266 07/01/2010 06/30/2013
1 Hospitals 2011-2014 2369 0.154073 07/01/2011 06/30/2014
2 Hospitals 2012-2015 2343 0.108408 07/01/2012 06/30/2015
In [120]:
#Check data tyeps

df_all2.dtypes
Out[120]:
TimePeriod            object
NumberOfHospitals      int64
ObservedRange        float64
MeasureStartDate      object
MeasureEndDate        object
dtype: object