Q1.Q2

1. Which variables in the person table have missing values? What number and proportion of those variables are missing?

#Check for values with NA
person_1000 %>%
  summarise_all(~sum(is.na(.))) %>%
        gather(parameters, missing_values) %>% #create new columns to store the missing values for each parameter
        filter(missing_values > 0) %>% #only show parameters with missing values
        mutate(proportion_missing = missing_values/nrow(person_1000)) #proportion calc. 

NA

2. Which variables in the person table have suspicious-looking values? (Choose 3 suspicious values to list, and explain why they are not what you expect).

#check +/- suspicious values in year_of_birth variable by identifying if there are any values that are not within the expected range of ~1900-2024
range(person_1000$year_of_birth) 
#1909 - 1983
range(person_1000$month_of_birth)
#1 1 
range(person_1000$day_of_birth)
#1 1
#identify if patients recruited for study were from diverse racial backgrounds
unique(person_1000$race_concept_id)
#8527; signifes White
unique(person_1000$ethnicity_concept_id)
#38003564; signifies non-Hispanic

1. What is the concept ID for Congestive Heart Failure?
2. What clinical concept is represented by the concept ID of 8507?

chf_observations <- concept_1000 %>%
  filter(grepl("congestive.*heart failure|heart failure.*congestive", concept_name, ignore.case = TRUE))
print(chf_observations)

NA

1. How many patients are there for each year included in the dataset?

#Construct a table from person_1000 outlining the count of patients for each value under "year_of_birth"
q3a<-person_1000 %>%
  count(year_of_birth)
knitr::kable(q3a, format = "markdown") #display the table in markdown format

2. What are the distributions of patients by race, ethnicity, and gender?

r_person_1000 <- person_1000 %>% 
        group_by(race_concept_id) %>%
        summarise(count=n()) %>% #creates a new column, 'count', to store the number of patients that corresponds to each unique value of race_concept_id
        mutate(proportion=count/sum(count))
print(r_person_1000)

e_person_1000 <- person_1000 %>%
        group_by(ethnicity_concept_id) %>%
        summarise(count=n()) %>%
        mutate(proportion=count/sum(count))
print(e_person_1000)

g_person_1000 <- person_1000 %>%
        group_by(gender_concept_id) %>%
        summarise(count=n()) %>%
        mutate(proportion=count/sum(count))
print(g_person_1000)

person_1000 %>% 
        count(ethnicity_concept_id) %>%
        ggplot(aes(x=ethnicity_concept_id,y=n)) +
        geom_bar(stat="identity")+
        labs(title = "Distribution of Patients by Ethnicity",
             x = "Ethnicity",
             y = "Number of Patients") +
        theme_bw()

3. What is the distribution of patients by year of birth and month of birth?

yob_person_1000 <- person_1000 %>% 
        group_by(year_of_birth) %>%
        summarise(count=n()) %>%
        mutate(percent=((count/sum(count)*100)))
print(yob_person_1000)
#Verify wrangle was correct by ensuring the sum of the percentages is 100
yob_check <-c(yob_person_1000$percent)
sum(yob_check)

knitr::kable(yob_person_1000, format = "markdown")

#Plot the distribution of patients by year of birth
ggplot(yob_person_1000, aes(x = factor(year_of_birth), y = count)) +
  geom_bar(stat = "identity") +
  coord_flip() + #allows for horizontal bar graph
        geom_text(aes(label = count), hjust = -0.1,size=2.5) +
  labs(title = "Distribution of Patients by Year of Birth",
       x = "Year of Birth",
       y = "Number of Patients") +
  theme_minimal() +
        scale_y_continuous(breaks = seq(0,70,by=10))

mob_person_1000 <- person_1000 %>%
        group_by(month_of_birth) %>%
        summarise(count=n()) %>%
        mutate(percent=((count/sum(count)*100)))
print(mob_person_1000)

mob_check <-c(mob_person_1000$percent)
sum(mob_check)

4. What is the distribution of patients in different states? Which states have the most patients? And the least?

state_location_1000 <- location_1000 %>% 
        group_by(state) %>%
        summarise(count=n()) %>%
        mutate(percent=((count/sum(count)*100))) %>% 
        arrange(desc(count))
print(state_location_1000)

#Plot the distribution of patients by state
ggplot(state_location_1000, aes(x = reorder(state, count), y = count)) +
  geom_bar(stat = "identity") +
  coord_flip() +
        geom_text(aes(label = count), hjust = -0.1,size=2.5) +
  labs(title = "Distribution of Patients by State",
       x = "State",
       y = "Number of Patients") +
  theme_minimal() +
        theme(axis.text.y = element_text(size = 6))

e.How many of these patients have death data? Which state had the most deaths? Are there any states that saw no deaths?

#Only a select number of patients have death data, so we will filter for those patients from the person_1000 dataset, since the person_1000 dataset contains location_id, corresponding to states. 
death_states <- person_1000 %>%
        filter(person_id %in% death_1000$person_id) %>%
        group_by(location_id) %>%
        summarise(count=n())
print(death_states)

#Join the death_states dataset with the location_1000 dataset to obtain the state names
death_states_0 <- death_states %>%
        left_join(location_1000, by = c("location_id" = "location_id")) %>%
        select(state, count)
print(death_states_0)

#Determine states that did not contain any deaths
dead_states <- c(death_states_0$state) #vector of states that had deaths
all_states <- c(state_location_1000$state) #vector of all states
no_death_states <- setdiff(all_states, dead_states) #states that did not have any deaths
print(no_death_states)

#Determine the year with the most deaths per death_1000
death_1000 %>%
        group_by(death_DATE) %>%
        summarise(count=n()) %>%
        arrange(desc(count))

1. What are the 5 most prevalent conditions in this dataset?

indication_df <- condition_occurrence_1000 %>%
        group_by(condition_concept_id) %>% #x-digit code for the condition
        summarise(count=n()) %>%
        arrange(desc(count))

print(indication_df)
#Join the indication_df with concept_1000 to obtain the condition names
indication_df0 <- indication_df %>%
        left_join(concept_1000, by = c("condition_concept_id" = "concept_id")) %>% 
        select(concept_name,count)
print(indication_df0)

2. Which condition was most prevalent in 2010?

#Filter for obs. in 2010 only 
condition_2010 <- condition_occurrence_1000 %>%
        filter(condition_start_DATE >= "2010-01-01" & condition_start_DATE <= "2010-12-31") %>%
        group_by(condition_concept_id) %>%
        summarise(count=n()) %>%
        arrange(desc(count))
#Join the condition_2010 with concept_1000 to obtain the condition names
condition_2010x <- condition_2010 %>%
        left_join(concept_1000, by = c("condition_concept_id" = "concept_id")) %>% 
        select(concept_name,count)
print(condition_2010x)

3. What was the most prevalent condition in CA (looking across all years)?

#Filter for patients from location_1000 to obtain location_id's specific for California
dfca1 <- location_1000 %>%
        filter(state == "CA") %>%
        select(location_id,state) %>% 
        distinct()
ca_id <- dfca1$location_id #Out of >3k location_id's, sixty represent california 
#Identify California-only pt. by searching for location_id's present from ca_id in person_1000
dfca2 <- person_1000 %>% 
        filter(location_id %in% ca_id) %>% #California-only pt. info
        distinct()

ca_ptinfo <- dfca2$person_id #person-id's for patients in California;86

#Filter for the patient_ID's from condition_occurrence_1000
dfca3 <- condition_occurrence_1000 %>%
        inner_join(dfca2, by = "person_id") %>% 
        select(condition_concept_id,person_id)
dfca4 <- dfca3 %>%
        group_by(condition_concept_id) %>%
        summarise(count=n()) %>%
        arrange(desc(count))
#Join the dfca4 with concept_1000 to obtain the condition names
dfca5 <- dfca4 %>%
        left_join(concept_1000, by = c("condition_concept_id" = "concept_id")) %>% 
        select(concept_name,count)

4. Which state had the highest incidence of Congestive Heart Failure across all years?

#Concept ID for CHF is 319835 
#Merge the location_1000 dataset with the person_1000 dataset to obtain the State for each person_id
person_location <- person_1000 %>%
        left_join(location_1000, by = c("location_id" = "location_id")) %>%
        select(person_id,state)
#Merge person_location with condition_occurrence_1000 dataset but filtering when condition_concept_id == 319835
chf_states <- condition_occurrence_1000 %>%
        filter(condition_concept_id == 319835) %>%
        inner_join(person_location, by = "person_id") %>%
        group_by(state) %>%
        summarise(count=n()) %>%
        arrange(desc(count))

1. What are the 5 most commonly used drugs?

drug_exposure_1000 %>%
        group_by(drug_concept_id) %>%
        summarise(count=n()) %>%
        arrange(desc(count)) %>%
        left_join(concept_1000, by = c("drug_concept_id" = "concept_id")) %>%
        select(concept_name,count) %>%
        head(5)

2. What is the average number of drugs per person?

#Group the drug_exposure_1000 dataset by person_id and count the number of drugs each person has taken
drug_per_person <- drug_exposure_1000 %>%
        group_by(person_id) %>%
        summarise(count=n())
#Calculate the average number of drugs per person
avg_drugs_per_person <- mean(drug_per_person$count)

3. What is the average duration that the Simvastatin 40 mg oral tablet is taken for?

#Filter the drug_exposure_1000 dataset for the drug_concept_id for Simvastatin 40 mg oral tablet, where drug_concept_ID == 1539407 | 1539406
simvastatin <- drug_exposure_1000 %>%
        filter(drug_concept_id == 1539407 | drug_concept_id == 1539406) %>% 
        select(drug_exposure_id, person_id, drug_concept_id, drug_exposure_start_DATE, drug_exposure_end_DATE) %>%
        mutate(duration = as.numeric(difftime(drug_exposure_end_DATE, drug_exposure_start_DATE, units = "days")))
mean(simvastatin$duration, na.rm = TRUE)