Geek Answers Handbook

Merging cells in a data frame using aggregate.data.frame

I have a data frame, for example:

df <- data.frame(bee.num=c(1,1,1,2,2,3,3), plant=c("d","d","w","d","d","w","d")) 
df$visits = list(1:3, 4:9, 10:11, 1:10, 11:12, 1:4,5:11)
df

    bee.num plant                        visits
1       1     d                          1, 2, 3
2       1     d                 4, 5, 6, 7, 8, 9
3       1     w                           10, 11
4       2     d    1, 2, 3, 4, 5, 6, 7, 8, 9, 10
5       2     d                           11, 12
6       3     w                       1, 2, 3, 4
7       3     d            5, 6, 7, 8, 9, 10, 11

I would like to aggregate visits to bee.num and plant with a function that combines the values ​​for visits based on the matching values ​​of bee.num and plant, for example, below

     bee.num plant                        visits
1       1     d                1, 2, 3, 4, 5, 6, 7, 8, 9
2       1     w                                   10, 11
3       2     d    1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
4       3     w                               1, 2, 3, 4
5       3     d                    5, 6, 7, 8, 9, 10, 11

I tried

aggregate.data.frame(df$visits, by=list(bee.num = df$bee.num, plant = df$plant), FUN=c)

and

aggregate.data.frame(df$visits, by=list(bee.num = df$bee.num, plant = df$plant), FUN=unlist)

but I always get the error "arguments imply a different number of lines." Any help would be greatly appreciated. Thanks in advance.

+4
source share
3 answers

The function works as expected if you pass the data frame containing the list as a column, and not pass the list itself.

x <- aggregate.data.frame(df['visits'], list(df$bee.num, df$plant) , FUN=c)
names(x) <- c('bee.num', 'plant', 'visits')
x
##   bee.num plant                                visits
## 1       1     d             1, 2, 3, 4, 5, 6, 7, 8, 9
## 2       2     d 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
## 3       3     d                 5, 6, 7, 8, 9, 10, 11
## 4       1     w                                10, 11
## 5       3     w                            1, 2, 3, 4

Note:

> class(df$visits)
[1] "list"
> class(df['visits'])
[1] "data.frame"

Thus, it would be enough to name aggregateabove.

, . aggregate.data.frame :

if (!is.data.frame(x)) 
    x <- as.data.frame(x)

df$visits, :

as.data.frame(df$visits)
## Error in data.frame(1:3, 4:9, 10:11, 1:10, 11:12, 1:4, 5:11, check.names = TRUE,  : 
##   arguments imply differing number of rows: 3, 6, 2, 10, 4, 7

"" data.frame. .

+3

, , unlist list , data.frame, :

visits <- unlist(df$visits, use.names=FALSE)
df <- df[rep(rownames(df), sapply(df$visits, length)), c("bee.num", "plant")]
df$visits <- visits
aggregate.data.frame(df$visits, by=list(bee.num = df$bee.num, plant = df$plant), FUN=c)
#   bee.num plant                                     x
# 1       1     d             1, 2, 3, 4, 5, 6, 7, 8, 9
# 2       2     d 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12
# 3       3     d                 5, 6, 7, 8, 9, 10, 11
# 4       1     w                                10, 11
# 5       3     w                            1, 2, 3, 4

## Or, better yet:
aggregate(visits ~ bee.num + plant, df, c)

, "data.table" :

library(data.table)
DT <- data.table(df)
setkey(DT, bee.num, plant)
DT[, list(visits = list(unlist(visits))), by = key(DT)]
#    bee.num plant        visits
# 1:       1     d  1,2,3,4,5,6,
# 2:       1     w         10,11
# 3:       2     d  1,2,3,4,5,6,
# 4:       3     d 5,6,7,8,9,10,
# 5:       3     w       1,2,3,4

. :

str(.Last.value)
# Classes ‘data.table’ and 'data.frame':  5 obs. of  3 variables:
#  $ bee.num: num  1 1 2 3 3
#  $ plant  : Factor w/ 2 levels "d","w": 1 2 1 1 2
#  $ visits :List of 5
#   ..$ : int  1 2 3 4 5 6 7 8 9
#   ..$ : int  10 11
#   ..$ : int  1 2 3 4 5 6 7 8 9 10 ...
#   ..$ : int  5 6 7 8 9 10 11
#   ..$ : int  1 2 3 4
#  - attr(*, "sorted")= chr  "bee.num" "plant"
#  - attr(*, ".internal.selfref")=<externalptr>
+1

, , aggregate.data.frame .

, R, , plyr.

However, since my first data analysis was performed using database scripts, I am still partial for the package sqldffor these kinds of tasks.

I also find that SQL is more transparent to non-R users (which I often see in the social science community, where most of my work is).

Here is a solution to the problem with sqldf:

#your data assigned to dat
bee.num <- c(1,1,1,2,2,3,3)
plant <- c("d", "d", "w", "d", "d", "w", "d")
visits <- c("1, 2, 3"
            ,"4, 5, 6, 7, 8, 9"
            ,"10, 11"
            ,"1, 2, 3, 4, 5, 6, 7, 8, 9, 10"
            ,"11, 12"
            ,"1, 2, 3, 4"
            ,"5, 6, 7, 8, 9, 10, 11")


dat <- as.data.frame(cbind(bee_num, plant, visits))

#load sqldf
require(sqldf)

#write a simple SQL aggregate query using group_concat()
#i.e. "select" your fields specifying the aggregate function for the 
#relevant field, "from" a table called dat, and "group by" bee_num
#(because sql_df converts "." into "_" for field names) and plant.
sqldf('select
        bee_num
        ,plant
        ,group_concat(visits) visits
      from dat
      group by 
        bee_num
        ,plant')


  bee_num plant                               visits
1       1     d             1, 2, 3,4, 5, 6, 7, 8, 9
2       1     w                               10, 11
3       2     d 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11, 12
4       3     d                5, 6, 7, 8, 9, 10, 11
5       3     w                           1, 2, 3, 4
0
source

More articles:


All Articles