Week 1, day 2

I planned to examine relationship between water source availabilities and number of reported malaria cases. Therefore, I downloaded data about water sources and malaria cases from WHO.
malaria
water
However, the both files about water sources need to be reorganized so they can be merged later.

library(dplyr)

## 
## Attaching package: 'dplyr'

## The following objects are masked from 'package:stats':
## 
##     filter, lag

## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union

water_original = read.csv("~/Documents/summerproj/data_science_programming/week_1/day2/water.csv")
water_original = water_original[-c(1,2),]
for(i in c(2000:2015))
{
  for(k in c(1:6))
  {
    if( k == 1 || k == 4)
    {
      areaName = "rural"
    }
    if( k == 2 || k == 5)
    {
      areaName = "urban"
    }
    if( k == 3 || k == 6)
    {
      areaName = "total"
    }
    if( k == 1 || k == 2 || k == 3)
    {
      basicOrSafeName = "basic"
    }
    if( k == 4 || k == 5 || k == 6)
    {
      basicOrSafeName = "safe"
    }
    water_tmp = cbind(select(water_original, 1), year = i, area = areaName, basicOrSafe = basicOrSafeName, select(water_original,(98-(6*(i-1999))+k-1)))
    names(water_tmp) = c("country", "year", "area", "basicOrSafe", "percentage")
    if ( i == 2000 && k == 1)
    {
      water = water_tmp
    }
    else
    {
      water = rbind(water, water_tmp)
    }
  }
}
rownames(water) = NULL
head(water) #print a few lines to see the result

##               country year  area basicOrSafe percentage
## 1         Afghanistan 2000 rural       basic         21
## 2             Albania 2000 rural       basic         82
## 3             Algeria 2000 rural       basic         83
## 4             Andorra 2000 rural       basic        100
## 5              Angola 2000 rural       basic         24
## 6 Antigua and Barbuda 2000 rural       basic

write.csv(water, file = "~/Documents/summerproj/data_science_programming/week_1/day2/water_rearranged.csv")

Here’s the result: Then do the same thing for the malaria.csv:

library(dplyr)

malaria_original = read.csv("~/Documents/summerproj/data_science_programming/week_1/day2/malaria.csv", skip = 1)

for(i in 2000:2017)
{
  malaria_tmp = cbind(select(malaria_original,1), year = i, select(malaria_original,(20-(i-1999))))
  names(malaria_tmp) = c("country", "year", "numberOfCases")
  if ( i == 2000)
  {
    malaria = malaria_tmp
  }
  else
  {
    malaria = rbind(malaria, malaria_tmp)
  }
}
head(malaria) #print a few lines to see the result

##       country year numberOfCases
## 1 Afghanistan 2000         94475
## 2     Algeria 2000           541
## 3      Angola 2000            NA
## 4   Argentina 2000           440
## 5     Armenia 2000           141
## 6  Azerbaijan 2000          1526

write.csv(malaria, file = "~/Documents/summerproj/data_science_programming/week_1/day2/malaria_rearranged.csv")

finally, merge the result:

library(dplyr)

malaria = read.csv("~/Documents/summerproj/data_science_programming/week_1/day2/malaria_rearranged.csv")
water = read.csv("~/Documents/summerproj/data_science_programming/week_1/day2/water_rearranged.csv")
mergedResult = merge(filter(water, area == "total" & basicOrSafe == "basic"), malaria, c("country", "year"))
head(mergedResult) #print a few lines to see the result

##       country year  X.x  area basicOrSafe percentage X.y numberOfCases
## 1 Afghanistan 2000  389 total       basic         27   1         94475
## 2 Afghanistan 2001 1553 total       basic         27 109         50850
## 3 Afghanistan 2002 2717 total       basic         30 217        415356
## 4 Afghanistan 2003 3881 total       basic         32 325        360940
## 5 Afghanistan 2004 5045 total       basic         35 433        242022
## 6 Afghanistan 2005 6209 total       basic         37 541        116444

write.csv(arrange(mergedResult, desc(numberOfCases)), file = "~/Documents/summerproj/data_science_programming/week_1/day2/merged.csv")

The complete results are too big to be printed here, but the files can be found in the following link: water malaria combined