R: splitting columns in two but keep the rest

Question

Hi I have this table here:

variable	level	vaccinated = 0 (n=16455)	vaccinated = 1 (n=1297	Total (n=17752)	p-value
Sex	M	8,586 (52.2)	714(55.1)	9,3023 (12,1)	1,22
	F	1,2323(12,1)	1,2323(12,1)	9,3023 (12,1)	1,223
Agegroup	0-15	12,313(9,1)	1,2323(12,1)	9,3023 (12,1)	12,23
	16-20	12,313(9,1)	1,2323(12,1)	9,3023 (12,1)	12.34
	21-25	12,313(9,1)	1,2323(12,1)	9,3023 (12,1)	12.33

I have a lot more categories under variable - but i just showed a bi ofthe table with artificial data.

I wish to split the columns vaccinated = 0 (n=16455), vaccinated = 1 (n=1297) and Total (n=17752) into two columns so the frequency and percentages in these variables are seperated in their own column - should be like this:

variable	level	vaccinated = 0 (n=16455)	pct. = 0	vaccinated = 1 (n=1297	pct. = 1	Total (n=17752)	p-value
Sex	M	8,586	52.2	714	55.1	9,3023 (12,1)	1,22
	F	1,2323	12,1	1,2323	12,1	9,3023 (12,1)	1,223
Agegroup	0-15	12,313	9,1	1,2323	12,1	9,3023 (12,1)	12,23
	16-20	12,313	9,1	1,2323	12,1	9,3023 (12,1)	12.34
	21-25	12,313	9,1	1,2323	12,1	9,3023 (12,1)	12.33

And then the same for the Total (n=17752)...

how can I do so?

r2evans · Answer 1 · 2021-11-08T15:27:34.390

Base R:

tmp <- do.call(cbind.data.frame,
  unname(Map(function(x,nm) strcapture("(.*?)\\s?\\((.*?)\\)", x,
                                       proto = setNames(rep("", 2), paste0(nm, c("","_pct")))),
             dat[,3:5],
             c("vacc0", "vacc1", "Total"))))
tmp
#    vacc0 vacc0_pct  vacc1 vacc1_pct  Total Total_pct
# 1  8,586      52.2    714      55.1 9,3023      12,1
# 2 1,2323      12,1 1,2323      12,1 9,3023      12,1
# 3 12,313       9,1 1,2323      12,1 9,3023      12,1
# 4 12,313       9,1 1,2323      12,1 9,3023      12,1
# 5 12,313       9,1 1,2323      12,1 9,3023      12,1

With this, we can bind it back into the original data:

cbind(dat[,1:2], tmp, dat[,6,drop=FALSE])
#   variable level  vacc0 vacc0_pct  vacc1 vacc1_pct  Total Total_pct p-value
# 1      Sex     M  8,586      52.2    714      55.1 9,3023      12,1    1,22
# 2              F 1,2323      12,1 1,2323      12,1 9,3023      12,1   1,223
# 3 Agegroup  0-15 12,313       9,1 1,2323      12,1 9,3023      12,1   12,23
# 4          16-20 12,313       9,1 1,2323      12,1 9,3023      12,1   12.34
# 5          21-25 12,313       9,1 1,2323      12,1 9,3023      12,1   12.33

R: splitting columns in two but keep the rest

1 Answers1