0

I have a created a new column 'twelve_wk_usage' using a filter, and the groupby and transform functions. The 'twelve_wk_usage' field is essentially summing up usage by unique id if the date corresponding to the usage falls 12 weeks before the maximum date in the dataset.

Code for the fields mentioned below:

df_DTO2['twelve_wks_prior_TF'] =  df_DTO2['request_week_end_day
'] == df_DTO2['12_wks_prior']

df_DTO2['twelve_wk_usage'] = df_DTO2[df_DTO2.twelve_wks_prior_TF == True
].groupby(['unique_id'])['sum_ni'].transform(sum))

I am trying to create a new column which divides the current usage by the twelve week usage, but it is not working. I am only getting Nans. Code below:

df_DTO2['current_div_twelve'] = ((df_DTO2['current_wk_usage'])/(
df_DTO2['twelve_wk_usage']))

It seems the only way I can get it to work is to recreate the referenced fields entirely from scratch in the new field. I understand this is because the indexes aren't aligned but am not sure how I can create usable fields with standardized indexes.

I have tried including "twelve_wks_prior_TF" in my grouping statement but it is still summing all usage not just the usage from twelve weeks prior.

Any ideas?

Christa Jacob
  • 1
  • 1
  • 1
    your indexes aren't aligned because `df_DTO2[df_DTO2.twelve_wks_prior_TF == True]` means you're only operating on a subset of rows – Paul H Jan 22 '21 at 21:52
  • @PaulH ah, okay i see. how would I replicate what I'm trying to do above so that I can re-use the field in other formulas? – Christa Jacob Jan 25 '21 at 17:18
  • I would include the "twelve_wks_prior_TF" in your grouping statement – Paul H Jan 25 '21 at 18:25
  • okay, i updated it to df_DTO2['twelve_wk_usage] = df_DTO2.groupby(['twelve_wks_prior_TF','unique_id'])['sum_ni'].transform(sum) but it's not filtering out the other weeks' usage. – Christa Jacob Jan 25 '21 at 19:25
  • without a working example, i have no idea what you're talking about – Paul H Jan 25 '21 at 19:51
  • Understood. What would be the best way to provide an example? Just commenting here, or can I message you directly somehow? Sorry, this is my first question on SO and I am relatively new to Python. – Christa Jacob Jan 25 '21 at 20:10
  • edit it into the question https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples – Paul H Jan 25 '21 at 20:20

0 Answers0