<jupyter_start><jupyter_text>Project: Investigate the 'No-show appointments' Dataset Table of ContentsIntroductionData WranglingExploratory Data AnalysisConclusions  Introduction> **Key notes**: "This dataset collects information from 100k medical appointments in Brazil and is focused on the question of whether or not patients show up for their appointment. A number of characteristics about the patient are included in each row.‘ScheduledDay’ tells us on what day the patient set up their appointment.‘Neighborhood’ indicates the location of the hospital.‘Scholarship’ indicates whether or not the patient is enrolled in Brasilian welfare program Bolsa Família.Be careful about the encoding of the last column: it says ‘No’ if the patient showed up to their appointment, and ‘Yes’ if they did not show up." > **Questions to explore**: > 1. What is the infomation regarding the oldest patient?> 2. What is the patients number of male versus female?> 3. What is the distribution of male and female patients?> 4. On which years, months and days did most patients as well as least patients made appointments?  How many patients are there in total in the list?> 5. Hou many patients's appintments were scheduled in the April and May of 2016? What is the relationship between scheduled_day and appointment_day?> 6. Among all the 4 kinds of dieseas, which has highest morbility？> 7. What is the most frequent neighbourhood, and it is mostly associated with what age group?> 8. What is the ratio of patients suffering from different kinds of diseases, respectively?> 9. What is the ratio of both male and female patients suffering from different kinds of diseases, respectively?> 10. Bwtween 16: 00  and 17: 00pm, how many patients's appointments were scheduled on daily bases?> 11. What factors are important for us to know in order to predict if a patient will show up for their scheduled appointment?<jupyter_code># Set up import statements for all of the packages that are planed to use
# Include a 'magic word' so that visualizations are plotted
# call on dataframe to display the first 5 rows

import pandas as pd
import numpy as np
import datetime
import calendar
from statistics import mode
! pip install prettytable
from prettytable import PrettyTable
% matplotlib inline
import matplotlib.pyplot as plt
%config InlineBackend.figure_format = 'retina'
import seaborn as sns
sns.set_style('whitegrid')
df = pd.read_csv('noshowappointments-kagglev2-may-2016.csv')<jupyter_output>Requirement already satisfied: prettytable in /Users/shilinli/anaconda3/lib/python3.6/site-packages (0.7.2)<jupyter_text>Data Wrangling> **Key notes**: In this section of the report, the following work will be done: load the data; check for cleanliness; trim and clean dataset for analysis. General Properties<jupyter_code># Load data and print out a few lines

df.head()
# return a tuple of the dimensions of the dataframe

df.shape
# print the column labels in the dataframe

for i, v in enumerate(df.columns):
    print(i, v)
# return the datatypes of the columns

df.dtypes
# check for duplicates in the data

sum(df.duplicated())
#  check if any value is NaN in DataFrame and in how many columns

df.isnull().any().any(), sum(df.isnull().any())
# displays a concise summary of the dataframe
# including the number of non-null values in each column

df.info()
# Generates descriptive statistics, excluding NaN values

df.describe()<jupyter_output><empty_output><jupyter_text>Data Cleaning<jupyter_code># Change column name into lower case for the convenience of analysis
# Confirm changes

df.rename(columns = lambda x: x.lower(), inplace = True)
df.head()
# Change some column name to avoid ambiguity
# Confirm changes

df.rename(columns = {'patientid': 'patient_id', 'appointmentid': 'appointment_id', 'scheduledday': 'scheduled_day',\
                    'appointmentday': 'appointment_day', 'handcap': 'handicap', 'hipertension': 'hypertension', \
                    'no-show': 'no_show'}, 
          inplace = True)
df.head()
# Fix datetime format
# Confirm changes

df.iloc[:, 3:5] = df.iloc[:, 3:5].apply(pd.to_datetime, errors='coerce')
df.head()<jupyter_output><empty_output><jupyter_text>Exploratory Data Analysis Research Question 1: What is the infomation regarding the oldest patient?<jupyter_code>df.loc[df['age'].idxmax()]<jupyter_output><empty_output><jupyter_text>> The oldest patient is a woman who is 115 yeas old and remains relatively heathy. Except for handicap, she had not been diagnosed suffering any major disease in the list.   Research Question 2: What is the patients number of male versus female?<jupyter_code># Use groupby to find out the value count for the gender column

# Print out the respective numbers for both male and female patients

all_gender = df.groupby(['gender'])['age'].value_counts()

print('The number of male and female patiens are {} and {}, respectively.'.format(\
all_gender.loc['M'].sum(), all_gender.loc['F'].sum()))<jupyter_output>The number of male and female patiens are 38687 and 71840, respectively.<jupyter_text>Research Question 3: What is the distribution of male and female patients?<jupyter_code># Use query to seperate male and female group and plot

m_age = df.query('gender == "M"').age
f_age = df.query('gender == "F"').age


sns.distplot(m_age, bins=range(0, 100, 5),label='Male')
sns.distplot(f_age, bins=range(0, 100, 5),label='Female')
plt.legend()
plt.show()<jupyter_output><empty_output><jupyter_text>Research Question 4: On which years, months and days did most patients as well as least patients made appointments?  How many patients are there in total in the list?<jupyter_code># Groupby patients number with year
# Find out in which year did most patients make their appointments

max_year = df.groupby(df['appointment_day'].apply(lambda x: x.year))['diabetes'].count()
max_year.idxmax()
# Print out the number of patients in the year when most of patients made their appointment

max_year.loc[2016]<jupyter_output><empty_output><jupyter_text>> According to the result, most patients made their appointments in 2016, and the number is 110527.<jupyter_code># Groupby patients number with year
# Find out in which year did least patients make their appointments

min_year = df.groupby(df['appointment_day'].apply(lambda x: x.year))['diabetes'].count()
min_year.idxmin()<jupyter_output><empty_output><jupyter_text>> According to the result, least patients made their appointments in 2016, which indicates this dataset only provided the data from 2016.<jupyter_code># Groupby patients number with month

max_mon = df.groupby(df['appointment_day'].apply(lambda x: x.month))['diabetes'].count()

# Convert month number to month name in the index

max_mon.index = max_mon.index.map(lambda x: calendar.month_name[x])

# Find out in which month did most patients make their appointments

max_mon.idxmax()
# Print out the number of patients in the month when most patients made their appointment

max_mon.loc['May']<jupyter_output><empty_output><jupyter_text>> According to the result, most patients made their appointments in May, and the number is 80841.<jupyter_code># Groupby patients numbers with month

min_mon = df.groupby(df['appointment_day'].apply(lambda x: x.month))['diabetes'].count()

# Convert month number to month name in the index

min_mon.index = min_mon.index.map(lambda x: calendar.month_name[x])

# Find out in which month least patients made their appointments

min_mon.idxmin()
# Print out the number of patients in the month when least patients made their appointment

min_mon.loc['April']<jupyter_output><empty_output><jupyter_text>> According to the result, least patients made their appointments in April, and the number is 3235.<jupyter_code># Groupby patients numbers with day

max_day = df.groupby(df['appointment_day'].apply(lambda x: x.weekday()))['diabetes'].count()

# Convert day number to day name in the index

max_day.index = max_day.index.map(lambda x: calendar.day_name[x])

# Find out on which day did most patients made their appointments

max_day.idxmax()
# Print out the number of patients on the day when most patients made their appointment

max_day.loc['Wednesday']<jupyter_output><empty_output><jupyter_text>> According to the result, most patients made their appointments on Wednesday, and the number is 25867.<jupyter_code># Groupby patients numbers with day

min_day = df.groupby(df['appointment_day'].map(lambda x: x.weekday()))['diabetes'].count()

# Convert day number to day name in the index

min_day.index = min_day.index.map(lambda x: calendar.day_name[x])

# Find out on which day least patients made their appointments

min_day.idxmin()
# Print out the number of patients on the day when most patients made their appointment

min_day.loc['Saturday']<jupyter_output><empty_output><jupyter_text>> According to the result, least patients made their appointments on 'Saturday' , and the number is 39. > As all the data is from 2016, so the total patients number from the dastaset is 110527<jupyter_code># Add prettytable to print out the result for better view

x = PrettyTable()
y = PrettyTable()
x.field_names = ["max_year", "min_year", "max_month", "min_month", "max_day", "min_day"]
y.field_names = ["max_year_number", "min_year_number", "max_month_number", "min_month_number", \
                 "max_day_number", "min_day_number"]
x.add_row([max_year.idxmax(), min_year.idxmax(), max_mon.idxmax(), min_mon.idxmin(), \
           max_day.idxmax(), min_day.idxmin()])

y.add_row([max_year.loc[2016], min_year.loc[2016], max_mon.loc['May'], min_mon.loc['April'], \
          max_day.loc['Wednesday'],  min_day.loc['Saturday']])

print(x, y)<jupyter_output>+----------+----------+-----------+-----------+-----------+----------+
| max_year | min_year | max_month | min_month |  max_day  | min_day  |
+----------+----------+-----------+-----------+-----------+----------+
|   2016   |   2016   |    May    |   April   | Wednesday | Saturday |
+----------+----------+-----------+-----------+-----------+----------+ +-----------------+-----------------+------------------+------------------+----------------+----------------+
| max_year_number | min_year_number | max_month_number | min_month_number | max_day_number | min_day_number |
+-----------------+-----------------+------------------+------------------+----------------+----------------+
|      110527     |      110527     |      80841       |       3235       |     25867      |       39       |
+-----------------+-----------------+------------------+------------------+----------------+----------------+<jupyter_text>Research Question 5: Hou many patients's appintments were scheduled in the April and May of 2016? What is the relationship between scheduled_day and appointment_day?<jupyter_code># Locate the desired time period
# Calculate the numbers
a_m_mon = df.query('scheduled_day > "2016-03-31"')
a_m_mon.groupby(['scheduled_day'])['diabetes'].count().sum()<jupyter_output><empty_output><jupyter_text>Research Question 6:  Among all the 4 kinds of dieseas, which has highest morbility？<jupyter_code># Use query function to seperate each dieseas, calculate the ratio of patients, seperately
# and print using PrettyTable

dia = df.query('diabetes == 1').diabetes.value_counts().tolist()[0]
hyp = df.query('hypertension == 1').hypertension.value_counts().tolist()[0]
alc = df.query('alcoholism == 1').alcoholism.value_counts().tolist()[0]
han = df.query('handicap == 1').handicap.value_counts().tolist()[0]


dise_dict = {"diabetes":dia, "hypertension":hyp, "alcoholism":alc, "handicap":han}
z = PrettyTable()
z.field_names =["diabetes", "hypertension", "alcoholism", "handicap"]
z.add_row([dia, hyp, alc, han])

print(z)
print('{} has the highest mobility among the five kinds of dieseas, and the number is {}.'\
.format(max(dise_dict, key=dise_dict.get), max(dise_dict.values())))<jupyter_output>+----------+--------------+------------+----------+
| diabetes | hypertension | alcoholism | handicap |
+----------+--------------+------------+----------+
|   7943   |    21801     |    3360    |   2042   |
+----------+--------------+------------+----------+
hypertension has the highest mobility among the five kinds of dieseas, and the number is 21801.<jupyter_text>Research Question 7:  What is the most frequent neighbourhood, and it is mostly associated with what age group?<jupyter_code># Use groupby to find out the name most frequent neighbourhood

fre_neigh = df.groupby(['neighbourhood'])['age'].count()
fre_neigh.idxmax()
# Print out the corresponding frequency

fre_neigh.loc['JARDIM CAMBURI']
# Find out the max age in this group

nei_age = df.query('neighbourhood == "JARDIM CAMBURI"')
nei_age.age.mode()<jupyter_output><empty_output><jupyter_text>Research Question 8: What is the ratio of patients suffering from different kinds of diseases, respectively?<jupyter_code># Calculate the ratio of patients suffering from different kinds of diseases and plot

diab = df['diabetes'].sum()/df['diabetes'].count()
hype = df['hypertension'].sum()/df['hypertension'].count()
alco = df['alcoholism'].sum()/df['alcoholism'].count()
hand = df['handicap'].sum()/df['handicap'].count()


x = ['diabetes', 'hypertension', 'alcoholism', 'handicap']
y = [diab, hype, alco, hand]

plt.subplots(figsize=(10,6))
sns.barplot(x,y, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>Research Question 9: What is the ratio of both male and female patients suffering from different kinds of diseases, respectively?<jupyter_code># Seperate male and famele group by query function
# Calculate the ratio of each disease and plot

man = df.query('gender == "M"')
woman = df.query('gender == "F"')

dia_man = man['diabetes'].sum()/df['diabetes'].count()
dia_woman = woman['diabetes'].sum()/df['diabetes'].count()

hype_man = man['hypertension'].sum()/df['hypertension'].count()
hype_woman = woman['hypertension'].sum()/df['hypertension'].count()

alco_man = man['alcoholism'].sum()/df['alcoholism'].count()
alco_woman = woman['alcoholism'].sum()/df['alcoholism'].count()

hand_man = man['handicap'].sum()/df['handicap'].count()
hand_woman = man['handicap'].sum()/df['handicap'].count()

x1 = ['diabetes_man', 'diabetes_woman', 'hypertension_man', 'hypertension_woman',\
      'alcoholism_man', 'alcoholism_woman',\
      'handicap_man', 'handicap_woman']

y1 = [dia_man, dia_woman, hype_man, hype_woman,\
      alco_man, alco_woman, hand_man, hand_woman]


plt.subplots(figsize=(10,10))
plt.xticks(rotation=90);
sns.barplot(x1,y1, alpha = 0.8);
# plot bars

x2 = [dia_man, hype_man, alco_man, hand_man]
y2 = [dia_woman, hype_woman, alco_woman, hand_woman]

ind = np.arange(len(x2))
width = 0.35

plt.figure(figsize=(10,6))
man_chart = plt.bar(ind, x2, width, color='r', alpha=.7, label='Man')
woman_chart = plt.bar(ind + width, y2, width, color='g', alpha=.7, label='Woman')

# title and labels
plt.ylabel('Ratio')
plt.xlabel('Diseases')
plt.title('Ratio of patients by man and woman')
locations = ind + width / 2  # xtick locations
labels = ['diabetes', 'hypertension', 'alcoholism_man', 'handicap_man']  # xtick labels
plt.xticks(locations, labels)
#plt.subplots(figsize=(20, 10))
#plt.xticks(rotation=90);

# legend
plt.legend();
plt.show()<jupyter_output><empty_output><jupyter_text>Research Question 10:  Bwtween 16: 00  and 17: 00pm, how many patients's appointments were scheduled on daily bases?<jupyter_code># Use query to locate the group which was scheduled between 16: 00  and 17: 00 pm and find out how many patients
# in total

patients_1617 = df.query('scheduled_day.dt.hour.values >= 16 & scheduled_day.dt.hour.values < 17')
tot_pat = patients_1617.count().tolist()
tot_pat
# Find out how many days this group cross

patients_1617['mnth_yr'] = patients_1617['scheduled_day'].apply(lambda x: x.strftime('%d-%B-%Y'))
patients_1617['mnth_yr'].nunique()
# Calculate the average number on daily basis

avg_num = tot_pat[0]/patients_1617['mnth_yr'].nunique()

print('The number of patients whose appointments were scheduled between 16:00 and \
17:00 was {:.2f} on daily basis.'.format(avg_num))<jupyter_output>The number of patients whose appointments were scheduled between 16:00 and 17:00 was 71.05 on daily basis.<jupyter_text>Research Question 11: What factors are important for us to know in order to predict if a patient will show up for their scheduled appointment?<jupyter_code># Calculate the relationship of neighbourhood and show up

df1= df.copy()
df1['no_show'].replace(['Yes'], 0, inplace = True)
df1['no_show'].replace(['No'], 1, inplace = True)
nbr_show = df1.groupby(['neighbourhood'])['no_show'].sum()/df1.groupby(['neighbourhood'])['no_show'].count()

# Select the top 10 for plot

nbr_show = nbr_show.nlargest(n = 10)

plt.subplots(figsize=(12,6))
plt.xticks(rotation=90)
sns.barplot(nbr_show.index, nbr_show, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>> The neighbourhood actually had impact on showup, histogram indicated that patients from 'PARQUE INDUSTRIAL' area made 100% show up, much higher even compared to second highest show up ratio (around 85%) which is from ILHADO BOI area. The show up ratio from 2nd high to 10th high are not quite different, ranging from 80% to 85%.<jupyter_code># Seperate man and woman
# Calculate the relationship of neighbourhood and show up

man_part = df1.query('gender == "M"')
woman_part = df1.query('gender == "F"')
man_show = man_part.groupby(['gender'])['no_show'].sum()/man_part.groupby(['gender'])['no_show'].count()
woman_show = woman_part.groupby(['gender'])['no_show'].sum()/woman_part.groupby(['gender'])['no_show'].count()
# Plot

x_gender = ['M', 'F']
y_gender =[man_show.tolist()[0], woman_show.tolist()[0]]
#plt.subplots(figsize=(12,6))
#sns.barplot(x_gender, y_gender, alpha = 0.8);
sns.swarmplot(x=x_gender, y=y_gender);<jupyter_output><empty_output><jupyter_text>> The gender has almost no impact on show up as indicated by histogram, both genders manifest aournd 80% attendance.<jupyter_code># Drop seconds, mintutes, hours and days as those ranges are too narrow

df1['scheduled_day'] = df1['scheduled_day'].map(lambda x: x.strftime('%Y-%m'))

# Calculate the relationship of scheduled_day and show up
# Plot the top 10

sday_show = df1.groupby(['scheduled_day'])['no_show'].sum()/df1.groupby(['scheduled_day'])['no_show'].count()
sday_show = sday_show.nlargest(n = 10)
plt.subplots(figsize=(12,6))
sns.barplot(sday_show, sday_show.index, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>> The scheduled_day has important impact on show up as indicated by histogram, with November in 2005 the highest ratio which is 100%.<jupyter_code># Calculate the relationship of appointment_day and show up
# Drop seconds, mintutes, hours and days as those ranges are too narrow

df1['appointment_day'] = df1['appointment_day'].map(lambda x: x.strftime('%Y-%m'))
# Plot the top 10

aday_show = df1.groupby(['appointment_day'])['no_show'].sum()/df1.groupby(['appointment_day'])['no_show'].count()
aday_show = aday_show.nlargest(n = 10)
plt.subplots(figsize=(12,6))
sns.barplot(aday_show, aday_show.index, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>> The appointment_day has little important impact on show up as indicated by histogram.<jupyter_code># Plot the relationship for age and show up

age_show = df1.groupby(['age'])['no_show'].sum()/df1.groupby(['age'])['no_show'].count()
age_show = age_show.nlargest(n = 10)
plt.subplots(figsize=(12,6))
sns.barplot(age_show.index, age_show, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>> Age also seems has little impact on on show up as indicated by histogram, however, there is one group which is -1 year, which might be wrong data.<jupyter_code># Find the rows whose age == 1

df1.query('age == -1')
# As there is only one row of such error, we can simply delete it without affeting the previous and later on analysis

df1.drop(df.index[[99832]], inplace = True)

# Comfirm changes

df1.iloc[99832]

# Find out the relationship of the 4 kinds of disease and show up


hyp_show = df1.groupby(['hypertension'])['no_show'].sum()/df1.groupby(['hypertension'])['no_show'].count()
dia_show = df1.groupby(['diabetes'])['no_show'].sum()/df1.groupby(['diabetes'])['no_show'].count()
alc_show = df1.groupby(['alcoholism'])['no_show'].sum()/df1.groupby(['alcoholism'])['no_show'].count()
han_show = df1.groupby(['handicap'])['no_show'].sum()/df1.groupby(['handicap'])['no_show'].count()

x_diseases = ['hypertension', 'diabetes', 'alcoholism', 'handicap']
y_diseases = [hyp_show.tolist()[0], dia_show.tolist()[0], \
              alc_show.tolist()[0], han_show.tolist()[0]]

plt.subplots(figsize=(12,6))
sns.barplot(x, y, alpha = 0.8);<jupyter_output><empty_output><jupyter_text>> The kind of diseases has important impact on show up, with hypertension as the highest ratio at around 20%.<jupyter_code># Calculate the relationship of scholarship and show up

schlar_show = df1.groupby(['neighbourhood'])['no_show'].sum()/df1.groupby(['neighbourhood'])['no_show'].count()
schlar_show = schlar_show.nlargest(n = 10)
plt.subplots(figsize=(12,8))
sns.barplot(schlar_show.index, schlar_show, alpha = 0.8);
plt.xticks(rotation=90);<jupyter_output><empty_output><jupyter_text>> The scholarship has important impact on show up, with 'PARQUE INDUSTRIAL' as the highest ratio at 100%.  Conclusions> In current study, a good amount of profound analysis has been carried out. Prior to each step, deailed instructions was given and interpretions was also provided afterwards. The dataset included 110527 pieces of patients's information from only 2016, which is substantial but limited to only one year. Therefore, even based on such large amount of data, the analysis would not be very representative. > The good aspect of current study was it didn't include NaN values nor duplicates, which could affect the process of analysis.<jupyter_code>from subprocess import call
call(['python', '-m', 'nbconvert', 'Investigate_the_No-show_appointments_Databaset_20180108.ipynb'])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.147191062264408, 'fraction_numerical': 0.021345201871111313, 'mean_word_length': 4.7795275590551185, 'pattern_counts': {'":': 4, '<': 105, '<?xml version=': 0, '>': 137, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '27', 'dir': 'jupyter-structured-clean-dedup', 'id': '185880', 'license': 'MIT', 'n_tokens_mistral': 7247, 'n_tokens_neox': 6686, 'n_words': 2183, 'path': 'Investigate_the_No-show_appointments_Databaset_20180108.ipynb', 'repo_name': 'Shilin0806/Investigate_the_No-show_appointments_Database', 'size': '22019'}