How do select the first row of a block to define the condition and restructure the following rows within each block?

Question

I have a data frame that looks like this:

trialcode response block
AM2A3     0        1
Empathy   1        1
ToM       4        1
Transfer  2.5      1
AM2D1     0        2
Empathy   4        2
ToM       2        2
Transfer  6        2
AM4M1     0        3
Empathy   6        3
ToM       6        3
Transfer  10       3

In each block, the first row defines the trial condition and the following three rows are my dependent variables. I would like to restructure it to look like this:

Empathy ToM Transfer condition block
1       4   2.5      AM2A3     1
4       2   6        AM2D1     2
6       6   10       AM4M1     3

Can anybody help? Thank you!

score 2 · Answer 1 · answered Jul 16 '21 at 08:24

library(data.table)

DT <- setDT(mydata)
# wrangling before casting
DT[, condition := trialcode[1], by = .(block)]
# cast 2nd - nth row of each group
dcast(DT[, .SD[2:.N], by = .(condition)], 
      block + condition ~ trialcode, value.var = "response")
#    block condition Empathy ToM Transfer
# 1:     1     AM2A3       1   4      2.5
# 2:     2     AM2D1       4   2      6.0
# 3:     3     AM4M1       6   6     10.0

MonJeanJean · Answer 2 · 2021-07-16T08:25:55.180

Welcome on SO! This should work:

library(tidyverse)
    output <- data %>% 
  mutate(response = ifelse(response == 0, trialcode, response)) %>% 
  mutate(trialcode = ifelse(str_starts(trialcode,"AM"), "condition", trialcode)) %>% 
  pivot_wider(names_from = trialcode, values_from = response) %>% 
  relocate(Empathy, ToM, Transfer, condition, block)

Data used:

data <- data.frame(trialcode = c("AM2A3","Empathy","ToM","Transfer","AM2D1","Empathy","ToM","Transfer"),
                 response = c(0,1,4,2.5,0,4,2,6),
                 block = c(1,1,1,1,2,2,2,2))

Output:

Empathy ToM   Transfer condition block
  <chr>   <chr> <chr>    <chr>     <dbl>
1 1       4     2.5      AM2A3         1
2 4       2     6        AM2D1         2

I do not think if it is a good idea to store numbers as `characters`. — bird, Jul 16 '21 at 08:55

bird · Answer 3 · 2021-07-16T08:53:50.837

1

Using dplyr:

library(dplyr)
df %>%
  pivot_wider(names_from = trialcode, values_from = response) %>% 
  pivot_longer(cols = c(AM2A3, AM2D1:AM4M1), 
               names_to = "condition", 
               values_drop_na = TRUE) %>% 
  select(Empathy, ToM, Transfer, condition, block, -value) # ordering columns like in your desired table

Output:

# A tibble: 3 x 5
  Empathy   ToM Transfer condition block
    <dbl> <dbl>    <dbl> <chr>     <int>
1       1     4      2.5 AM2A3         1
2       4     2      6   AM2D1         2
3       6     6     10   AM4M1         3

edited Jul 16 '21 at 08:53

answered Jul 16 '21 at 08:31

bird

2,938
1
6
27

1

Very good bird: I tried the same logic but was missing `values_drop_na = TRUE` . So I tried another way! Upvote! – TarJae Jul 16 '21 at 08:46

score 0 · Accepted Answer · answered Jul 16 '21 at 08:29

Optional solution:

library(data.table)
dt[,.(Empathy = .SD[trialcode == "Empathy", response],
      Tom = .SD[trialcode == "ToM", response], 
      Transfer = .SD[trialcode == "Transfer", response],
      condition = head(trialcode,1)),
    by = block]

Data:

dt <- fread("
trialcode response block
AM2A3     0        1
Empathy   1        1
ToM       4        1
Transfer  2.5      1
AM2D1     0        2
Empathy   4        2
ToM       2        2
Transfer  6        2
AM4M1     0        3
Empathy   6        3
ToM       6        3
Transfer  10       3
")

score 0 · Answer 5 · answered Jul 16 '21 at 08:44

The first part with pivot_wider is clear. The second part is related to this question of mine: Concatenate column names in one column conditional on using mutate, across and case_when


library(tidyverse)
df %>% 
  pivot_wider(
    names_from = trialcode,
    values_from = response
  ) %>% 
  mutate(across(starts_with("AM"), ~case_when(. == 0 ~ cur_column()), .names = 'new_{col}')) %>% 
  unite(condition, starts_with('new'), na.rm = TRUE, sep = ' ') %>% 
  select(Empathy, ToM, Transfer, condition, block)

Output:

  Empathy   ToM Transfer condition block
    <dbl> <dbl>    <dbl> <chr>     <dbl>
1       1     4      2.5 AM2A3         1
2       4     2      6   AM2D1         2
3       6     6     10   AM4M1         3

How do select the first row of a block to define the condition and restructure the following rows within each block?

5 Answers5