Exciting times!
You've been running our very successful gadget webshop 'DataGadgets' for a few years and have recently expanded into new territories. While you've been focussed on the US market for the first five years of our existence, you now are shipping our cool data gadgets to the UK and Europe, too! But now our in-house built reporting has broken! Transactions don't only come in USD, but you're also receiving transactions in EUR and GPB.
To better understand the volume of transactions being made, you should convert the non-USD transactions to USD and sum up the total. To do this, however, you'll need to use the proper exchange rates.
In this project, you'll start with a CSV file containing all e-commerce transactions made on January 21st, but in their original currencies. Your job is to calculate the total sum in USD of all transactions so you know how much USD you sold on January 21st. To do this, you'll need to convert any non-USD transactions to USD using the exchange rate from January 21st, 2024.
To get the exchange rates from January 21st, 2024, you'll rely on VAT Comply rates API's public and free currency exchange API. You'll also use pandas to load the CSV file into a DataFrame and the requests package to make interacting with the API easier.
You need to update the orders DataFrame so the final version has two new columns: exchange_rate and amount_usd. The final version should look as follows:
amount | currency | exchange_rate | amount_usd |
|---|---|---|---|
| 43.75 | EUR | ... | ... |
| 385.5 | GBP | ... | ... |
| 495.5 | GBP | ... | ... |
| 117.99 | GBP | ... | ... |
| 624 | USD | ... | ... |
# Import required packages/libraries
import pandas as pd
import requests
# Read the CSV file into a DataFrame
orders = pd.read_csv('data/orders-2024-01-21.csv')
orders.head()# TODO: Create comments for each step
#Import requests
import requests
# set API endpoint where currency exchange rates will be fetched from
api = 'https://api.vatcomply.com/rates'
# Define the query parameters: base to USD and the date to 2024-01-21
query_params = {'base': 'USD', 'date': '2024-01-21'}
# Make a GET request to the API with the query parameters
response = requests.get(api, params=query_params)
print(response.url)
# Convert JSON into a python dictionary
data = response.json()
# Extract the currency exchange rates dictionary
rates = data['rates']
# Map each of the order's currency to its corresponding exchange rate
orders['exchange_rate'] = orders['currency'].map(rates)
# Convert all transaction amounts in USD currency
orders['amount_usd'] = orders['amount'] * orders['exchange_rate']
# Calculate for the total sales in USD
total_usd_sales = orders['amount_usd'].sum()
# Print the total sales in USD
print(total_usd_sales)
# Display the modified orders DataFrame
orders