I've got a dataset with groups and a bunch of yes/no value (0=no, 1=yes). I'm trying to create a report with the totals per group, and nice layout, using just R-script.
Here's what I've got so far.
# test data (real data from API service is larger)
df_visits <- read.table(text = "
PATIENT_ID,RAND,UNIT_GT3,HEMOR_YN
1000,ARM1,1,0
1001,ARM2,0,0
1003,ARM1,1,0
1005,ARM1,1,0
1006,ARM2,1,1
1008,ARM2,0,0
1009,ARM1,1,1
1010,ARM2,1,0
1011,ARM1,1,0
1014,ARM2,0,0
1015,ARM2,1,0
1018,ARM1,1,1
", header = TRUE, sep = ",")
# results per group, using 'ifelse' feels clunky but it works
df_per_group <- data.frame(
cbind(
RAND = tapply(df_visits$RAND, list(df_visits$RAND), max, na.rm=TRUE),
UNIT_GT3_Y = tapply(ifelse(df_visits$UNIT_GT3 == 1, 1, 0), list(df_visits$RAND), sum, na.rm=TRUE),
UNIT_GT3_N = tapply(ifelse(df_visits$UNIT_GT3 == 0, 1, 0), list(df_visits$RAND), sum, na.rm=TRUE),
HEMOR_YN_Y = tapply(ifelse(df_visits$HEMOR_YN == 1, 1, 0), list(df_visits$RAND), sum, na.rm=TRUE),
HEMOR_YN_N = tapply(ifelse(df_visits$HEMOR_YN == 0, 1, 0), list(df_visits$RAND), sum, na.rm=TRUE)
)
)
# t{base} = Matrix Transpose
df_pivot <- as.data.frame(t(df_per_group))
The resulting df_pivot dataframe looks like this and I can export it using write.csv():
# RND ARM1 ARM2
# ------------+-----+-----+
# UNIT_GT3_Y | 6 | 3 |
# UNIT_GT3_N | 0 | 3 |
# HEMOR_YN_Y | 2 | 1 |
# HEMOR_YN_N | 4 | 5 |
# ------------+-----+-----+
However, I would like the goal dataset to also have total percentages per question, and have nicer question labels, so something like this:
# RND ARM1 ARM2
# --------------+-------------+------------+
# Units>3 : Yes | 6 (100%) | 3 (50%) |
# No | 0 (0%) | 3 (50%) |
# --------------+-------------+------------+
# Hemmorage Yes | 2 (33.3%) | 1 (16.7%) |
# No | 4 (66.7%) | 5 (83.3%) |
# --------------+-------------+------------+
Is there a way change the labels and to add the percentages per question like that?
Also, I'm new to R-script so is there maybe a more efficient way to get the desired result?
You could use the tables package which allows you to generate the summary table directly out of the initial data :
library(tables)
tables::tabular(((`Units>3` = factor(ifelse(UNIT_GT3==0,'No','Yes'),levels=c('Yes','No'))) +
(Hemmorage = factor(ifelse(HEMOR_YN==0,'No','Yes'),levels=c('Yes','No'))))
~(RND = factor(RAND))* ((n=1) + Percent(Equal(factor(RAND))))
,data = df_visits)
RND
ARM1 ARM2
n Percent n Percent
Units>3 Yes 6 100.00 3 50.00
No 0 0.00 3 50.00
Hemmorage Yes 2 33.33 1 16.67
No 4 66.67 5 83.33
EDIT : following your question in comments :
tables::tabular(((`Units>3` = factor(ifelse(UNIT_GT3==0,'No','Yes'),levels=c('Yes','No'))) +
(Hemmorage = factor(ifelse(HEMOR_YN==0,'No','Yes'),levels=c('Yes','No'))))
~(Total = 1)+(RND = factor(RAND))* ((Nb=1) + Percent(Equal(factor(RAND))))
,data = df_visits)
RND
ARM1 ARM2
Total Nb Percent Nb Percent
Units>3 Yes 9 6 100.00 3 50.00
No 3 0 0.00 3 50.00
Hemmorage Yes 3 2 33.33 1 16.67
No 9 4 66.67 5 83.33