How to transpose this dataframe to long format ?

[Andrzej]

Hi,
How to transpose this dataframe to long format, please ?

dt <- readr::read_fwf("
Listen_Podcast     Transport               City_Bikini_Bottom  City_Frostbite_Falls  City_New_York
Yes                Drive                       13                     17                     5
Yes                Public                      27                     25                    27
No                 Drive                       23                     22                    17
No                 Public                      44                     31                    22
")

dt %<>%
  janitor::row_to_names(row_number = 1)

My desired result would be like in here below "Long-format with counts":
https://rcompanion.org/handbook/H_06.html

I just have tried with pivot_longer, but tricky part is that, there is names_sep which I set to "_", but it does not give me what I want. Probably it could be done with names_pattern I suppose. I am still trying, please help.

[technocrat]

dt <- readr::read_fwf("
Listen_Podcast     Transport               City_Bikini_Bottom  City_Frostbite_Falls  City_New_York
Yes                Drive                       13                     17                     5
Yes                Public                      27                     25                    27
No                 Drive                       23                     22                    17
No                 Public                      44                     31                    22
")
#> Rows: 5 Columns: 5
#> ── Column specification ────────────────────────────────────────────────────────
#> 
#> chr (5): X1, X2, X3, X4, X5
#> 
#> ℹ Use `spec()` to retrieve the full column specification for this data.
#> ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.
colnames(dt) <- dt[1,]
dt <- dt[-1,]
tidyr::pivot_longer(dt,cols = starts_with("City"))[c(3,1,2,4)]
#> # A tibble: 12 × 4
#>    name                 Listen_Podcast Transport value
#>    <chr>                <chr>          <chr>     <chr>
#>  1 City_Bikini_Bottom   Yes            Drive     13   
#>  2 City_Frostbite_Falls Yes            Drive     17   
#>  3 City_New_York        Yes            Drive     5    
#>  4 City_Bikini_Bottom   Yes            Public    27   
#>  5 City_Frostbite_Falls Yes            Public    25   
#>  6 City_New_York        Yes            Public    27   
#>  7 City_Bikini_Bottom   No             Drive     23   
#>  8 City_Frostbite_Falls No             Drive     22   
#>  9 City_New_York        No             Drive     17   
#> 10 City_Bikini_Bottom   No             Public    44   
#> 11 City_Frostbite_Falls No             Public    31   
#> 12 City_New_York        No             Public    22

^{Created on 2023-01-22 with reprex v2.0.2}

[Andrzej]

Thank you,
In the meantime I have tried the following code, but of course solution is yours:

library(readr)
library(tidyverse)
library(janitor)
library(magrittr)


dt <- readr::read_fwf("
Listen_Podcast     Transport               City_Bikini_Bottom  City_Frostbite_Falls  City_NewNew_York
Yes                Drive                       13                     17                     5
Yes                Public                      27                     25                    27
No                 Drive                       23                     22                    17
No                 Public                      44                     31                    22
")

dt %<>%
  janitor::row_to_names(row_number = 1)

dt %>%
  pivot_longer(cols = starts_with("City"), names_to = "City", names_prefix = "City_", values_to = "Count")
 %>% relocate(City) %>% arrange(City)
#> # A tibble: 12 × 4
#>    City            Listen_Podcast Transport Count
#>    <chr>           <chr>          <chr>     <chr>
#>  1 Bikini_Bottom   Yes            Drive     13   
#>  2 Bikini_Bottom   Yes            Public    27   
#>  3 Bikini_Bottom   No             Drive     23   
#>  4 Bikini_Bottom   No             Public    44   
#>  5 Frostbite_Falls Yes            Drive     17   
#>  6 Frostbite_Falls Yes            Public    25   
#>  7 Frostbite_Falls No             Drive     22   
#>  8 Frostbite_Falls No             Public    31   
#>  9 NewNew_York     Yes            Drive     5    
#> 10 NewNew_York     Yes            Public    27   
#> 11 NewNew_York     No             Drive     17   
#> 12 NewNew_York     No             Public    22

^{Created on 2023-01-22 with reprex v2.0.2}

Just for the learning purposes could you please help me with going back to original wide dt dataframe ?
I will try my luck with piwot_wider.

[technocrat]

start a new tread "How to restore a draft frame that has been pivoted longer, please.

@Andrzej

dt <- readr::read_fwf("
Listen_Podcast     Transport               City_Bikini_Bottom  City_Frostbite_Falls  City_NewNew_York
Yes                Drive                       13                     17                     5
Yes                Public                      27                     25                    27
No                 Drive                       23                     22                    17
No                 Public                      44                     31                    22
")

dt %<>%
  janitor::row_to_names(row_number = 1)

dt %>%
  pivot_longer(cols = starts_with("City"), names_to = "City", names_prefix = "City_", values_to = "Count")
 %>% relocate(City) %>% arrange(City)