I have a few columns
Location|Yes M & M Peanuts| No M& M Peanuts | Yes M & M Almond| No M& M Almond|Location
5 10 20 6 NYC
I would like to do using the table function or something more convenient where i turn those columns into
Yes | No
M & M Peanuts 5 10
M & M Almond 20 6
updated example
df2 <- structure(list(`Yes M & M Peanuts` = 5L, `No M & M Peanuts` = 10L,
`Yes M & M Almond` = 20L, `No M & M Almond` = 6L, "Location" = "NYC"), class = "data.frame",
row.names = c(NA,
-1L))
This can be done easily with pivot_longer
, specify the names_pattern
to extract the value (.value
) part to go into columns 'Yes', 'No' and another column 'grp' that extracts the suffix part of the column name. Then, the 'grp' column can be converted to row names with column_to_rownames
library(dplyr)
library(tidyr)
library(tibble)
df1 %>%
pivot_longer(cols = everything(), names_to = c(".value", "grp"),
names_pattern = "(Yes|No)\\s*(.*)") %>%
column_to_rownames('grp')
-output
# Yes No
#M & M Peanuts 5 10
#M & M Almond 20 6
using the OP's second dataset in the updated post, we need to specify the cols
without the 'Location'
df2 %>%
pivot_longer(cols = -Location, names_to = c(".value", "grp"),
names_pattern = "(Yes|No)\\s*(.*)") %>%
column_to_rownames('grp')
# Location Yes No
#M & M Peanuts NYC 5 10
#M & M Almond NYC 20 6
df1 <- structure(list(`Yes M & M Peanuts` = 5L, `No M & M Peanuts` = 10L,
`Yes M & M Almond` = 20L, `No M & M Almond` = 6L), class = "data.frame",
row.names = c(NA,
-1L))