I have a dataset that has columns with semi-colon separated values that represent countries like this:
row countries weights
1: 22;3 1.254
2: 5 0.54
3: 6;8;123 2.65
4: 16 0.35
5: 77;21;1 0.98
6: 89 1.74
etc.
with data.tables, I can sum per unique values like this:
dt[!is.na(countries),.(sum(weights)), by= countries]
This gives me this:
countries V1
1: 2 791.243
2: 230 10.644
3: 50 4.517
4: 1 544.056
5: 1;75;77;91 0.370
The problem is that the semi-colon separated values are not splitted to their unique values. What I want is a sum per unique value in the column so that the result contains no more semi-colon separated values.
How can I split the column up and then build the sum per unique value?
