时间戳

首页 > 解决方案 > 基于 R 中的多个变量创建人口普查表

r - 基于 R 中的多个变量创建人口普查表

问题描述

我是 R 的新手，并且真的在为一个感觉很简单的问题而苦苦挣扎（我一直无法找到答案）。

我有一个相对较大的数据表，其中基本上包括 - 人 - 他们住在哪里 - 他们做什么 - 搬入日期 - 搬出日期。我的目标是导出一个运行中的每周人口普查表，其中每周为一行，每个职业和城市为一列，其中填充了当时的人数。

#MRE

library(tidyverse) 
library(lubridate)

data <- data.frame(
  first_names = c("joe", "sally", "bob", "frank", "susy"),
  move_in = as.Date(c("2020-01-01", "2021-01-04", "2020-04-01", "2018-12-20", "2019-10-12")),
  move_out = as.Date(c("2021-01-01", NA, "2021-10-01", NA, NA)),
  city = c("Denver", "Phoenix", "Austin", "Denver", "Seattle"),
  occupation = c("doctor", "doctor", "architect", "teacher", "teacher"))

#what I've tried :
  
cities = unique(data$city)[!is.na(unique(data$city))]
occupations = unique(data$occupation)[!is.na(unique(data$occupation))]
weeks <- (date = seq(from = as.Date("2020-12-27"), to = as.Date(today()), by="1 week"))

census <- matrix(data=NA, nrows=44, ncols=12) 

for (i in seq(cities)){
  for (j in seq(occupations)){
    count <- data %>% 
      filter(cities == i) %>%
      filter(occupations == j) %>% 
      sapply(weeks, function(x)
        sum(
          ((as.Date(data$move_in)) <= as.Date(x) &
           (as.Date(data$move_out)) > as.Date(x))|
          ((as.Date(data$move_in)) <= as.Date(x) &
           is.na(data$move_out))))
  
  census[j,x] <- count
}}

任何帮助是极大的赞赏！

标签： rdataframedatecensusgather

解决方案

这是使用一些 tidyverse 动词的可能解决方案，因为您加载了该包。我们会遍历您对使用该map_dfr功能感兴趣的几周，并且每周我们都会收集使用上述逻辑陈述的人的子集。然后，我们可以group_by直接跳过双外循环和count它们。最后，我们mutate在一周内新建一个专栏，以便在它们绑定在一起后保持直立。在循环之外，我们然后pivot_wider获得您正在寻找的每职业一列和每周一行的格式。

library(tidyverse)

data <- data.frame(
  first_names = c("joe", "sally", "bob", "frank", "susy"),
  move_in = as.Date(c("2020-01-01", "2021-01-04", "2020-04-01", "2018-12-20", "2019-10-12")),
  move_out = as.Date(c("2021-01-01", NA, "2021-10-01", NA, NA)),
  city = c("Denver", "Phoenix", "Austin", "Denver", "Seattle"),
  occupation = c("doctor", "doctor", "architect", "teacher", "teacher"))

# Avoid needing to load lubridate by using Sys.Date() instead of today()
weeks <- (date = seq(from = as.Date("2020-12-27"), to = as.Date(Sys.Date()), by="1 week"))

map_dfr(weeks, function(week_i){
  data %>%
    filter(move_in<week_i & move_out > week_i | move_in < week_i & is.na(move_out)) %>%
    group_by(city, occupation) %>%
    count() %>%
    mutate(week=week_i)
}) %>%
  pivot_wider(values_from = n, names_from = occupation, values_fill = 0)

返回

# A tibble: 170 x 5
# Groups:   city [4]
   city    week       architect doctor teacher
   <chr>   <date>         <int>  <int>   <int>
 1 Austin  2020-12-27         1      0       0
 2 Denver  2020-12-27         0      1       1
 3 Seattle 2020-12-27         0      0       1
 4 Austin  2021-01-03         1      0       0
 5 Denver  2021-01-03         0      0       1
 6 Seattle 2021-01-03         0      0       1
 7 Austin  2021-01-10         1      0       0
 8 Denver  2021-01-10         0      0       1
 9 Phoenix 2021-01-10         0      1       0
10 Seattle 2021-01-10         0      0       1
# ... with 160 more rows

由于几个拼写错误，您似乎遇到了错误。您正在使用filter动词来请求cities列，但数据在示例数据集中只有一city列。同样适用于occupationsvs occupation。很好地记住未来，但伟大的第一次努力和很好的例子！

推荐阅读