<jupyter_start><jupyter_text>HOSPITALIZATION ANALYSISHEALTH DEPARTMENT OF FEDERAL DISTRICT    ![](images/SES_DF.jpg) This notebook is about data analysis of patients hospitalizations in all health institutions of Health Department of Federal District from year 2010 to 2018.The data was obtained from DataSUS online platform (IT department of Unique Health System of Brazil). The data was collected from (.dbc) format, after that was decompressed to dBase III format and also transformed into Pandas Dataframes. Ultimately, was sent to (.csv) file.**Goals**- Analyze the relations between hospitalizations and health institutions- Check the origin of patients in institutions- Relate the medical procedures costs and hospitalizations- Predict the amount of hospitalizations in 2019 (Machine Learning).The data visualization will be made on Microsoft Power BI. In this notebook, I'll just make data wrangling, the results will be visualized on Power BI. **The Source of Data**The data was collected from "Share files" link on Health Portal.Link: The data is about 20 million of rows, after it'll be reduced. **InovaSES**![](images/INOVASES-DF.png)InovaSES supports this project  Support to new ideas, innovative projects and entrepreneurship of Health Department Services.  1- Get Data **Import**Import the libraries and relate dBase III files on "files" list using (glob) function.<jupyter_code>import pandas as pd
from simpledbf import Dbf5
import glob
import numpy as np
files = glob.glob('DBF_NAIH\\*.dbf')
files[0:10]<jupyter_output><empty_output><jupyter_text>**Transformation of Data**In order to manipulate (.dbf) files, the Project Simpledbf python library was chosen. This project is kept on PyPI Python repository: .Therefore, using the library all (.dbf) files turned into Pandas Dataframes using the function Dbf5().to_dataframe(), excluding all selected columns from the list (columns_excl) and removing using (drop). After the transformation, each Dataframe was inserted into variable (df_int) list.The codec "latin-1" was set due the information dBase III source format.**The Original Dataset SUS**The original dataset has 37 columns, but only 9 was selected to analysis.The 9 columns:- Hospitalization Year (SP_AA)- Hospitalization Month (SP_MM)- Health Institution Code (SP_CNES)- Hospitalization Unique Code (SP_NAIH)- Patient Admission Date (SP_DTINTER)- Patient Discharge Date (SP_DTSAIDA)- Amount of Professional Act (SP_QTD_ATO)- Value of Professional Act (SP_VALATO)- Origin of Patient (SP_DES_HOS)<jupyter_code>df_int = []
columns_excl=['SP_GESTOR', 'SP_UF', 'SP_PROCREA', 'SP_NUM_PR', 'SP_TIPO',
       'SP_CPFCGC', 'SP_ATOPROF', 'SP_TP_ATO', 'SP_PTSP',
       'SP_NF', 'SP_M_HOSP', 'SP_M_PAC', 'SP_DES_PAC', 'SP_COMPLEX',
       'SP_FINANC', 'SP_CO_FAEC', 'SP_PF_CBO','SP_PF_DOC', 'SP_PJ_DOC', 
       'IN_TP_VAL', 'SEQUENCIA', 'REMESSA', 'SP_CIDPRI', 
       'SP_CIDSEC', 'SP_QT_PROC', 'SP_U_AIH']

for file in files:
    df_int.append(Dbf5(file, codec='latin-1').to_dataframe().drop(columns_excl,axis=1))
    
    print(file[-12:]+' processado.')<jupyter_output>SPDF1001.dbf processado.
SPDF1002.dbf processado.
SPDF1003.dbf processado.
SPDF1004.dbf processado.
SPDF1005.dbf processado.
SPDF1006.dbf processado.
SPDF1007.dbf processado.
SPDF1008.dbf processado.
SPDF1009.dbf processado.
SPDF1010.dbf processado.
SPDF1011.dbf processado.
SPDF1012.dbf processado.
SPDF1101.dbf processado.
SPDF1102.dbf processado.
SPDF1103.dbf processado.
SPDF1104.dbf processado.
SPDF1105.dbf processado.
SPDF1106.dbf processado.
SPDF1107.dbf processado.
SPDF1108.dbf processado.
SPDF1109.dbf processado.
SPDF1110.dbf processado.
SPDF1111.dbf processado.
SPDF1112.dbf processado.
SPDF1201.dbf processado.
SPDF1202.dbf processado.
SPDF1203.dbf processado.
SPDF1204.dbf processado.
SPDF1205.dbf processado.
SPDF1206.dbf processado.
SPDF1207.dbf processado.
SPDF1208.dbf processado.
SPDF1209.dbf processado.
SPDF1210.dbf processado.
SPDF1211.dbf processado.
SPDF1212.dbf processado.
SPDF1301.dbf processado.
SPDF1302.dbf processado.
SPDF1303.dbf processado.
SPDF1304.dbf processado.
[...]<jupyter_text>**General view of first Dataframe on list (df_int[0])**<jupyter_code>df_int[0].head(50)<jupyter_output><empty_output><jupyter_text>**Concatenation of Data**For the best analysis, it's necessary to join all 108 Dataframes in just one, for this purpose the pandas function (concat) was chosen. For analysis the (SERV_CLAL) column was removed.After de concatenation, at the moment there were 20379185 rows of data on only one Dataframe (df_int)<jupyter_code>df_int = pd.concat(df_int)
df_int.drop(df_int.columns[9],axis=1,inplace=True)
df_int.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 20376185 entries, 0 to 199618
Data columns (total 9 columns):
SP_AA         object
SP_MM         object
SP_CNES       object
SP_NAIH       object
SP_DTINTER    object
SP_DTSAIDA    object
SP_QTD_ATO    int64
SP_VALATO     float64
SP_DES_HOS    object
dtypes: float64(1), int64(1), object(7)
memory usage: 1.5+ GB<jupyter_text>**Conversion** Conversion of (SP_CNES), (SP_NAIH), (SP_DES_HOS) to numeric format.<jupyter_code>for col in ['SP_CNES','SP_NAIH','SP_DES_HOS']:
    df_int[col] = df_int[col].astype('int64')
df_int.isnull().sum()<jupyter_output><empty_output><jupyter_text>2- Monthly Value of Hospitalizations **Monthly Value file**For future analysis, was created another file with all values grouped by month and year.It first group all data by year (SP_AA) and month (SP_MM) and aggregate the hospitalizations costs (SP_VALATO) applying the (sum) function. Therefore, make a (DATE) column getting information from (SP_AA) and (SP_MM).<jupyter_code>df_value_month = df_int.groupby(['SP_AA','SP_MM'],as_index = False)['SP_VALATO'].sum()
df_value_month['DATE'] = pd.to_datetime(df_value_month['SP_AA'] + df_value_month['SP_MM'].apply(lambda x: x.zfill(2)), format='%Y%m')
df_value_month.drop(columns = ['SP_AA','SP_MM'],axis=1, inplace=True)
df_value_month.columns = ['VALOR_MENSAL','DATE']
df_value_month.to_csv('INT_VALOR_MENSAL.csv')<jupyter_output><empty_output><jupyter_text>3- Data Manipulation2 new Datasets will be created from rows and column manipulation.- **Int_2010_to_2018.csv**: Dataset with all hospitalizations informations.- **INT_VALOR_MENSAL.csv**: Dataset with all Monthly and Yearly values of hospitalizations. **Grouping Rows**The Dataframe has about 20 million of rows. In order to reduce that, the all data was grouped by (SP_NAIH) because it has unique numbers codes. In fact, the whole data was reduced to 1685512 million, for reach this goal the functions (groupby) and(agg) was used to aggregate data by first parameter (first) and (sum).<jupyter_code>df_int_new = df_int.groupby('SP_NAIH',as_index = False).agg({
                                 'SP_AA': 'first',
                                 'SP_MM': 'first',
                                 'SP_CNES':'first', 
                                 'SP_DTINTER': 'first',
                                 'SP_DTSAIDA': 'first',
                                 'SP_QTD_ATO': 'sum',
                                 'SP_VALATO': 'sum',
                                 'SP_DES_HOS': 'first'})
df_int_new.head()
#Rename the columns
df_int_new.columns = ['NAIH','ANO','MES','CNES','DTINTER','DTSAIDA','QTD_PROD','VALOR_AIH','DF']
df_int_new.head()<jupyter_output><empty_output><jupyter_text>**Date Field**<jupyter_code>df_int_new['DTSAIDA'] = pd.to_datetime(df_int_new['DTSAIDA'], format='%Y%m%d')
df_int_new['DTINTER'] = pd.to_datetime(df_int_new['DTINTER'], format='%Y%m%d')
#zfill function to fill with 0 the only one character months like January 1 or July 7
df_int_new['DATE'] = pd.to_datetime(df_int_new['ANO'] + df_int_new['MES'].apply(lambda x: x.zfill(2)) + '01', format='%Y%m%d')
df_int_new.head()
#Remove the column ANO (Year) and MES (Month)
df_int_new.drop(columns = ['ANO','MES'],axis=1, inplace=True)
df_int_new
#Temporary save
df_int_new.to_csv("INT_2010_to_2018.csv", index=False )<jupyter_output><empty_output><jupyter_text>**QTD_NAIH Field on INT_VALOR_MENSAL.csv***Reading the Datasets again*<jupyter_code>df_int_new = pd.read_csv('INT_2010_to_2018.csv')

df_value_month_new = pd.read_csv('INT_VALOR_MENSAL.csv')<jupyter_output><empty_output><jupyter_text>**Arranging Date field on datasets**<jupyter_code>df_int_new['DATE']= df_int_new['DATE'].apply(lambda x: x.split("-")[0]+"-"+x.split("-")[1])
df_value_month_new['DATE']= df_value_month_new['DATE'].apply(lambda x: x.split("-")[0]+"-"+x.split("-")[1])
df_int_new.head()<jupyter_output><empty_output><jupyter_text>**Variable (df_value_month_temp)**New temporary variable to get grouped data by Date and aggregated by sum of hospitalizations (NAIH).<jupyter_code>df_value_month_temp = df_int_new.groupby('DATE', as_index = False)['NAIH'].count()
df_value_month_temp.head()<jupyter_output><empty_output><jupyter_text>**(QTD_NAIH) field**Take the values from temporary variable to (INT_VALOR_MENSAL.csv)<jupyter_code>df_value_month_new['QTD_NAIH'] = df_value_month_temp['NAIH']
#Remove "unnamed" column
df_value_month_new.drop(df_value_month_new.columns[df_value_month_new.columns.str.contains('unnamed',case = False)],axis = 1, inplace=True)
df_int_new.to_csv("INT_2010_to_2018.csv", index=False )
df_value_month_new.to_csv('INT_VALOR_MENSAL.csv', index=False)<jupyter_output><empty_output><jupyter_text>4- Medical Procedures GroupsFor visualize the medical procedures data, was created a dataset just to relate the raw (.dbf) dataset field (SP_ATOPROF) to new medical procedures group (GP_PROCED) and subgroup (SB_PROCED). (SP_ATOPROF) contains the Main medical procedure, the subprocedure and de specific procedure.In order to create this column, was extracted the 10 characters of each row (SP_ATOPROF).The meaning of these characters is:- 2 first digits: Medical procedures Main Group - EG: 04 (Surgery Procedure)- 4 first digits: Medical procedures Subgroup - EG: **04**03 (Surgery of the Central and Peripheral Nervous System)- All 10 digits: Specific medical procedure - EG: **0403**010195 (Surgical Treatment of Intracranial Abscess) **Original Dataset**Read all (.dbf) files again but containing only 3 columns:- Hospitalization Unique Code (SP_NAIH)- Professional Act code (SP_ATOPROF)- Value of Professional Act (SP_VALATO)Ultimately, the **INT_PROCEDS.csv** will be created.<jupyter_code>files = glob.glob('DBF_NAIH\\*.dbf')
files[0:10]
df_int_prd = []
columns_excl_prd=['SP_GESTOR', 'SP_UF', 'SP_PROCREA', 'SP_NUM_PR', 'SP_TIPO',
       'SP_CPFCGC', 'SP_TP_ATO', 'SP_PTSP',
       'SP_NF', 'SP_M_HOSP', 'SP_M_PAC', 'SP_DES_PAC', 'SP_COMPLEX',
       'SP_FINANC', 'SP_CO_FAEC', 'SP_PF_CBO','SP_PF_DOC', 'SP_PJ_DOC', 
       'IN_TP_VAL', 'SEQUENCIA', 'REMESSA', 'SP_CIDPRI', 
       'SP_CIDSEC', 'SP_QT_PROC', 'SP_U_AIH', 'SP_AA', 'SP_MM', 'SP_CNES', 
       'SP_DTINTER', 'SP_DTSAIDA', 'SP_QTD_ATO', 'SP_DES_HOS']

for file in files:
    df_int_prd.append(Dbf5(file, codec='latin-1').to_dataframe().drop(columns_excl_prd,axis=1))
    
    print(file[-12:]+' processado.')
df_int_prd[0].head(20)
df_int_prd = pd.concat(df_int_prd)
#Remove (SERV_CLAL) column
df_int_prd.drop(df_int_prd.columns[3],axis=1,inplace=True)<jupyter_output><empty_output><jupyter_text>**Association**Association of the Professional Act (SP_ATOPROF) to the Group (GP_PROCED) and Subgroup (SB_PROCED)<jupyter_code>df_int_prd['GP_PROCED'] = df_int_prd['SP_ATOPROF'].str[0:2]
df_int_prd['SB_PROCED'] = df_int_prd['SP_ATOPROF'].str[0:4]
#Remove column (SP_ATOPROF)
df_int_prd.drop(columns=['SP_ATOPROF'],inplace=True)
df_int_prd.head(20)
df_int_prd.shape
df_int_prd.to_csv('INT_PROCEDS.csv')<jupyter_output><empty_output><jupyter_text>5- MACHINE LEARNING (Time Series)In order to make predictions about the amount of hospitalizations in Health Department in 2019, the amount of hospitalizations (QTD_NAIH) and the Monthly hospitalization value (VALOR_MENSAL) were collected from **INT_VALOR_MENSAL.csv**.For making the prediction, the SARIMAX was used. This model is based on ARIMA (AR) auto-regressive and (MA) move averages used in time series.The "S" on SARIMAX is the Seasonal component, to give periodicity to ARIMA where there is seasonality paterns on ranges of time. **Import**<jupyter_code>import warnings
from itertools import product
import pandas as pd
import numpy as np
import statsmodels.api as sm
import matplotlib.pyplot as plt
df_ts = pd.read_csv('INT_VALOR_MENSAL.csv')
df_ts.info()
#Transformation of DATA into Timestamp
df_ts['DATE'] = pd.to_datetime(df_ts['DATE'])
df_ts.head(20)
#Setting Date as index
df_ts = df_ts.set_index('DATE')<jupyter_output><empty_output><jupyter_text>**Outliers**Despite there are few outliers, there weren't reasons to remove because:- There weren't many available data to machine learning model- The distance of data isn't so far from the range<jupyter_code>df_ts.boxplot(column='QTD_NAIH')
df_ts.boxplot(column='VALOR_MENSAL')<jupyter_output><empty_output><jupyter_text>**Trend** This chart has all data of hospitalizations split on months. Here, you can see a trend of 12 months (1 year) throughout data. The year start increasing the hospitalizations and after august this trend goes decreasing until December.<jupyter_code>df_ts.groupby(df_ts.index.month)['QTD_NAIH'].sum().plot()<jupyter_output><empty_output><jupyter_text>**SARIMAX Machine Learning Model**The SARIMAX model is specified by:$(p,d,q)-(P,D,Q)s$- $(p,d,q)$ -> Trend Components Configuration (ARIMA)- $(P,D,Q)$ -> Seasonality Components Configuration (SARIMA)- $s$ -> Period configuration (Eg: 12 months, 30 days)The model has s = 12 (12 months)In order to find the best specification, an automatized method was used to test all combination of components (p,d,q,P,D,Q,s) on a 0, 1 range of values.**AIC (results.aic)**Each specification was scored with AIC score, that shows us how much the model fits on data (Lower is better).<jupyter_code>p = d = q = range(0, 2)
pdq = list(product(p, d, q))
seasonal_pdq = [(x[0], x[1], x[2], 12) for x in list(product(p, d, q))]
warnings.filterwarnings("ignore")
lower_aic = None

for param in pdq:

    for param_seasonal in seasonal_pdq:
            
        mod = sm.tsa.statespace.SARIMAX(df_ts['QTD_NAIH'],order=param,seasonal_order=param_seasonal,enforce_stationarity=False,enforce_invertibility=False)
        results = mod.fit()
        print('ARIMA{}x{}12 - AIC:{}'.format(param, param_seasonal, results.aic))
        
        if lower_aic == None:
            lower_aic = results.aic
            
        if lower_aic > results.aic:
                lower_aic = results.aic
                opt_pdq = param
                opt_pdq_seasonal = param_seasonal
            
print('The best model is: ',opt_pdq,'x',opt_pdq_seasonal,'  - AIC: ',lower_aic)<jupyter_output>ARIMA(0, 0, 0)x(0, 0, 0, 12)12 - AIC:2373.0627427670593
ARIMA(0, 0, 0)x(0, 0, 1, 12)12 - AIC:2048.47696085024
ARIMA(0, 0, 0)x(0, 1, 0, 12)12 - AIC:1693.0980430797858
ARIMA(0, 0, 0)x(0, 1, 1, 12)12 - AIC:1481.9345473162273
ARIMA(0, 0, 0)x(1, 0, 0, 12)12 - AIC:1711.5674742664999
ARIMA(0, 0, 0)x(1, 0, 1, 12)12 - AIC:1693.465464599374
ARIMA(0, 0, 0)x(1, 1, 0, 12)12 - AIC:1494.4830163401966
ARIMA(0, 0, 0)x(1, 1, 1, 12)12 - AIC:1481.1573216789177
ARIMA(0, 0, 1)x(0, 0, 0, 12)12 - AIC:2223.587708164753
ARIMA(0, 0, 1)x(0, 0, 1, 12)12 - AIC:2005.7873379988866
ARIMA(0, 0, 1)x(0, 1, 0, 12)12 - AIC:1643.280599005199
ARIMA(0, 0, 1)x(0, 1, 1, 12)12 - AIC:1431.459321818493
ARIMA(0, 0, 1)x(1, 0, 0, 12)12 - AIC:1690.8383982871746
ARIMA(0, 0, 1)x(1, 0, 1, 12)12 - AIC:1639.536949733573
ARIMA(0, 0, 1)x(1, 1, 0, 12)12 - AIC:1467.7274365462988
ARIMA(0, 0, 1)x(1, 1, 1, 12)12 - AIC:1433.0544771219757
ARIMA(0, 1, 0)x(0, 0, 0, 12)12 - AIC:1825.85063424484
ARIMA(0, 1, 0)x(0, 0, 1, 12)12 - AIC:1608.8783471569827
A[...]<jupyter_text>**Results**  After test all possibly combinations, the best configuration was:$(p,d,q)-(P,D,Q)s$(1, 1, 1) x (0, 1, 1, 12) - AIC: 1377For the best visualization of model, plot_diagnostics function was used to analyze the charts (QQ Plot, KDE). The normal distribution fitting was confirmed from that. The (Standardized residual) show us a uniform variancy lying around 0. The (Correlogram) show us the residual erros aren't autocorrelates, this implies that there aren't error patterns without understanding inside the model. This model seems good to proceed.<jupyter_code>mod = sm.tsa.statespace.SARIMAX(df_ts['QTD_NAIH'],
                                order=opt_pdq,
                                seasonal_order=opt_pdq_seasonal,
                                enforce_stationarity=False,
                                enforce_invertibility=False)

results = mod.fit()

print(results.summary().tables[1])
results.plot_diagnostics(figsize=(15, 12))
plt.show()
pred = results.get_prediction(start=1, dynamic=False)
pred_ci = pred.conf_int()<jupyter_output><empty_output><jupyter_text>**Comparing the Models**Here, is compared the machine learning model to real data.The blue line is the real hospitalizations amount and green line is the machine learning prediction.<jupyter_code>#Remove (VALOR_MENSAL) column
df_naih = df_ts.drop('VALOR_MENSAL', axis=1)
df_naih.head()
ax = df_naih['2010':].plot(label='Observed')

pred.predicted_mean.plot(ax=ax, label='Forecast', alpha=.7)

ax.fill_between(pred_ci.index,
                pred_ci.iloc[:, 0],
                pred_ci.iloc[:, 1], color='w', alpha=.2)

ax.set_xlabel('Date')
ax.set_ylabel('Hospitalizations')
plt.legend(["HOSPITALIZATIONS", "FORECAST"])

plt.show()<jupyter_output><empty_output><jupyter_text>**Prediction**Here, show us the real prediction after 2018 until December 2019.<jupyter_code>pred_uc = results.get_forecast(steps=12)
pred_ci = pred_uc.conf_int()
ax = df_naih.plot(label='Internações', figsize=(20, 15), fontsize=26, title='HOSPITALIZATIONS PREDICTION',legend=True)

pred_uc.predicted_mean.plot(ax=ax, label='Previsão')
ax.fill_between(pred_ci.index,
                pred_ci.iloc[:, 0],
                pred_ci.iloc[:, 1], color='r', alpha=.25)

ax.set_xlabel('Date')
ax.set_ylabel('Hospitalizations')

ax.legend(["HOSPITALIZATIONS", "FORECAST"]);
plt.show()
pred_uc.predicted_mean.plot(label='PREDICTION', figsize=(12, 10), fontsize=20, title='HOSPITALIZATION 2019 FORECAST',legend=True)
ax.fill_between(pred_ci.index,
                pred_ci.iloc[:, 0],
                pred_ci.iloc[:, 1], color='k', alpha=.25)
ax.set_xlabel('Date')
ax.set_ylabel('Hospitalizations')

plt.legend()
plt.show()
pred_uc.predicted_mean<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12398834574295889, 'fraction_numerical': 0.04904499838135319, 'mean_word_length': 5.194852941176471, 'pattern_counts': {'":': 0, '<': 83, '<?xml version=': 0, '>': 87, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '296606', 'license': 'MIT', 'n_tokens_mistral': 7387, 'n_tokens_neox': 6631, 'n_words': 1678, 'path': 'hosp_analysis_US_version.ipynb', 'repo_name': 'leoitcode/sesdfproject', 'size': '18534'}