# 9-4 Exercise 1 - Building a large dataframe

# The data is held in three files
#    AugustNumbers.csv
#    JulyNumbers.csv
#    JuneNumbers.csv
#    HospitalLocations.csv
# These are within the SP09 folder of the current working directory
# The files contain headers

# Load August file

augustpatients<-read.csv("SP09/AugustNumbers.csv", header=TRUE)

# Display augustpatients

augustpatients

# Load July file

julypatients<-read.csv("SP09/JulyNumbers.csv", header=TRUE)

# Display julypatients

julypatients

# Load June file

junepatients<-read.csv("SP09/JuneNumbers.csv", header=TRUE)

# Display junepatients

junepatients

# Create a new dataframe, allpatients, by rbinding julypatients
# to augustpatients

allpatients<-rbind(augustpatients,julypatients, junepatients)

# Display allpatients

allpatients

# Merge allpatients with location data from HospitalLocations.csv

# Load HospitalLocations file

hospitallocations<-read.csv("SP09/HospitalLocations.csv", header=TRUE)

# Display hospitallocations

hospitallocations

# Merge using by function
# by.x is the primary key, by.y is the foreign key

withloc<-merge(hospitallocations,
               allpatients,
               by.x="LocationID", 
               by.y="Location")

# Order the dataframe by the HospID field

withloc<-withloc[order(withloc$HospID),]

# Display the dataframe

withloc