Splitting a string into new rows in R [duplicate]
I have a data set like below:
Country Region Molecule Item Code
IND NA PB102 FR206985511
THAI AP PB103 BA-107603 / F000113361 / 107603
LUXE NA PB105 1012701 / SGP-1012701 / F041701000
IND AP PB106 AU206985211 / CA-F206985211
THAI HP PB107 F034702000 / 1010701 / SGP-1010701
BANG NA PB108 F000007970/25781/20009021
I want to split based the string values in ITEMCODE column on / and create a new row for each entry.
For instance, the desired output will be:
Country Region Molecule Item.Code
IND NA PB102 FR206985511
THAI AP PB103 BA-107603
THAI AP PB103 F000113361
THAI AP PB103 107603
LUXE NA PB105 1012701
LUXE NA PB105 SGP-1012701
LUXE NA PB105 F041701000
IND AP PB106 AU206985211
IND AP PB106 CA-F206985211
THAI HP PB107 F034702000
THAI HP PB107 1010701
THAI HP PB107 SGP-1010701
BANG NA PB108 F000007970
BANG NA PB108 25781
BANG NA PB108 20009021
I tried the below code
library(splitstackshape)
df2=concat.split.multiple(df1,"Plant.Item.Code","/", direction="long")
but got the Error
"Error: memory exhausted (limit reached?)"
When i tried strsplit() i got the below error message.
Error in strsplit(df1$Plant.Item.Code, "/") : non-character argument
Solution 1:
Try the cSplit function (as you already using @Anandas package). Note that is will return a data.table object, so make sure you have this package installed. You can revert back to data.frame (if you want to) by doing something like setDF(df2)
library(splitstackshape)
df2 <- cSplit(df1, "Item.Code", sep = "/", direction = "long")
df2
# Country Region Molecule Item.Code
# 1: IND NA PB102 FR206985511
# 2: THAI AP PB103 BA-107603
# 3: THAI AP PB103 F000113361
# 4: THAI AP PB103 107603
# 5: LUXE NA PB105 1012701
# 6: LUXE NA PB105 SGP-1012701
# 7: LUXE NA PB105 F041701000
# 8: IND AP PB106 AU206985211
# 9: IND AP PB106 CA-F206985211
# 10: THAI HP PB107 F034702000
# 11: THAI HP PB107 1010701
# 12: THAI HP PB107 SGP-1010701
# 13: BANG NA PB108 F000007970
# 14: BANG NA PB108 25781
# 15: BANG NA PB108 20009021
Solution 2:
Another approach in base R:
as.data.frame(do.call(rbind, apply(df1, 1, function(x) {
do.call(expand.grid, strsplit(x, " */ *"))
})))
The result:
Country Region Molecule Item.Code
1 IND <NA> PB102 FR206985511
2 THAI AP PB103 BA-107603
3 THAI AP PB103 F000113361
4 THAI AP PB103 107603
5 LUXE <NA> PB105 1012701
6 LUXE <NA> PB105 SGP-1012701
7 LUXE <NA> PB105 F041701000
8 IND AP PB106 AU206985211
9 IND AP PB106 CA-F206985211
10 THAI HP PB107 F034702000
11 THAI HP PB107 1010701
12 THAI HP PB107 SGP-1010701
13 BANG <NA> PB108 F000007970
14 BANG <NA> PB108 25781
15 BANG <NA> PB108 20009021
Solution 3:
Try something like this
d <- structure(list(Country = c("A", "B", "C"), `Item Code` = c("FR206985511",
"BA-107603/F000113361/107603", "1012701/SGP-1012701/F041701000")),
.Names = c("Country", "Item Code"), row.names = c(NA, -3L),
class = "data.frame")
d
# Country Item code
# A FR206985511
# B BA-107603/F000113361/107603
# C 1012701/SGP-1012701/F041701000
codes <- strsplit(d$"Item Code", "/")
code.lengths <- sapply(codes, length)
new.d <- d[rep(1:nrow(d), code.lengths), ]
new.d$"Item Code" <- unlist(codes)
new.d
# Country Item Code
#1 A FR206985511
#2 B BA-107603
#2.1 B F000113361
#2.2 B 107603
#3 C 1012701
#3.1 C SGP-1012701
#3.2 C F041701000
If you want to get rid of spaces (which your original data seems to contain), you can do it by d$"Item Code" <- gsub(" ", "", d$"Item Code")