From count to cases in R

March 25, 2022

I have a dataset with a column which indicate the number of occurence of a group constituted by multiples variables. Here SEXand COLOR.

CASES <- base::data.frame(SEX   = c("M", "M", "F", "F", "F"), 
                          COLOR = c("brown", "blue", "brown", "brown", "brown"))
COUNT <- base::as.data.frame(base::table(CASES))
COUNT

I need to change the structure of the dataset, so I have one row for each occurence of the group. Someone helped me to create a function which works perfectly.

countsToCases <- function(x, countcol = "Freq") {
    # Get the row indices to pull from x
    idx <- rep.int(seq_len(nrow(x)), x[[countcol]])
    # Drop count column
    x[[countcol]] <- NULL
    # Get the rows from x
    x[idx, ]
}

CASES <- countsToCases(base::as.data.frame(COUNT))
CASES

The problem is now that I have a HUGE dataset (the babyname dataset from tidytuesday), and this is not working since it’s too slow.

db_babynames <- data.table::as.data.table(tuesdata$babyname)

db_babynames <- db_babynames[
  j = characters_n := stringr::str_count(string  = name,
                                         pattern = ".")
][
  j = c("year", "characters_n", "n")
]

I’m looking for a faster solution, working with the data.table package if possible.

>Solution :

If an uncounted version is needed I would use tidyr::uncount(), but consider the recommendation in this post to work with your original data

library(dplyr)
library(tidyr)

CASES <- base::data.frame(
  SEX   = c("M", "M", "F", "F", "F"),
  COLOR = c("brown", "blue", "brown", "brown", "brown")
  )

COUNT <- count(CASES, SEX, COLOR, name = 'Freq')

tidyr::uncount(base::as.data.frame(COUNT), Freq)
#>   SEX COLOR
#> 1   F brown
#> 2   F brown
#> 3   F brown
#> 4   M  blue
#> 5   M brown

^{Created on 2022-03-25 by the reprex package (v2.0.1)}