Using values from one data file to select rows from another data file-CodePudding

The

It includes values of score from rows 1-10 (for 012) 8-16 (for 013) and 2-8 (for 014).

I would then have another file corresponding to Movie2 (by selecting rows 11-20, 19-28, and 21-26), and so on for each participant id. Each participant might have a slightly different number of rows.

This is just beyond my skill and I would appreciate any help as to how I can accomplish this. Thanks!

CodePudding user response：

Assuming you have your id data in a directory data\id, and that they all have the same 2 columns time and score, we can read them in all at once with readr::read_csv() while specifying the id argument which will save the file path as a column. We can then split that file path and keep just the part we want to get a Source column that matches the video data.

We can then join the data on the Source column, but only after pivoting both wide. With the data joined, we can then pivot them long and define a new Event.Type column by finding the time with the start and end parameters. Finally we select only the columns we want, and pivot them wider again to get the desired output.

This will still contain all the movies, but you can easily add a dplyr::filter(Event.Type == "Movie1") to get unique files.

library(tidyverse) library(here) # Here I take your dummy data and write them as csv's `012_data` <- data.frame( time = c(1:30), score = c(0,0,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0) ) %>% write_csv(here::here('data', 'id', '012_data.csv')) `013_data` <- data.frame( time = c(1:30), score = c(0,0,0,0,0,0,0,0,1,0,0,1,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0) ) %>% write_csv(here::here('data', 'id', '013_data.csv')) `014_data` <- data.frame( time = c(1:30), score = c(0,1,0,0,0,1,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0) ) %>% write_csv() # Read them back in all at once into a single tibble id_data <- here::here('data', 'id') %>% list.files(full.names = T) %>% readr::read_csv(id = 'Source') %>% dplyr::mutate(Source = stringr::str_split_i(Source, pattern = "\\/", i = -1), Source = stringr::str_remove(Source, pattern = "_data.csv")) %>% tidyr::pivot_wider(names_from = time, values_from = score) videodata <- data.frame( Event.Type = c("Movie1", "Movie1", "Movie2", "Movie2", "Movie3", "Movie3", "Movie1", "Movie1", "Movie2", "Movie2", "Movie3", "Movie3", "Movie1", "Movie1", "Movie2", "Movie2", "Movie3", "Movie3"), Name = c("start", "end", "start", "end","start", "end", "start", "end", "start", "end","start", "end", "start", "end", "start", "end","start", "end"), Row = c(1, 10, 11, 20, 21, 30, 8, 16, 19, 28, 1, 7, 2, 8, 21, 26, 9, 20), Source = c("012", "012", "012", "012", "012", "012", "013", "013", "013", "013", "013", "013", "014", "014", "014", "014", "014", "014") ) %>% tidyr::pivot_wider(names_from = c(Event.Type, Name), values_from = Row) df <- dplyr::left_join(videodata, id_data) %>% tidyr::pivot_longer(cols = 8:37, names_to = "time", values_to = "score") %>% dplyr::mutate( time = as.numeric(time), Event.Type = dplyr::case_when( time >= Movie1_start & time <= Movie1_end ~ 'Movie1', time >= Movie2_start & time <= Movie2_end ~ 'Movie2', time >= Movie3_start & time <= Movie3_end ~ 'Movie3', T ~ as.character(NA)), score = ifelse(is.na(Event.Type), NA, score)) %>% dplyr::select(Event.Type, Source, time, score) %>% tidyr::pivot_wider(names_from = Source, values_from = score) df # A tibble: 70 × 5 Event.Type time `012` `013` `014` <chr> <dbl> <dbl> <dbl> <dbl> 1 Movie1 1 0 NA NA 2 Movie1 2 0 NA 1 3 Movie1 3 0 NA 0 4 Movie1 4 0 NA 0 5 Movie1 5 1 NA 0 6 Movie1 6 0 NA 1 7 Movie1 7 0 NA 0 8 Movie1 8 0 0 1 9 Movie1 9 1 1 NA 10 Movie1 10 0 0 NA # … with 60 more rows # ℹ Use `print(n = ...)` to see more rows

CodePudding user response：

I managed to generate the files as you wanted. Please check the comments in the code for a deeper explanation.

I think the last block (the double loop) may not be the most efficient solution in terms of time, but it is the "cleanest" I could come up with. Sometimes is better to sacrifice some performance for the sake of understandability.

### load necessary packages library(data.table) ### read file from disk (I used your data) videodata = fread("videodata.csv") ### read individual files from disk. If not exactly 80, adjust ### the number. If the filename pattern doesn't match exactly, ### adjust accordingly. (I used your data) individualFileNames = paste(sprintf("d", 1:80), "data.csv", sep = "_") ### files are read into a **list** individualData = lapply(individualFileNames, fread) ### we need this because of your naming convention :'( ### In general, it's bad idea to name files starting with a number names(individualData) = paste(sprintf("d", 1:80), "_data", sep = "") ### a function to pad with NAs the vectors of different lengts padNAs = function(x){ n = max(sapply(x, length)) for (i in 1 : length(x)){ length(x[[i]])= n } as.data.table(x) } ### This double loop will go over each event type (movie) and ### each source in it, and create a table of the scores (which ### are obtained from each individual data table). The table ### is then exported as a csv file with the name of the movie. for (i in videodata[, unique(Event.Type)]){ j_score = list() counter = 1 for (j in videodata[, unique(Source)]){ filename = paste0(i, ".csv") startRow = videodata[Event.Type == i & Source == j & Name == "start", Row] endRow = videodata[Event.Type == i & Source == j & Name == "end", Row] j_score[[counter]] = individualData[paste0(j, "_data")][[1]][startRow : endRow, score] names(j_score)[counter] = j counter = counter 1 } fwrite(padNAs(j_score), file = filename) }