R - 将重复的行转换为列数未知的多列 [英] R - pivoting duplicate rows into multiple column with unknown number of columns

问题描述

我有一个这样的数据框:

I have a data frame like this:

由此创建:

companies = c("ABC Ltd", "ABC Ltd", "ABC Ltd", "Derwent plc", "Derwent plc")
sic = c("12345", "24155", "31231", "55346", "34234")

df = data.frame(companies, sic)

如您所见，由于 SIC 代码，公司列重复.

我想扩大范围，以便每个 SIC 代码都有自己的列，并且每行只有 1 家公司.

类似于以下内容，我不知道可能有多少列(即可能有些公司有 20 个 sic 代码).

As you can see, the companies column is duplicated due to the SIC code.

I want to pivot wider so that each SIC code has its own column and that it is only 1 company per row.

Something like the following where I don't know how many columns there might be (i.e. there could be some companies with 20 sic codes).

我尝试使用 pivot_wider 旋转它，但我无法让它做我需要它做的事情.

I have tried pivoting it using pivot_wider but I cannot get it to do what I need it to do.

非常感谢任何帮助.

推荐答案

使用包 dplyr 和 tidyr 你可以使用

With the packages dplyr and tidyr you can use

library(dplyr)
library(tidyr)

df %>% 
  group_by(companies) %>% 
  mutate(row_n = row_number()) %>% 
  pivot_wider(companies, names_from = row_n, values_from = sic, names_glue = "sic.{row_n}")

输出

# A tibble: 2 x 4
# Groups:   companies [2]
#   companies   sic.1 sic.2 sic.3
#   <chr>       <chr> <chr> <chr>
# 1 ABC Ltd     12345 24155 31231
# 2 Derwent plc 55346 34234 NA   

这篇关于R - 将重复的行转换为列数未知的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！