按日期分列的唯一值

Question

我想获得一个包含两列的数据框架: 1.不同的水果(没有重复) 2.特定水果(即kiwis)出现的第一天。

fruits <- c("apples, oranges, pears, bananas",
"pineapples, mangos, guavas",
"bananas, apples, kiwis") 


fruits<-as.data.frame(fruits)
fruits$date<-c( "12.8.16", "22.4.17", "12.9.16")

fruits[with(fruits, order(date)), ]

我试图编写一个循环或使用match命令。但是，无法识别唯一的字符串值。

提前谢谢你！詹尼斯

Answer 1

以下是一些解决办法：

1)使用dplyr和tidyr来构造/取消/汇总。首先，将date列转换为"Date"类，然后拆分fruits列，生成一个列，其中每个单元格包含一个水果向量。unnest，并找到最小值：

library(dplyr)
library(tidyr)

fruits %>%
       mutate(date = as.Date(date, "%d.%m.%y"),
              fruits = strsplit(as.character(fruits), ", ")) %>%
       unnest %>%
       group_by(fruits) %>%
       summarize(date = min(date)) %>%
       ungroup

给予：

# A tibble: 8 × 2
      fruits       date
       <chr>     <date>
1     apples 2016-08-12
2    bananas 2016-08-12
3     guavas 2017-04-22
4      kiwis 2016-09-12
5     mangos 2017-04-22
6    oranges 2016-08-12
7      pears 2016-08-12
8 pineapples 2017-04-22

( 1a)分离_rows/这个稍微短一些的变化使用separate_rows (用一个简单的命令替换strsplit和unnest行)。它要求tidyr 0.5或更高。它得出了同样的结果：

fruits %>%
       mutate(date = as.Date(date, "%d.%m.%y")) %>%
       separate_rows(fruits) %>%
       group_by(fruits) %>%
       summarize(date = min(date)) %>%
       ungroup

2) str拆分/堆栈/聚合--它不使用任何包。首先，我们拆分水果列，并将结果列表的组件命名为L，并指定日期。然后，我们将列表堆叠，创建一个数据框架，并重命名这些列，同时还创建一个真正的"Date"类列。最后，我们用aggregate方法求出最小值。

L <- with(fruits, setNames(strsplit(as.character(fruits), ", "), as.Date(date,"%d.%m.%y")))
stk <- with(stack(L), data.frame(fruits = values, date = as.Date(ind)))
aggregate(date ~ fruits, stk, min)

给这个data.frame：

      fruits       date
1     apples 2016-08-12
2    bananas 2016-08-12
3     guavas 2017-04-22
4      kiwis 2016-09-12
5     mangos 2017-04-22
6    oranges 2016-08-12
7      pears 2016-08-12
8 pineapples 2017-04-22

Answer 2

下面是一种使用splitstackshape包的方法，它使用下面的data.table包。我们可以使用cSplit()在逗号处拆分fruits列，然后使用data.table语法获得最小的date。

library(splitstackshape)
## create the long data frame from the split 'fruits' column
DT <- cSplit(fruits, "fruits", sep = ",", direction = "long")
## convert the 'date' column to date class and take the minimum row
DT[, .(date = min(as.IDate(date, "%d.%m.%y"))), by = fruits]
#        fruits       date
# 1:     apples 2016-08-12
# 2:    oranges 2016-08-12
# 3:      pears 2016-08-12
# 4:    bananas 2016-08-12
# 5: pineapples 2017-04-22
# 6:     mangos 2017-04-22
# 7:     guavas 2017-04-22
# 8:      kiwis 2016-09-12

Answer 3

我想这就是你想要的。

fruits <- c("apples, oranges, pears, bananas",
        "pineapples, mangos, guavas",
        "bananas, apples, kiwis") 

fruits<-as.data.frame(fruits,stringsAsFactors=FALSE) #probably easier for the fruits to be strings rather than factors
fruits$date<-as.Date(c( "12.8.16", "22.4.17", "12.9.16"),format="%d.%m.%y") #and set your dates to be Dates rather than strings (otherwise they will be sorted alphabetically)

fruits[with(fruits, order(date)), ]

#need to convert your df to one-fruit-per-row
fruits2 <- do.call(rbind, #this binds together the data frames created by the lapply loop
               lapply(1:nrow(fruits), #loops through the rows of fruits df to create a list of data frames, each corresponding to one row
                      function(i) data.frame(fruit=trimws(strsplit((fruits$fruits),",")[[i]]), #splits your strings at commas, and trims off the whitespace
                                             date=fruits$date[i],stringsAsFactors = FALSE))) #adds the date corresponding to each row

#finding the first appearance is easily done using dplyr
library(dplyr)
fruits3 <- fruits2 %>% group_by(fruit) %>% summarise(firstdate=min(date))

或者另一种方法是使用水果的唯一名称设置数据帧，然后使用grep查找每个水果的第一个日期。

fruits <- fruits[order(fruits$date),]
firstfruits <- data.frame(fruit=unique(trimws(unlist(strsplit(fruits$fruits,",")))),stringsAsFactors = FALSE)
firstfruits$date <- do.call(c,lapply(firstfruits$fruit, function(F) fruits$date[grep(F,fruits$fruits)[1]]))