Pivot wider to account for duplicates

Question

My dataset associates a single application number with a range of different applicants. The state in which each of these applicants lives is listed in a column.

I want to transform my dataset so that:

• the application number column only features distinct values,
• each state has its own column
• the number of applicants linked to the application is counted in their respective state column

e.g.

appli.number	State
600000	AK
600000	AK
600000	AL
600000	AL
600000	AL
600001	AK
600002	MA
600002	MA
600003	AL

application number	AK	AL	MA
600000	2	3	0
600001	1	0	0
600002	0	0	2
600003	0	1	0

I thought it should use pivot_wider and count functions but I am relatively new to R and still haven't yet got my head around lots of stuff.

Any help or pointers would be greatly appreciated thanks :)

Answer 1

How about this:

  data <- tribble(
  ~appli.number,    ~State,
  600000,   "AK",
  600000,   "AK",
  600000,   "AL",
  600000,   "AL",
  600000,   "AL",
  600001,   "AK",
  600002,   "MA",
  600002,   "MA",
  600003,   "AL"
)


table(data)

or if you want to use pivot_wider()

library(tidyverse)    

data %>%
  group_by(appli.number, State)  %>%
  count()  %>%
  pivot_wider(names_from = "State", values_from = "n", values_fill = 0)