Split Delimited Strings in a Column and Insert as New Rows

Split delimited strings in a column and insert as new rows

Here is another way of doing it..

df <- read.table(textConnection("1|a,b,c\n2|a,c\n3|b,d\n4|e,f"), header = F, sep = "|", stringsAsFactors = F)

df
## V1 V2
## 1 1 a,b,c
## 2 2 a,c
## 3 3 b,d
## 4 4 e,f

s <- strsplit(df$V2, split = ",")
data.frame(V1 = rep(df$V1, sapply(s, length)), V2 = unlist(s))
## V1 V2
## 1 1 a
## 2 1 b
## 3 1 c
## 4 2 a
## 5 2 c
## 6 3 b
## 7 3 d
## 8 4 e
## 9 4 f

How to split strings into new rows while maintaining other columns in R

An easier option is separate_rows

library(tidyr)
separate_rows(dat, ID)
# ID keep
#1 E87 1
#2 E42 2
#3 E39 3
#4 E16 4
#5 E17 4
#6 E18 4
#7 E760 5
#8 E761 5
#9 E762 5

Or using the OP's method, after splitting the 'ID', name it with 'keep' column and then stack it to a two column data.frame

stack(setNames(strsplit(dat$ID, ","), dat$keep))

R Split delimited strings in a column and insert as new column (in binary)

A solution using dplyr and tidyr. dt2 is the final output.

# Load packages
library(dplyr)
library(tidyr)

# Create example data frame
dt <- lot <- c("A01", "A01", "A02", "A03","A04")
Combination <- c("A,B,C,D,E,F", "A,B,C","B,C,D,E", "A,B,D,F", "A,C,D,E,F")
dt <- data_frame(lot, Combination)

# Process the data
dt2 <- dt %>%
mutate(ID = 1:n()) %>%
mutate(Combination = strsplit(Combination, split = ",")) %>%
unnest() %>%
mutate(Value = 1) %>%
spread(Combination, Value, fill = 0) %>%
select(-ID)

Splitting a string into new rows in R

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

How can I get comma delimited values into new row in BigQuery?

Try this one:

with mytable as (
select "X" as model, "1,34,60,1000" as alarm union all
select "Y", "2,4,5,900,1000" union all
select "Z", "1"
)
select model, newalarm
from mytable, unnest(split(alarm)) as newalarm

Sample Image

Split comma separated string and insert into new table with corresponding PK

try this:

Select t.Id,f.SplitData AS Value from #MyTable t
CROSS APPLY dbo.fnSplitString([Values],',') f

Is there a R function to split cell into new rows (while duplicating data)?

We can use separate_rows

library(dplyr)
library(tidyr)
mydf %>%
separate_rows(Send_To)

-ouput

# A tibble: 8 x 3
Sent_From Timestamp Send_To
<chr> <chr> <chr>
1 1 01:00 id1
2 2 02:00 id2
3 2 02:00 id3
4 3 03:00 id4
5 4 04:00 id5
6 4 04:00 id1
7 4 04:00 id2
8 4 04:00 id4


Related Topics



Leave a reply



Submit