# Plotly OFTW App Building Challenge **URL:** \ **Category:** Dash Python\ **Tags:** app-challenge\ **Created:** [March 7, 2025, 10:07pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724 "2025-03-07T22:07:20Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 7, 2025, 10:07pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/1 "2025-03-07T22:07:20Z")
For this month’s app-building challenge, we’ve partnered with [One For The World (OFTW)](https://1fortheworld.org/), 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](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724#p-190049-the-data-1). [OFTW’s metrics wishlist](https://docs.google.com/spreadsheets/d/1fIjasvRyjI2VPyGnNvs6SAwG5PuKw2sQkGlAwlCX1WA/edit?usp=sharing) 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): 🥇 $500 USD 🥈 $250 USD 🥉 $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: - [https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-pledges.json](https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-pledges.json) - [https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json](https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json) - [Metadata file](https://docs.google.com/spreadsheets/d/1XSlvYfBxqAPvdXLCG7hnVzjCCjAtQ5CpGUebhU-8sPg/edit?usp=sharing) - [OFTW’s metrics wishlist](https://docs.google.com/spreadsheets/d/1fIjasvRyjI2VPyGnNvs6SAwG5PuKw2sQkGlAwlCX1WA/edit?usp=sharing) 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: ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/3X/8/3/83064ff4cbdde76de78baf9964bb6b480d3e89f5.png) ```python 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](https://discord.gg/fPNPZQjmjc) on Plotly’s Discord server at 11am Eastern Time. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 7, 2025, 10:08pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/2 "2025-03-07T22:08:17Z")
Pinned globally on March 7, 2025, 10:08pm UTC ---
**Author:** ![ThomasD21M](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/thomasd21m/32/29571_2.png) [@ThomasD21M](https://community.plotly.com/u/ThomasD21M)\ **Post date:** [March 10, 2025, 12:27am UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/3 "2025-03-10T00:27:59Z")
Great way to spend a Sunday afternoon! Using the actual AI within the app itself here is how it describes this tool: ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/3X/b/e/be44498c8a119b9472dbeebc51e361b221caf735.png) 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** : 3. **Non-Chart Elements** : 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 ![image](https://us1.discourse-cdn.com/flex024/uploads/plot/original/3X/f/5/f55894861fc0a91967465c59f3bc3b8e1a715a57.png) [ Home | Loom - 9 March 2025 - Watch Video ](https://www.loom.com/share/0d9707d3237f412ab012381b30cf9539)[![](https://cdn.loom.com/sessions/thumbnails/0d9707d3237f412ab012381b30cf9539-73a1aa4fa90d8bc9-full-play.gif)](https://www.loom.com/share/0d9707d3237f412ab012381b30cf9539) ```auto ``` ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 10, 2025, 1:19pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/4 "2025-03-10T13:19:07Z")
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? ---
**Author:** ![ThomasD21M](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/thomasd21m/32/29571_2.png) [@ThomasD21M](https://community.plotly.com/u/ThomasD21M)\ **Post date:** [March 10, 2025, 2:28pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/5 "2025-03-10T14:28:05Z")
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: > **[GitHub - ProgInNameOnly/oftw-dashboard](https://github.com/ProgInNameOnly/oftw-dashboard)** > > Contribute to ProgInNameOnly/oftw-dashboard development by creating an account on GitHub. ---
**Author:** ![mavwolverine](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mavwolverine](https://community.plotly.com/u/mavwolverine)\ **Post date:** [March 11, 2025, 1:46pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/6 "2025-03-11T13:46:10Z")
@adamschroeder How are different currencies to be handled? Thanks ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 11, 2025, 3:05pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/7 "2025-03-11T15:05:00Z")
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](https://discord.gg/fPNPZQjmjc). I’ll post the code here when I’m done. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 11, 2025, 4:18pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/8 "2025-03-11T16:18:52Z")
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 ```python 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 ```python 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') ``` ---
**Author:** ![odedloe87](https://avatars.discourse-cdn.com/v4/letter/o/c77e96/32.png) [@odedloe87](https://community.plotly.com/u/odedloe87)\ **Post date:** [March 12, 2025, 9:03am UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/9 "2025-03-12T09:03:08Z")
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())]) ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 12, 2025, 1:33pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/10 "2025-03-12T13:33:35Z")
> [@odedloe87](#): > > currency\_converter I think it might work. I’m trying it out right now in the [live session](https://discord.gg/m7c4u3gB). Do you have the pypi package? ---
**Author:** ![odedloe87](https://avatars.discourse-cdn.com/v4/letter/o/c77e96/32.png) [@odedloe87](https://community.plotly.com/u/odedloe87)\ **Post date:** [March 12, 2025, 5:22pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/11 "2025-03-12T17:22:36Z")
Sure, here it is: pip install CurrencyConverter ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 12, 2025, 8:13pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/12 "2025-03-12T20:13:59Z")
That library works as well, @odedloe87. Here’s the code I used to test it: ```auto 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. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 12, 2025, 8:17pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/13 "2025-03-12T20:17:08Z")
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](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.interpolate.html). ## Create CSV sheets of all non-USD to USD rates: ```Python 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 ```Python 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') ``` ---
**Author:** ![mavwolverine](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mavwolverine](https://community.plotly.com/u/mavwolverine)\ **Post date:** [March 13, 2025, 2:06pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/14 "2025-03-13T14:06:21Z")
@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. ---
**Author:** ![mavwolverine](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mavwolverine](https://community.plotly.com/u/mavwolverine)\ **Post date:** [March 13, 2025, 2:11pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/15 "2025-03-13T14:11:46Z")
Not sure if I am doing something wrong here ```auto _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']) ``` ```auto 5681 15522.0 Name: amount, dtype: float64 5681 0.798531 Name: conversion_rate, dtype: float64 5681 12394.79358 dtype: float64 ``` and ```auto _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) ``` ```auto 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. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 13, 2025, 2:48pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/16 "2025-03-13T14:48:45Z")
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](https://discord.gg/fPNPZQjmjc). ---
**Author:** ![mavwolverine](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mavwolverine](https://community.plotly.com/u/mavwolverine)\ **Post date:** [March 13, 2025, 3:51pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/17 "2025-03-13T15:51:09Z")
Unfortunately, the sessions are during school drop off PST time. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 13, 2025, 4:57pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/18 "2025-03-13T16:57:13Z")
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. ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 13, 2025, 9:02pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/19 "2025-03-13T21:02:00Z")
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: ```Python 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 ```Python 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) ``` ---
**Author:** ![adamschroeder](https://sea2.discourse-cdn.com/flex024/user_avatar/community.plotly.com/adamschroeder/32/17280_2.png) [@adamschroeder](https://community.plotly.com/u/adamschroeder)\ **Post date:** [March 13, 2025, 9:02pm UTC](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724/20 "2025-03-13T21:02:20Z")
@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. ```auto 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 ) ``` [Next page](https://community.plotly.com/t/plotly-oftw-app-building-challenge/90724.md?page=2)