Adding new Rows Using Existing Column Names and Data

[caveman23]

I have the below dataset

                Team     ca     ub     bo     fb     88     dk     fd     sh     b3
       Detroit Lions +20000 +20000 +25000 +12500 +20000 +20000 +18000 +20000 +17500
     Las Vegas Raiders  +8000  +9000  +8000  +6600  +9000 +10000  +7500  +9000  +6000
  Tampa Bay Buccaneers   +650   +650   +700   +650   +650   +600   +650   +650   +650
   San Francisco 49ers  +1400  +1400  +1400  +1400  +1400  +1400  +1400  +1400  +1200

I want to transform it so each row has the team name, the column header (e.g., ca) and the Odds (e.g., +20000). Ideally it would look like so:

            Team            Type   Odds 
       Detroit Lions         ca   +20000
       Detroit Lions         ub   +20000
       Detroit Lions         bo   +25000

[nirgrahamuk]

you would use tidyr::pivot_longer() to achieve this.
however, I can't demo on your data, as you haven't shared it in a forum friendly way.
If you would like that additional support please see this guide, to see how to create one:

FAQ: How to do a minimal reproducible example ( reprex ) for beginners

A minimal reproducible example consists of the following items: A minimal dataset, necessary to reproduce the issue The minimal runnable code necessary to reproduce the issue, which can be run on the given dataset, and including the necessary information on the used packages. Let's quickly go over each one of these with examples: Minimal Dataset (Sample Data) You need to provide a data frame that is small enough to be (reasonably) pasted on a post, but big enough to reproduce your issue. Let's say, as an example, that you are working with the iris data frame head(iris) #> Sepal.Length Sepal.Width Petal.Length Petal.Width Species #> 1 5.1 3.5 1.4 0.…

[caveman23]

Thanks so much, sorry for not sharing it in a forum friendly way! Very new to this.

Below is the data set,

structure(list(Team = c("Detroit Lions", "Las Vegas Raiders", 
"Tampa Bay Buccaneers", "San Francisco 49ers", "Cincinnati Bengals"
), ca = c("+20000", "+8000", "+650", "+1400", "+12500"), ub = c("+20000", 
"+9000", "+650", "+1400", "+15000"), bo = c("+25000", "+8000", 
"+700", "+1400", "+15000")), row.names = 2:6, class = "data.frame")

[EconProf]

Good job on dput( )!

If you want the odds in numeric format instead of character, then delete the + signs in the file you import. It is OK to have - signs in front of negative numbers.

The –Team in pivot_longer is to select all of the columns except Team to pivot. The names of those columns become entries in the new Type column and values in those columns are in the new Odds column.

library(tidyverse)

data <- structure(list(Team = c("Detroit Lions", "Las Vegas Raiders", 
"Tampa Bay Buccaneers", "San Francisco 49ers", "Cincinnati Bengals"
), ca = c("+20000", "+8000", "+650", "+1400", "+12500"), ub = c("+20000", 
"+9000", "+650", "+1400", "+15000"), bo = c("+25000", "+8000", 
"+700", "+1400", "+15000")), row.names = 2:6, class = "data.frame")

pivot_longer(data, -Team, names_to = "Type", values_to = "Odds")
#> # A tibble: 15 × 3
#>    Team                 Type  Odds  
#>    <chr>                <chr> <chr> 
#>  1 Detroit Lions        ca    +20000
#>  2 Detroit Lions        ub    +20000
#>  3 Detroit Lions        bo    +25000
#>  4 Las Vegas Raiders    ca    +8000 
#>  5 Las Vegas Raiders    ub    +9000 
#>  6 Las Vegas Raiders    bo    +8000 
#>  7 Tampa Bay Buccaneers ca    +650  
#>  8 Tampa Bay Buccaneers ub    +650  
#>  9 Tampa Bay Buccaneers bo    +700  
#> 10 San Francisco 49ers  ca    +1400 
#> 11 San Francisco 49ers  ub    +1400 
#> 12 San Francisco 49ers  bo    +1400 
#> 13 Cincinnati Bengals   ca    +12500
#> 14 Cincinnati Bengals   ub    +15000
#> 15 Cincinnati Bengals   bo    +15000

^{Created on 2021-08-31 by the reprex package (v2.0.1)}

[caveman23]

This is exactly what I was looking for. Thank you so much! You have no idea how helpful this was!