blocks|key|360573|text|这是另一种方法..|type|unstyled|depth|inlineStyleRanges|entityRanges|data|360574|df+<-+read.table(textConnection("1%7Ca,b,c\n2%7Ca,c\n3%7Cb,d\n4%7Ce,f"),+header+=+F,+sep+=+"%7C",+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|code-block|syntax|javascript|360575|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is another way of doing it..

<pre><code>df &lt;- 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 &lt;- 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
</code></pre>

blocks|key|360760|text|这是一个data.table解决方案：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360761|d.df+<-+read.table(header=T,+text="V1+%7C+V2
1+%7C+a,b,c
2+%7C+a,c
3+%7C+b,d
4+%7C+e,f",+stringsAsFactors=F,+sep="%7C",+strip.white+=+TRUE)
require(data.table)
d.dt+<-+data.table(d.df,+key="V1")
out+<-+d.dt[,+list(V2+=+unlist(strsplit(V2,+","))),+by=V1]

#++++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

>+sapply(out$V2,+nchar)+#+(or+simply+nchar(out$V2))
#+a+b+c+a+c+b+d+e+f+
#+1+1+1+1+1+1+1+1+1+|code-block|syntax|javascript|360762|entityMap^0|4|A|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|P|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|D|@]|E|$]]]|L|$]]

Here's a <code>data.table</code> solution:

<pre><code>d.df &lt;- read.table(header=T, text="V1 | V2
1 | a,b,c
2 | a,c
3 | b,d
4 | e,f", stringsAsFactors=F, sep="|", strip.white = TRUE)
require(data.table)
d.dt &lt;- data.table(d.df, key="V1")
out &lt;- d.dt[, list(V2 = unlist(strsplit(V2, ","))), by=V1]

# 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

&gt; sapply(out$V2, nchar) # (or simply nchar(out$V2))
# a b c a c b d e f 
# 1 1 1 1 1 1 1 1 1 
</code></pre>

blocks|key|360828|text|从2014年12月开始，这可以使用Hadley+Wickham的tidyr包中的unnest函数来完成(参见发布说明http://blog.rstudio.org/2014/12/08/tidyr-0-2-0/)|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|360829|>+library(tidyr)
>+library(dplyr)
>+mydf

++V1++++V2
2++1+a,b,c
3++2+++a,c
4++3+++b,d
5++4+++e,f
6++.+++++.


>+mydf+%25>%25+
++++mutate(V2+=+strsplit(as.character(V2),+","))+%25>%25+
++++unnest(V2)

+++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
10++.++.|code-block|syntax|javascript|360830|更新2017:注意下面@Tif所描述的separate_rows函数。|style|CODE|360831|它的效果要好得多，它允许在一条语句中“取消嵌套”多个列：|360832|>+head(mydf)
geneid++++++++++++++chrom++++start++end+strand++length++gene_count
ENSG00000223972.5+++chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1++++11869;12010;12179;12613;12613;12975;13221;13221;13453+++12227;12057;12227;12721;12697;13052;13374;14409;13670+++%2B;%2B;%2B;%2B;%2B;%2B;%2B;%2B;%2B+++1735++++11
ENSG00000227232.5+++chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1++14404;15005;15796;16607;16858;17233;17606;17915;18268;24738;29534+++14501;15038;15947;16765;17055;17368;17742;18061;18366;24891;29570+++-;-;-;-;-;-;-;-;-;-;-+++1351++++380
ENSG00000278267.1+++chr1++++17369+++17436+++-+++68++14
ENSG00000243485.4+++chr1;chr1;chr1;chr1;chr1++++29554;30267;30564;30976;30976+++30039;30667;30667;31097;31109+++%2B;%2B;%2B;%2B;%2B+++1021++++22
ENSG00000237613.2+++chr1;chr1;chr1++34554;35277;35721+++35174;35481;36081+++-;-;-+++1187++++24
ENSG00000268020.3+++chr1++++52473+++53312+++%2B+++840+14


>+mydf+%25>%25+separate_rows(strand,+chrom,+gene_start,+gene_end)
geneid++length++gene_count++strand++chrom+++start+++end
ENSG00000223972.5+++1735++++11++%2B+++chr1++++11869+++12227
ENSG00000223972.5+++1735++++11++%2B+++chr1++++12010+++12057
ENSG00000223972.5+++1735++++11++%2B+++chr1++++12179+++12227
ENSG00000223972.5+++1735++++11++%2B+++chr1++++12613+++12721
ENSG00000223972.5+++1735++++11++%2B+++chr1++++12613+++12697
ENSG00000223972.5+++1735++++11++%2B+++chr1++++12975+++13052
ENSG00000223972.5+++1735++++11++%2B+++chr1++++13221+++13374
ENSG00000223972.5+++1735++++11++%2B+++chr1++++13221+++14409
ENSG00000223972.5+++1735++++11++%2B+++chr1++++13453+++13670
ENSG00000227232.5+++1351++++380+-+++chr1++++14404+++14501
ENSG00000227232.5+++1351++++380+-+++chr1++++15005+++15038
ENSG00000227232.5+++1351++++380+-+++chr1++++15796+++15947
ENSG00000227232.5+++1351++++380+-+++chr1++++16607+++16765
ENSG00000227232.5+++1351++++380+-+++chr1++++16858+++17055
ENSG00000227232.5+++1351++++380+-+++chr1++++17233+++17368
ENSG00000227232.5+++1351++++380+-+++chr1++++17606+++17742
ENSG00000227232.5+++1351++++380+-+++chr1++++17915+++18061
ENSG00000227232.5+++1351++++380+-+++chr1++++18268+++18366
ENSG00000227232.5+++1351++++380+-+++chr1++++24738+++24891
ENSG00000227232.5+++1351++++380+-+++chr1++++29534+++29570
ENSG00000278267.1+++68++5+++-+++chr1++++17369+++17436
ENSG00000243485.4+++1021++++8+++%2B+++chr1++++29554+++30039
ENSG00000243485.4+++1021++++8+++%2B+++chr1++++30267+++30667
ENSG00000243485.4+++1021++++8+++%2B+++chr1++++30564+++30667
ENSG00000243485.4+++1021++++8+++%2B+++chr1++++30976+++31097
ENSG00000243485.4+++1021++++8+++%2B+++chr1++++30976+++31109
ENSG00000237613.2+++1187++++24++-+++chr1++++34554+++35174
ENSG00000237613.2+++1187++++24++-+++chr1++++35277+++35481
ENSG00000237613.2+++1187++++24++-+++chr1++++35721+++36081
ENSG00000268020.3+++840+0+++%2B+++chr1++++52473+++53312|360833|entityMap|0|LINK|mutability|MUTABLE|url|http://blog.rstudio.org/2014/12/08/tidyr-0-2-0/^0|1M|1B|0|0|0|J|D|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@$A|Z|B|10|1|11]]|C|$]]|$1|D|3|E|5|F|7|12|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|13|8|@$A|14|B|15|K|L]]|9|@]|C|$]]|$1|M|3|N|5|6|7|16|8|@]|9|@]|C|$]]|$1|O|3|P|5|F|7|17|8|@]|9|@]|C|$G|H]]|$1|Q|3|-4|5|6|7|18|8|@]|9|@]|C|$]]]|R|$S|$5|T|U|V|C|$W|X]]]]

As of Dec 2014, this can be done using the unnest function from Hadley Wickham's tidyr package (see release notes <a href="http://blog.rstudio.org/2014/12/08/tidyr-0-2-0/" rel="noreferrer">http://blog.rstudio.org/2014/12/08/tidyr-0-2-0/</a>)

<pre><code>&gt; library(tidyr)
&gt; library(dplyr)
&gt; mydf

 V1 V2
2 1 a,b,c
3 2 a,c
4 3 b,d
5 4 e,f
6 . .


&gt; mydf %&gt;% 
 mutate(V2 = strsplit(as.character(V2), ",")) %&gt;% 
 unnest(V2)

 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
10 . .
</code></pre>

Update 2017: note the <code>separate_rows</code> function as described by @Tif below.

It works so much better, and it allows to "unnest" multiple columns in a single statement:

<pre><code>&gt; head(mydf)
geneid chrom start end strand length gene_count
ENSG00000223972.5 chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1 11869;12010;12179;12613;12613;12975;13221;13221;13453 12227;12057;12227;12721;12697;13052;13374;14409;13670 +;+;+;+;+;+;+;+;+ 1735 11
ENSG00000227232.5 chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1;chr1 14404;15005;15796;16607;16858;17233;17606;17915;18268;24738;29534 14501;15038;15947;16765;17055;17368;17742;18061;18366;24891;29570 -;-;-;-;-;-;-;-;-;-;- 1351 380
ENSG00000278267.1 chr1 17369 17436 - 68 14
ENSG00000243485.4 chr1;chr1;chr1;chr1;chr1 29554;30267;30564;30976;30976 30039;30667;30667;31097;31109 +;+;+;+;+ 1021 22
ENSG00000237613.2 chr1;chr1;chr1 34554;35277;35721 35174;35481;36081 -;-;- 1187 24
ENSG00000268020.3 chr1 52473 53312 + 840 14


&gt; mydf %&gt;% separate_rows(strand, chrom, gene_start, gene_end)
geneid length gene_count strand chrom start end
ENSG00000223972.5 1735 11 + chr1 11869 12227
ENSG00000223972.5 1735 11 + chr1 12010 12057
ENSG00000223972.5 1735 11 + chr1 12179 12227
ENSG00000223972.5 1735 11 + chr1 12613 12721
ENSG00000223972.5 1735 11 + chr1 12613 12697
ENSG00000223972.5 1735 11 + chr1 12975 13052
ENSG00000223972.5 1735 11 + chr1 13221 13374
ENSG00000223972.5 1735 11 + chr1 13221 14409
ENSG00000223972.5 1735 11 + chr1 13453 13670
ENSG00000227232.5 1351 380 - chr1 14404 14501
ENSG00000227232.5 1351 380 - chr1 15005 15038
ENSG00000227232.5 1351 380 - chr1 15796 15947
ENSG00000227232.5 1351 380 - chr1 16607 16765
ENSG00000227232.5 1351 380 - chr1 16858 17055
ENSG00000227232.5 1351 380 - chr1 17233 17368
ENSG00000227232.5 1351 380 - chr1 17606 17742
ENSG00000227232.5 1351 380 - chr1 17915 18061
ENSG00000227232.5 1351 380 - chr1 18268 18366
ENSG00000227232.5 1351 380 - chr1 24738 24891
ENSG00000227232.5 1351 380 - chr1 29534 29570
ENSG00000278267.1 68 5 - chr1 17369 17436
ENSG00000243485.4 1021 8 + chr1 29554 30039
ENSG00000243485.4 1021 8 + chr1 30267 30667
ENSG00000243485.4 1021 8 + chr1 30564 30667
ENSG00000243485.4 1021 8 + chr1 30976 31097
ENSG00000243485.4 1021 8 + chr1 30976 31109
ENSG00000237613.2 1187 24 - chr1 34554 35174
ENSG00000237613.2 1187 24 - chr1 35277 35481
ENSG00000237613.2 1187 24 - chr1 35721 36081
ENSG00000268020.3 840 0 + chr1 52473 53312
</code></pre>

blocks|key|360872|text|另一种data.table解决方案，它不依赖于原始数据中任何唯一字段的存在。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|360873|DT+=+data.table(read.table(header=T,+text="blah+%7C+splitme
++++T+%7C+a,b,c
++++T+%7C+a,c
++++F+%7C+b,d
++++F+%7C+e,f",+stringsAsFactors=F,+sep="%7C",+strip.white+=+TRUE))

DT[,.(+blah
+++++,+splitme
+++++,+splitted=unlist(strsplit(splitme,+","))
+++++),by=seq_len(nrow(DT))]|code-block|syntax|javascript|360874|重要的是by=seq_len(nrow(DT))，这是发生分裂的“假”uniqueID。使用by=.I是很有诱惑力的，因为它的定义应该是一样的，但是.I似乎是一个神奇的东西，它改变了它的值，最好还是使用by=seq_len(nrow(DT))|360875|输出中有三列。我们只需命名现有的两个列，然后将第三个列计算为拆分|360876|.(+blah+++++++#+first+column+of+original
+,+splitme++++#+second+column+of+original
+,+splitted+=+unlist(strsplit(splitme,+","))
+)|360877|entityMap^0|3|A|0|0|4|K|1A|5|22|2|2T|K|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]|$9|13|A|14|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|15|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|16|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|17|8|@]|D|@]|E|$]]]|R|$]]

Another <code>data.table</code> solution, which doesn't rely on the existence of any unique fields in the original data.

<pre><code>DT = data.table(read.table(header=T, text="blah | splitme
 T | a,b,c
 T | a,c
 F | b,d
 F | e,f", stringsAsFactors=F, sep="|", strip.white = TRUE))

DT[,.( blah
 , splitme
 , splitted=unlist(strsplit(splitme, ","))
 ),by=seq_len(nrow(DT))]
</code></pre>

The important thing is <code>by=seq_len(nrow(DT))</code>, this is the 'fake' uniqueID that the splitting occurs on. It's tempting to use <code>by=.I</code> instead, as it should be defined the same, but <code>.I</code> appears to be a magical thing that changes its value, better to stick with <code>by=seq_len(nrow(DT))</code>

There are three columns in the output. We simply name the two existing columns, and then compute the third as a split

<pre><code>.( blah # first column of original
 , splitme # second column of original
 , splitted = unlist(strsplit(splitme, ","))
 )
</code></pre>

I have a data frame as follow:

<pre><code>+-----+-------+
| V1 | V2 |
+-----+-------+
| 1 | a,b,c |
| 2 | a,c |
| 3 | b,d |
| 4 | e,f |
| . | . |
+-----+-------+
</code></pre>

Each of the alphabet is a character separated by comma. I would like to split V2 on each comma and insert the split strings as new rows. For instance, the desired output will be:

<pre><code>+----+----+
| V1 | V2 |
+----+----+
| 1 | a |
| 1 | b |
| 1 | c |
| 2 | a |
| 2 | c |
| 3 | b |
| 3 | d |
| 4 | e |
| 4 | f |
+----+----+
</code></pre>

I am trying to use <code>strsplit()</code> to spit V2 first, then cast the list into a data frame. It didn't work. Any help will be appreciated.

Split delimited strings in a column and insert as new rows

我有一个数据框，如下：+-----+-------+|  V1 |  V2   |+-----+-------+|  1  | a,b,c ||  2  | a,c   ||  3  | b,d   ||  4  | e,f   ||  .  | .     |+-----+-------+每个字母表都是用逗号分隔的字...

问拆分列中的分隔字符串并作为新行插入
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拆分列中的分隔字符串并作为新行插入EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问拆分列中的分隔字符串并作为新行插入
EN