Splitting Certain Rows in R into Multiple Rows?

Question

Is there possibly a way to turn this data...

Month      Animal        Number    CodeName
8-9       cat             2         whiskers
3-4       cat|dog         1|4       whiskers|spot
10-11     elephant        5         trunks
7-8       cat|snake       3|2       whiskers|thomas
5-6       elephant|dog    0|7       trunks|spot

In to this...

Month      Animal        Number    CodeName
8-9         cat           2         whiskers
3-4         cat           1         whiskers
3-4         dog           4         spot
10-11       elephant      5         trunks
7-8         cat           3         whiskers
7-8         snake         2         thomas
5-6         elephant      0         trunks
5-6         dog           7         spot

By breaking up the pipe?

I would be keeping the Month column the same, but the Animal, Number, and CodeName pipe columns would be split.

The last code that I tried for this was...

df %>% separate_rows(., Animal, Number, CodeName, convert = TRUE)

But I received the error of "Incompatible lengths".

Any help would be greatly appreciated

Can you paste the output of `dput(df)`, where `df` is the dataframe? Your `separate_rows` call looks correct. — RyanFrost, Jul 02 '20 at 19:31

score 1 · Answer 1 · answered Jul 02 '20 at 19:32

You can use separate_rows from tidyr, but you have to correctly specify the sep argument as sep = "\\|". Please note that you have to escape | because in the regrex it is a special character:

df <- read.table(text = "Month      Animal        Number    CodeName
8-9       cat             2         whiskers
3-4       cat|dog         1|4       whiskers|spot
10-11     elephant        5         trunks
7-8       cat|snake       3|2       whiskers|thomas
5-6       elephant|dog    0|7       trunks|spot", header = TRUE, stringsAsFactors = FALSE)

library(dplyr)
library(tidyr)

df %>% separate_rows(Animal, Number, CodeName, sep = "\\|")
  Month   Animal Number CodeName
1   8-9      cat      2 whiskers
2   3-4      cat      1 whiskers
3   3-4      dog      4     spot
4 10-11 elephant      5   trunks
5   7-8      cat      3 whiskers
6   7-8    snake      2   thomas
7   5-6 elephant      0   trunks
8   5-6      dog      7     spot

score 0 · Answer 2 · answered Jul 02 '20 at 19:19

We can use cSplit from splitstackshape

library(splitstackshape)
cSplit(df, c("Animal", "Number", "CodeName"), sep="|", "long")
#  Month   Animal Number CodeName
#1:   8-9      cat      2 whiskers
#2:   3-4      cat      1 whiskers
#3:   3-4      dog      4     spot
#4: 10-11 elephant      5   trunks
#5:   7-8      cat      3 whiskers
#6:   7-8    snake      2   thomas
#7:   5-6 elephant      0   trunks
#8:   5-6      dog      7     spot

data

df <- structure(list(Month = c("8-9", "3-4", "10-11", "7-8", "5-6"), 
    Animal = c("cat", "cat|dog", "elephant", "cat|snake", "elephant|dog"
    ), Number = c("2", "1|4", "5", "3|2", "0|7"), CodeName = c("whiskers", 
    "whiskers|spot", "trunks", "whiskers|thomas", "trunks|spot"
    )), class = "data.frame", row.names = c(NA, -5L))

Splitting Certain Rows in R into Multiple Rows?

2 Answers2

data