CODEWITHSUNDEEP
rdataframereshapedata-cleaning
LordVoldemort
LordVoldemort

Reputation: 107

Getting Data in a single row into multiple rows

I have a code where I see which people work in certain groups. When I ask the leader of each group to present those who work for them, in a survey, I get a row of all of the team members. What I need is to clean the data into multiple rows with their group information.

This is what my data frame looks like,

LeaderName <- c('John','Jane','Louis','Carl')

Group <- c('3','1','4','2')

Member1 <- c('Lucy','Stephanie','Chris','Leslie')

Member1ID <- c('1','2','3','4')

Member2 <- c('Earl','Carlos','Devon','Francis')

Member2ID <- c('5','6','7','8')

Member3 <- c('Luther','Peter','','Severus')

Member3ID <- c('9','10','','11')

GroupInfo <- data.frame(LeaderName, Group, Member1, Member1ID, Member2 ,Member2ID, Member3, Member3ID)

This is what I would like it to show with a certain code

LeaderName_ <- c('John','Jane','Louis','Carl','John','Jane','Louis','Carl','John','Jane','','Carl')

Group_ <- c('3','1','4','2','3','1','4','2','3','1','','2')

Member <- c('Lucy','Stephanie','Chris','Leslie','Earl','Carlos','Devon','Francis','Luther','Peter','','Severus')

MemberID <- c('1','2','3','4','5','6','7','8','9','10','','11')

ActualGroupInfor <- data.frame(LeaderName_,Group_,Member,MemberID)

Upvotes: 1

Views: 121

Answers (2)

M--
M--

Reputation: 29109

Here is a solution in base r:

reshape(
 data=GroupInfo, 
 idvar=c("LeaderName", "Group"),
 varying=list(
  Member=which(names(GroupInfo) %in% grep("^Member[0-9]$",names(GroupInfo),value=TRUE)),
  MemberID=which(names(GroupInfo) %in% grep("^Member[0-9]ID",names(GroupInfo),value=TRUE))), 
 direction="long", 
 v.names = c("Member","MemberID"),
 sep="_")[,-3]

#>           LeaderName Group    Member MemberID
#> John.3.1        John     3      Lucy        1
#> Jane.1.1        Jane     1 Stephanie        2
#> Louis.4.1      Louis     4     Chris        3
#> Carl.2.1        Carl     2    Leslie        4
#> John.3.2        John     3      Earl        5
#> Jane.1.2        Jane     1    Carlos        6
#> Louis.4.2      Louis     4     Devon        7
#> Carl.2.2        Carl     2   Francis        8
#> John.3.3        John     3    Luther        9
#> Jane.1.3        Jane     1     Peter       10
#> Louis.4.3      Louis     4                   
#> Carl.2.3        Carl     2   Severus       11

Created on 2019-05-23 by the reprex package (v0.2.1)

Upvotes: 1

akrun
akrun

Reputation: 887501

An option would be melt from data.table and specify the column name patterns in the measure parameter

library(data.table)
melt(setDT(GroupInfo), measure = patterns("^Member\\d+$", 
    "^Member\\d+ID$"), value.name = c("Member", "MemberID"))[, variable := NULL][]
#    LeaderName Group    Member MemberID
# 1:       John     3      Lucy        1
# 2:       Jane     1 Stephanie        2
# 3:      Louis     4     Chris        3
# 4:       Carl     2    Leslie        4
# 5:       John     3      Earl        5
# 6:       Jane     1    Carlos        6
# 7:      Louis     4     Devon        7
# 8:       Carl     2   Francis        8
# 9:       John     3    Luther        9
#10:       Jane     1     Peter       10
#11:      Louis     4                   
#12:       Carl     2   Severus       11

Upvotes: 1

Related Questions