Non-structured table to narrow format table transformation?

Question

I need to make the data go from Example 1 to Example 2.

# Example 1
data.frame(Player = c("Brad Abbey", "Wins", "Losses", "Neither", "Caleb Aekins", "Wins", "Losses", "Neither"),
                 No = c(1, NA, NA, NA))

#         Player No
# 1   Brad Abbey  1
# 2         Wins NA
# 3       Losses NA
# 4      Neither NA
# 5 Caleb Aekins  1
# 6         Wins NA
# 7       Losses NA
# 8      Neither NA


# Example 2
structure(list(Player = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L), .Label = c("Brad Abbey", "Caleb Aekins"), class = "factor"), 
Result = structure(c(3L, 4L, 1L, 2L, 3L, 4L, 1L, 2L), class = "factor", 
.Label = c("Losses", 
"Neither", "Overall", "Wins"))), class = "data.frame", row.names = c(NA, 
-8L))

#         Player  Result
# 1   Brad Abbey Overall
# 2   Brad Abbey    Wins
# 3   Brad Abbey  Losses
# 4   Brad Abbey Neither
# 5 Caleb Aekins Overall
# 6 Caleb Aekins    Wins
# 7 Caleb Aekins  Losses
# 8 Caleb Aekins Neither

How can I move 'wins', 'losses' and 'neither' to the column next to it, while copying the person's name down to the rows below it?

1. Copy the `Player` column to a new column, `Result`. 2. Where `Player == Result`, `Result = "Overall`. — Marius, Nov 15 '18 at 03:09
Please provide example data as plain text (using _e.g._ `dput`), not images, so as users can easily copy/paste it. — neilfws, Nov 15 '18 at 03:12
Welcome to SO Adrian! Please have a read and update your question accordingly: https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example — s_baldur, Nov 15 '18 at 09:28

Artem · Answer 1 · 2018-12-19T06:44:33.103

You could extract players' names from the colum Players using setdiff function. Then create an output table with each player name repeated 4 times as well as cycle known Result outcomes c("Overall","Wins", "Losses", "Neither").

Please see the code below

df <- data.frame(Player = c("Brad Abbey", "Wins", "Losses", "Neither", "Caleb Aekins", "Wins", "Losses", "Neither"),
                 No = c(1, NA, NA, NA))

outcome <- c("Overall","Wins", "Losses", "Neither")
name <- setdiff(unique(df$Player), outcome)
res <- data.frame(Player = unlist(lapply(name, rep, 4)), Result = outcome)
res

Output:

        Player  Result
1   Brad Abbey Overall
2   Brad Abbey    Wins
3   Brad Abbey  Losses
4   Brad Abbey Neither
5 Caleb Aekins Overall
6 Caleb Aekins    Wins
7 Caleb Aekins  Losses
8 Caleb Aekins Neither

Non-structured table to narrow format table transformation?

1 Answers1