R-带循环的left_join数据框 [英] R - left_join dataframe with loop

问题描述

我的数据框x看起来像这样

my dataframe x looks like this

    id 
1   42

我的第二个data.frame z是:

My second data.frame z is:

   id amoumt     date
1  42      3  2013-09
2  42      8  2013-09
3  42      1  2011-09

目标是:

• 找到最低和最高日期
• 计算每个月的金额(如果来自一个id的金额更多且相同，则将其计算为一行)
• 创建带有日期的列并将其附加到x数据框中(请注意，在我的项目范围内，该范围将超过10年).

我创建了以下代码:

dates <- as.character(seq(as.Date(min(z$date), max(z$date), by=c("months"))))

找到第一个和最后一个日期.然后我有这个循环，它不起作用:

It finds first and last date. And then I have this loop, which do not works:

for (d in dates) {
  z %>%
    group_by(id) %>%
    summarise(amount = sum(amount)) %>%
    setNames(c(names(.)[1], paste("in", format(as.Date(d), "%Y-%m"), sep=" "))) %>%
    left_join(
      .,
      x,
      by=c("id")        )
}

结果应如下所示:

   id 2011-09    2013-09
1  42       0         11
2  42       1          0

推荐答案

我们可以用 dplyr + tidyr:

library(dplyr)
library(tidyr)

x %>%
  inner_join(z) %>%
  mutate(row = 1:n()) %>%
  group_by(id, date) %>%
  filter(which.min(date) || which.max(date)) %>%
  summarize(amount = sum(amount),
            row = max(row)) %>%
  spread(date, amount) %>%
  select(-row)

结果:

# A tibble: 2 x 3
# Groups:   id [1]
     id `2011-09` `2013-09`
  <int>     <int>     <int>
1    42        NA        11
2    42         1        NA

数据:

x = structure(list(id = 42L), .Names = "id", class = "data.frame", row.names = "1")

z = structure(list(id = c(42L, 42L, 42L), amount = c(3L, 8L, 1L), 
    date = structure(c(2L, 2L, 1L), .Label = c("2011-09", "2013-09"
    ), class = "factor")), .Names = c("id", "amount", "date"), class = "data.frame", row.names = c("1", 
"2", "3"))

这篇关于R-带循环的left_join数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！