Add multiple columns below each other in R

Question

Beginner here, I have a dataframe df with over 200 columns and I want to transform this dataframe as follows. This is the input:

Company Team_1   Team_Desc_1   Team_URL_1   Team_2     Team_Desc_2   Team_URL_2  Team_3     Team_Desc_3   Team_URL_3 ...
abc     Name1    Desc1         URL1         Name2      Desc2         URL2        Name3      Desc3         URL3       ...
def     Name1.1  Desc1.1       URL1.1       Name2.1    Desc2.1       URL2.1      Name3.1    Desc3.1       URL3.1     ...
ghi     Name1.2  Desc1.2       URL1.2       Name2.2    Desc2.2       URL2.2      Name3.2    Desc3.2       URL3.2     ...

and I want to transform this df as follows:

Company Team      Team_Desc  Team_URL
abc     Name1     Desc1      URL1
abc     Name2     Desc2      URL2
abc     Name3     Desc3      URL3
def     Name1.1   Desc1.1    URL1.1
def     Name2.1   Desc2.1    URL2.1
def     Name3.1   Desc3.1    URL3.1
ghi     Name1.2   Desc1.2    URL1.2
ghi     Name2.2   Desc2.2    URL2.2
ghi     Name3.2   Desc3.2    URL3.2
...     ...       ...        ...

so I want to add all names in one column with the corresponding Company, Desc and URL. How can I achieve this in R with a loop to do this for multiple columns that are all names the same with ascending values in the end of the column name, as in this example? Thank you!

Answer 1

With pivot_longer:

library(tidyr)
pivot_longer(dat, 
             -Company,
             names_to = ".value",
             names_pattern = "(Team[_|Desc|URL]*)_\\d")

  Company Team    Team_Desc Team_URL
1 abc     Name1   Desc1     URL1    
2 abc     Name2   Desc2     URL2    
3 abc     Name3   Desc3     URL3    
4 def     Name1.1 Desc1.1   URL1.1  
5 def     Name2.1 Desc2.1   URL2.1  
6 def     Name3.1 Desc3.1   URL3.1  
7 ghi     Name1.2 Desc1.2   URL1.2  
8 ghi     Name2.2 Desc2.2   URL2.2  
9 ghi     Name3.2 Desc3.2   URL3.2