Aggregating table() over multiple columns in R without a "by" breakdown
Solution 1
Using just Vanilla R, you can do
aggregate(rep(1, nrow(coords)), by = list(x = coords$x, y = coords$y), sum)
Solution 2
Better than ddply is count
:
library(plyr)
count(coords)
It's a lot faster than table for sparse 2d results too.
Solution 3
You can use ddply
from the plyr
library
plyr::ddply(coords, .(x, y), summarize, count = length(x))
Solution 4
You could also use data.table
library(data.table)
DT <- data.table(coords)
DT[,.N,by=list(x,y)]
## x y N
## 1: 1 1 2
## 2: 2 2 1
## 3: 2 1 1
## 4: 3 1 2
See this answer for more details on the use of .N
and creating frequency tables with data.table
Solution 5
With dplyr
library(dplyr)
count(coords, x, y)
With data.table
library(data.table)
setDT(coords)
coords[, .(n = .N), by = .(x, y)]
Related videos on Youtube
Gregor Thomas
Gregor Thomas, Data scientist, R user and teacher; board game and cooking enthusiast. I compulsively correct the capitalizaton of ggplot2 (all lower case is always correct) and remove the rstudio tag from the 99% of questions that don't need it.
Updated on June 04, 2022Comments
-
Gregor Thomas almost 2 years
I have a 2-column data frame of x- and y-coordinates of points. I want to generate a table of the number of occurrences of each point. Using the
table()
command produces a table for all possible x-y pairs. I can eliminate the extras withfullTable <- table(coords) smalLTable <- subset(fullTable, fullTable > 0)
And then I'm sure I could do a little something with
dimnames(fullTable)
to get the appropriate coordinates, but is there a better way? Something built in? Something that withcoords <- data.frame(x = c(1, 1, 2, 2, 3, 3), y = c(1, 1, 2, 1, 1, 1))
would return
x y count 1 1 2 2 1 1 2 2 1 3 1 2