How to assign binary value for each id with conditions

user_12088756 0
Viewed 89

I want to see if each member is addicted to a type of drug or not, 1 for addicted,0 else.

the data below are prescription records for each member. Each member had a prescription record for this drug on day 0. supply is the number of days supplied by the count of this drug.

id supply days
1   30   -200
1   30   0
1   100  183
1   80   250
2   5    0
2   5    10
3   5    0
3   30   100
3   30   150
3   30   200
3   30   280
3   50   310

For the logic of assign 1 or 0:

If a member not having drug on hand in the preceding 90-day period base on supply and days, it becomes "naive status". This member could return to naive status if not taking drug for 90-day.

And if a member has more than 162 days of drug supplied on hand within 3 months after 'naive status', he will be at highly risk of being addictive. so we assign value 1 for this member.

The desire outcome should be

id      y
1       1
2       0
3       0

What I have tried is

#Create Z,Z is the number of each naive status.
z=[]
y=0
z.append(y)
for i in range(len(abc)-1):
 if ((df.days[i+1]-df.days[i])>90)&((df.id[i+1]-df.id[i])==0):
  y=y+1
  z.append(y)
 else:
    if (df.id[i+1]-df.id[i])!=0: 
     y=0
     z.append(y)
    else:
     if (df.id[i+1]-df.id[i])==0:
      y=y
      z.append(y)
df['z']=z
#groupby id and z . sum.
df2=df.groupby(['id','z']).sum()
#create y to assign value.
df2['y'] = np.where((df2.supply>=162) , 1, 0)

my outcome is

id  supply  days   z
1   30     -200    0
1   30      0      1
1   100     183    2
1   80      250    2
2   5       0      0
2   5       10     0
3   30      0      0
3   30      100    1
3   30      150    1
3   30      200    1
3   30      280    1
3   30      310    1

       supply_sum
id  z
-------------   
1   0   30
    1   30
    2   180
-----------
2   0   10
-----------
3   0   30
    1   170


    id z     y
-------------------
    1  0     
       1
       2     1
-----------------
    2  0     0
------------------
    3  0     
       1     1

Not work because I add up all the number of supply for each z. It should only add up 3 month supply( 180 days )based on every every naive status (z). For examplze, y of id 3 should be 0 because in 3 months after naive status 2(z=1), he only took 120 pills<162. But my code gave me 170 in this case.

scikit-learn jupyter-notebook pandas numpy python
1 Answers

See if this works for you

df_d=df.loc[(0<=df.days)&(df.days<=180)]
g=df_d.days.eq(0).cumsum()

df_new = df_d.groupby([g,'id'])['supply'].sum().reset_index().drop('days',axis=1)
df_new['y'] = np.where((df_new.supply>=162) , 1, 0)
print(df_new)

Output

    id  supply  y
0   1   97      0
1   2   10      0
2   3   180     1

Details

With df_d=df.loc[(0<=df.days)&(df.days<=180)] we create a new df with days between 0 & 180.

Then with g=df_d.days.eq(0).cumsum() we create a series which changes number starting at each zero.

Then basically what you had already tried. Group by g & id and summing the supply counts.

user_112320910
Related