setwd('E:/audit/20354')
library(lubridate)
data=read.csv('data-2019-02-06.csv')
data$date_saturday=as.Date(data$date_saturday,origin='1996-01-01')
head(data)

##transform
table=data.frame(who_region='AMR',
                ISO_A1='HTI',
                ISO_A2_L1=data$department,
                ISO_A2_L2='',
                TL=data$date_saturday-6,
                TR=data$date_saturday,
                Primary=TRUE,
                Phantom=FALSE,
                sCh=data$n_case_seen)
head(table)

##update departments (ISO_A2_L1 to ISO code)
table$ISO_A2_L1=as.character(table$ISO_A2_L1)
unique(table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Artibonite','HT-AR',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Centre','HT-CE',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=="Grand'Anse",'HT-GA',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Nippes','HT-NI',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Nord','HT-ND',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Nord Est','HT-NE',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Nord Ouest','HT-NO',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Ouest','HT-OU',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Sud','HT-SD',table$ISO_A2_L1)
table$ISO_A2_L1=ifelse(table$ISO_A2_L1=='Sud Est','HT-SE',table$ISO_A2_L1)

##Port au Prince is an arrondissement, should be at ISO_A2_L2 level;
table$ISO_A2_L2=as.character(table$ISO_A2_L2)
table$ISO_A2_L2=ifelse(table$ISO_A2_L1=='Port au Prince','Port au Prince',table$ISO_A2_L2)
table$ISO_A2_L1=ifelse(table$ISO_A2_L2=='Port au Prince','HT-OU',table$ISO_A2_L1)

write.csv(table,'new20354(1).csv',row.names = F,na = '')