Create frequency data frame and transfer columns from old data frame

user_14852823 0
Viewed 43

I am using the map function to create frequency tables from a list of data frames. I would like to import the name column from the original data frame. For example, when I enter df_freq$C I want to see three columns, value, n, and name. For the name column I want all values equal to "C".

# load packages and define variables
rm(list  = ls())
library(purrr)
library(dplyr)

## load data
df_raw <- data.frame(name = c("C", "A", "B", "A", "B", "C"), 
                          start = c(2, 1, 3, 4, 5, 2),  
                          end = c(7, 6, 7, 8, 10, 9)) 

df <- df_raw %>%
  split(.$name) %>% # split data by name
  imap(function(x, x_name) {
    data.frame(value = Map(seq.int, x$start, x$end) %>% unlist,
               name = x_name) })

## create frequency plot with name column
df_freq <- df %>%
map(., ~count(.x, value))```
purrr dplyr dataframe r pipe
2 Answers

It can be done more directly in tidyverse. Create a rowwise attribute, then transmute to return the name and list of sequence from 'start' to 'end' for each row, unnest the list column and do the count

library(dplyr)
library(tidyr)
df_raw %>% 
    rowwise %>% 
    transmute(name, value = list(start:end)) %>%
    unnest(c(value)) %>% 
    count(name, value)

-output

# A tibble: 24 x 3
#   name  value     n
#   <chr> <int> <int>
# 1 A         1     1
# 2 A         2     1
# 3 A         3     1
# 4 A         4     2
# 5 A         5     2
# 6 A         6     2
# 7 A         7     1
# 8 A         8     1
# 9 B         3     1
#10 B         4     1
# … with 14 more rows

Or instead of rowwise, may use map2

library(purrr)
df_raw %>%
    transmute(name, value = map2(start, end, `:`)) %>% 
    unnest(c(value)) %>%
    count(name, value)

In the OP's code, the count needs the name column as well

df %>%
     map(., ~count(.x, name, value))
user_37322710

Here is a data.table option

setDT(df)[, .(value = unlist(Map(seq, start, end)), n = 1), .(name)][, .(n = sum(n)), by = .(name, value)]

which gives

    name value n
 1:    C     2 2
 2:    C     3 2
 3:    C     4 2
 4:    C     5 2
 5:    C     6 2
 6:    C     7 2
 7:    C     8 1
 8:    C     9 1
 9:    A     1 1
10:    A     2 1
11:    A     3 1
12:    A     4 2
13:    A     5 2
14:    A     6 2
15:    A     7 1
16:    A     8 1
17:    B     3 1
18:    B     4 1
19:    B     5 2
20:    B     6 2
21:    B     7 2
22:    B     8 1
23:    B     9 1
24:    B    10 1
    name value n
user_121587570
Related