datetime label option_title option_value lead difference
1 2016-07-22 GE 3 - Commercial Review 3 2 -1
2 2017-02-20 GE 2 - Solution Review 2 1 -1
3 2017-02-20 GE 1 - Opportunity Review 1 2 1
4 2017-04-18 GE 2 - Solution Review 2 3 1
5 2017-04-19 GE 3 - Commercial Review 3 4 1
6 2017-04-19 GE 4 - Submit Proposal 4 5 1
7 2017-08-08 GE 5 - Proposal Awarded 5 NA NA
8 2016-08-02 HSBC 5 - Proposal Awarded 5 6 1
9 2016-12-13 HSBC 6 - Delivery Phase 1 6 7 1
10 2017-08-07 HSBC 7 - Phase 1 Live 7 NA NA
11 2016-07-22 Lowes Pre-Qualification 0 NA NA
12 2016-08-02 Danske Bank 6 - Delivery Phase 1 6 NA NA
13 2016-07-22 AP Moller Maersk (IT Transformation) 3 - Commercial Review 3 NA NA
14 2016-07-22 BHP Billiton - APJ Pre-Qualification 0 2 2
15 2016-07-26 BHP Billiton - APJ 2 - Solution Review 2 0 -2
16 2016-07-26 BHP Billiton - APJ Pre-Qualification 0 2 2
我想从这个框架创建一个新的数据框架，该框架仅选择具有“差”值为负的“标签”。但是，我想选择所有类似的“标签”，如下所示：
datetime label option_title option_value lead difference
1 2016-07-22 GE 3 - Commercial Review 3 2 -1
2 2017-02-20 GE 2 - Solution Review 2 1 -1
3 2017-02-20 GE 1 - Opportunity Review 1 2 1
4 2017-04-18 GE 2 - Solution Review 2 3 1
5 2017-04-19 GE 3 - Commercial Review 3 4 1
6 2017-04-19 GE 4 - Submit Proposal 4 5 1
7 2017-08-08 GE 5 - Proposal Awarded 5 NA NA
8 2016-07-22 BHP Billiton - APJ Pre-Qualification 0 2 2
9 2016-07-26 BHP Billiton - APJ 2 - Solution Review 2 0 -2
10 2016-07-26 BHP Billiton - APJ Pre-Qualification 0 2 2
我不确定如何在dplyr中执行此操作。...SQL对此会更好吗？ （我没有在R中使用sql包太多）
最佳答案
另一种可能的方法是使用dplyr：
library(dplyr)
df %>% group_by(label) %>% filter(any(difference < 0))
#> # A tibble: 10 x 6
#> # Groups: label [2]
#> datetime label option_title option_value lead
#> <date> <chr> <chr> <int> <int>
#> 1 2016-07-22 GE 3 - Commercial Review 3 2
#> 2 2017-02-20 GE 2 - Solution Review 2 1
#> 3 2017-02-20 GE 1 - Opportunity Review 1 2
#> 4 2017-04-18 GE 2 - Solution Review 2 3
#> 5 2017-04-19 GE 3 - Commercial Review 3 4
#> 6 2017-04-19 GE 4 - Submit Proposal 4 5
#> 7 2017-08-08 GE 5 - Proposal Awarded 5 NA
#> 8 2016-07-22 BHP Billiton - APJ Pre-Qualification 0 2
#> 9 2016-07-26 BHP Billiton - APJ 2 - Solution Review 2 0
#> 10 2016-07-26 BHP Billiton - APJ Pre-Qualification 0 2
#> # ... with 1 more variables: difference <int>
数据
library(readr)
df <- read_csv("rowid, datetime, label, option_title, option_value, lead, difference
1, 2016-07-22, GE, 3 - Commercial Review, 3, 2, -1
2, 2017-02-20, GE, 2 - Solution Review, 2, 1, -1
3, 2017-02-20, GE, 1 - Opportunity Review, 1, 2, 1
4, 2017-04-18, GE, 2 - Solution Review, 2, 3, 1
5, 2017-04-19, GE, 3 - Commercial Review, 3, 4, 1
6, 2017-04-19, GE, 4 - Submit Proposal, 4, 5, 1
7, 2017-08-08, GE, 5 - Proposal Awarded, 5, NA, NA
8, 2016-08-02, HSBC, 5 - Proposal Awarded, 5, 6, 1
9, 2016-12-13, HSBC, 6 - Delivery Phase 1, 6, 7, 1
10, 2017-08-07, HSBC, 7 - Phase 1 Live, 7, NA, NA
11, 2016-07-22, Lowes, Pre-Qualification, 0, NA, NA
12, 2016-08-02, Danske Bank, 6 - Delivery Phase 1, 6, NA, NA
13, 2016-07-22, AP Moller Maersk (IT Transformation), 3 - Commercial Review, 3, NA, NA
14, 2016-07-22, BHP Billiton - APJ, Pre-Qualification, 0, 2, 2
15, 2016-07-26, BHP Billiton - APJ, 2 - Solution Review, 2, 0, -2
16, 2016-07-26, BHP Billiton - APJ, Pre-Qualification, 0, 2, 2")
df <- df[-1]