0

I have a data frame with participants (ID) who answered several questionnaires consecutively (each row is a questionnaire). All of them started with a "general"-questionnaire and then answered pairs of "pre" and "post"-questionnaire (column "Order"). Column "Value" shows example data (there are many more columns with data, and many more participants). The amount of answered "pairs" are different among participants. 

    ID   Order     Value
1   1    general     1
2   1    pre         3
3   1    post        4
4   1    post        7
5   1    pre         0
6   1    post       10
7   2    general     1
8   2    post        0
9   2    pre        12
10  3    general    12
11  3    pre         3
12  3    post        4
13  3    pre         6
14  3    pre         8

Example data:

df1 <- data.frame("ID" = as.factor(c('1', '1', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '3')), "Order" = as.factor(c('general', 'pre', 'post', 'post', 'pre', 'post', 'general', 'post', 'pre', 'general', 'pre', 'post', 'pre', 'pre')), "Value" = as.numeric(c('1', '3','4','7','0','10', '1','0','12', '12', '3', '4', '6','8')))

Problem: Some participants forgot/failed to answer a pre-questionnaire of a pre/post-pair, others forgot/failed to answer a post-questionnaire of a pre/post-pair.

Aim: I need to add a "pre"-row or a "post"-row for each pair which is not complete. Hence, the consecutive rows should always read pre post pre post pre post etc. The added row should include the ID as well as the value from the existing part of the pair. 

> df2
   ID    Order Value
1   1  general     1
2   1      pre     3
3   1     post     4
4   1      pre     7
5   1     post     7
6   1      pre     0
7   1     post    10
8   2  general     1
9   2      pre     0
10  2     post     0
11  2      pre    12
12  2     post    12
13  3  general    12
14  3      pre     3
15  3     post     4
16  3      pre     6
17  3     post     6
18  3      pre     8
19  3     post     8

See example data here:

df2 <- data.frame("ID" = as.factor(c('1', '1', '1', '1', '1', '1', '1', '2', '2', '2', '2', '2', '3', '3', '3', '3', '3', '3', '3')), "Order" = as.factor(c('general', 'pre', 'post', 'pre', 'post', 'pre', 'post', 'general', 'pre', 'post', 'pre', 'post', 'general', 'pre', 'post', 'pre', 'post', 'pre', 'post')), "Value" = as.numeric(c('1', '3', '4', '7', '7', '0', '10', '1', '0', '0', '12', '12', '12', '3', '4', '6', '6', '8', '8')))

The amount of pre/post-pairs can be different for each participant.

I asked a similar question here - but this did not work for this particular case. The other suggested solution also did not. I tried different versions of the complete()-function and expand.grid.

Improve this question

3 Answers 3

Reset to default
1

This may be an alternative approach:

library(tidyverse)

df1 %>%
  mutate(rn = row_number()) %>%
  pivot_wider(id_cols = c(ID, rn), names_from = Order, values_from = Value) %>%
  mutate(post2 = if_else(!is.na(lead(post)), lead(post), pre),
         pre2 = if_else(!is.na(post2) & is.na(pre), post2, pre)) %>%
  select(-c(rn, pre, post)) %>%
  pivot_longer(cols = c(general, pre2, post2), names_to = "Order", values_to = "Value") %>%
  drop_na()

Output

# A tibble: 19 x 3
   ID    Order   Value
   <fct> <chr>   <dbl>
 1 1     general     1
 2 1     pre2        3
 3 1     post2       4
 4 1     pre2        7
 5 1     post2       7
 6 1     pre2        0
 7 1     post2      10
 8 2     general     1
 9 2     pre2        0
10 2     post2       0
11 2     pre2       12
12 2     post2      12
13 3     general    12
14 3     pre2        3
15 3     post2       4
16 3     pre2        6
17 3     post2       6
18 3     pre2        8
19 3     post2       8

Edit:

To generalize this solution for multiple Value columns, you will need to first pivot_longer to put data into a more workable format. In addition, you will want to group_by the column name variable so that using lead you are only looking at values appropriate for that variable.

Say for example you have two columns, Value1 and Value2:

df1 <- data.frame("ID" = as.factor(c('1', '1', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '3')), 
                  "Order" = as.factor(c('general', 'pre', 'post', 'post', 'pre', 'post', 'general', 'post', 'pre', 'general', 'pre', 'post', 'pre', 'pre')), 
                  "Value1" = as.numeric(c('1', '3','4','7','0','10', '1','0','12', '12', '3', '4', '6','8')),
                  "Value2" = as.numeric(c('4', '2','1','9','2','15', '2','11','18', '16', '5', '5', '8','10')))

You can do the following:

df1 %>%
  pivot_longer(cols = starts_with("Value"), names_to = "ValueName", values_to = "Value") %>%
  mutate(rn = row_number()) %>%
  pivot_wider(id_cols = c(ID, rn, ValueName), names_from = Order, values_from = Value) %>%
  group_by(ID, ValueName) %>%
  mutate(post2 = if_else(!is.na(lead(post)), lead(post), pre),
         pre2 = if_else(!is.na(post2) & is.na(pre), post2, pre)) %>%
  select(-c(rn, pre, post)) %>%
  rename(pre = pre2, post = post2) %>%
  pivot_longer(cols = c(general, pre, post), names_to = "Order", values_to = "Value") %>%
  drop_na() %>%
  arrange(ValueName, ID) %>%
  print(n=50)

Output

# A tibble: 38 x 4
# Groups:   ID, ValueName [6]
   ID    ValueName Order   Value
   <fct> <chr>     <chr>   <dbl>
 1 1     Value1    general     1
 2 1     Value1    pre         3
 3 1     Value1    post        4
 4 1     Value1    pre         7
 5 1     Value1    post        7
 6 1     Value1    pre         0
 7 1     Value1    post       10
 8 2     Value1    general     1
 9 2     Value1    pre         0
10 2     Value1    post        0
11 2     Value1    pre        12
12 2     Value1    post       12
13 3     Value1    general    12
14 3     Value1    pre         3
15 3     Value1    post        4
16 3     Value1    pre         6
17 3     Value1    post        6
18 3     Value1    pre         8
19 3     Value1    post        8
20 1     Value2    general     4
21 1     Value2    pre         2
22 1     Value2    post        1
23 1     Value2    pre         9
24 1     Value2    post        9
25 1     Value2    pre         2
26 1     Value2    post       15
27 2     Value2    general     2
28 2     Value2    pre        11
29 2     Value2    post       11
30 2     Value2    pre        18
31 2     Value2    post       18
32 3     Value2    general    16
33 3     Value2    pre         5
34 3     Value2    post        5
35 3     Value2    pre         8
36 3     Value2    post        8
37 3     Value2    pre        10
38 3     Value2    post       10

The data is left in long format - but could be converted to wide as well in the end with pivot_wider.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you very much for your answer. Seems to be working well. (1) Did you rename to "pre2" and "post2" on purpose? Should I simply rename them back in a next step? (2) Could you give me a hint about how to extend your solution, if I have a lot more columns that just "Value" (as this was only an example… )
(1) yes, you can just rename back to pre and post instead of pre2 and post2 in the end - I just wanted to keep columns separate/clear while working through the problem. (2) to generalize, it depends on what this looks like. If you want, edit your question with a second Value column and expected result. Are there NA values in there? Or are all additional Value columns collected at same time for pre and post together?
All other remaining columns could have na for the entire inserted row.
@Slyrs please see edited answer for multiple value columns, see if this is what you had in mind.
A somewhat dowdy way–which works–is to left join the original data frame with multiple columns df1+ to the new new df: final <- df2 %>% left_join(distinct(df1, .keep_all = T))
0

This does the trick:

df1 <- data.frame("ID" = as.factor(c('1', '1', '1', '1', '1', '1', '2', '2', '2', '3', '3', '3', '3', '3')), "Order" = as.factor(c('general', 'pre', 'post', 'post', 'pre', 'post', 'general', 'post', 'pre', 'general', 'pre', 'post', 'pre', 'pre')), "Value" = as.numeric(c('1', '3','4','7','0','10', '1','0','12', '12', '3', '4', '6','8')))
df2 <- data.frame("ID" = as.factor(c('1', '1', '1', '1', '1', '1', '1', '2', '2', '2', '2', '2', '3', '3', '3', '3', '3', '3', '3')), "Order" = as.factor(c('general', 'pre', 'post', 'pre', 'post', 'pre', 'post', 'general', 'pre', 'post', 'pre', 'post', 'general', 'pre', 'post', 'pre', 'post', 'pre', 'post')), "Value" = as.numeric(c('1', '3', '4', '7', '7', '0', '10', '1', '0', '0', '12', '12', '12', '3', '4', '6', '6', '8', '8')))

temp <- df1 %>% 
  mutate(
    ID = as.character(ID),
    Order = as.character(Order),
  ) %>% 
  group_by(ID) %>% 
  mutate(
    last = lag(Order),
    `next` = lead(Order),
    rowID = row_number(),
    filter = if_else((rowID == 2 & Order == "post") | (Order == "pre" & `next` != "post") | (Order == "post" & last != "pre"), 1, 0)
  ) %>% 
  ungroup() %>% 
  replace_na(list(filter = 1))
add_rows <- temp %>% 
  filter(filter == 1) %>% 
  mutate(
    Order = if_else(Order == "post", "pre", "post")
  )

temp %>% 
  bind_rows(add_rows) %>% 
  arrange(ID, rowID) %>% 
  select(ID, Order, Value) %>% 
  mutate(
    ID = as.factor(ID),
    Order = as.factor(Order),
  )
Improve this answer

1 Comment

Thank you very much for your help. I get an error running the code: code "Error in replace_na(., list(filter = 1)) : argument "value" is missing, with no default"
0

For the sake of completeness, here is also a data.table solution which uses rowid(), CJ(), and nafill(). In general, the approach consists of three steps:

  1. create a table of complete pairs,
  2. join with the original table,
  3. fill in the missing values.
library(data.table)
setDT(df1)[, oid := rowid(ID, Order)][]
df1[, Order := factor(Order, level = c("general", "pre", "post"))]
tmp <- df1[, CJ(oid, Order, unique = TRUE), by = ID][!(oid > 1 & Order == "general")]
result <- df1[tmp, on = .(ID, Order, oid)][
  , Value := nafill(nafill(Value, "locf"), "nocb"), by = .(ID, oid)][, oid := NULL][]
result

    ID   Order Value
 1:  1 general     1
 2:  1     pre     3
 3:  1    post     4
 4:  1     pre     0
 5:  1    post     7
 6:  1     pre    10
 7:  1    post    10
 8:  2 general     1
 9:  2     pre    12
10:  2    post     0
11:  3 general    12
12:  3     pre     3
13:  3    post     4
14:  3     pre     6
15:  3    post     6
16:  3     pre     8
17:  3    post     8

Explanation in detail

  1. After coercing df1 to data.table class, an new column oid is added which counts the rows which belong to ID and Order. So, df1 becomes
    ID   Order Value oid
 1:  1 general     1   1
 2:  1     pre     3   1
 3:  1    post     4   1
 4:  1    post     7   2
 5:  1     pre     0   2
 6:  1    post    10   3
 7:  2 general     1   1
 8:  2    post     0   1
 9:  2     pre    12   1
10:  3 general    12   1
11:  3     pre     3   1
12:  3    post     4   1
13:  3     pre     6   2
14:  3     pre     8   3
  1. The factor levels of Order must be reordered so that "pre" is the second level, and "post" is the third level. This is required for the next step
  2. Now, a data.table tmp is created which holds all the complete pairs. This is achieved by cross joining the sequence of unique oid, e.g., 1, 2, 3 with the factor levels of Order for each ID. CJ() is similar to expand.grid(). The result is filtered to keep only one "general" row and as many pairs of "pre" and "post" as required for each ID.
    ID oid   Order
 1:  1   1 general
 2:  1   1     pre
 3:  1   1    post
 4:  1   2     pre
 5:  1   2    post
 6:  1   3     pre
 7:  1   3    post
 8:  2   1 general
 9:  2   1     pre
10:  2   1    post
11:  3   1 general
12:  3   1     pre
13:  3   1    post
14:  3   2     pre
15:  3   2    post
16:  3   3     pre
17:  3   3    post
  1. df1 is right joined with tmp to append the Value column to the matching rows. Missing values where df1 has no matching row appear as NA. These missing values are replaced by last observation carried forward and next observation carried backward, i.e., in both directions, using the nafill() function (new to data.table version 1.12.4 as of 03 Oct 2019). Finally, the oid column is removed.
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.