Python Scripting in Magic ETL | Iterating through close dates to get unique account id values

cmoreno · August 22

Hello! I'm trying to find the issue in my current python code that iterates through each date while maintaining a set of already logged accounts and then counting new accounts each day. I have sorted by the 'Close Date' , grouped by the 'Close Date' and the 'Owner Division', and have 'new accounts' and 'seen_accounts' variables. I was hoping a unique count on the 'new_accounts' would work, but I'm running into an error where I can't preview the execution. Error message is "Output data not found or not parsable".

This is my code below:

DavidChurchman · August 22

You need to tell the tile what you want it to output:

write_dataframe(unique_account_count_cumulative)

I also think you want to unindent your current last line to take it out of the loop, otherwise you're unnecessarily over-writing the same dataframe over and over.

cmoreno · August 22

Wait, I forgot to write back my data frame!

ArborRose · August 22

Try this:

from domomagic import *

import pandas as pd



# Reading the data from Domo

input1 = read_dataframe('Existing - Sales Rev')



# Sorting the DataFrame by 'Close Date'

df = input1.sort_values(by='Close Date')



# Initialize a set to keep track of seen accounts

seen_accounts = set()



# Initialize a list to store the results

results = []



# Group by 'Close Date' and 'Owner ID.Division'

for date, group in df.groupby(['Close Date', 'Owner ID.Division']):

    # Find new accounts that haven't been seen before

    new_accounts = group[~group['Account ID'].isin(seen_accounts)]


    # Count unique new accounts

    unique_count = new_accounts['Account ID'].nunique()


    # Append the results to the list

    results.append({

        'Close Date': date[0],

        'Owner ID.Division': date[1],

        'UniqueAccountCount': unique_count

    })


    # Update the set of seen accounts with the newly found ones

    seen_accounts.update(new_accounts['Account ID'])



# Create a DataFrame from the results

unique_account_count_cumulative = pd.DataFrame(results)



# Write the output DataFrame back to Domo

write_dataframe(unique_account_count_cumulative, 'Output Dataset Name')

DavidChurchman · August 22

You need to tell the tile what you want it to output:

write_dataframe(unique_account_count_cumulative)

I also think you want to unindent your current last line to take it out of the loop, otherwise you're unnecessarily over-writing the same dataframe over and over.

ArborRose · August 22

from domomagic import *

import pandas as pd



# Reading the data from Domo

input1 = read_dataframe('Existing - Sales Rev')



# Convert input to a DataFrame

df = pd.DataFrame(input1)



# Sort the DataFrame by 'Close Date'

df = df.sort_values(by='Close Date')



# Initialize a set to keep track of seen accounts

seen_accounts = set()



# Initialize a list to store the results

results = []



# Group by 'Close Date' and 'Owner ID.Division'

for date, group in df.groupby(['Close Date', 'Owner ID.Division']):

    # Find new accounts that haven't been seen before

    new_accounts = group[~group['Account ID'].isin(seen_accounts)]


    # Count unique new accounts

    unique_count = new_accounts['Account ID'].nunique()


    # Append the results to the list

    results.append({

        'Close Date': date[0],

        'Owner ID.Division': date[1],

        'UniqueAccountCount': unique_count

    })


    # Update the set of seen accounts with the newly found ones

    seen_accounts.update(new_accounts['Account ID'])



# Create a DataFrame from the results

unique_account_count_cumulative = pd.DataFrame(results)



# Optionally write the output DataFrame back to Domo (if needed)

# write_dataframe(unique_account_count_cumulative, 'Output Dataset Name')

Python Scripting in Magic ETL | Iterating through close dates to get unique account id values

Best Answer

Answers

Categories