R - Concatenate string from dataframe and remove html tags

Question

I'm looking to use R to clean up some text strings from a database. The database stores the text complete with HTML tags. Unfortunately, due to database limitations, each string is broken into multiple fragments in the database. I think I could figure out how to remove the html tags with regular expressions and the help of other posts, but I don't expect those solutions will work unless I concatenate the fragments back together (opening/closing html tags can be spread across records in the dataframe). Here is some sample data:

Existing dataframe

Record_nbr  fragment    Comments
1   1   "The quick brown"
1   2   "fox jumped over"
1   3   "the lazy dog."
2   1   "New Record."

Desired output dataframe

Record_nbr  fragment    Comments
1   3   "The quick brown fox jumped over the lazy dog."
2   2   "New Record."

Data:

dat <- read.table(text='Record_nbr  fragment    Comments
1   1   "The quick brown"
1   2   "fox jumped over"
1   3   "the lazy dog."
2   1   "New Record."', header=TRUE)

Guy Adini · Accepted Answer · 2014-07-25 16:14:39Z

1

I am assuming that you didn't actually want to keep the fragment column. In this case you can use this quick one-liner:

aggregate(comment ~ Record_nbr, data=dat, function(x) paste(x, collapse=" "))

answered Jul 25, 2014 at 16:14

Guy Adini

5,5245 gold badges35 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rich Scriven · Accepted Answer · 2014-07-25 18:14:57Z

0

It seems like the fragment column becomes unusable after the split? Maybe

> aggregate(dat[3], dat[1], paste)
#   Record_nbr                                             x
# 1          1 The quick brown fox jumped over the lazy dog.
# 2          2                                   New Record.

equivalent to

aggregate(Comments~Record_nbr, data = dat, paste)

edited Jul 25, 2014 at 18:14

answered Jul 25, 2014 at 17:29

Rich Scriven

99.8k11 gold badges190 silver badges252 bronze badges

2 Comments

rascale Over a year ago

grouped <- aggregate(dataframe[[12]], dataframe[1:9],paste, collapse = " ")

Rich Scriven Over a year ago

like aggregate(dat[-1], dat[1], paste) for this example

Tyler Rinker · Accepted Answer · 2014-07-25 16:20:34Z

0

Here's one of many approaches:

## ensure order
dat <- with(dat, dat[order(Record_nbr, fragment), ])

do.call(rbind, lapply(split(dat, dat$Record_nbr), function(x) {
    data.frame(
        x[1, 1, drop=FALSE], 
        fragment = max(x[, 2]), 
        Comments = paste(x$Comments, collapse=" ")
    )
}))

##   Record_nbr fragment                                      Comments
## 1          1        3 The quick brown fox jumped over the lazy dog.
## 2          2        1                                   New Record.

edited Jul 25, 2014 at 16:20

answered Jul 25, 2014 at 16:03

Tyler Rinker

111k74 gold badges335 silver badges534 bronze badges

Comments

Tyler Rinker · Accepted Answer · 2014-07-25 16:21:06Z

0

Using dplyr:

library(dplyr)
dat %>% 
group_by(Record_nbr) %>% 
summarize(fragment= n(), Comments=paste(Comments, collapse= " "))

#  Record_nbr fragment                                      Comments
#1          1        3 The quick brown fox jumped over the lazy dog.
#2          2        1                                   New Record.

edited Jul 25, 2014 at 16:21

Tyler Rinker

111k74 gold badges335 silver badges534 bronze badges

answered Jul 25, 2014 at 16:13

akrun

891k38 gold badges590 silver badges700 bronze badges

Comments

nfmcclure · Accepted Answer · 2014-07-25 16:21:40Z

0

Also consider using the quicker 'aggregate' function:

aggregate(dat,  by=list(dat$Record_nbr), paste, collapse=" ")

##   Group.1 Record_nbr fragment                                      Comments
## 1       1      1 1 1    1 2 3 The quick brown fox jumped over the lazy dog.
## 2       2          2        1                                   New Record.

Edit: You might have to play with the function inputs to get the exact outcome you want.

edited Jul 25, 2014 at 16:21

answered Jul 25, 2014 at 16:16

nfmcclure

3,1713 gold badges26 silver badges40 bronze badges

Collectives™ on Stack Overflow

R - Concatenate string from dataframe and remove html tags

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related