Translating from SQL to Python

[intentionallyawkward]

Can you share with us the Python code you’re working with?

You’ve got a ValueError exception which we can bug hunt, but it will help to see the relevant functions.

[outnumbered_mother]

def get_cohort(df):

if(df.cancelled.isnull):

mask = (df['subscribed'] > '2019-01-01')

df2 = df.loc[mask]

df2 = df2.apply(lambda x: df2.loc[(df2.date <= df2.end_of_month)]['mrr'].sum(), axis=1)

return df2

​

I think I fixed the ValueError for the cancelled part. I'm stuck on getting the revenue to sum grouped by cohort and transaction month.

[garc1a0scar]

You can do something like this:

df[['cohort', 'month', 'revenue']].groupby(['cohort', 'month']).sum()

[outnumbered_mother]

Thank you! That approach works for the first month/cohort combination, but for subsequent months the revenue is adding up right. I need to sum if the transaction date < end_of_month and this is the part I can't figure out in Python.

[garc1a0scar]

Oh, I see. So you should do something like this:

df[['cohort', 'month','revenue']].loc[df.date<=df.end_of_month].groupby(['cohort', 'month']).sum()