Tidy a WEXTOR log into one row per participant

Takes the tibble returned by read_WEXTOR_log and collapses it into 'tidy' format: one row per participant. Every WEXTOR variable submitted anywhere in the study becomes its own column, keyed by a single unique id per row and the resulting data frame contains the commonly relevant paradata variables.

Internally this happens:

1. For each participant, their (usually many) rows are collapsed into one by taking, for every column, the first non-missing value in reverse chronological order by default – i.e. the last value submitted/logged for that column. This matters if a participant navigated back and resubmitted a page: the latest value wins, consistent with how read_WEXTOR_log itself resolves repeated keys within a single request. Set duplicates = "first" to keep the earliest value instead (e.g. the participant's very first timestamp/client_ip). Empty-string answers ("", submitted but left blank) are turned into NA in the final output, same as a variable that was never submitted at all.

2. Participants who share an IP address with at least one other participant are flagged, in a anomaly_flag column added at the very end, containing "double IP address" if so, NA otherwise. This is a common sign of one person completing the study more than once from the same computer/network – worth a manual check before treating those rows as independent observations. It only catches an exact IP match; it won't catch someone who switched networks between attempts.

Usage

tidy_WEXTOR_log(data, id_col = "id", duplicates = c("last", "first"))

Arguments

data

A tibble as returned by read_WEXTOR_log, i.e. containing query, timestamp, client_ip, user_agent and duration_ms columns.

id_col

Name of the participant identifier variable inside query. Defaults to "id", WEXTOR's standard variable name.

duplicates

How to resolve a participant having more than one non-missing value logged for the same column. "last" (default) keeps the most recently submitted/logged value; "first" keeps the earliest.

Value

A tibble with one row per unique participant id (sorted numerically when every id looks numeric, alphabetically otherwise), columns id, timestamp, client_ip, user_agent and duration_ms (each keeping its original type from data), one column per WEXTOR variable collected anywhere in the study (character; convert types such as SUS/UEQ items to numeric downstream as needed), and a final anomaly_flag column as described above.

Examples

log <- read_WEXTOR_log(path_to_file("fake_wextor_log.txt"))
data <- tidy_WEXTOR_log(log)

# anything worth a manual look:
dplyr::filter(data, !is.na(anomaly_flag))
#> # A tibble: 6 × 19
#>   id    timestamp           client_ip     user_agent duration_ms pagetrail var1 
#>   <chr> <dttm>              <chr>         <chr>            <int> <chr[1d]> <chr>
#> 1 22097 2000-01-01 10:02:03 203.0.113.109 Mozilla/5…         333 start.ht… 1    
#> 2 42092 2000-01-01 10:28:47 198.51.100.1… Mozilla/5…         436 start.ht… 54   
#> 3 46048 2000-01-01 09:03:50 203.0.113.29  Mozilla/5…         324 start.ht… 18   
#> 4 51588 2000-01-01 10:33:35 198.51.100.1… Mozilla/5…         346 start.ht… 55   
#> 5 65057 2000-01-01 11:01:52 203.0.113.29  Mozilla/5…         462 start.ht… 56   
#> 6 79163 2000-01-01 09:31:51 203.0.113.109 Mozilla/5…         267 start.ht… 97   
#> # ℹ 12 more variables: var2 <chr>, var3 <chr>, var4 <chr>, var5 <chr>,
#> #   var6 <chr>, var7 <chr>, var8 <chr>, var9 <chr>, var10 <chr>, var11 <chr>,
#> #   var12 <chr>, anomaly_flag <chr>