Plotly OFTW App Building Challenge

For this month’s app-building challenge, we’ve partnered with One For The World (OFTW), whose mission is to scale the effective giving movement addressing extreme poverty.

OFTW is asking the Plotly community to help it build an internal-facing data app that will provide insights into its monetary pledges and payments from 2014 to the present time. Find out more about the data at the end of the post.

OFTW’s metrics wishlist is a list of insights that OFTW is hoping to gain from the data. However, OFTW welcomes new data insights that are not part of the wishlist.

The winning apps will be judged according to the following categories:

  • Data insights revealed from the dataset (how helpful the insights are to the OFTW team)
  • App UI/UX Design
  • Bonus points for integration of LLMs to facilitate app interactivity and insights

The winners will be announced at the end of April and will receive the following rewards (thank you to OFTW for contributing to the rewards):

:1st_place_medal: $500 USD

:2nd_place_medal: $250 USD

:3rd_place_medal: $100 USD

To submit your app, share it by replying to this Forum topic by midnight Sunday (your time zone), April 13. Please include a link to the app, the code on GitHub, and a short description of the app.

The Data:

The two datasets (pledges and payments), metadata file, and wishlist:

If there is a need, both datasets can be joined with the pledge_id field. For more context, people sign up (“pledge”) to OFTW to start giving some amount in the future. Once the pledge becomes live, they start making donations (“payment”).

Getting Started:

from dash import Dash, dcc, callback, Output, Input, no_update
import dash_ag_grid as dag
import plotly.express as px
import pandas as pd

# df_pledges = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-pledges.json")
df_payments = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json")


fig = px.histogram(df_payments, x='payment_platform')

grid = dag.AgGrid(
    id='payments-table',
    rowData=df_payments.to_dict("records"),
    columnDefs=[{"field": i, 'filter': True, 'sortable': True} for i in df_payments.columns],
    dashGridOptions={"pagination": True}
)

app = Dash()
app.layout = [
    dcc.Markdown("# One For The World - Getting Started"),
    grid,
    dcc.Graph(id='platform-fig', figure=fig)
]


if __name__ == "__main__":
    app.run(debug=True)

Building together:

Want to work on this app with other community members? Starting March 10, we will be running weekly sessions on Plotly’s Discord server at 11am Eastern Time.

8 Likes

Great way to spend a Sunday afternoon!

Using the actual AI within the app itself here is how it describes this tool:

This tool is a dashboard designed to track and analyze data related to donations and pledges for One For The World (OFTW), a charitable organization. It contains information about donors, pledges, payments, and key performance indicators (KPIs). Here are the main components of the tool:

  1. Glossary: Provides definitions for terms used in the dashboard such as donor_id, pledge_status, contribution_amount, etc.

  2. Charts:

    • Active Annualized Run Rate by Top 10 Chapters: Shows the total monthly donation amounts from active pledges for the top 10 chapters, helping to identify which chapters contribute the most to recurring revenue.
    • Pledge Attrition Rate: Displays the proportion of pledges with status ‘Payment failure’ or ‘Churned donor’ compared to all pledges, indicating donor retention challenges.
    • Time Lag Distribution (Days): Illustrates the distribution of time lag between pledge creation date and payment date, highlighting delays in donor payments.
  3. Non-Chart Elements:

    • Total Counterfactual Money Moved (YTD: July 1, 2024 - March 09, 2025): Reports the total amount of money moved year-to-date, adjusted by
  4. counterfactuality

, reflecting OFTW’s impact for the fiscal year.

  • Merged Data Sample: A table displaying merged data on pledges and payments, providing a comprehensive view of donor activity.

Overall, this tool helps OFTW monitor donor contributions, assess pledge performance, and track key metrics to improve fundraising strategies and donor engagement.

The AI addition is quite impressive, here is a request where I asked it to describe the time lag distribution


Home | Loom - 9 March 2025 - Watch Video

2 Likes

That’s a unique way of adding AI to the app. Nice job, @ThomasD21M . Do you plan to deploy the app and share a link to it?

Also, there is still a lot of time left for the challenge. Were you thinking of developing your app even further?

Hi Adam, I do plan to continue refining the app. Regarding the AI, as a user I often find myself wishing I could query explanation of charts on a site or dashboard to have them explained in a simple way. Sometimes charts are visually stunning but difficult to interpret. My thinking was using AI to assist here or as a general assistant that explains the variables and KPI’s that the charts are comprised of. like “pledge vs payment” , in this example.

Couldn’t fit the script in the last response. Here it is:

2 Likes

@adamschroeder How are different currencies to be handled? Thanks

hi @mavwolverine
we should try to convert everything to USD. I’m actually working on it right now on a live session on Plotly Discord. Feel free to join me.
I’ll post the code here when I’m done.

Ok, the code still needs to be worked on, but here are two python files.

A script to grab GBP exchange rates from 2014 to Feb 2025.

  • These other currencies need to be added: [‘CAD’, ‘AUD’, ‘EUR’, ‘SGD’, ‘CHF’]
  • pandas_datareader.data has exchange rate gaps on a few days. These gaps need to be filled or we use a different API to gran historical exchange rates
import pandas_datareader.data as web
import datetime

# Add these currencies: 'GBP', 'CAD', 'AUD', 'EUR', 'SGD', 'CHF'

def get_historical_exchange_rates(start_date="2020-01-01", end_date=None):
    """Fetch historical GBP to USD exchange rates from FRED."""
    if end_date is None:
        end_date = datetime.datetime.today().strftime("%Y-%m-%d")

    try:
        df = web.DataReader("DEXUSUK", "fred", start_date, end_date)
        # save df as a csv sheet
        df.to_csv("gbp_exchange_rates.csv")
        # df.to_csv("cad_exchange_rates.csv")
        # df.to_csv("aus_exchange_rates.csv")

    except Exception as e:
        print("Error fetching data:", e)

get_historical_exchange_rates("2014-03-01", "2025-02-28")

A script to merge the exchange rate CSV sheets with the main payments dataset and convert non-USD currencies to USD

import pandas as pd

gbt_USD_df = pd.read_csv('exchange_rates_gbp.csv')
gbt_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
gbt_USD_df['date'] = pd.to_datetime(gbt_USD_df['date'], errors='coerce')

df_payments = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json")
df_payments['date'] = pd.to_datetime(df_payments['date'], errors='coerce')
df_payments = df_payments.sort_values(by='date')

# Merge dataframes on the date column
df_merged = df_payments.merge(gbt_USD_df, on='date', how='left')
# df_merged = df_merged.merge(cad_USD_df, on='date', how='left')
# df_merged = df_merged.merge(gbt_USD_df, on='date', how='left')

# Convert currencies to USD
df_merged['amount_usd'] = df_merged.apply(
    lambda row: row['amount'] * row['DEXUSUK'] if row['currency'] == 'GBP' else
                row['amount'] * row['DEXUSCAD'] if row['currency'] == 'CAD' else
                row['amount'] * row['DEXUSAUD'] if row['currency'] == 'AUD' else
                row['amount'], axis=1
)

# Display the updated dataframe
df_merged.to_csv('merged_data.csv')

1 Like

Hi Adam, I used the following to get the usd amount, is it ok or is it necessary to use your method?

import tqdm
from currency_converter import CurrencyConverter

import datetime

c = CurrencyConverter(fallback_on_missing_rate=True)

df_payments.insert(5,‘amount_usd’,[c.convert(row[‘amount’], row[‘currency’], ‘USD’,date=row[‘date’]) if row[‘currency’]!=‘USD’ else row[‘amount’] for _,row in tqdm.tqdm(df_payments.iterrows())])

1 Like

I think it might work. I’m trying it out right now in the live session.

Do you have the pypi package?

Sure, here it is:

pip install CurrencyConverter

That library works as well, @odedloe87. Here’s the code I used to test it:

from currency_converter import CurrencyConverter
c = CurrencyConverter()
from datetime import date # datetime works too
data = c.convert(1, 'SGD', 'USD', date=date(2014, 3, 10))
print(data)

But you will face the same issue that we faced with pandas_datareader.data as web where Saturdays and Sundays don’t have exchange rates. One way around that is to use pd.interpolate to fill in the missing days.

Hey everyone,
Here’s the updated code for converting everything to USD. There are some date gaps for exchange rates that don’t show on weekends. So I would use pandas to fill in the missing gaps with pd.interpolate.

Create CSV sheets of all non-USD to USD rates:

import pandas_datareader.data as web
import datetime

# Use pandas to fill in the missing rate gaps not reported on weekends with pd.interpolate: https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.interpolate.html

def get_historical_exchange_rates(start_date="2020-01-01", end_date=None):
    """Fetch historical GBP to USD exchange rates from FRED."""
    if end_date is None:
        end_date = datetime.datetime.today().strftime("%Y-%m-%d")

    try: # DEXUSUK, DEX..., DEXUSAL, DEXUSEU, DEXSIUS, DEX...
        df = web.DataReader("DEXSIUS", "fred", start_date, end_date)
        # save df as a csv sheet
        # df.to_csv("gbp_exchange_rates.csv")
        # df.to_csv("cad_exchange_rates.csv")
        # df.to_csv("aus_exchange_rates.csv")
        # df.to_csv("eur_exchange_rates.csv")
        df.to_csv("sgd_exchange_rates.csv")
        # df.to_csv("chf_exchange_rates.csv")


    except Exception as e:
        print("Error fetching data:", e)

get_historical_exchange_rates("2014-03-01", "2025-02-28")

Merge the exchange rate CSV sheets with the main payments dataset and convert non-USD currencies to USD

import pandas as pd

gbp_USD_df = pd.read_csv('gbp_exchange_rates.csv')
gbp_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
gbp_USD_df['date'] = pd.to_datetime(gbp_USD_df['date'], errors='coerce')

cad_USD_df = pd.read_csv('cad_exchange_rates.csv')
cad_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
cad_USD_df['date'] = pd.to_datetime(cad_USD_df['date'], errors='coerce')

aus_USD_df = pd.read_csv('aus_exchange_rates.csv')
aus_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
aus_USD_df['date'] = pd.to_datetime(aus_USD_df['date'], errors='coerce')

eur_USD_df = pd.read_csv('eur_exchange_rates.csv')
eur_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
eur_USD_df['date'] = pd.to_datetime(eur_USD_df['date'], errors='coerce')

chf_USD_df = pd.read_csv('chf_exchange_rates.csv')
chf_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
chf_USD_df['date'] = pd.to_datetime(chf_USD_df['date'], errors='coerce')

sgd_USD_df = pd.read_csv('chf_exchange_rates.csv')
sgd_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
sgd_USD_df['date'] = pd.to_datetime(sgd_USD_df['date'], errors='coerce')

df_payments = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json")
df_payments['date'] = pd.to_datetime(df_payments['date'], errors='coerce')
df_payments = df_payments.sort_values(by='date')

# Merge dataframes on the date column
df_merged = df_payments.merge(gbp_USD_df, on='date', how='left')
df_merged = df_merged.merge(cad_USD_df, on='date', how='left')
df_merged = df_merged.merge(aus_USD_df, on='date', how='left')
df_merged = df_merged.merge(eur_USD_df, on='date', how='left')
df_merged = df_merged.merge(chf_USD_df, on='date', how='left')
df_merged = df_merged.merge(sgd_USD_df, on='date', how='left')



# Convert currencies to USD
df_merged['amount_usd'] = df_merged.apply(
    lambda row: row['amount'] * row['DEXUSUK'] if row['currency'] == 'GBP' else
                row['amount'] * row['DEXCAUS'] if row['currency'] == 'CAD' else
                row['amount'] * row['DEXUSAL'] if row['currency'] == 'AUD' else
                row['amount'] * row['DEXUSEU'] if row['currency'] == 'EUR' else
                row['amount'] / row['DEXSZUS'] if row['currency'] == 'CHF' else
                row['amount'] / row['DEXSIUS'] if row['currency'] == 'SGD' else
                row['amount'], axis=1
)


# Display the updated dataframe
df_merged.to_csv('merged_data.csv')
1 Like

@adamschroeder Thanks for sharing this. One issue with your conversion though, not all conversion rates are “to US”, some of them are “from US”. So I think it needs to be adjusted accordingly.

Not sure if I am doing something wrong here

_df = df_payments_ytd[(df_payments_ytd['date'] == '2024-12-30') & (df_payments_ytd['amount'] == 15522.00)]
print(_df['amount'])
print(_df['conversion_rate'])
print(_df['amount'] * _df['conversion_rate'])
5681    15522.0
Name: amount, dtype: float64
5681    0.798531
Name: conversion_rate, dtype: float64
5681    12394.79358
dtype: float64

and

_df = pd.DataFrame({'amount': [15522.0], 'conversion_rate': [0.798531]})
print(_df['amount'])
print(_df['conversion_rate'])
print(_df['amount'] * _df['conversion_rate'])
print(15522.0 * 0.798531)
0    15522.0
Name: amount, dtype: float64
0    0.798531
Name: conversion_rate, dtype: float64
0    12394.798182
dtype: float64
12394.798182

Why is calculated value in first case 12394.79358 but in second case 12394.798182 which matches calculator?

Altough the difference is miniscule in this case, rounding would result in 12394.79 vs 12394.80.

Anyways, just would like to understand why the difference when the dtype are same.

That’s a good question @mavwolverine that we still need to explore. We will be looking at it in 12 minutes (11am Eastern Time) if you’d like to join our live coding session.

Unfortunately, the sessions are during school drop off PST time.

no worries, @mavwolverine .
I finalized the currency convertor scripts together with the community members about an hour ago. I’ll upload the script here later this afternoon.

Ok, here’s are the final two python scripts to pull the currency exchange rates and convert all non-USD currencies to USD on the main payments dataset.

The exchange-rates.py file should be run first.

exchange-rates.py:


import pandas_datareader.data as web
import datetime
import pandas as pd


def get_historical_exchange_rates(start_date="2020-01-01", end_date=None):
    """Fetch historical GBP to USD exchange rates from FRED."""
    if end_date is None:
        end_date = datetime.datetime.today().strftime("%Y-%m-%d")

    try:
        for currency in ['DEXUSUK', 'DEXCAUS', 'DEXUSAL', 'DEXUSEU', 'DEXSIUS', 'DEXSZUS']:
            df = web.DataReader(currency, "fred", start_date, end_date)
            df.reset_index(inplace=True)

            # Convert DATE column to datetime format
            df['DATE'] = pd.to_datetime(df['DATE'])

            # Create a new DataFrame with all dates in the range
            complete_dates = pd.date_range(start=df['DATE'].min(), end=df['DATE'].max(), freq='D')
            complete_df = pd.DataFrame({'DATE': complete_dates})

            # Merge the original data with the complete date range
            merged_df = pd.merge(complete_df, df, on='DATE', how='left')

            # Set the DATE column as the index for interpolation
            merged_df.set_index('DATE', inplace=True)

            # Interpolate the missing values using linear method
            merged_df[currency] = merged_df[currency].interpolate(method='linear')

            # Display the result
            print(merged_df.head(10))

            # save df as a csv sheet
            merged_df.to_csv(f"{currency}_exchange_rates.csv")


    except Exception as e:
        print("Error fetching data:", e)

get_historical_exchange_rates("2014-03-01", "2025-02-28")

currency-converter.py

import pandas as pd

gbp_USD_df = pd.read_csv('DEXUSUK_exchange_rates.csv')
gbp_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
gbp_USD_df['date'] = pd.to_datetime(gbp_USD_df['date'], errors='coerce')

cad_USD_df = pd.read_csv('DEXCAUS_exchange_rates.csv')
cad_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
cad_USD_df['date'] = pd.to_datetime(cad_USD_df['date'], errors='coerce')

aus_USD_df = pd.read_csv('DEXUSAL_exchange_rates.csv')
aus_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
aus_USD_df['date'] = pd.to_datetime(aus_USD_df['date'], errors='coerce')

eur_USD_df = pd.read_csv('DEXUSEU_exchange_rates.csv')
eur_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
eur_USD_df['date'] = pd.to_datetime(eur_USD_df['date'], errors='coerce')

chf_USD_df = pd.read_csv('DEXSZUS_exchange_rates.csv')
chf_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
chf_USD_df['date'] = pd.to_datetime(chf_USD_df['date'], errors='coerce')

sgd_USD_df = pd.read_csv('DEXSIUS_exchange_rates.csv')
sgd_USD_df.rename(columns={'DATE': 'date'}, inplace=True)
sgd_USD_df['date'] = pd.to_datetime(sgd_USD_df['date'], errors='coerce')

df_payments = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json")
df_payments['date'] = pd.to_datetime(df_payments['date'], errors='coerce')
df_payments = df_payments.sort_values(by='date')

# Merge dataframes on the date column
df_merged = df_payments.merge(gbp_USD_df, on='date', how='left')
df_merged = df_merged.merge(cad_USD_df, on='date', how='left')
df_merged = df_merged.merge(aus_USD_df, on='date', how='left')
df_merged = df_merged.merge(eur_USD_df, on='date', how='left')
df_merged = df_merged.merge(chf_USD_df, on='date', how='left')
df_merged = df_merged.merge(sgd_USD_df, on='date', how='left')


# Convert currencies to USD
df_merged['amount_usd'] = df_merged.apply(
    lambda row: row['amount'] * row['DEXUSUK'] if row['currency'] == 'GBP' else
                row['amount'] / row['DEXCAUS'] if row['currency'] == 'CAD' else
                row['amount'] * row['DEXUSAL'] if row['currency'] == 'AUD' else
                row['amount'] * row['DEXUSEU'] if row['currency'] == 'EUR' else
                row['amount'] / row['DEXSIUS'] if row['currency'] == 'SGD' else
                row['amount'] / row['DEXSZUS'] if row['currency'] == 'CHF' else
                row['amount'], axis=1
)


# Create csv sheet to look over the updated dataframe and verify exchange rate conversions worked
df_merged.to_csv('merged_data.csv')

# remove the exchange rate columns from the dataframe
df_merged = df_merged.drop(['DEXUSUK', 'DEXCAUS', 'DEXUSAL', 'DEXUSEU', 'DEXSIUS', 'DEXSZUS'], axis=1)

@mavwolverine please double check the final conversions, but I think we solved the problem you mentioned by choosing to multiple or divide the columns based on the exchange rates order.

df_merged['amount_usd'] = df_merged.apply(
    lambda row: row['amount'] * row['DEXUSUK'] if row['currency'] == 'GBP' else
                row['amount'] / row['DEXCAUS'] if row['currency'] == 'CAD' else
                row['amount'] * row['DEXUSAL'] if row['currency'] == 'AUD' else
                row['amount'] * row['DEXUSEU'] if row['currency'] == 'EUR' else
                row['amount'] / row['DEXSIUS'] if row['currency'] == 'SGD' else
                row['amount'] / row['DEXSZUS'] if row['currency'] == 'CHF' else
                row['amount'], axis=1
)
1 Like