Transposing dataframe in specific number of columns in R-CodePudding

I would like to transpose a dataframe with a specific structure. The dataframe looks something like this

Col1    Col2       Col3  Col4
NameA   PositionA  URLA  Firm1
NameB   PositionB  URLB  Firm1
NameC   PositionC  URLC  Firm2
NameD   PositionD  URLD  Firm3
NameE   PositionE  URLE  Firm3
...

I want the output to look like this:

Col4   Name1  Position1  URL1  Name2  Position2  URL2
Firm1  NameA  PositionA  URLA  NameB  PositionB  URLB
Firm2  NameC  PositionC  URLC  
Firm3  NameD  PositionD  ULRD  NameE  PositionE  URLE
...

So basically ordering the respective people per firm according to their name, position and url. The maximum of people I have for a firm (so rows per firm) is 49. Most firms have less than 49 rows, but I still want to have 49 columns per name, position and url and if a firm has less people than 49 the rest of the columns should be empty.

Is there any code where this can be done easily for a large dataset? I hope the question is worded understandably. Thanks in advance!

CodePudding user response：

With pivot_wider:

library(dplyr)
library(tidyr)
dat %>% 
  rename_with(~ c("Name", "Position", "URL", "Firm")) %>% 
  group_by(Firm) %>% 
  mutate(id = row_number()) %>% 
  pivot_wider(names_from = id, values_from = Name:URL, names_vary = "slowest", names_sep = "")


#   Firm  Name1 Position1 URL1  Name2 Position2 URL2 
# 1 Firm1 NameA PositionA URLA  NameB PositionB URLB 
# 2 Firm2 NameC PositionC URLC  NA    NA        NA   
# 3 Firm3 NameD PositionD URLD  NameE PositionE URLE