如何按第n个最高值汇总列数据并在新df中获取日期

时间:2021-01-12 13:13:46

I have this dataset

我有这个数据集

date<-as.Date(c("2007-01-01","2007-01-02","2007-01-03","2007-01-04","2007-01-05"))
a<-c(55,8,3,7,126)
b<-c(3,199,7,66,8)
c<-c(91,333,2,9,4)
df<-data.frame(date,a,b,c)

  date        a   b   c
 2007-01-01  55   3  91
 2007-01-02   8 199 333
 2007-01-03   3   7   2
 2007-01-04   7  66   9
 2007-01-05 126   8   4

I want to 'select' the 2nd highest value from each column (a, b, and c) and get the date of occurrence, and coerce into a new dataframe as below...

我想“选择”每列中的第二个最高值(a,b和c)并获取发生日期,并强制转换为新的数据帧,如下所示......

Type    Date    2nd highest
a    2007-01-01  55
b    2007-01-04  66
c    2007-01-01  91

I have tried many different verbs in dplyr, tidyr, apply functions and I really can not even get close. Please help, thanks.

我在dplyr,tidyr,apply函数中尝试了很多不同的动词,我甚至无法接近。请帮忙,谢谢。

2 个解决方案

#1


3  

date<-as.Date(c("2007-01-01","2007-01-02","2007-01-03","2007-01-04","2007-01-05"))
a<-c(55,8,3,7,126)
b<-c(3,199,7,66,8)
c<-c(91,333,2,9,4)
df<-data.frame(date,a,b,c)

library(tidyverse)

df %>%
  gather(Type,value,-date) %>%  # reshape dataset
  arrange(desc(value)) %>%      # arrange in descending order
  group_by(Type) %>%            # for each type 
  slice(2) %>%                  # get 2nd row (2nd highest value)
  ungroup()                     # forget the grouping

# # A tibble: 3 x 3
#   date       Type  value
#   <date>     <chr> <dbl>
# 1 2007-01-01 a        55
# 2 2007-01-04 b        66
# 3 2007-01-01 c        91

#2


1  

You can also use nth (forking AntoniosK's solution):

你也可以使用nth(分叉AntoniosK的解决方案):

library(tidyverse)

df %>%
  gather(Type,value,-date) %>%  # reshape dataset
  group_by(Type) %>%            # for each type 
  filter(value==nth(value,2,-value)) %>%
  ungroup

# # A tibble: 3 x 3
# date  Type value
#       <date> <chr> <dbl>
# 1 2007-01-01     a    55
# 2 2007-01-04     b    66
# 3 2007-01-01     c    91

And a base R solution:

和基础R解决方案:

pos  <- sapply(df[-1],function(x) which(rank(-x)==2))
rows <- lapply(1:3,function(x) 
  setNames(transform(df[pos[x],c(1,1+x)],Type=names(pos)[x]),c("date","value","type")))
do.call(rbind,rows)

#          date value type
# 1  2007-01-01    55    a
# 4  2007-01-04    66    b
# 11 2007-01-01    91    c

#1


3  

date<-as.Date(c("2007-01-01","2007-01-02","2007-01-03","2007-01-04","2007-01-05"))
a<-c(55,8,3,7,126)
b<-c(3,199,7,66,8)
c<-c(91,333,2,9,4)
df<-data.frame(date,a,b,c)

library(tidyverse)

df %>%
  gather(Type,value,-date) %>%  # reshape dataset
  arrange(desc(value)) %>%      # arrange in descending order
  group_by(Type) %>%            # for each type 
  slice(2) %>%                  # get 2nd row (2nd highest value)
  ungroup()                     # forget the grouping

# # A tibble: 3 x 3
#   date       Type  value
#   <date>     <chr> <dbl>
# 1 2007-01-01 a        55
# 2 2007-01-04 b        66
# 3 2007-01-01 c        91

#2


1  

You can also use nth (forking AntoniosK's solution):

你也可以使用nth(分叉AntoniosK的解决方案):

library(tidyverse)

df %>%
  gather(Type,value,-date) %>%  # reshape dataset
  group_by(Type) %>%            # for each type 
  filter(value==nth(value,2,-value)) %>%
  ungroup

# # A tibble: 3 x 3
# date  Type value
#       <date> <chr> <dbl>
# 1 2007-01-01     a    55
# 2 2007-01-04     b    66
# 3 2007-01-01     c    91

And a base R solution:

和基础R解决方案:

pos  <- sapply(df[-1],function(x) which(rank(-x)==2))
rows <- lapply(1:3,function(x) 
  setNames(transform(df[pos[x],c(1,1+x)],Type=names(pos)[x]),c("date","value","type")))
do.call(rbind,rows)

#          date value type
# 1  2007-01-01    55    a
# 4  2007-01-04    66    b
# 11 2007-01-01    91    c