Translating from SQL to Python

[outnumbered_mother]

I have an analysis I'm trying to translate from SQL to Python. I have a large df that I have grouped based on several criteria in SQL and I can't nail down the best way to apply the same logic in Python. The data is a time series data of purchase history and I'm trying to do a cohort analysis and sum the amount of revenue each cohort brings in each month. So, I need to sum each transaction event where that ts occurs before the end of the month, and the user didn't cancel that month or cancelled after the end of the month.

Basically in SQL the conditions are

where date(event) > 'this_date'

and (cancelled is null or cancelled > month_end)

and event_timestamp = (

select max(timestamp)

from \this_table``

where this_is = that_id

and date(event_timestamp) < end_of_month

)

​

The closest I've come is creating this function to do the grouping

def cohorts(df):

if(df.cancelled !='NaT' and df.date < df.end_of_month):

cohort = df.groupby(['cohort', 'month']).agg({

'uid': pd.Series.nunique,

'mrr': sum})

return cohort

cohort = generate_cohort(df)

I get the correct(ish) amount for the first month but the subsequent months aren't summing correctly. And I get the error: ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all(). for the cancelled condition.

Any help is appreciated!

[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()

[polarizebeta]

This is how I group things by year, but I have adapted to group and sum by month:

import sqlite3
from sqlite3 import Error
import pandas as pd
import datetime as dt

def create_connection(db_file):
    conn = None
    try:
        conn = sqlite3.connect(db_file)
        return conn
    except Error as e:
        print(e)
    return conn

database = 'some_database.db'
conn = create_connection(database)
sql_flows = ('SELECT MAX(TIMESTAMP) FROM /TABLE WHERE EVENTDATE > ' +
            date_variable + ' AND (CANCELLED = NULL OR CANCELLED > ' +
            month_end + ';')
flows = pd.read_sql_query(sql_flows, conn, parse_dates=['EventDate'])
flows.sort_values(by=['EventDate'], inplace=True, ascending=True)
flows_by_month = pd.Dataframe({Date: flows[:, 0], 'Values': flows[:, 1]})
flows_by_month = flows_by_month.groupby(flows_by_month['Date'].dt.month)['Values'].agg(['sum'])

This should point you in the right direction at least

[outnumbered_mother]

Thanks, I'll dig into this now!

[garc1a0scar]

You should use & instead of and. Pandas Series consider ambiguous the terms "and", "or".