时间戳

首页 > 解决方案 > R：使用参考文件转换数据集

r - R：使用参考文件转换数据集

问题描述

不完全确定如何表达我要查找的内容，但我有一个主数据集（1000 行）和另一个详细信息文件（1000 行）。这是一对多的关系，我想加入表格，但我不想拥有多行主数据集，而是想基于详细文件中的列创建列。

下面是我正在寻找的一个例子：

> maindata <- data.frame(ID = c(1,2,3,4), Customer = c("Bob","Mary","Bill","Steve"), stringsAsFactors = FALSE)
> maindata
  ID Customer
  1      Bob
  2     Mary
  3     Bill
  4    Steve

> reference <- data.frame(ID = c(1,1,1,1,2,2,4), Code = c("Credit","Price","Cust","Task","Credit","Task","Cust"), Num = c(5,4,3,8,3,4,6), stringsAsFactors = FALSE)
> reference
  ID   Code Num
  1 Credit   5
  1  Price   4
  1   Cust   3
  1   Task   8
  2 Credit   3
  2   Task   4
  4   Cust   6

我希望它转变为：

> IdealOutput
  ID Customer Credit Price Cust Task
1  1      Bob      5     4    3    8
2  2     Mary      3    NA   NA    4
3  3     Bill     NA    NA   NA   NA
4  4    Steve     NA    NA    6   NA

编辑

有 2 个标题列和 2 个值列。

> reference <- data.frame(ID = c(1,1,1,1,2,2,4), Code = c("Credit","Price","Cust","Task","Credit","Task","Cust"), Num = c(5,4,3,8,3,4,6), Code2 = c("Banana","Apple","Pear","Grapes","Pear","Grapes","Banana"), Num2 = c(2,5,4,7,3,6,5), stringsAsFactors = FALSE)
> reference
  ID   Code Num  Code2 Num2
1  1 Credit   5 Banana    2
2  1  Price   4  Apple    5
3  1   Cust   3   Pear    4
4  1   Task   8 Grapes    7
5  2 Credit   3   Pear    3
6  2   Task   4 Grapes    6
7  4   Cust   6 Banana    5

我想将其转换为：

> IdealOutput
  ID Customer Credit Price Cust Task Banana Apple Pear Grapes
1  1      Bob      5     4    3    8      2     5    4      7
2  2     Mary      3    NA   NA    4     NA    NA    3      6
3  3     Bill     NA    NA   NA   NA     NA    NA   NA     NA
4  4    Steve     NA    NA    6   NA      5    NA   NA     NA

我可以做两个单独的spreads然后Left_join但这不直观。

标签： rtransform

解决方案

我们可以full_join通过'ID'做一个然后sread

library(tidyverse)
full_join(reference, maindata) %>% 
      spread(Code, Num) %>%
      select(1:6)
#   ID Customer Credit Cust Price Task
#1  1      Bob      5    3     4    8
#2  2     Mary      3   NA    NA    4
#3  3     Bill     NA   NA    NA   NA
#4  4    Steve     NA    6    NA   NA

推荐阅读