NDMA_DeIdentification_Embu_2000_2020
Pheroze
2024-10-18
NDMA Data Pre processing
The following are steps undertaken for deidentifying NDMA data. The data is dis aggregated per county for all 23 counties - ASAL. The information covers the years of 2000 - 2020, where data prior to 2016 was stored in a different database (REWAS) and data from 2016 henceforth in the new database (DEWS). In each county data set workbook there are 6 different sheets:
HHA REWAS, HHA DEWS, KIA REWAS, KIA DEWS, MUAC REWAS, MUAC DEWS
The process involves inspecting individual sheets for each data set, dropping P.I.I columns, and then writing all the sheets to a single workbook - Embu.
EMBU
1. HHA REWAS
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, unionlibrary(ggplot2)## Warning: package 'ggplot2' was built under R version 4.3.2library(geosphere)## Warning: package 'geosphere' was built under R version 4.3.3## The legacy packages maptools, rgdal, and rgeos, underpinning the sp package,
## which was just loaded, will retire in October 2023.
## Please refer to R-spatial evolution reports for details, especially
## https://r-spatial.org/r/2023/05/15/evolution4.html.
## It may be desirable to make the sf package available;
## package maintainers should consider adding sf to Suggests:.
## The sp package is now running under evolution status 2
## (status 2 uses the sf package in place of rgdal)library(openxlsx)## Warning: package 'openxlsx' was built under R version 4.3.3file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
hha_rewas_data <- read.xlsx(file_path, sheet = "HHA REWAS")There are no P.I.I columns in this particular sheet so we save it to a new workbook and populate the rest of the sheets sequentially to the same Embu workbook after relevant pre processing steps have been taken.
# Define the path for the new Excel workbook
new_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Create a new workbook
wb <- createWorkbook()
# Add the HHA-REWAS data to the new Embu workbook
addWorksheet(wb, "HHA REWAS")
writeData(wb, "HHA REWAS", hha_rewas_data)
# Save the new workbook
saveWorkbook(wb, new_file_path, overwrite = TRUE)2. HHA DEWS
The geocoordinates in the HHA dataset represent household coordinates, we will mask them (random displacement) using the Haversine Formula to randomly distribute a point around a central coordinate within a radius of 2.5 KM and drop other P.I.I.s.
library(openxlsx)
file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
hha_dews_data <- read.xlsx(file_path, sheet = "HHA DEWS")The dataset contains household coordinates in columns “Lat” and “Long” which are considered P.I.I’s so we mask the coordinates, verify by plotting a histogram of the distribution of displacement distances of the original and displaced coordinates to establish uniformity.
We check for and deal with outliers if any in the “Lat” and “Long” columns
# Verify the dataset
summary(hha_dews_data$Lat)## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## -1.038 -0.675 -0.461 -0.529 -0.453 0.000 3365summary(hha_dews_data$Long)## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.00 37.42 37.64 32.98 37.74 37.90 3365There seems to be erroneous entries. 3365 rows also dont have entries for coordinates.
# Replace (0,0) coordinates with NA only in the Lat and Long columns
hha_dews_data$Lat[hha_dews_data$Lat == 0 & hha_dews_data$Long == 0] <- NA
hha_dews_data$Long[hha_dews_data$Lat == 0 & hha_dews_data$Long == 0] <- NA
# Calculate the mean for Lat and Long, ignoring NA values
lat_mean <- mean(hha_dews_data$Lat, na.rm = TRUE)
lon_mean <- mean(hha_dews_data$Long, na.rm = TRUE)
lat_sd <- sd(hha_dews_data$Lat, na.rm = TRUE)
lon_sd <- sd(hha_dews_data$Long, na.rm = TRUE)
# Calculate Z-scores
hha_dews_data <- hha_dews_data %>%
mutate(lat_z = (Lat - lat_mean) / lat_sd,
lon_z = (Long - lon_mean) / lon_sd)
# Set threshold for identifying outliers
threshold <- 3 # Common threshold for Z-scores
# Replace outliers with NA
hha_dews_data <- hha_dews_data %>%
mutate(Lat = ifelse(abs(lat_z) > threshold & !is.na(lat_z), NA, Lat),
Long = ifelse(abs(lon_z) > threshold & !is.na(lon_z), NA, Long))
# Remove the Z-score columns
hha_dews_data <- hha_dews_data %>%
select(-lat_z, -lon_z)
# Verify the dataset
summary(hha_dews_data$Lat)## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## -1.038 -0.701 -0.584 -0.604 -0.455 -0.317 3726summary(hha_dews_data$Long)## Min. 1st Qu. Median Mean 3rd Qu. Max. NA's
## 0.00 37.42 37.64 32.98 37.74 37.90 3365400 rows in total are affected by the outlier and are replaced with NAs, bringing the total to 3726 NAs. We proceed to mask the coordinates.
# Create backup columns for original coordinates
hha_dews_data$Original_Lat <- hha_dews_data$Lat
hha_dews_data$Original_Long <- hha_dews_data$Long
# Function to generate random displaced coordinates with uniform distance distribution
mask_coordinates_uniform <- function(lat, lon, radius_km) {
R <- 6371 # Earth radius in kilometers
# Random bearing angle (in radians)
bearing <- runif(1, 0, 2 * pi)
# Random distance uniformly sampled from [0, radius_km]
rand_dist <- runif(1, 0, radius_km) / R # Uniformly sampled distance in radians
# Convert original coordinates to radians
lat_rad <- lat * pi / 180
lon_rad <- lon * pi / 180
# Calculate new latitude (in radians)
new_lat <- asin(sin(lat_rad) * cos(rand_dist) +
cos(lat_rad) * sin(rand_dist) * cos(bearing))
# Calculate new longitude (in radians)
new_lon <- lon_rad + atan2(sin(bearing) * sin(rand_dist) * cos(lat_rad),
cos(rand_dist) - sin(lat_rad) * sin(new_lat))
# Convert back to degrees
new_lat <- new_lat * 180 / pi
new_lon <- new_lon * 180 / pi
return(c(new_lat, new_lon))
}
# Set displacement radius in kilometers
radius_km <- 2.5
# Generate masked coordinates for each row using the modified function
masked_coords <- t(apply(hha_dews_data, 1, function(row) {
mask_coordinates_uniform(as.numeric(row["Original_Lat"]), as.numeric(row["Original_Long"]), radius_km)
}))
# Replace the original Lat and Long columns with the masked coordinates
hha_dews_data$Lat <- masked_coords[, 1]
hha_dews_data$Long <- masked_coords[, 2]We then evaluate the distribution of the displacement distances before dropping the original coordinates by plotting a histogram.
# Calculate displacement distances (in kilometers) as before
displacement_distances <- distHaversine(
cbind(hha_dews_data$Long, hha_dews_data$Lat), # Masked coordinates
cbind(hha_dews_data$Original_Long, hha_dews_data$Original_Lat) # Original coordinates
) / 1000 # Convert meters to kilometers
# Add displacement distances to the dataset for further analysis
hha_dews_data$Displacement_Distance <- displacement_distances
# Plot: Histogram of displacement distances
ggplot(hha_dews_data, aes(x = Displacement_Distance)) +
geom_histogram(binwidth = 0.1, fill = "skyblue", color = "black") +
labs(title = "Distribution of Displacement Distances",
x = "Displacement Distance (km)", y = "Count") +
theme_minimal()## Warning: Removed 3726 rows containing non-finite values (`stat_bin()`).
There are no significant peaks or valleys in the histogram, suggesting that the displacements are indeed more uniformly distributed, as intended.
We then drop the original coordinates column leaving only the masked coordinates columns. We also drop other PII’s which are the “HouseholdName”, “HouseHoldHead”, and “RespondentName”.
# Drop the specified PII columns along with original coordinates
hha_dews_data <- hha_dews_data %>%
select(-c(Original_Lat, Original_Long, Displacement_Distance, HouseholdName, HouseHoldHead, RespondentName))
# Check the updated dataset
head(hha_dews_data)## QID County SubCounty Ward LivelihoodZone Month Year Lat Long
## 1 9728 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## 2 9742 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## 3 9752 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## 4 9760 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## 5 9768 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## 6 10239 Embu Mbeere South Mwea Mixed Farming July 2016 NA NA
## InterviewDate HouseholdCode HeadEducationLevel MainHHIncomeSource HeadGender
## 1 42574 24 <NA> <NA> Male
## 2 42574 25 <NA> <NA> Male
## 3 42574 26 <NA> <NA> Male
## 4 42574 27 <NA> <NA> Male
## 5 42574 28 <NA> <NA> Male
## 6 42574 29 <NA> <NA> Male
## RespondentGender MaleMembers FemaleMembers ChildrenBelow5 KeepLivestock
## 1 Female 2 2 0 TRUE
## 2 Male 3 1 4 TRUE
## 3 Female 4 6 10 TRUE
## 4 Female 3 2 5 TRUE
## 5 Female 3 3 6 TRUE
## 6 Male 3 2 5 TRUE
## MilkAnimals MilkSource HowOftenMilked AverageMilkedPerDay
## 1 FALSE <NA> NA NA
## 2 FALSE <NA> NA NA
## 3 FALSE <NA> NA NA
## 4 TRUE <NA> NA NA
## 5 FALSE <NA> NA NA
## 6 FALSE <NA> NA NA
## AverageMilkConsumedPerDay WhoDrankMilk AverageMilkPrice
## 1 NA Children under 5 years NA
## 2 NA Children under 5 years NA
## 3 NA Breast feeding mothers NA
## 4 NA Everyone in the household NA
## 5 NA Children under 5 years NA
## 6 NA Children under 5 years NA
## HarvestedInLastWeeks AcresHarvested BagsHarvested HaveFoodStock
## 1 TRUE NA NA FALSE
## 2 TRUE NA NA TRUE
## 3 TRUE NA NA FALSE
## 4 TRUE NA NA FALSE
## 5 TRUE NA NA FALSE
## 6 TRUE NA NA FALSE
## FoodStockSources DaysStockLast WaterSource1 WaterSource2 WaterSource3
## 1 Production 0 Rivers Pans and dams Boreholes
## 2 Production 30 Rivers Pans and dams Boreholes
## 3 Production 0 Rivers Pans and dams Boreholes
## 4 Production 0 Rivers Boreholes Pans and dams
## 5 Production 0 Rivers Boreholes Pans and dams
## 6 Production 0 Rivers Pans and dams Boreholes
## NormalWaterSource WhyNotNormalWaterSource DaysWaterSourceExpectedToLast
## 1 TRUE Breakdown of water source 5
## 2 TRUE Breakdown of water source 5
## 3 TRUE Breakdown of water source 5
## 4 TRUE Breakdown of water source 5
## 5 TRUE Breakdown of water source 5
## 6 TRUE Breakdown of water source 5
## DistanceFromWaterSource NoWaterJerryCans JerryCansCost
## 1 1.5 5 0
## 2 1.5 5 0
## 3 2.0 5 0
## 4 2.5 4 10
## 5 2.0 4 0
## 6 2.0 4 10
## NormalHHWaterConsumption HHPayForWater CostTransportJerryCan
## 1 0 FALSE 0
## 2 0 FALSE 0
## 3 0 FALSE 20
## 4 0 TRUE 0
## 5 0 FALSE 0
## 6 0 TRUE 0
## TreatWaterBeforeDrinking WaterTreatmentMethodUsed CSI_ReliedOnLess
## 1 FALSE <NA> 2
## 2 FALSE <NA> 2
## 3 FALSE <NA> 3
## 4 FALSE <NA> 3
## 5 FALSE <NA> 3
## 6 FALSE <NA> 3
## CSI_BorrowedFood CSI_ReducedNoOfMeals CSI_ReducedPortionMealSize
## 1 0 0 0
## 2 0 0 0
## 3 0 0 0
## 4 0 0 0
## 5 0 0 0
## 6 0 0 0
## CSI_QuantityForAdult CSI_SoldHouseholdAssets CSI_ReducedNonFoodExpenses
## 1 0 1 1
## 2 0 1 1
## 3 0 1 1
## 4 0 1 1
## 5 0 1 1
## 6 0 1 1
## CSI_SoldProductiveAssets CSI_SpentSavings CSI_BorrowedMoney CSI_SoldHouseLand
## 1 1 1 NA 1
## 2 1 1 NA 1
## 3 1 1 NA 1
## 4 1 1 NA 1
## 5 1 1 NA 1
## 6 1 1 NA 1
## CSI_WithdrewChildrenSchool CSI_SoldLastFemaleAnimal CSI_Begging
## 1 1 1 1
## 2 1 1 1
## 3 1 1 1
## 4 1 1 1
## 5 1 1 1
## 6 1 1 1
## CSI_SoldMoreAnimals HFC_GrainDays HFC_GrainSource HFC_RootsDays
## 1 1 7 1 NA
## 2 1 7 1 NA
## 3 1 5 1 NA
## 4 1 4 1 NA
## 5 1 4 1 NA
## 6 1 4 1 NA
## HFC_RootsSource HFC_PulsesNutsDays HFC_PulsesNutsSource HFC_OrangeVegDays
## 1 NA 3 5 NA
## 2 NA 4 1 NA
## 3 NA 2 1 NA
## 4 NA 3 5 NA
## 5 NA 2 5 NA
## 6 NA 2 1 NA
## HFC_OrangeVegSource HFC_GreenLeafyDays HFC_GreenLeafySource HFC_OtherVegDays
## 1 NA NA NA 7
## 2 NA NA NA 2
## 3 NA NA NA 7
## 4 NA NA NA 7
## 5 NA NA NA 7
## 6 NA NA NA 7
## HFC_OtherVegSource HFC_OrangeFruitsDays HFC_OrangeFruitsSource
## 1 5 NA NA
## 2 5 NA NA
## 3 5 NA NA
## 4 5 NA NA
## 5 5 NA NA
## 6 5 NA NA
## HFC_OtherFruitsDays HFC_OtherFruitsSource HFC_MeatDays HFC_MeatSource
## 1 3 5 1 5
## 2 0 10 1 5
## 3 1 1 2 5
## 4 1 1 2 5
## 5 0 10 1 5
## 6 1 5 1 5
## HFC_LiverDays HFC_LiverSource HFC_FishDays HFC_EggsDays HFC_EggsSource
## 1 NA NA NA NA NA
## 2 NA NA NA NA NA
## 3 NA NA NA NA NA
## 4 NA NA NA NA NA
## 5 NA NA NA NA NA
## 6 NA NA NA NA NA
## HFC_MilkDays HFC_MilkSource HFC_OilDays HFC_OilSource HFC_SugarDays
## 1 1 5 1 5 7
## 2 0 10 7 5 7
## 3 1 5 7 7 7
## 4 2 1 7 5 2
## 5 0 10 7 7 7
## 6 1 1 7 5 7
## HFC_SugarSource HFC_CondimentsDays HFC_CondimentsSource MainIncomeSource
## 1 5 1 5 8. Employment income
## 2 5 7 5 1. Sale of crops
## 3 5 7 5 1. Sale of crops
## 4 5 7 5 8. Employment income
## 5 5 7 5 4. Casual labour
## 6 2 7 5 4. Casual labour
## MaleCasualLabour FemaleCasualLabour CasualLabourEarn CharcoalSaleEarn
## 1 1 0 20000 0
## 2 1 0 15400 0
## 3 2 1 1500 0
## 4 0 1 5000 0
## 5 1 0 5000 0
## 6 1 1 10000 0
## WoodSaleEarn DivisionID CountyID SiteID LivelihoodZoneID DateCaptured
## 1 0 72 10 1472 3 NA
## 2 0 72 10 1472 3 NA
## 3 0 72 10 1472 3 NA
## 4 0 72 10 1472 3 NA
## 5 0 72 10 1472 3 NA
## 6 0 72 10 1472 3 NAWe also have to ensure that the “InterviewDate” column is parsed correctly as a date before saving the worksheet to the new workbook.
# Ensure the column is numeric
hha_dews_data$InterviewDate <- as.numeric(hha_dews_data$InterviewDate)
# Convert the numeric date to Date format
hha_dews_data$InterviewDate <- as.Date(hha_dews_data$InterviewDate, origin = "1899-12-30")
# View the first few dates to verify the conversion
head(hha_dews_data$InterviewDate)## [1] "2016-07-23" "2016-07-23" "2016-07-23" "2016-07-23" "2016-07-23"
## [6] "2016-07-23"Save the cleaned data set as a different sheet in the Embu workbook
# Define the path for the existing Excel workbook
existing_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Load the existing workbook
wb <- loadWorkbook(existing_file_path)
# Add the cleaned HHA DEWS data to the existing workbook
addWorksheet(wb, "HHA DEWS")
writeData(wb, "HHA DEWS", hha_dews_data)
# Save the updated workbook
saveWorkbook(wb, existing_file_path, overwrite = TRUE)3. KIA REWAS
library(openxlsx)
file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
kia_rewas_data <- read.xlsx(file_path, sheet = "KIA REWAS")There are no P.I.I columns in this particular sheet. We ensure that the “date” column is parsed correctly as a date.
library(lubridate)##
## Attaching package: 'lubridate'## The following objects are masked from 'package:base':
##
## date, intersect, setdiff, union# Ensure the column is numeric
kia_rewas_data$date <- as.numeric(kia_rewas_data$date)
# Convert the Excel serial date to R Date
kia_rewas_data$date <- as.Date(kia_rewas_data$date, origin ="1899-12-30")
# Verify the output
head(kia_rewas_data$date)## [1] "2016-01-03" "2016-01-03" "2016-01-03" "2016-01-03" "2016-01-03"
## [6] "2016-01-03"We save this new sheet alongside the previous two in the Embu workbook created
# Define the path for the existing Excel workbook
existing_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Load the existing workbook
wb <- loadWorkbook(existing_file_path)
# Add the KIA REWAS data to the existing workbook
addWorksheet(wb, "KIA REWAS")
writeData(wb, "KIA REWAS", kia_rewas_data)
# Save the updated workbook
saveWorkbook(wb, existing_file_path, overwrite = TRUE)4. KIA DEWS
library(openxlsx)
file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
kia_dews_data <- read.xlsx(file_path, sheet = "KIA DEWS")There are no P.I.I columns in this particular sheet. We ensure that the “InterviewDate” column is parsed correctly as a date
library(lubridate)
# Convert the numeric date to Date format
kia_dews_data$InterviewDate <- as.Date(kia_dews_data$InterviewDate, origin = "1899-12-30")
# View the first few dates to verify the conversion
head(kia_dews_data$InterviewDate)## [1] "2016-07-03" "2016-07-13" "2016-07-23" "2016-07-06" "2016-05-29"
## [6] "2016-07-05"We save this new sheet alongside the previous three in the Embu workbook created
# Define the path for the existing Excel workbook
existing_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Load the existing workbook
wb <- loadWorkbook(existing_file_path)
# Add the KIA DEWS data to the existing workbook
addWorksheet(wb, "KIA DEWS")
writeData(wb, "KIA DEWS", kia_dews_data)
# Save the updated workbook
saveWorkbook(wb, existing_file_path, overwrite = TRUE)5. MUAC REWAS
library(openxlsx)
file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
muac_rewas_data <- read.xlsx(file_path, sheet = "MUAC REWAS")We drop PII’s which are the “FNAME”, and “HHNAME”.
# Drop the specified PII columns
muac_rewas_data <- muac_rewas_data %>%
select(-c(FNAME, HHNAME ))
# Check the updated dataset
head(muac_rewas_data)## Item HHAID GENDER AGE MUAC DISEASECODE LIVEINHH HHAMUACID YEAR MONTH DISTRICT
## 1 1 5851 2 33 145 0 2 209636 2000 2 12
## 2 2 5851 1 42 144 0 2 209635 2000 2 12
## 3 3 5851 2 56 123 0 2 209634 2000 2 12
## 4 4 5851 2 28 142 0 2 209633 2000 2 12
## 5 5 5851 1 35 128 0 2 209632 2000 2 12
## 6 6 5851 2 26 139 0 2 209631 2000 2 12
## DIVISION SACODE BATCHID LZONEHH HASCHILDBEENSICK SERIALNO ADMIN6ID
## 1 73 227 1340 22 2 1 KE0207010101
## 2 73 227 1340 22 2 1 KE0207010101
## 3 73 227 1340 22 2 1 KE0207010101
## 4 73 227 1340 22 2 1 KE0207010101
## 5 73 227 1340 22 2 1 KE0207010101
## 6 73 227 1340 22 2 1 KE0207010101We save this new sheet alongside the previous four in the Embu workbook created
# Define the path for the existing Excel workbook
existing_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Load the existing workbook
wb <- loadWorkbook(existing_file_path)
# Add the KIA DEWS data to the existing workbook
addWorksheet(wb, "MUAC REWAS")
writeData(wb, "MUAC REWAS", muac_rewas_data)
# Save the updated workbook
saveWorkbook(wb, existing_file_path, overwrite = TRUE)6. MUAC DEWS
library(openxlsx)
file_path <- "C:/Users/AAH USER/Downloads/Embu.xlsx"
# Read the specific sheet into a data frame
muac_dews_data <- read.xlsx(file_path, sheet = "MUAC DEWS")This data set has P.I.I’s in the “ChildName” column so we will drop that.
# Drop the specified PII columns along with original coordinates
muac_dews_data <- muac_dews_data %>%
select(-ChildName)
# Check the updated dataset
head(muac_dews_data)## MUACIndicatorID QID County SubCounty Ward LivelihoodZone Month Year
## 1 19106 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## 2 19107 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## 3 19108 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## 4 19109 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## 5 19110 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## 6 19111 9728 Embu Mbeere South Mwea Mixed Farming July 2016
## HouseholdCode Gender MUAC MUAC_Color AgeInMonths LiveInHousehold
## 1 24 Female 147 33 TRUE <NA>
## 2 24 Male 156 34 FALSE <NA>
## 3 24 Male 143 41 FALSE <NA>
## 4 24 Female 150 59 FALSE <NA>
## 5 24 Male 145 55 FALSE <NA>
## 6 24 Male 147 52 FALSE <NA>
## SufferedIllnesses InterviewDate DivisionID CountyID SiteID LivelihoodZoneID
## 1 42574 72 10 1472 3 NA
## 2 42574 72 10 1472 3 NA
## 3 42574 72 10 1472 3 NA
## 4 42574 72 10 1472 3 NA
## 5 42574 72 10 1472 3 NA
## 6 42574 72 10 1472 3 NA
## X22
## 1 <NA>
## 2 <NA>
## 3 <NA>
## 4 <NA>
## 5 <NA>
## 6 <NA>We also have to ensure that the “InterviewDate” column is parsed correctly as a date. The row values are displaced to the “SufferedIllnesses” column. We align this and proceed to parse the date correctly.
# Create a mask for rows to modify
rows_to_modify <- which(muac_dews_data$Year %in% 2016:2019)
# Ensure the columns being shifted are correctly specified
# We will create an index to specify which columns to shift
shift_columns <- c("MUAC_Color", "AgeInMonths", "LiveInHousehold",
"SufferedIllnesses", "InterviewDate",
"DivisionID", "CountyID", "SiteID", "LivelihoodZoneID")
# Create an empty data frame for the shifted values
shifted_values <- muac_dews_data[rows_to_modify, shift_columns]
# Replace the values in the original DataFrame with NA in the selected rows
muac_dews_data[rows_to_modify, shift_columns] <- NA
# Move the values one column to the right
for (i in seq_along(shift_columns)[-length(shift_columns)]) {
muac_dews_data[rows_to_modify, shift_columns[i + 1]] <- shifted_values[[i]]
}library(lubridate)
# Ensure the column is numeric
muac_dews_data$InterviewDate <- as.numeric(muac_dews_data$InterviewDate)
# Convert the Excel serial date to R Date
muac_dews_data$InterviewDate <- as.Date(muac_dews_data$InterviewDate, origin = "1899-12-30")
# Verify the output
head(muac_dews_data$InterviewDate)## [1] "2016-07-23" "2016-07-23" "2016-07-23" "2016-07-23" "2016-07-23"
## [6] "2016-07-23"Save this final sheet to the existing workbook
# Define the path for the existing Excel workbook
existing_file_path <- "C:/Users/AAH USER/OneDrive - Action Against Hunger USA/Documents/NDMA_DeIdentified/Embu.xlsx"
# Load the existing workbook
wb <- loadWorkbook(existing_file_path)
# Add the KIA DEWS data to the existing workbook
addWorksheet(wb, "MUAC DEWS")
writeData(wb, "MUAC DEWS", muac_dews_data)
# Save the updated workbook
saveWorkbook(wb, existing_file_path, overwrite = TRUE)