rfrequency-table

Using R and Table function can i use 4 specific columns out of a larger dataset to create a two by two frequency table


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))

Solution

  • 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
    

    data

    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))