Step-by-Step LMD data cleaning and preparation
Vagner Ovani
17/08/2026
- 1 - Importing the Raw LMD Data
- 2 - Summarizing Calibration Data
- 3 - Removing Calibration Records
- 4.1 - Background: extracting Data
- 4.2 - Background: Identifying Values Outside the Central 95% Range
- 4.3 - Background: Averaging ppm Measurements per Second
- 5.1 - Animal data - Measurement Frequency per Second
- 5.2 - Animal data - Averaging ppm Measurements per Second
- 5.3 - Animal data - Creating Date and Time-Based Variables
- 5.4 - Animal data - Summary of Measurement Duration
- 5.5 - Animal data - Visual Assessment of ppm Measurements and 3-SD Threshold
- 5.6 - Animal data -Removing Values Above the 3-SD Threshold
- 6 - Background correction: Subtracting mean background from ppm values
- 7 - Normality, Skewness, and Kurtosis Assessment
1 - Importing the Raw LMD Data
This chunk imports the raw LMD dataset from the Excel file into R for further cleaning and processing.
library(readxl)
LMD = read_excel("C:/Users/Samsung/OneDrive/Documentos/2 - Material de trabalho/7 - Projetos/7.1 Pesquisa/LMD/LMD Raw Data 06-24 apr 2026.xlsx", sheet = "R table")
head(LMD)2 - Summarizing Calibration Data
This chunk separates the calibration records and calculates the mean, standard deviation, minimum, and maximum ppm values.
# Separate calibration data
Calibration = subset(LMD, treatment == "Calibration")
# Calibration summary
Calibration_summary = data.frame(
Mean = mean(Calibration$ppm.m, na.rm = TRUE),
SD = sd(Calibration$ppm.m, na.rm = TRUE),
Min = min(Calibration$ppm.m, na.rm = TRUE),
Max = max(Calibration$ppm.m, na.rm = TRUE))
head(Calibration_summary)writexl::write_xlsx(Calibration, "1 - Calibration Data.xlsx")3 - Removing Calibration Records
This chunk removes all rows identified as Calibration, retaining only the measurement data.
LMD = subset(LMD, treatment != "Calibration" | is.na(treatment))
print(LMD)## # A tibble: 621,871 × 9
## SerialNo SessionName treatment DateTime ppm.m `Distance(m)`
## <dbl> <dbl> <chr> <dttm> <dbl> <dbl>
## 1 4647 865 G2 2026-04-06 11:00:35 0 1
## 2 4647 865 G2 2026-04-06 11:00:35 0 1
## 3 4647 865 G2 2026-04-06 11:00:35 0 1
## 4 4647 865 G2 2026-04-06 11:00:35 0 1
## 5 4647 865 G2 2026-04-06 11:00:35 0 1
## 6 4647 865 G2 2026-04-06 11:00:35 0 1
## 7 4647 865 G2 2026-04-06 11:00:35 0 1
## 8 4647 865 G2 2026-04-06 11:00:35 0 1
## 9 4647 865 G2 2026-04-06 11:00:35 0 1
## 10 4647 865 G2 2026-04-06 11:00:34 0 1
## # ℹ 621,861 more rows
## # ℹ 3 more variables: Latitude <dbl>, Longitude <dbl>, Media <chr>writexl::write_xlsx(LMD, "2 - Main data without Calibration.xlsx")4.1 - Background: extracting Data
This chunk identifies measurements taken more than 5 m away as background.
Background = subset(LMD, `Distance(m)` > 5)
head(Background)writexl::write_xlsx(Background, "3 - Background.xlsx")4.2 - Background: Identifying Values Outside the Central 95% Range
This chunk identifies ppm measurements below the 2.5th percentile or above the 97.5th percentile as potential outliers.
# Define the central 95% range
Lower_limit = quantile(Background$ppm.m, 0.025, na.rm = TRUE)
Upper_limit = quantile(Background$ppm.m, 0.975, na.rm = TRUE)
Lower_limit## 2.5%
## 0Upper_limit## 97.5%
## 34.65Outliers = subset(Background, ppm.m < Lower_limit | ppm.m > Upper_limit)
nrow(Outliers)## [1] 151head(Outliers)Background_clean = subset(Background,ppm.m >= Lower_limit & ppm.m <= Upper_limit)
print(Background_clean)## # A tibble: 5,864 × 9
## SerialNo SessionName treatment DateTime ppm.m `Distance(m)`
## <dbl> <dbl> <chr> <dttm> <dbl> <dbl>
## 1 4647 865 G2 2026-04-06 10:58:43 0 20
## 2 4647 865 G2 2026-04-06 10:58:43 0 20
## 3 4647 865 G2 2026-04-06 10:58:43 0 20
## 4 4647 865 G2 2026-04-06 10:58:43 0 20
## 5 4647 865 G2 2026-04-06 10:58:42 0 20
## 6 4647 865 G2 2026-04-06 10:58:42 0 20
## 7 4647 865 G2 2026-04-06 10:58:42 0 20
## 8 4647 865 G2 2026-04-06 10:58:42 0 20
## 9 4647 865 G2 2026-04-06 10:58:42 0 20
## 10 4647 865 G2 2026-04-06 10:58:42 0 20
## # ℹ 5,854 more rows
## # ℹ 3 more variables: Latitude <dbl>, Longitude <dbl>, Media <chr>writexl::write_xlsx(Background_clean, "4 - Background_clean.xlsx")4.3 - Background: Averaging ppm Measurements per Second
This chunk summarizes their ppm values.
# Background summary
Background_summary = data.frame(
Mean = mean(Background_clean$ppm.m, na.rm = TRUE),
SD = sd(Background_clean$ppm.m, na.rm = TRUE),
Min = min(Background_clean$ppm.m, na.rm = TRUE),
Max = max(Background_clean$ppm.m, na.rm = TRUE))
Background_summary5.1 - Animal data - Measurement Frequency per Second
This chunk counts the number of LMD measurements recorded each second and calculates the mean, standard deviation, minimum, and maximum measurement frequency.The obtained average can also be interpreted approximately as the sampling frequency in Hz.
Animal_data = subset(LMD, `Distance(m)` < 5)
View(Animal_data)
writexl::write_xlsx(Animal_data, "5 - Animal_data.xlsx")
# Count the number of measurements per second
Measurements_per_second = as.data.frame(table(Animal_data$DateTime))
Measurements_per_secondnames(Measurements_per_second) = c("DateTime", "Measurements")
# Summary of measurements per second
Measurement_rate_summary = data.frame(
Mean = mean(Measurements_per_second$Measurements),
SD = sd(Measurements_per_second$Measurements),
Min = min(Measurements_per_second$Measurements),
Max = max(Measurements_per_second$Measurements))
Measurements_per_second[Measurements_per_second$Measurements == max(Measurements_per_second$Measurements),]Measurement_rate_summary5.2 - Animal data - Averaging ppm Measurements per Second
This chunk averages all ppm measurements recorded within each second, resulting in one ppm value per second.
names(Animal_data)## [1] "SerialNo" "SessionName" "treatment" "DateTime" "ppm.m"
## [6] "Distance(m)" "Latitude" "Longitude" "Media"LMD_second = aggregate(ppm.m ~ SerialNo + SessionName + treatment + DateTime + `Distance(m)`,
data = Animal_data, FUN = mean,na.rm = TRUE)
View(LMD_second)
writexl::write_xlsx(LMD_second, "6 - Animal_data_second.xlsx")5.3 - Animal data - Creating Date and Time-Based Variables
This chunk creates the measurement date and period, assigns a sequential number to each unique second within each animal, day, and period, and calculates the hours elapsed since feeding at 09:00.
# Measurement date
LMD_second$Day = as.Date(LMD_second$DateTime)
# Morning or afternoon
LMD_second$Period = ifelse(as.integer(format(LMD_second$DateTime, "%H")) < 12,"Morning","Afternoon")
LMD_second = LMD_second[order(LMD_second$Day,LMD_second$SessionName,LMD_second$Period,LMD_second$DateTime),]
head(LMD_second)# Sequential unique seconds for each animal, day and period
LMD_second$Second = ave(as.numeric(LMD_second$DateTime),
LMD_second$SessionName,
LMD_second$Day,
LMD_second$Period,
FUN = function(x) match(x, unique(x)))
head(LMD_second)# Hours after feeding at 09:00
LMD_second$Hours_after_feeding = (
as.numeric(format(LMD_second$DateTime, "%H")) +
as.numeric(format(LMD_second$DateTime, "%M")) / 60 +
as.numeric(format(LMD_second$DateTime, "%S")) / 3600) - 9
View(LMD_second)
writexl::write_xlsx(LMD_second, "7 - Animal_data_Time-Based_Variables.xlsx")5.4 - Animal data - Summary of Measurement Duration
This chunk calculates the total number of measurement seconds for each animal, day, and period, and summarizes the mean, standard deviation, minimum, and maximum duration.
# Total measurement seconds for each animal, day and period
Seconds_summary = aggregate(Second ~ SessionName + Day + Period,data = LMD_second,FUN = max)
# Summary statistics
Second_statistics = data.frame(
Mean = mean(Seconds_summary$Second, na.rm = TRUE),
SD = sd(Seconds_summary$Second, na.rm = TRUE),
Min = min(Seconds_summary$Second, na.rm = TRUE),
Max = max(Seconds_summary$Second, na.rm = TRUE))
Second_statisticsSeconds_summary[Seconds_summary$Second == max(Seconds_summary$Second),]5.5 - Animal data - Visual Assessment of ppm Measurements and 3-SD Threshold
This chunk plots ppm measurements over measurement time and adds an upper threshold defined as the overall mean plus three standard deviations to visually identify unusually high values.
# Calculate mean and SD
Mean_ppm = mean(LMD_second$ppm.m, na.rm = TRUE)
SD_ppm = sd(LMD_second$ppm.m, na.rm = TRUE)
# Upper 3-SD threshold
Upper_3SD = Mean_ppm + (3 * SD_ppm)
library(ggplot2)
# Plot
ggplot(LMD_second, aes(x = Second, y = ppm.m)) +
geom_point(size = 0.7, alpha = 0.5) +
geom_hline(yintercept = Upper_3SD,color = "red",linetype = "dashed",linewidth = 0.5) +
coord_cartesian(xlim = c(0, 600)) +
labs(x = "Measurement time (s)", y = "ppm", title = "ppm Measurements over Time") +
theme_classic()
# Display threshold
Upper_3SD## [1] 301.96575.6 - Animal data -Removing Values Above the 3-SD Threshold
This chunk identifies and removes ppm measurements above the mean plus three standard deviations.
# Identify values above the 3-SD threshold
Outliers_3SD = subset(LMD_second,ppm.m > Upper_3SD)
Outliers_3SD# Remove values above the threshold
LMD_3SD = subset(LMD_second, ppm.m <= Upper_3SD | is.na(ppm.m))
LMD_3SDwritexl::write_xlsx(LMD_3SD, "8 - Animal_data_LMD_Clean_3SD.xlsx")6 - Background correction: Subtracting mean background from ppm values
This chunk corrects the ppm measurements by subtracting the mean background concentration from each observation. Negative corrected values are removed, and the remaining ppm values are rounded to two decimal places.
# Subtract the mean background value
LMD_3SD$ppm.m = LMD_3SD$ppm.m - Background_summary$Mean
# Remove negative values
LMD_second_corr = subset(LMD_3SD, ppm.m >= 0)
LMD_second_corr$ppm.m = round(LMD_second_corr$ppm.m, 2)
View(LMD_second_corr)
writexl::write_xlsx(LMD_second_corr, "9 - LMD_clean_second_background_correction.xlsx")7 - Normality, Skewness, and Kurtosis Assessment
This chunk evaluates the distribution of ppm measurements using the Shapiro–Wilk test, skewness, kurtosis, and a Q-Q plot.
# Shapiro-Wilk test
Shapiro = shapiro.test(sample(LMD_second_corr$ppm.m, min(5000, length(LMD_second_corr$ppm.m))))
Shapiro##
## Shapiro-Wilk normality test
##
## data: sample(LMD_second_corr$ppm.m, min(5000, length(LMD_second_corr$ppm.m)))
## W = 0.54353, p-value < 2.2e-16# Skewness and kurtosis
Skewness = e1071::skewness(LMD_second_corr$ppm.m, type = 2)
Kurtosis = e1071::kurtosis(LMD_second_corr$ppm.m, type = 2)
# Summary
Distribution_summary = data.frame(n = length(LMD_second_corr$ppm.m),
Shapiro_W = unname(Shapiro$statistic),
Shapiro_p = Shapiro$p.value,
Skewness = Skewness,
Kurtosis = Kurtosis)
Distribution_summary# Q-Q plot
qqnorm(LMD_second_corr$ppm.m,main = "Q-Q Plot of ppm Measurements",
xlab = "Theoretical Quantiles",
ylab = "Sample Quantiles")
# Histogram of ppm measurements
hist(LMD_second_corr$ppm.m, main = "Distribution of ppm Measurements", xlab = "ppm", ylab = "Frequency")