我们如何在R中透视数据并在之后添加计算

发布于02月22日

我正在try 更长时间地透视数据集，在该操作之后，我需要添加一个计算.我面临的挑战是，计算可能会特定于每一行.

因此，给定此数据集:


df <- data.frame(
  person = c("Alice", "Bob", "Charlie", "Jen","Zar"),
  filed_taxes = c(1,0,1,0,0),
  required_to_file_taxes =c(1,1,1,0,0),
  bought_items = c(1,1,1,0,1),
  required_to_buy_items = c(1,1,1,1,1),
  took_vacation = c(1,0,0,1,1),
  required_to_take_vacation = c(1,1,1,1,1)
)

以"Required"开头的列将用于计算该相对类别中总计的百分比.

所以输出看起来像这样:

Measure        Completed     % Completed
filed_taxes        2             66%
bought_items       4             80%
took_vacation      3             60%

我try 从pivot_longer到pivot_wider来计算百分比，因为它们基本上是按行计算的，然后再try pivot_longer来计算最终输出，但这不起作用.

有什么 idea 或小贴士吗？

推荐答案

在这里，我计算每列中出现了多少个1，透视更长，将列名转换为一对值，指定观察/必需或有问题的行为，然后再次透视更宽.

library(tidyverse)
df |>
  pivot_longer(-person) |>
  count(name, wt = value) |>
  mutate(req = if_else(str_detect(name, "required"), "required", "observed"),
         name = case_when(
    name %>% str_detect("items") ~ "buy items",
    name %>% str_detect("taxes") ~ "file taxes",
    name %>% str_detect("vacation") ~ "take vacation"
  )) %>%
  pivot_wider(names_from = req, values_from = n) |>
  mutate(pct = observed / required)

结果

# A tibble: 3 × 4
  name          observed required   pct
  <chr>            <dbl>    <dbl> <dbl>
1 buy items            4        5 0.8  
2 file taxes           2        3 0.667
3 take vacation        3        5 0.6

或者，如果我们知道所有列都是成对排列的，我们可以使用这些位置来获得基本上相同的输出:

df |>
  pivot_longer(-person) |>
  mutate(pair_num = (row_number()-1) %/% 2, .by = person) |>
  mutate(req = if_else(str_detect(name, "required"), "required", "observed")) |>
  mutate(name = first(name), .by = c(person, pair_num)) |>
  count(name, req, wt = value) |>
  pivot_wider(names_from = req, values_from = n) |>
  mutate(pct = observed / required)

结果

# A tibble: 3 × 4
  name          observed required   pct
  <chr>            <dbl>    <dbl> <dbl>
1 bought_items         4        5 0.8  
2 filed_taxes          2        3 0.667
3 took_vacation        3        5 0.6