How to reshape long format data and count the values-CodePudding

I think this is a basic question, but it seems to be frustrating me...

I am using R and have data in long format; a data.frame with each value a string. I want to produce a summary table of the counts of each value. So for the data:

Location	Colour
North	red
North	blue
North	red
South	red
South	red
North	red
South	blue
North	blue
South	red
South	red

I would like to produce the summary table:

Location	red	blue
North	3	2
South	4	1

I've tried numerous attempts of reshape and cast. I'm drawing a blank as there are no numeric 'values' in the table.

CodePudding user response：

Also using table():

df |>
  with(table(Location, Colour)) |>
  rbind() |>
  as_tibble(rownames = "Location")

  Location  blue   red
  <chr>    <int> <int>
1 North        2     3
2 South        1     4

Reproducible data

df = data.frame(
  Location = c("North", "North", "North", "South", "South", "North", "South", "North", "South", "South"), 
  Colour = c("red", "blue", "red", "red", "red", "red", "blue", "blue", "red", "red" )
)

CodePudding user response：

With tidyverse, dplyr::count() first, then pivot:

library(dplyr)
library(tidyr)

dat %>% 
  count(Location, Colour) %>% 
  pivot_wider(names_from = Colour, values_from = n)
# # A tibble: 2 × 3
#   Location  blue   red
#   <chr>    <int> <int>
# 1 North        2     3
# 2 South        1     4

In base R, you can use table():

table(Location = dat$Location, dat$Colour)
# Location blue red
#    North    2   3
#    South    1   4

Note, however, that the output of table() isn't a data.frame, so may not work for you depending on what your next step is.

CodePudding user response：

Using tabyl from janitor

library(janitor)
tabyl(df1, Location, Colour)
 Location blue red
    North    2   3
    South    1   4