CODEWITHSUNDEEP
r
coderX
coderX

Reputation: 474

Creating new columns from information contained in other row/column combinations

I've got a data frame with match up information (Team, Opponent), as well as the betting spread of that game in different sportsbooks. I've got one row for each of the Teams, so there will be two rows for each game. As an example, see the below data frame:

example <- data.frame(Team = c("Tennessee","Vanderbilt"),
                       Opponent = c("Vanderbilt","Tennessee"),
                       PointsBet = c(-13, 13),
                       DraftKings = c(-12.5, 12.5))

        Team   Opponent PointsBet DraftKings
1  Tennessee Vanderbilt       -13      -12.5
2 Vanderbilt  Tennessee        13       12.5

What I'm trying to do is create "Opponent_PointsBet" and "Opponent_DraftKings" columns. So for each row, not only do we have the Team's betting spread, but we also have the betting spread of the Opponent. In a small example like this, it's easy to manually do this, but my actual data set has hundreds of rows and about 25 other columns, each of which I'd like to copy.

Is it possible to take one row of data for a specific "Team", and apply those columns as new columns in the row of data where that team is being identified as the "Opponent"? My output would look like this:

        Team   Opponent PointsBet DraftKings Opp_PointsBet Opp_DraftKings
1  Tennessee Vanderbilt       -13      -12.5            13           12.5
2 Vanderbilt  Tennessee        13       12.5           -13          -12.5

Also one thing to note, the columns I'd like to duplicate aren't always going to be opposites, so I can't simply just multiply the value by -1 to get the Opp_ column.

Upvotes: 1

Views: 36

Answers (1)

akrun
akrun

Reputation: 886938

We can create the two columns in base R. Create a position index to match the 'Team' with 'Opponent' and use that to rearrange the column values in 'PointsBet' and 'DraftKings' to create new columns

nm1 <- names(example)[3:4]
i1 <-  with(example,match(Team, Opponent))
example[paste0("Opp_", nm1)] <- lapply(example[nm1], function(x) x[i1])
example
#       Team   Opponent PointsBet DraftKings Opp_PointsBet Opp_DraftKings
#1  Tennessee Vanderbilt       -13      -12.5            13           12.5
#2 Vanderbilt  Tennessee        13       12.5           -13          -12.5

Upvotes: 1

Related Questions