setwd('E:/audit/3182')
data=read.csv('DistrictData_Annual_current.csv')
head(data)
data=data[,-2]
data=data[-nrow(data),]
head(data)

library(tidyr)
data_long=gather(data,year,sCh,X2001:X2016)
head(data_long)
data_long$year=substr(data_long$year,2,5)


#location
std_loc=read.csv('region_location_ISO code.csv')
head(std_loc)
data1=merge(data_long,std_loc,by='District')
head(data1)

#time
data1$TL=as.Date('1996-01-01',origin='1996-01-01')
data1$TR=as.Date('1996-01-01',origin='1996-01-01')

data1[data1$year=='2001',]$TL=as.Date('2000-11-01',origin='1996-01-01')
data1[data1$year=='2001',]$TR=as.Date('2001-10-31',origin='1996-01-01')

data1[data1$year=='2002',]$TL=as.Date('2001-11-01',origin='1996-01-01')
data1[data1$year=='2002',]$TR=as.Date('2002-10-31',origin='1996-01-01')

data1[data1$year=='2003',]$TL=as.Date('2002-11-01',origin='1996-01-01')
data1[data1$year=='2003',]$TR=as.Date('2003-10-31',origin='1996-01-01')

data1[data1$year=='2004',]$TL=as.Date('2003-11-01',origin='1996-01-01')
data1[data1$year=='2004',]$TR=as.Date('2004-10-31',origin='1996-01-01')

data1[data1$year=='2005',]$TL=as.Date('2004-11-01',origin='1996-01-01')
data1[data1$year=='2005',]$TR=as.Date('2005-10-31',origin='1996-01-01')

data1[data1$year=='2006',]$TL=as.Date('2005-11-01',origin='1996-01-01')
data1[data1$year=='2006',]$TR=as.Date('2006-10-31',origin='1996-01-01')

data1[data1$year=='2007',]$TL=as.Date('2006-11-01',origin='1996-01-01')
data1[data1$year=='2007',]$TR=as.Date('2007-10-31',origin='1996-01-01')

data1[data1$year=='2008',]$TL=as.Date('2007-11-01',origin='1996-01-01')
data1[data1$year=='2008',]$TR=as.Date('2008-10-31',origin='1996-01-01')

data1[data1$year=='2009',]$TL=as.Date('2008-11-01',origin='1996-01-01')
data1[data1$year=='2009',]$TR=as.Date('2009-10-31',origin='1996-01-01')

data1[data1$year=='2010',]$TL=as.Date('2009-11-01',origin='1996-01-01')
data1[data1$year=='2010',]$TR=as.Date('2010-10-31',origin='1996-01-01')

data1[data1$year=='2011',]$TL=as.Date('2010-11-01',origin='1996-01-01')
data1[data1$year=='2011',]$TR=as.Date('2011-10-31',origin='1996-01-01')

data1[data1$year=='2012',]$TL=as.Date('2011-11-01',origin='1996-01-01')
data1[data1$year=='2012',]$TR=as.Date('2012-10-31',origin='1996-01-01')

data1[data1$year=='2013',]$TL=as.Date('2012-11-01',origin='1996-01-01')
data1[data1$year=='2013',]$TR=as.Date('2013-10-31',origin='1996-01-01')

data1[data1$year=='2014',]$TL=as.Date('2013-11-01',origin='1996-01-01')
data1[data1$year=='2014',]$TR=as.Date('2014-10-31',origin='1996-01-01')

data1[data1$year=='2015',]$TL=as.Date('2014-11-01',origin='1996-01-01')
data1[data1$year=='2015',]$TR=as.Date('2015-10-31',origin='1996-01-01')

data1[data1$year=='2016',]$TL=as.Date('2015-11-01',origin='1996-01-01')
data1[data1$year=='2016',]$TR=as.Date('2016-10-31',origin='1996-01-01')

head(data1)


##transform the data set
data2=data.frame(who_region='AFR',
                ISO_A1='MWI',ISO_A2_L1=data1$Parent.subdivision,
                ISO_A2_L2=data1$X3166.2.code,TL=data1$TL,TR=data1$TR,Primary=TRUE,Phantom=FALSE,sCh=data1$sCh)
head(data2)

#get rid of NA values in sCh
table(is.na(data2$sCh))
data3=data2[is.na(data2$sCh)==F,]
write.csv(data3,'new3182(1).csv',row.names = F)