CODEWITHSUNDEEP
rdataframe
Nicholas Hamilton
Nicholas Hamilton

Reputation: 10506

R, Operation on data frame across consecutive rows with repeated entries

I have a data frame of x and y coordinates, and also a classification (A or B) What is the best way to apply an operation on all the consecutive rows where the classification type has been repeated?

Here is an example:

set.seed(1)
n  = 9
x  = 1:n
y  = runif(n)
df = data.frame(x,y,type=sample(c("A","B"),n,replace=TRUE))

Which produces the following:

+---+---+-----------+------+
|   | x |     y     | type |
+---+---+-----------+------+
| 1 | 1 | 0.2655087 | A    |
| 2 | 2 | 0.3721239 | A    |
| 3 | 3 | 0.5728534 | A    |
| 4 | 4 | 0.9082078 | B    |
| 5 | 5 | 0.2016819 | A    |
| 6 | 6 | 0.8983897 | B    |
| 7 | 7 | 0.9446753 | A    |
| 8 | 8 | 0.6607978 | B    |
| 9 | 9 | 0.6291140 | B    |
+---+---+-----------+------+

So I want to to a ddply(...) type orperation, to get the average x and y coordinate when the 'type' classification is repeated across consecutive rows, in the above, rows 1:3 should collapse to 1 row, rows 4:7 would be unaffected and rows 8:9, also collapsing to 1 row, the result should return 6 rows.

Upvotes: 2

Views: 504

Answers (2)

tospig
tospig

Reputation: 8333

A few methods I can think of using Base, dplyr and data.table

## put into numerical groups
df$grp <- match(df$type, LETTERS)

## use rle to find consecutive groups
nGroups <- length(rle(df$grp)[[1]])  ## returns number of groups
grp <- rep(seq(1,nGroups,1), rle(df$grp)$length)

## put rle groups onto data
df$rle_grp <- grp

## perform calculation

Base R

aggregate(x=df[,c("x","y")], by=list(df$rle_grp), FUN=mean)

#  Group.1   x         y
#1       1 2.0 0.4034953
#2       2 4.0 0.9082078
#3       3 5.0 0.2016819
#4       4 6.0 0.8983897
#5       5 7.0 0.9446753
#6       6 8.5 0.6449559

dplyr

## using dplyr (you asked for ddply, but I don't use plyr anymore)
library(dplyr)
df %>%
  group_by(rle_grp) %>%
  summarise(avgX = mean(x),
            avgY = mean(y)) %>%
  ungroup

#  rle_grp  avgX      avgY
#    (dbl) (dbl)     (dbl)
#1       1   2.0 0.4034953
#2       2   4.0 0.9082078
#3       3   5.0 0.2016819
#4       4   6.0 0.8983897
#5       5   7.0 0.9446753
#6       6   8.5 0.6449559

data.table

## or using data.table which is my package of choice
library(data.table)
setDT(df)

df[, .(avgX = mean(x), avgY = mean(y)) , by=.(rle_grp)]

#   rle_grp avgX      avgY
#1:       1  2.0 0.4034953
#2:       2  4.0 0.9082078
#3:       3  5.0 0.2016819
#4:       4  6.0 0.8983897
#5:       5  7.0 0.9446753
#6:       6  8.5 0.6449559

Upvotes: 1

Ott Toomet
Ott Toomet

Reputation: 1956

To achieve it with base R only:

changed <- which(c(TRUE, diff(as.integer(df$type)) != 0))
class <- rep(changed, diff(c(changed, nrow(df) + 1)))
df1 <- data.frame(meanX=tapply(df$x, class, mean), 
                  meanY=tapply(df$y, class, mean))

 df1
  meanX     meanY
1   2.0 0.4034953
4   4.0 0.9082078
5   5.0 0.2016819
6   6.0 0.8983897
7   7.0 0.9446753
8   8.5 0.6449559

Upvotes: 1

Related Questions