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

Question

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

[gather](https://tidyr.tidyverse.org/reference/gather.html) %>% [separate](https://tidyr.tidyverse.org/reference/separate.html) %>% [spread](https://tidyr.tidyverse.org/reference/spread.html) — missuse, Mar 18 '21 at 19:31

akrun · Accepted Answer · 2021-03-18T20:00:48.703

2

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

edited Mar 18 '21 at 20:00

answered Mar 18 '21 at 19:34

akrun

874,273
37
540
662

sample %>% pivot_longer(cols = c(`Yes M & M Peanuts`,`No M& M Peanuts`, `Yes M & M Almond`,`No M& M Almond`), names_to = c(".value", "grp"), names_pattern = "(Yes|No)\\s*(.*)") %>% column_to_rownames('grp') – user35131 Mar 18 '21 at 19:43
doing it like that i got this error Error in get(nm, envir = fn, mode = "function") : object 'column_to_rownames' of mode 'function' was not found – user35131 Mar 18 '21 at 19:43
@user35131 it is from `tibble`. I used `packageVersion('tibble')# [1] ‘3.0.3’` – akrun Mar 18 '21 at 19:44
Error: Can't subset elements that don't exist. x Locations 1, 2, 3, and 4 don't exist. i There are only 0 elements. – user35131 Mar 18 '21 at 19:44
@user35131 Are you getting the same error from the data in my post. I was just copy/pasting your data to create that data – akrun Mar 18 '21 at 19:45
1

ok nevermind. My yes was lowercase. Thank you. it worked. – user35131 Mar 18 '21 at 19:45
I added a sample of the dataset. I didn't foresee this being an issue, ,but in the table i get the other columns from the dataset. It's not limited to the 2 by 2. – user35131 Mar 18 '21 at 19:48
@user35131 Sorry, I didn't see any update in your post – akrun Mar 18 '21 at 19:49
I made an update adding an extra column in the dataset that i would not like in the final frequency table – user35131 Mar 18 '21 at 19:51
@user35131 can you use `dput` to show that example so that it becomes easier for me to test it. – akrun Mar 18 '21 at 19:53
df1 <- 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)) – user35131 Mar 18 '21 at 19:56
@user35131 can you update in your post as some quotes are missing in comments – akrun Mar 18 '21 at 19:57
1

Was that how you wanted? – user35131 Mar 18 '21 at 20:00

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

1 Answers1

data