%%{init: {'theme': 'base', 'themeVariables': { 'primaryColor': '#e8f4f8', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#5c9ead', 'lineColor': '#5c9ead', 'secondaryColor': '#f0f7e6', 'tertiaryColor': '#fff5e6'}}}%%
flowchart LR
A[Aggregated<br/>CSV Files] -->|combine| B[Combined Data]
subgraph C[Cleaning]
direction TB
C1[Remove Random MACs] --> C2[Remove Stationary Devices]
end
B --> C
C --> D[Cleaned Data]
style A fill:#e8f4f8,stroke:#5c9ead
style B fill:#f0f7e6,stroke:#7cb342
style C fill:#fff5e6,stroke:#f9a825
style D fill:#fce4ec,stroke:#c2185b
5 Cleaning
The aggregated data still contains noise that distorts device counts. Randomized MAC addresses cause the same phone to appear as multiple different devices. Stationary devices like access points, fixed desktops, and IoT equipment add non-pedestrian signals.
This chapter removes both sources of error: first filtering out randomized MACs, then excluding devices that stay in place for hours. The result is a dataset of unique mobile devices suitable for pedestrian analysis.
5.1 Load Data
NoteSample data
Download sample_aggregated.zip if you don’t have aggregated data from the previous chapter.
Install Packages
pacman::p_load() installs missing packages and loads them in one step.
if (!require(pacman)) install.packages("pacman")
pacman::p_load(data.table, purrr)data.table: Fast data manipulationpurrr: Functional programming tools
Combine Files
Multiple sensors produce separate CSV files. Combine them into a single dataset before cleaning.
csv_files <- list.files(
"../workflow/ch3_tutorial", pattern = "1second.*\\.csv$", full.names = TRUE
)
wifi_data <- csv_files |> map(fread) |> rbindlist()Here are the first few rows:
head(wifi_data, 5) sensor_name source_address
<char> <char>
1: A01 d94147cf12befe41bb40dd7957733c54442de7a9d45a75ec3c747856c4bdc129
2: A01 5e69a0bc9bd73c0b72642e2e0f4f99670b85e8fdf4616bc19fb1f8d63107bfe5
3: A01 05d29a432f4ff4c5f2e49e185334619d4365ef65370fcf9891bc7b1f8c0a68b6
4: A01 a6a0a285818a48c083c72c885283f1652208b3239f70e859f49067b36781acc6
5: A01 b3268f2d7ca90e7ea3ff549decbf484d478c3eaf28784a7bbfbd5aaee22d3a6a
source_address_randomized timestamp median_rssi count
<int> <POSc> <int> <int>
1: 1 2024-04-09 19:17:27 -66 2
2: 1 2024-04-09 19:17:27 -75 2
3: 0 2024-04-09 19:17:27 -78 2
4: 0 2024-04-09 19:17:27 -75 1
5: 1 2024-04-09 19:17:27 -78 25.2 Clean the Data
Remove Random MAC Addresses
Modern smartphones randomize their MAC addresses for privacy, creating a new identifier each time they scan for networks. One phone can appear as dozens of different devices. We use the source_address_randomized flag to filter these out. This removal is conservative, since devices that only broadcast randomized addresses are lost entirely; downstream counts are therefore a lower bound. Appendix B examines what the retained devices preserve.
wifi_filtered <- wifi_data[source_address_randomized != 1]
NoteHow the sensor detects randomized MACs
The sensor checks the second-lowest bit of the first byte in the MAC address (equivalently, the second hex character is 2, 3, 6, 7, A, B, E, or F). If this bit is set to 1, the address is “locally administered,” meaning it was generated by the device rather than assigned by the manufacturer. This is the standard indicator for randomized addresses.
MAC: A4:C3:F0:85:7E:2D
^
First byte: A4 = 10100100 in binary
^
Second-lowest bit = 0 → manufacturer-assigned (not randomized)
MAC: 4E:7F:B2:91:3A:C8
^
First byte: 4E = 01001110 in binary
^
Second-lowest bit = 1 → locally administered (randomized)The sensor flags each packet during capture, so you don’t need to parse MAC addresses yourself.
Remove Stationary Devices
Stationary devices (access points, fixed desktops, IoT sensors) are detected continuously for hours at the same location. We identify them by session duration: how long a device stays at one sensor without leaving.
What is a session?
A session is a continuous period of detection. If a device disappears for more than 5 minutes and reappears, that counts as a new session.
Device A at Sensor 1:
|----detected----| gap > 5min |----detected----|
Session 1 Session 2
(45 minutes) (30 minutes)A pedestrian typically has one short session (a few minutes). A fixed desktop or IoT device has sessions lasting hours.
NoteWhy sessions instead of total detection time?
Total detection time can mislead. A delivery worker passing the sensor 30 times in a day might accumulate over 2 hours of total detection and be wrongly flagged as stationary, even though each pass lasts only minutes.
But a stationary device (like a fixed desktop or access point) stays continuously for hours at a time. By grouping detections into sessions, we can distinguish between:
- Pedestrian: multiple short visits (each under 2 hours)
- Stationary: at least one long continuous presence (2+ hours)
The 5-minute gap threshold reflects typical probe request intervals: if a device isn’t detected for 5+ minutes, it likely left the area.
Define thresholds
session_gap <- 300 # 5 minutes: gap that starts a new session
duration_threshold <- 3600 * 2 # 2 hours: flag as stationarysession_gap: If no detection for 5+ minutes, start a new sessionduration_threshold: If any session exceeds 2 hours, mark as stationary
Calculate session duration
The code below sorts by device and time, then calculates the time gap between consecutive detections, groups them into sessions, and computes each session’s total duration.
setorder(wifi_filtered, source_address, sensor_name, timestamp)
wifi_filtered[, time_diff := as.numeric(
difftime(timestamp, shift(timestamp), units = "secs")
), by = .(source_address, sensor_name)]
wifi_filtered[, session := cumsum(
time_diff > session_gap | is.na(time_diff)
), by = .(source_address, sensor_name)]
wifi_filtered[, session_duration := sum(
time_diff[time_diff <= session_gap], na.rm = TRUE
), by = .(source_address, sensor_name, session)]Filter out stationary devices
Any device with at least one session exceeding 2 hours is flagged as stationary and removed from the dataset.
stationary <- unique(
wifi_filtered[session_duration >= duration_threshold, source_address]
)
wifi_cleaned <- wifi_filtered[!source_address %in% stationary]Save the Result
Drop the helper columns and export the cleaned data to CSV.
wifi_cleaned[, c("time_diff", "session", "session_duration") := NULL]
fwrite(wifi_cleaned, "../workflow/ch3_tutorial/cleaned_sample.csv")5.3 Pipeline Summary
Each cleaning step reduces noise. In this sample, random MAC filtering reduces 243 apparent devices to 178, a 27% reduction. These removed entries are duplicates from phones using different randomized identifiers. The stationary filter has no effect here because the sample spans only a few minutes; with longer deployments (12+ hours), this filter removes significantly more devices.
summary_table <- data.table(
Step = c("Initial", "After Random MAC Removal", "After Stationary Removal"),
Records = c(nrow(wifi_data), nrow(wifi_filtered), nrow(wifi_cleaned)),
Unique_Devices = c(
uniqueN(wifi_data$source_address),
uniqueN(wifi_filtered$source_address),
uniqueN(wifi_cleaned$source_address)
)
)
print(summary_table) Step Records Unique_Devices
<char> <int> <int>
1: Initial 2694 243
2: After Random MAC Removal 1300 178
3: After Stationary Removal 1300 1785.4 Automate the Pipeline
For larger deployments spanning days or weeks, you’ll have many CSV files to process. The clean_wifi_data() function below wraps all cleaning steps into a single reusable call.
clean_wifi_data <- function(csv_files, session_gap = 300, duration_threshold = 7200) {
# Combine all files
wifi_data <- rbindlist(lapply(csv_files, fread))
# Remove random MACs
wifi_filtered <- wifi_data[source_address_randomized != 1]
# Calculate sessions (per device per sensor)
setorder(wifi_filtered, source_address, sensor_name, timestamp)
wifi_filtered[, time_diff := as.numeric(
difftime(timestamp, shift(timestamp), units = "secs")
), by = .(source_address, sensor_name)]
wifi_filtered[, session := cumsum(
time_diff > session_gap | is.na(time_diff)
), by = .(source_address, sensor_name)]
wifi_filtered[, session_duration := sum(
time_diff[time_diff <= session_gap], na.rm = TRUE
), by = .(source_address, sensor_name, session)]
# Remove stationary devices
stationary <- unique(
wifi_filtered[session_duration >= duration_threshold, source_address]
)
wifi_cleaned <- wifi_filtered[!source_address %in% stationary]
# Drop helper columns
wifi_cleaned[, c("time_diff", "session", "session_duration") := NULL]
return(wifi_cleaned)
}Run on all aggregated files:
csv_files <- list.files("../workflow/ch3_tutorial", pattern = "1second.*\\.csv$", full.names = TRUE)
cleaned_data <- clean_wifi_data(csv_files)
head(cleaned_data) sensor_name source_address
<char> <char>
1: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
2: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
3: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
4: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
5: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
6: A01 01f37d672ab24e5b1efc9eb702b837b79ed01b5e16a8b956a258f48371d51cf6
source_address_randomized timestamp median_rssi count
<int> <POSc> <int> <int>
1: 0 2024-04-09 19:18:00 -74 2
2: 0 2024-04-09 19:18:15 -73 1
3: 0 2024-04-09 19:18:45 -41 3
4: 0 2024-04-09 19:18:46 -45 7
5: 0 2024-04-09 19:18:58 -41 3
6: 0 2024-04-09 19:19:03 -39 5Save the combined result as in the manual walkthrough: fwrite(cleaned_data, "../workflow/ch3_tutorial/cleaned_sample.csv").