Data Split in R based on Row Relationship Values

user_15067442 0
Viewed 52

My Dataframe has the following structure:

Start Value Middle Value(s) Final Value Relationship
A B C lowers, lowers
A D, E C lowers, raises, raises

I am trying to format the data so that I end up with:

Start Value Relationship Final Value
A lowers B
B lowers C
A lowers D
D raises E
E raises C

I have been struggling to format my data like this. At first I tried tidyr separate_rows() and some form of melting the data with reshape2 and data.table, but to no avail. Any suggestions?

data.table tidyr reshape dataframe r
1 Answers

We could unite the columns and then split the column with separate_rows

library(dplyr)
library(tidyr)
df1 %>% 
  unite(StartValue, StartValue, MiddleValue, sep=", ", remove = FALSE) %>%
  unite(FinalValue, MiddleValue, FinalValue, sep=", ") %>% 
  separate_rows(c(StartValue, FinalValue, Relationship))

-output

# A tibble: 5 x 3
#  StartValue FinalValue Relationship
#  <chr>      <chr>      <chr>       
#1 A          B          lowers      
#2 B          C          lowers      
#3 A          D          lowers      
#4 D          E          raises      
#5 E          C          raises

data

df1 <- structure(list(StartValue = c("A", "A"), MiddleValue = c("B", 
"D, E"), FinalValue = c("C", "C"), Relationship = c("lowers, lowers", 
"lowers, raises, raises")), class = "data.frame", row.names = c(NA, 
-2L))
user_37322710
Related