Reshape the data from three columns to two column

Question

I have a data like this :

   Response Prompt Prompt.Label Comments
1    Friend    who          si3      3_a
2         1    was       si4_en      3_a
3         1    was      si4_nag      3_a
4    Family    who          si3      3_b
5         1    was      si4_nag      3_b
6         1    was       si4_en      3_b
7     other    who          si3      4_a
8         1    was       si4_sp      4_a
9     other    who          si3      4_b
10        1    was       si4_en      4_b
11   Friend    who          si3      5_a
12        1    was       si4_en      5_a
13   Family    who          si3      5_b
14        1    was       si4_en      5_b

data<-structure(list(Response = c("Friend", "1", "1", "Family", "1", 
"1", "other", "1", "other", "1", "Friend", "1", "Family", "1"
), Prompt = c("who", "was", "was", "who", "was", "was", "who", 
"was", "who", "was", "who", "was", "who", "was"), Prompt.Label = c("si3", 
"si4_en", "si4_nag", "si3", "si4_nag", "si4_en", "si3", "si4_sp", 
"si3", "si4_en", "si3", "si4_en", "si3", "si4_en"), Comments = c("3_a", 
"3_a", "3_a", "3_b", "3_b", "3_b", "4_a", "4_a", "4_b", "4_b", 
"5_a", "5_a", "5_b", "5_b")), class = "data.frame", row.names = c(NA, 
-14L))

I am looking to reshape the data to have two columns, including Source, and Target. "Source" should have the values from "Response" when the "Prompt"=="who", and "Target" should be the values of "Prompt.Label" if "Prompt"= "was" for each value of the "Comments" columns. For instance, for the first row, Friend is going for "Source" , and si4_en is for "Target".

The desire data should be :

  Source  Target
1 Friend  si4_en
2 Friend si4_nag
3 Family si4_nag
4 Family  si4_en
5  other  si4_sp
6  other  si4_en
7 Friend  si4_en
8 Family  si4_en

TarJae · Accepted Answer · 2022-09-22 19:50:32Z

2

Here is one way to get the desired output:

library(dplyr)
data %>% 
  group_by(cumsum(Response!=1)) %>% 
  mutate(Response = ifelse(Response==1, first(Response), Response)) %>% 
  filter(row_number() !=1) %>%
  ungroup() %>% 
  select(Response, Target=Prompt.Label)

  Response Target 
  <chr>    <chr>  
1 Friend   si4_en 
2 Friend   si4_nag
3 Family   si4_nag
4 Family   si4_en 
5 other    si4_sp 
6 other    si4_en 
7 Friend   si4_en 
8 Family   si4_en

answered Sep 22, 2022 at 19:50

TarJae

80.2k6 gold badges30 silver badges94 bronze badges

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

Comments

GuedesBF · Accepted Answer · 2022-09-22 22:11:27Z

2

I feel we can do it with some very simple tidyverse functions, as long as we do some basic data wrangling beforehand. This data.frame is not tidy. The Response variable is not self-explicable, and needs some contextual clues for understanding. I feel we may be better off by first tidying it up with tidyr::fill. Then we can do some simple select/filter.

data %>%
    mutate(Response = na_if(Response, 1)) %>%
    fill(Response) %>%
    filter(Prompt == 'was') %>%
    select(Source = Response, Target = Prompt.Label)

  Source  Target
1 Friend  si4_en
2 Friend si4_nag
3 Family si4_nag
4 Family  si4_en
5  other  si4_sp
6  other  si4_en
7 Friend  si4_en
8 Family  si4_en

answered Sep 22, 2022 at 22:11

GuedesBF

9,9515 gold badges23 silver badges42 bronze badges

Collectives™ on Stack Overflow

Reshape the data from three columns to two column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related