时间戳

首页 > 解决方案 > 使用 R 中不同列的值透视数据

r - 使用 R 中不同列的值透视数据

问题描述

抱歉，如果我重复发布，但我尝试了我在 stackoverflow 中看到的不同的东西并且不能完全解决问题或理解我为什么会遇到它。

所以我有一个这样的数据集：

council_name <- c("Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barking and Dagenham","Barnet","Barnet")
period <- c("1st 2006", "1st 2006", "1st 2006", "1st 2006", "2nd 2006", "2nd 2006", "2nd 2006", "2nd 2006", "1st 2006", "1st 2006") 
category <- c ("glass", "fridges", "paper", "glass", "glass", "fridges", "paper", "glass", "glass", "fridges")
data <- c(333, 222, 100, 98, 450, 540, 33, 450, 560, 120)
category2 <- c ("collected", "collected", "collected", "no donors", "collected", "collected", "collected", "no donors", "collected", "collected")
df <- data.frame (council_name, period, category, category2, data)

我想要的是这样的：

council_name <- c("Barking and Dagenham","Barking and Dagenham","Barnet")
period <- c("1st 2006", "2nd 2006", "1st 2006") 
glass <- c(333, 450, 560)
fridges <- c(222,540,120)
paper <- c(100, 33, NA)
no.donors <- c(98, 450, NA)
df.desired <- data.frame (council_name, period, glass, fridges, paper, no.donors)

我一直在尝试使用数据透视函数做多种事情，但事实上我需要从 category1 和 category2 中提取列名，但是用 df 中同一列的值填充单元格，这给我带来了各种各样的问题。

非常感谢您的帮助！

标签： rdplyrdata.tablepivottidyr

解决方案

这是一个tidyverse解决方案，pivot_wider用于将数据转换为宽格式，然后用于rename更改列名。

library(tidyverse)

df %>%
  # Pivot from long to wide format using the first two columns as id cols and using both category and category2 columns to get the new column names
  pivot_wider(id_cols = c(council_name,period),
              names_from = c(category, category2),
              values_from = data) %>%
  # Rename the columns
  rename("glass" = "glass_collected",
         "fridges" = "fridges_collected",
         "paper" = "paper_collected",
         "no.donors" = "glass_no donors")

# A tibble: 3 x 6
# council_name         period   glass fridges paper no.donors
# <fct>                <fct>    <dbl>   <dbl> <dbl>     <dbl>
# 1 Barking and Dagenham 1st 2006   333     222   100        98
# 2 Barking and Dagenham 2nd 2006   450     540    33       450
# 3 Barnet               1st 2006   560     120    NA        NA

推荐阅读