1
\$\begingroup\$

I have some really wide data in R that I want to transform into the "tidy data" format. Unfortunately, I cannot change the process that generates this data, so it must be done within R.

My raw data looks like this (but with more records):

+---------+-----------+-------+------+------+----------+-----------+---------+------+------+---------+-----------+------+------+------+
| FTname  | FTleader  |  FT1   | FT2 | FT3  | FT2name  | FT2leader |  FT21   | FT22 | FT23 | FT3name | FT3leader | FT31 | FT32 | FT33 |
+---------+-----------+-------+------+------+----------+-----------+---------+------+------+---------+-----------+------+------+------+
| Billing | Rob       | Rob   | Ryan | Brad | Shipping | Brad      | Brad    | Dave | Kim  |         |           |      |      |      |
| Sales   | Sarah     | Sarah | Drew |      |          |           |         |      |      |         |           |      |      |      |
| Phone   | Tim       | Tim   | Zach | Ron  | Store    | Michael   | Michael | Ron  |      | Email   | Sean      | Sean | Ron  | Ben  |
| Repair  | Evan      | Evan  | Tim  | Jim  |          |           |         |      |      |         |           |      |      |      |
+---------+-----------+-------+------+------+----------+-----------+---------+------+------+---------+-----------+------+------+------+
  • FTname contains the name of the team
  • FTleader contains the leader for that team
  • FT1, FT2, FT3 are the members of the that team
  • An individual might belong to only 1 team, or they might belong to multiple teams
  • Each team has 1 team leader
  • There are 7 separate teams (Billing, Shipping, Sales, Store, Email, ..., etc.)

Ultimately, I want to reshape this data to look like below. Ideally, a row represents each person, the team they're on, and their role on that team. People will be listed multiple times, as they might be a part of more than 1 team. The first person on the team is always going to be the team leader, so no need to list them twice.

+---------+------+----------+
|  Name   | Role |   Team   |
+---------+------+----------+
| Rob     | TL   | Billing  |
| Rob     | DR   | Billing  |
| Ryan    | DR   | Billing  |
| Brad    | DR   | Billing  |
| Brad    | TL   | Shipping |
| Brad    | DR   | Shipping |
| Dave    | DR   | Shipping |
| Kim     | DR   | Shipping |
| Sarah   | TL   | Sales    |
| Sarah   | DR   | Sales    |
| Drew    | DR   | Sales    |
| Tim     | TL   | Phone    |
| Tim     | DR   | Phone    |
| Zach    | DR   | Phone    |
| Ron     | DR   | Phone    |
| Michael | TL   | Store    |
| Michael | DR   | Store    |
| Ron     | DR   | Store    |
| Sean    | TL   | Email    |
| Sean    | DR   | Email    |
| Ron     | DR   | Email    |
| Ben     | DR   | Email    |
| Evan    | TL   | Repair   |
| Evan    | DR   | Repair   |
| Tim     | DR   | Repair   |
| Jim     | DR   | Repair   |
+---------+------+----------+

Whatever solution I arrive at needs to be flexible to handle a variety of teams that a single person can be a part of.

Here is my approach so far:

library(dplyr)
library(reshape2)

# read data into a datarfame
data <- read.csv('tests/testthat/panel.csv', stringsAsFactors = FALSE, na = c(''))

# add team number to columns FT1 columns
names(data)[names(data)=="FTname"] <- "FT1name"
names(data)[names(data)=="FTleader"] <- "FT1leader"
names(data)[names(data)=="FT1"] <- "FT11"
names(data)[names(data)=="FT2"] <- "FT12"
names(data)[names(data)=="FT3"] <- "FT13"
names(data)[names(data)=="FT4"] <- "FT14"
names(data)[names(data)=="FT5"] <- "FT15"
names(data)[names(data)=="FT6"] <- "FT16"
names(data)[names(data)=="FT7"] <- "FT17"
names(data)[names(data)=="FT8"] <- "FT18"
names(data)[names(data)=="FT9"] <- "FT19"

# subset data to only columns that start with "FT" plus a number
teams <- data[ , grep("FT\\d+" , names(data))]

# get the teams represented in the dataset
team_names <- substr(names(teams[grep("FT\\d+\\leader", names(teams))]), 0, 3)

# create an empty list to hold individual team dataframes
team_data = list()

# iterate over each team...
for (t in team_names){

  # grab the columns associated with that team...
  team_t <- teams %>% select(starts_with(t))

  # rename columns by dropping team number asscoiated with the team
  colnames(team_t) = gsub(t, substr(t, 1, 2), colnames(team_t))

  # add team data to the list
  team_data[[t]] <- team_t
}

# combine list into one dataframe
team_data_combined <- bind_rows(team_data)

# get only the unique rows
team_data_combined <- unique(team_data_combined)

# drop rows where the whole row is blank
team_data_combined <- team_data_combined[apply(team_data_combined,1,function(x)any(!is.na(x))),] 

# drop unnecessary columns
team_data_combined$FTrole <- NULL
team_data_combined$FTsize <- NULL

# reshape the data from wide to narrow
df <- melt(team_data_combined, id=c("FTname"))

# rename columns in new dataframe
names(df)[names(df)=="FTname"] <- "team"
names(df)[names(df)=="variable"] <- "role"
names(df)[names(df)=="value"] <- "name"

# drop rows where name is empty
df <- df[!is.na(df$name),]

# drop exact duplicates
df <- unique(df)

# drop rows where role is "FT1" as these are the leaders
df <- subset(df, role!='FT1')

# impute team leader and direct report status
df$role <- as.character(df$role)
df$role[df$role == "FTleader"] <- "TL"
df$role[df$role != "TL"] <- "DR"
df$role <- as.factor(df$role)
df$team <- as.factor(df$team)

While I believe this approach appears to get me to what I'm looking forward, I'm open to any improvements that might make the improve the process.

Here is a sample of the data that I'm working with:

df <- structure(list(Email = c("JPerkins@company.com", "JClayton@company.com", 
"SBowen@company.com", "LThomas@company.com", "PDaniel@company.com", 
"BRomero@company.com", "OGarrett@company.com", "GGill@company.com", 
"PRowe@company.com", "SMurray@company.com", "GGibson@company.com", 
"LWells@company.com", "JDiaz@company.com", "SSpencer@company.com", 
"AWatkins@company.com", "LRyan@company.com", "JMyers@company.com", 
"CWilliamson@company.com", "CSimon@company.com", "DWilkerson@company.com", 
"YMorton@company.com", "KWeber@company.com", "JWoods@company.com", 
"RWarner@company.com", "ASantiago@company.com", "KReid@company.com", 
"JChavez@company.com", "IFerguson@company.com", "CAndrews@company.com", 
"WBrooks@company.com", "NNorris@company.com", "HWatson@company.com", 
"TSchmidt@company.com", "JGarner@company.com", "OClarke@company.com", 
"GJacobs@company.com", "MMccormick@company.com", "IJefferson@company.com", 
"HPatterson@company.com", "SSims@company.com", "SPratt@company.com", 
"RCastillo@company.com", "ADaniels@company.com", "ERivera@company.com", 
"DDouglas@company.com", "BErickson@company.com", "CDrake@company.com", 
"CHiggins@company.com", "LSharp@company.com", "RHarrington@company.com", 
"RNorman@company.com", "KParsons@company.com", "TTaylor@company.com", 
"WFord@company.com", "FManning@company.com", "JHampton@company.com", 
"MMitchell@company.com", "SFloyd@company.com", "AKennedy@company.com", 
"CSummers@company.com"), First.Name = c("Juan", "Janice", "Stella", 
"Leigh", "Pearl", "Bethany", "Oliver", "Glen", "Pat", "Shaun", 
"Gretchen", "Loretta", "Jeffrey", "Sally", "Andrea", "Lynda", 
"Jerry", "Cristina", "Cecilia", "Danny", "Yvette", "Katrina", 
"Janis", "Rosemary", "Albert", "Kristi", "Jim", "Ismael", "Clint", 
"Warren", "Noah", "Hazel", "Ted", "Jean", "Oscar", "Geneva", 
"Milton", "Iris", "Henry", "Santos", "Sam", "Randy", "Abel", 
"Enrique", "Daniel", "Bob", "Connie", "Carl", "Lana", "Ramiro", 
"Rosemarie", "Kristen", "Taylor", "Willard", "Frederick", "Jan", 
"Mike", "Sonia", "Archie", "Cary"), Last.Name = c("Perkins", 
"Clayton", "Bowen", "Thomas", "Daniel", "Romero", "Garrett", 
"Gill", "Rowe", "Murray", "Gibson", "Wells", "Diaz", "Spencer", 
"Watkins", "Ryan", "Myers", "Williamson", "Simon", "Wilkerson", 
"Morton", "Weber", "Woods", "Warner", "Santiago", "Reid", "Chavez", 
"Ferguson", "Andrews", "Brooks", "Norris", "Watson", "Schmidt", 
"Garner", "Clarke", "Jacobs", "Mccormick", "Jefferson", "Patterson", 
"Sims", "Pratt", "Castillo", "Daniels", "Rivera", "Douglas", 
"Erickson", "Drake", "Higgins", "Sharp", "Harrington", "Norman", 
"Parsons", "Taylor", "Ford", "Manning", "Hampton", "Mitchell", 
"Floyd", "Kennedy", "Summers"), External.Data.Reference = c(34L, 
35L, 44L, 48L, 14L, 29L, 40L, 47L, 52L, 23L, 28L, 38L, 12L, 43L, 
8L, 57L, 31L, 19L, 6L, 7L, 9L, 33L, 39L, 55L, 13L, 21L, 27L, 
54L, 1L, 17L, 30L, 3L, 37L, 45L, 51L, 2L, 20L, 56L, 25L, 36L, 
42L, 59L, 46L, 10L, 41L, 24L, 26L, 5L, 49L, 50L, 53L, 4L, 32L, 
58L, 60L, 11L, 15L, 18L, 22L, 16L), Language = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA), ELTname = c("Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team"), Role = c("ELT", "ELT", "CEO", 
"ELT", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", 
"MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", 
"MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", 
"MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", 
"MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", "MM", 
"MM", "MM", "MM"), FTname = c("Company Top Management Team", 
"Company Top Management Team", "Company Top Management Team", 
"Company Top Management Team", "Accounting team", "Accounting team", 
"Accounting team", "Accounting team", "Accounting team", "Asset Management team", 
"Asset Management team", "Asset Management team", "Asset Management team", 
"Asset Management team", "Business Development team", "Business Development team", 
"Business Development team", "Drilling & Completion team", "Drilling & Completion team", 
"Drilling & Completion team", "Drilling & Completion team", "Drilling & Completion team", 
"Drilling & Completion team", "Production Operations & Company Midstream Services team", 
"Production Operations & Company Midstream Services team", "Production Operations & Company Midstream Services team", 
"Production Operations & Company Midstream Services team", "EH&S team", 
"EH&S team", "EH&S team", "EH&S team", "Investor Relations team", 
"Investor Relations team", "Investor Relations team", "Investor Relations team", 
"Investor Relations team", "Business Development Company Midstream team", 
"Business Development Company Midstream team", "Human Resources & Employee Development team", 
"Human Resources & Employee Development team", "Human Resources & Employee Development team", 
"Human Resources & Employee Development team", "Human Resources & Employee Development team", 
"Infrastructure & Technology team", "Infrastructure & Technology team", 
"Infrastructure & Technology team", "IT Strategy team", "IT Strategy team", 
"IT Strategy team", "IT Strategy team", "IT Strategy team", NA, 
"Legal team", "Legal team", "Legal team", "Marketing team", "Marketing team", 
"Marketing team", "Government Affairs & Regulatory Compliance team", 
"Government Affairs & Regulatory Compliance team"), FTrole = c("DR", 
"DR", "TL", "DR", "DR", "TL", "DR", "DR", "DR", "DR", "DR", "DR", 
"DR", "TL", "TL", "DR", "DR", "TL", "DR", "DR", "DR", "DR", "DR", 
"TL", "DR", "DR", "DR", "DR", "DR", "DR", "DR", "DR", "DR", "DR", 
"TL", "DR", "TL", "DR", "DR", "TL", "DR", "DR", "DR", "DR", "DR", 
"TL", "TL", "DR", "DR", "DR", "DR", NA, "DR", "TL", "DR", "DR", 
"DR", "TL", "DR", "DR"), FTnum = c(NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 7L, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, 16L, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), FTsize = c(4L, 4L, 4L, 4L, 5L, 5L, 5L, 
5L, 5L, 5L, 5L, 5L, 5L, 5L, 3L, 3L, 3L, 6L, 6L, 6L, 6L, 6L, 6L, 
4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 5L, 2L, 2L, 5L, 
5L, 5L, 5L, 5L, 3L, 3L, 3L, 5L, 5L, 5L, 5L, 5L, 0L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 3L), FTleader = c("Stella Bowen", "Stella Bowen", 
"Stella Bowen", "Stella Bowen", "Bethany Romero", "Bethany Romero", 
"Bethany Romero", "Bethany Romero", "Bethany Romero", "Bret Newton", 
"Bret Newton", "Bret Newton", "Bret Newton", "Bret Newton", "Robin Clark", 
"Robin Clark", "Robin Clark", "Cristina Williamson", "Cristina Williamson", 
"Cristina Williamson", "Cristina Williamson", "Cristina Williamson", 
"Cristina Williamson", "Rosemary Warner", "Rosemary Warner", 
"Rosemary Warner", "Rosemary Warner", "Yvette Morton", "Yvette Morton", 
"Yvette Morton", "Yvette Morton", "Richard Robuck ", "Richard Robuck ", 
"Richard Robuck ", "Richard Robuck ", "Richard Robuck ", "Milton Mccormick", 
"Milton Mccormick", "Santos Sims", "Santos Sims", "Santos Sims", 
"Santos Sims", "Santos Sims", "Bob Erickson", "Bob Erickson", 
"Bob Erickson", "Connie Drake", "Connie Drake", "Connie Drake", 
"Connie Drake", "Connie Drake", "Kristen Parsons", "Willard Ford", 
"Willard Ford", "Willard Ford", "Sonia Floyd", "Sonia Floyd", 
"Sonia Floyd", "Michael Kucuk", "Michael Kucuk"), FT1 = c("Stella Bowen", 
"Stella Bowen", "Stella Bowen", "Stella Bowen", "Bethany Romero", 
"Bethany Romero", "Bethany Romero", "Bethany Romero", "Bethany Romero", 
"Bret Newton", "Bret Newton", "Bret Newton", "Bret Newton", "Bret Newton", 
"Robin Clark", "Robin Clark", "Robin Clark", "Cristina Williamson", 
"Cristina Williamson", "Cristina Williamson", "Cristina Williamson", 
"Cristina Williamson", "Cristina Williamson", "Rosemary Warner", 
"Rosemary Warner", "Rosemary Warner", "Rosemary Warner", "Yvette Morton", 
"Yvette Morton", "Yvette Morton", "Yvette Morton", "Oscar Clarke", 
"Oscar Clarke", "Oscar Clarke", "Oscar Clarke", "Oscar Clarke", 
"Milton Mccormick", "Milton Mccormick", "Santos Sims", "Santos Sims", 
"Santos Sims", "Santos Sims", "Santos Sims", "Bob Erickson", 
"Bob Erickson", "Bob Erickson", "Connie Drake", "Connie Drake", 
"Connie Drake", "Connie Drake", "Connie Drake", "Kristen Parsons", 
"Willard Ford", "Willard Ford", "Willard Ford", "Sonia Floyd", 
"Sonia Floyd", "Sonia Floyd", "Michael Kucuk", "Michael Kucuk"
), FT2 = c("Leigh Thomas", "Leigh Thomas", "Leigh Thomas", "Leigh Thomas", 
"Pearl Daniel", "Pearl Daniel", "Pearl Daniel", "Pearl Daniel", 
"Pearl Daniel", "Loretta Wells", "Loretta Wells", "Loretta Wells", 
"Loretta Wells", "Loretta Wells", "Jerry Myers", "Jerry Myers", 
"Jerry Myers", "Janis Woods", "Janis Woods", "Janis Woods", "Janis Woods", 
"Janis Woods", "Janis Woods", "Jim Chavez", "Jim Chavez", "Jim Chavez", 
"Jim Chavez", "Clint Andrews", "Clint Andrews", "Clint Andrews", 
"Clint Andrews", "Doug Madeley ", "Doug Madeley ", "Doug Madeley ", 
"Doug Madeley ", "Doug Madeley ", "Iris Jefferson", "Iris Jefferson", 
"Henry Patterson", "Henry Patterson", "Henry Patterson", "Henry Patterson", 
"Henry Patterson", "Enrique Rivera", "Enrique Rivera", "Enrique Rivera", 
"Lana Sharp", "Lana Sharp", "Lana Sharp", "Lana Sharp", "Lana Sharp", 
NA, "Taylor Taylor", "Taylor Taylor", "Taylor Taylor", "Mike Mitchell", 
"Mike Mitchell", "Mike Mitchell", "Archie Kennedy", "Archie Kennedy"
), FT3 = c("Juan Perkins", "Juan Perkins", "Juan Perkins", "Juan Perkins", 
"Oliver Garrett", "Oliver Garrett", "Oliver Garrett", "Oliver Garrett", 
"Oliver Garrett", "Jeffrey Diaz", "Jeffrey Diaz", "Jeffrey Diaz", 
"Jeffrey Diaz", "Jeffrey Diaz", "Lynda Ryan", "Lynda Ryan", "Lynda Ryan", 
"Katrina Weber", "Katrina Weber", "Katrina Weber", "Katrina Weber", 
"Katrina Weber", "Katrina Weber", "Kristi Reid", "Kristi Reid", 
"Kristi Reid", "Kristi Reid", "Warren Brooks", "Warren Brooks", 
"Warren Brooks", "Warren Brooks", "Geneva Jacobs", "Geneva Jacobs", 
"Geneva Jacobs", "Geneva Jacobs", "Geneva Jacobs", NA, NA, "Abel Daniels", 
"Abel Daniels", "Abel Daniels", "Abel Daniels", "Abel Daniels", 
"Daniel Douglas", "Daniel Douglas", "Daniel Douglas", "Carl Higgins", 
"Carl Higgins", "Carl Higgins", "Carl Higgins", "Carl Higgins", 
NA, "Frederick Manning", "Frederick Manning", "Frederick Manning", 
"Jan Hampton", "Jan Hampton", "Jan Hampton", "Cary Summers", 
"Cary Summers"), FT4 = c("Janice Clayton", "Janice Clayton", 
"Janice Clayton", "Janice Clayton", "Glen Gill", "Glen Gill", 
"Glen Gill", "Glen Gill", "Glen Gill", "Gretchen Gibson", "Gretchen Gibson", 
"Gretchen Gibson", "Gretchen Gibson", "Gretchen Gibson", NA, 
NA, NA, "Cecilia Simon", "Cecilia Simon", "Cecilia Simon", "Cecilia Simon", 
"Cecilia Simon", "Cecilia Simon", "Albert Santiago", "Albert Santiago", 
"Albert Santiago", "Albert Santiago", "Daniel Shehan", "Daniel Shehan", 
"Daniel Shehan", "Daniel Shehan", "Hazel Watson", "Hazel Watson", 
"Hazel Watson", "Hazel Watson", "Hazel Watson", NA, NA, "Randy Castillo", 
"Randy Castillo", "Randy Castillo", "Randy Castillo", "Randy Castillo", 
NA, NA, NA, "Ramiro Harrington", "Ramiro Harrington", "Ramiro Harrington", 
"Ramiro Harrington", "Ramiro Harrington", NA, NA, NA, NA, NA, 
NA, NA, NA, NA), FT5 = c(NA, NA, NA, NA, "Pat Rowe", "Pat Rowe", 
"Pat Rowe", "Pat Rowe", "Pat Rowe", "Shaun Murray", "Shaun Murray", 
"Shaun Murray", "Shaun Murray", "Shaun Murray", NA, NA, NA, "Danny Wilkerson", 
"Danny Wilkerson", "Danny Wilkerson", "Danny Wilkerson", "Danny Wilkerson", 
"Danny Wilkerson", NA, NA, NA, NA, "Noah Norris", "Noah Norris", 
"Noah Norris", "Noah Norris", "Jean Garner", "Jean Garner", "Jean Garner", 
"Jean Garner", "Jean Garner", NA, NA, "Sam Pratt", "Sam Pratt", 
"Sam Pratt", "Sam Pratt", "Sam Pratt", NA, NA, NA, "Rosemarie Norman", 
"Rosemarie Norman", "Rosemarie Norman", "Rosemarie Norman", "Rosemarie Norman", 
NA, NA, NA, NA, NA, NA, NA, NA, NA), FT6 = c(NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "Yvette Morton", 
"Yvette Morton", "Yvette Morton", "Yvette Morton", "Yvette Morton", 
"Yvette Morton", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), FT7 = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA), FT8 = c(NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA), FT2role = c(NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
"TL", NA, NA, NA, NA, NA, NA, NA, NA, NA, "TL", NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA), FT2name = c(NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, "EH&S team", NA, NA, NA, NA, NA, NA, NA, NA, NA, "Government Affairs & Regulatory Compliance team", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), FT2size = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, 5L, NA, NA, NA, NA, NA, NA, NA, NA, NA, 3L, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), FT2leader = c(NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, "Yvette Morton", NA, NA, NA, NA, NA, NA, NA, NA, 
NA, "Noah Norris", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA), FT21 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "Yvette Morton", NA, NA, 
NA, NA, NA, NA, NA, NA, NA, "Noah Norris", NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA), FT22 = c(NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "Clint Andrews", 
NA, NA, NA, NA, NA, NA, NA, NA, NA, "Archie Kennedy", NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), FT23 = c(NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, "Warren Brooks", NA, NA, NA, NA, NA, NA, NA, NA, NA, 
"Cary Summers", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA), FT24 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, "Daniel Shehan", NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA), FT25 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, "Noah Norris", NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA), FT26 = c(NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA), FT27 = c(NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA), FT28 = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA), X = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
NA)), class = "data.frame", row.names = c(NA, -60L))
\$\endgroup\$
2
  • \$\begingroup\$ Can you supply your example data with dput so we can copy and run your example? \$\endgroup\$ – minem Nov 7 '18 at 7:12
  • \$\begingroup\$ @minem I added sample data to the question. \$\endgroup\$ – CurtLH Nov 8 '18 at 2:26
2
\$\begingroup\$

Using tidyverse you could do the following :

library(tidyverse)
res <- df %>%
  select(FTname, FTrole,matches("^FT\\d+$")) %>%
  gather(col,Name,matches("^FT\\d+$")) %>%
  filter(!is.na(Name)) %>%
  select(-col)


head(res)
#                        FTname FTrole           Name
# 1 Company Top Management Team     DR   Stella Bowen
# 2 Company Top Management Team     DR   Stella Bowen
# 3 Company Top Management Team     TL   Stella Bowen
# 4 Company Top Management Team     DR   Stella Bowen
# 5             Accounting team     DR Bethany Romero
# 6             Accounting team     TL Bethany Romero
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.