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
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
Rstudio Suddenly Stopped Showing Plots in the Plot Pane
How to Change the Default Colors in Plotly Chart
Error in Confusion Matrix:The Data and Reference Factors Must Have the Same Number of Levels
Sum Across Multiple Columns With Dplyr
Error: Could Not Find Function ... in R
Select Rows from a Data Frame Based on Values in a Vector
Collapse Text by Group in Data Frame
Removing Duplicate Combinations (Irrespective of Order)
Generate List of All Possible Combinations of Elements of Vector
Adding Value from One Data.Frame to Another Data.Frame by Matching a Variable
Filter a Data Frame According to Minimum and Maximum Values
Creating a for Loop to Subset Data on R
Aggregate/Summarize Multiple Variables Per Group (E.G. Sum, Mean)
Combine Two Data Frames by Rows (Rbind) When They Have Different Sets of Columns