​

https://preview.redd.it/q2g7zh2vvb8b1.png?width=1347&format=png&auto=webp&s=2fac1d266c07f0e343ab7ac35ae7d7c82333b43f

Hi All, Im currently cleaning up some rather dirty blood culture data ( pun intended) and its veering away from my comfort zone in bioinformatics into basic data manipulation and im starting to struggle, as im not entirely sure the best way to approach this.

As you can see in the above df, I have a row with a string of data - this is the highly cleaned up version( the S,R or I before the : indicates sensitive, resistant or intermediate, and the letters afterwards stand for the antibiotic).

What I want, is to extract those S,R,I data for each abx in a row and where relevant paste that information into a corresponding col to facilitate analysis. I have created new cols with the names of each antibiotic.

My pseudocode is some sort of loop:

for each row in df, split the string in the df$concat by ","
then split each new item(?) by ":"
for each second element in these new pairs, find the matching colname in df and paste the first element into the corresponding row
then move onto the next element and repeat 

These seems very clunky and will be hard to write I think, so Im fairly sure Im missing some basic tidyverse solution - whats the best approach here?