CODEWITHSUNDEEP
r
min
min

Reputation: 309

R - select and assign value to group based on condition in column

Having a data frame that looks like the following:

d
   year  pos  days   sal
1  2009  A     31   2000
2  2009  B     60   4000
3  2009  C     10    600
4  2010  B     10   1000
5  2010  D     90   7000

I would like to group data by year, adding days and sal, and select pos where days is maximum in the group.

The result should be like:

  year   pos  days   sal
1 2009    B    101   6600
2 2010    D    100   8000

I could deal with numeric values such as days and sal using functions like tapply(d$days, d$year, sum).

However, I have no idea how I can select pos that meets a condition on days and assign it to the group.

Any comments will be greatly appreciated!

Upvotes: 3

Views: 8991

Answers (3)

Sandipan Dey
Sandipan Dey

Reputation: 23101

With sqldf:

library(sqldf)
cbind.data.frame(sqldf('select year, sum(days) as days, sum(sal) as sal 
                        from d group by year'),
                 sqldf('select pos from d group by year having days=max(days)'))


  year days  sal pos
1 2009  101 6600   B
2 2010  100 8000   D

Upvotes: 0

h3rm4n
h3rm4n

Reputation: 4187

A solution with base R:

m1 <- d[as.logical(with(d, ave(days, year, FUN = function(x) seq_along(x) == which.max(x))  )), c('year','pos')]
m2 <- aggregate(cbind(days, sal) ~ year, d, sum)

merge(m1, m2, by = 'year')

Or with the data.table package:

library(data.table)
setDT(d)[order(days), .(pos = pos[.N], days = sum(days), sal = sum(sal)), by = year]

the resulting data.frame / data.table:

  year pos days  sal
1 2009   B  101 6600
2 2010   D  100 8000

Upvotes: 1

akrun
akrun

Reputation: 886938

We can use dplyr. After grouping by 'year', get the 'pos' where the 'days' are max (which.max(days)), as well do the sum of 'days' and 'sal'.

library(dplyr)
d %>% 
  group_by(year) %>% 
  summarise(pos = pos[which.max(days)], days = sum(days), sal = sum(sal))
# # A tibble: 2 × 4
#   year   pos  days   sal
#  <int> <chr> <int> <int>
#1  2009     B   101  6600
#2  2010     D   100  8000

Upvotes: 3

Related Questions