Subset rows incrementaly from different files

user_11052706 0
Viewed 71

I have a couple of files (60) containing tables with identical column names. I write down an exemple of three of them.

what I need is to read from each file (for(i in files) source(i) ?) the data and create a single table out of them where I extract for each ID one single row (with all variables) from each file. The first row starts at a certain date which gets incremented in the next files by one.

For instance, the "starting" row in the first file would be row no. 10 , which start on 10th of Jan. 10 ID1 2000-01-10 20

Now, I jump to the next file, I search for the same ID and subset the row no. 11 (incremented by one day) which represents the dates from the next day, 2000-01-11 Step to the next file, the same ID, subset the row no. 12 (incremented by one compared to the previous file), etc. The number of rows to be subsetted for each ID equals files number. The iteration goes till we reach all rows for ID1 or the last file. Then, we repeat the operations above for the next ID, namely ID2 where the starting row comes from the first file as well and has the same starting date as ID1. It is also row number 10 relative to its ID but 22 in the file: 22 = 10 (ID1 + 2 incremental).

 Day =  seq(as.Date("2000-01-01"), length = 12, by = "days")                
file1: 
set.seed(123)
  df1 <- data.frame( rowid =  c(1:24),
          ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", 
              "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
          Date =  rep(Day, 2),
          Var1 = sample(1:30, 24, TRUE) )
file2:
set.seed(123)
   df2 <- data.frame( rowid =  c(1:24),
              ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
              Date =  rep(Day, 2),
              Var1 = sample(40:60, 24, TRUE))
file3:
set.seed(123)
  df3 <- data.frame(rowid =  c(1:24),
              ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
              Date =  rep(Day, 2),
              Var1 = sample(70:100, 24, TRUE))

The output should look like:

              10 ID1 2000-01-10   20
              11 ID1 2000-01-11   44
              12 ID1 2000-01-12   83
              22 ID2 2000-01-10    9
              23 ID2 2000-01-11   50
              24 ID2 2000-01-12   98

Any thought on how to get it? Thank you

subset r
3 Answers

Preparing your input like so:

df_list <- list(df1, df2, df3)
start <- 10

You could do:

library(purrr)
library(dplyr)

imap_dfr(df_list, \(file, index) split(file, ~ ID) %>%
     map_dfr(~ slice(.x, start - 1 + index))) %>%
  arrange(ID)

Which returns the desired:

  rowid  ID       Date Var1
1    10 ID1 2000-01-10   20
2    11 ID1 2000-01-11   44
3    12 ID1 2000-01-12   83
4    22 ID2 2000-01-10    9
5    23 ID2 2000-01-11   50
6    24 ID2 2000-01-12   98
user_70100560

You can also use the following solution. For this I first find the starting Date and then I used map2 function to iterate along data sets as well as Date's increments:

library(dplyr)
library(purrr)
library(magrittr)

df_list %>%
  map2_dfr(0:(length(df_list)-1), ~ .x %>% 
             group_by(ID) %>% 
             filter(Date == df_list %>%
                      map(~ .x %>% 
                            filter(rowid == start) %>% 
                            pull(Date)) %>%
                      extract2(1) + .y)) %>%
  arrange(ID)

# A tibble: 6 x 4
# Groups:   ID [2]
  rowid ID    Date        Var1
  <int> <chr> <date>     <int>
1    10 ID1   2000-01-10    20
2    11 ID1   2000-01-11    44
3    12 ID1   2000-01-12    83
4    22 ID2   2000-01-10     9
5    23 ID2   2000-01-11    50
6    24 ID2   2000-01-12    98

In case you had already rbind all of your data sets instead of gathering them in a list you can use this:

df %>%
  group_split(ID) %>%
  map_dfr(~ .x %>% 
            filter(Date == df %>%
                     filter(rowid == start) %>%
                     pull(Date) %>% extract2(1) + 0:2)) %>%
  group_split(Date) %>% 
  map2_dfr(1:length(.), ~ .x %>% 
             group_by(rowid, ID) %>% 
             slice(n = .y)) %>%
  arrange(ID)

# A tibble: 6 x 4
# Groups:   rowid, ID [6]
  rowid ID    Date        Var1
  <int> <chr> <date>     <int>
1    10 ID1   2000-01-10    20
2    11 ID1   2000-01-11    44
3    12 ID1   2000-01-12    83
4    22 ID2   2000-01-10     9
5    23 ID2   2000-01-11    50
6    24 ID2   2000-01-12    98
user_143145200

I would have done it like this,

Day =  seq(as.Date("2000-01-01"), length = 12, by = "days")                

set.seed(123)
df1 <- data.frame( rowid =  c(1:24),
                   ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", 
                          "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
                   Date =  rep(Day, 2),
                   Var1 = sample(1:30, 24, TRUE) )
set.seed(123)
df2 <- data.frame( rowid =  c(1:24),
                   ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
                   Date =  rep(Day, 2),
                   Var1 = sample(40:60, 24, TRUE))
set.seed(123)
df3 <- data.frame(rowid =  c(1:24),
                  ID = c("ID1", "ID1","ID1", "ID1","ID1", "ID1","ID1", "ID1",  "ID1", "ID1","ID1", "ID1","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2","ID2", "ID2", "ID2", "ID2"),
                  Date =  rep(Day, 2),
                  Var1 = sample(70:100, 24, TRUE))


df_list <- list(df1, df2, df3)
start <- 10
library(tidyverse)

reduce(df_list[-1], .init = df_list[[1]] %>% filter(Date == Date[rowid == start]), 
       ~bind_rows(..1, ..2 %>% filter(Date == 1 + max(..1$Date)) ))

#>   rowid  ID       Date Var1
#> 1    10 ID1 2000-01-10   20
#> 2    22 ID2 2000-01-10    9
#> 3    11 ID1 2000-01-11   44
#> 4    23 ID2 2000-01-11   50
#> 5    12 ID1 2000-01-12   83
#> 6    24 ID2 2000-01-12   98

Try this!

reduce2(df_list[-1], seq_along(df_list[-1]), .init = df_list[[1]] %>% filter(Date == Date[rowid == start]), 
       ~bind_rows(..1, ..2 %>% filter(Date == ..3 + min(..1$Date)) ))
user_28848590
Related