Duplicating rows n times, where n is a value of a string

user_9794399 0
Viewed 38

I have a dataset where I list states with their respective cities, some of these places have been aggregated (not by me) and are classified as "Other ([count of places])" (e.g. Other (99)). Appended to this list of places are numeric 'count' values. I'd like to 1.) find the average count per place and 2.) duplicate these 'Other...' places along with the average according to the number within the parenthesis. Example below:

set.seed(5)
df <- data.frame(state = c('A','B'), city = c('Other (3)','Other (2)'), count = c('250','50'))

Output:

state city count
A Other (3) 83.333
A Other (3) 83.333
A Other (3) 83.333
B Other (2) 25.000
B Other (2) 25.000

So far I've only been able to figure out how to pull the numbers from the parenthesis and create an average:

average = df$count/as.numeric(gsub(".*\\((.*)\\).*", "\\1", df$city))
repeat r
2 Answers

An option with uncount. Extract the numeric part in 'city' with parse_number, divide the 'count' by 'n' and replicate the rows with uncount

library(dplyr)
library(tidyr)
df %>%
    mutate(n = readr::parse_number(city), count = as.numeric(count)/n) %>%
    uncount(n)

-output

state      city    count
1     A Other (3) 83.33333
2     A Other (3) 83.33333
3     A Other (3) 83.33333
4     B Other (2) 25.00000
5     B Other (2) 25.00000
user_37322710

You could extend your example with the followign code:

set.seed(5)
df <- data.frame(state = c('A','B'), city = c('Other (3)','Other (2)'), count = c('250','50'))
times <- as.numeric(gsub(".*\\((.*)\\).*", "\\1", df$city))
df$count <- as.numeric(df$count)/times
output <- df[rep(seq_along(times),times),]

The key addition is the line creating output, which uses row indexing on the input dataframe to repeat each row as required.

user_33796750
Related