2

I have a database with columns Age, year, Birth Order 1, Birth Order 2, Parity 0 and Parity 1'. I have a birth order column for each order that goes until 8, and Parities columns until 7. I need to divide Birth Order 1/Parity 0; Birth Order 2/Parity 1 ... and more to estimate my rates.

This is my database, just a sample.

year <- c(1998, 1999, 2000, 2010)  
Age <- c(15, 16, 17, 18) 
'Birth Order 1' <- c(10, 25, 25, 35)
'Parity 0' <- c(100, 150, 140, 150)
'Birth Order 2' <- c(5, 10, 10, 30)
'Parity 1' <- c(110, 160, 150, 150)

mat <- data.frame(year, Age, `Birth Order 1`, `Birth Order 2`, `Parity 0`, `Parity 1`)

I've done this using a simple code, but I want to use a command to optimize my script.

mat <- mat %>% 
  mutate(mat1 = `Birth Order 1`/`Parity 0`,
         mat2 = `Birth Order 2`/`Parity 1`,
         mat3 = `Birth Order 3`/`Parity 2`,
         mat4 = `Birth Order 4`/`Parity 3`,
         mat5 = `Birth Order 5`/`Parity 4`,
         mat6 = `Birth Order 6`/`Parity 5`) %>% 
  select("AGE", "year", starts_with("mat"))

My expected results are:

year    Age mat1              mat2
1998    15  0.1         0.04545455
1999    16  0.1666667   0.0625
2000    17  0.1785714   0.06666667
2010    18  0.2333333   0.2

3 Answers 3

3

A fairly simple solution:


year <- c(1998, 1999, 2000, 2010)  
Age <- c(15, 16, 17, 18) 
'Birth Order 1' <- c(10, 25, 25, 35)
'Parity 0' <- c(100, 150, 140, 150)
'Birth Order 2' <- c(5, 10, 10, 30)
'Parity 1' <- c(110, 160, 150, 150)

mat <- data.frame(year, Age, `Birth Order 1`, `Birth Order 2`, `Parity 0`, `Parity 1`)

for(i in 1:2){
    mat[[paste0("mat",i)]] <- mat[[paste0("Birth.Order.",i)]]/mat[[paste0("Parity.",i-1)]]
}

mat

#  year Age Birth.Order.1 Birth.Order.2 Parity.0 Parity.1      mat1       mat2
#1 1998  15            10             5      100      110 0.1000000 0.04545455
#2 1999  16            25            10      150      160 0.1666667 0.06250000
#3 2000  17            25            10      140      150 0.1785714 0.06666667
#4 2010  18            35            30      150      150 0.2333333 0.20000000

Sign up to request clarification or add additional context in comments.

Comments

1

You can use dplyr, combining arrange() and group_by() to then compute the lags of birth order / parity.

# toy data with 8 levels for birth order and parity
df <- data.frame(age=(15:18),year=c(1999:2002),
                 variable=c(
                   rep(paste0('birth.order',seq(1:8)),each=4),
                   rep(paste0('parity',seq(from=0,to=7,by=1)),each=4)),
                 value=c(
                   sample(10:40,32,T),
                   sample(100:150,32,T)))

# change to wide format, as in example
df <- df %>% spread(variable,value)

# gather, arrange, and compute lags, then output desired numbers
df %>% gather(k,v,-age,-year) %>% mutate(num=sub('.*([0-9])','\\1',k)) %>% 
  arrange(age,year,num,k) %>% group_by(age,year) %>% 
  mutate(mat=v/lag(v)) %>% filter(grepl('birth',k)) %>% 
  select(-v,-num)

# A tibble: 32 x 4
# Groups:   age, year [4]
     age  year k              mat
   <int> <int> <chr>        <dbl>
 1    15  1999 birth.order1 0.306
 2    15  1999 birth.order2 0.194
 3    15  1999 birth.order3 0.310
 4    15  1999 birth.order4 0.159
 5    15  1999 birth.order5 0.116
 6    15  1999 birth.order6 0.193
 7    15  1999 birth.order7 0.221
 8    15  1999 birth.order8 0.110
 9    16  2000 birth.order1 0.305
10    16  2000 birth.order2 0.264
# ... with 22 more rows

Comments

1

You can divide it directly in base R after identifying the columns

birth_cols <- grep("^Birth", names(mat))
parity_cols <- grep("^Parity", names(mat))
cbind(mat[1:2], mat[birth_cols]/mat[parity_cols])

#  year Age Birth.Order.1 Birth.Order.2
#1 1998  15     0.1000000    0.04545455
#2 1999  16     0.1666667    0.06250000
#3 2000  17     0.1785714    0.06666667
#4 2010  18     0.2333333    0.20000000

which is same as doing in dplyr as

library(dplyr)
mat %>%  select(starts_with("Birth")) / mat %>%  select(starts_with("Parity"))

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.