ID start_date new_end_Date drug drug_1 drug_2 Need
x1 36526 36708 t t 1
x1 39448 40086 l l 2
x1 40087 40421 a,l a l 3
x1 40422 41890 a a 3
x1 41891 42453 l l 3
x1 42454 43698 c,l c l 4
x1 43699 44026 l l 4
x1 44027 44304 l,r l r 5
x1 44305 44474 r r 5
Hello Guys,
I need to create a column if there is true change of drug(Column 'need'). Drug_1 and drug_2 is nothing but 'drug' separated into different columns.
What is needed (NEED Column above):
1) first row - the drug is t and first drug so column need should have value of 1
2) Second row - the drug is l (so drug changed from t to l) - column value should be 2
3) third row - the drug is a,l (even though drug l from second row exists here but new drug 'a' is added) - column value should be 3
4) Fourth row - drug is 'a' (previous row drug 'a' exists is here, not a true change of drug) - column value should be still 3.
5) Fifth row - drug is l (fourth row drug 'a' doesn't exists is here, but third row have a,l (a,l changed to a and then to l so continued) - not a true change of drug) - column value should be still 3.
6) sixth row - drug is c,l (3,4,5 rows have a,l & a & l - but c is added) - column value should be 4.
7) seventh row - drug is l (6 row had c,l and this row had l so no change) - column value should be 4.
😎 eighth row - drug is l,r (6 row had c,l and 7row had l - but r is added here ) - column value should be 5
9) ninth row - drug is r (8 row had l,r and this row had r so no change ) - column value should be 5
I know this is tricky, sorry if my question is not clear. Appreciate any help. Thank you All.
