Multiple entries single cell

Question

I have a CSV file with the following structure:

Age    Sex   Ailments
73      F    Diabetes; Mobility; Hypertension
70      M    Hypertension; Memory problems; 
68      M    Hypertension; Diabetes

The 'Ailments' column has multiple elements delimited by a ';'.

The multiple entries in 'Ailments' have two options. Either they are a flat list, in which case all the entries have equal priority; or they are a priority list, in which I'd like multiple tables as below for Primary, Secondary, Tertiary etc entries.

 Ailment        Male      Female
 Diabetes       65.4%     71.45%
 Hypertension   75.23%    68.23%
 Memory         19.23%    24.34%

Any help is much appreciated.

s1b.

Answer 1

We can do this by

library(splitstackshape)
dcast(cSplit(df1, "Ailments", ";", "long"), Ailments~Sex, value.var="Age",
      sum)[, (2:3) := lapply(.SD, function(x) round(100*x/sum(x), 2)), .SDcols = 2:3][]