Using Pandas to analyze the exchange rates of countries around the world

World Currency Rates – Curate and Publish Dashboard

WeChat search and follow “Python Learning and Research Base Camp”, join the reader group, and share more exciting

The purpose of this note is to discuss how easily publicly available data can be processed, allowing us to gain a deeper understanding of Underline stories that we would otherwise not be able to perceive by looking at their original versions. For example, let’s build this analysis using currency data (Exchange Rate vs. US Dollar).

Download data

This data is available on the IMF website. Exchange rates can be easily downloaded by selecting the possible options listed here, as shown in the image below.

Figure 1 – Screenshot of IMF exchange rate download page

Organize data

After downloading this data, it needs to be organized for further processing. An example picture of the raw data is below.

Figure 2 – Raw Data Screenshot

Now, did you notice the cell anchored by the red box? These cells contain data that must be deleted (such as rows – 1 and 2) or need to be pre-filled with previous data in order for the “average function” (which we will use later) to work properly. Why do we need the AVG function? The reason – Tablaue requires some form of aggregation to work – the visualizer we’ll be using here. The only form of “aggregation” that ends up working here is “AVG” because “SUM” will end up adding all adjacent currency rates when we decide to shrink the data axis using “Month”, “Quarter” and “Year” As an aggregator. Also, not pre-filling data in empty cells with the above data will cause the AVG function to throw incorrect results, because when performing Average, Tablaue will run it at the lowest granularity, at The dataset is set as level “Day”.

Problems in curating

But populating the data with the above will give us some serious problems. Why? Since a blank cell may be the result of “unavailable data”, it may line up over multiple lines – confusing the overall analysis and thus leading to erroneous conclusions. Fortunately, we can handle this very easily. how? On days when the data is prefilled, the exchange rate values will remain the same, and these can be easily filtered out when calculating %Change, which will result in zeros – which can be easily filtered out from the rest of the data.

Curatorial process

For prefilling empty cells with above values can be done using excel and python. I find python simpler and more convenient. The code used for this purpose is listed below.

file_to_process = "IMF_Currency_Rates.csv"

with open(file_to_process,"r") as file:
    df = pd. read_csv(file)

df =df.fillna(method='ffill')
df.to_csv("currencyratesimf.csv")

A snapshot of the featured file is embedded in the image below.

Figure 3 – Selected files for visualization

Note – for the visualizer to work seamlessly, make sure to change the type of column B to “Date Type” – either manually in the CSV file or using the processing code. Otherwise Tablaue will simply ignore the lines that mistyped the data fields in the input file.

Data visualization

The image below gives a snapshot of the Tablaue worksheet.

The image below gives a snapshot of the Tablaue worksheet.

Figure 4 – Snapshot of the Tablaue worksheet

Note that the key ingredients in this worksheet are some calculations – derived from raw data. Note that before you start this process, use Tablaue’s pivot feature to pivot the raw data so you can aggregate all countries in one column and exchange rates in another.

Calculate percent change

Now in order to calculate the percent change in the exchange rate, we need to find the exchange rate that we plan to use to compare the current exchange rate. This can be easily done by using Tablaue’s built-in LOOKUP function. The code is embedded in the image below.

Figure 5 – Lookup Rate Formula

%Change can be easily calculated using the method above, as described below. Note that this formula has a field called “Time Units in View”. This is a user-defined parameter that flexibly changes the width of the viewing window.

Figure 6 – Formula for %Change

Next, we need a “DateFilter” so that we can remove all unnecessary clutter from the viewing window, as described in my previous note “Tableau – Views for the Management Window”

Sort window data

With all these calculations above, we are almost done, except for one. What? We need the dashboard to sort the data based on the value of %Change. Now, for sorting to work, we need to give Tablaue a specific column, not many. Therefore, we have to choose a specific column (out of many defined by the parameter “time unit in view”). For this exercise, I decided to choose the last column. The formula to enable this column is as follows.

Figure 7 – Latest Percent Change Calculation

Now using Tablaue’s RANKUNIQUE function, we can list these percent changes in the order of our choice – “asc” or “desc”. The Rank unique function is defined as follows.

Figure 8 – rank function for sorting

After writing all the calculations, we are all set. We simply insert these calculations into the worksheet, as shown in Figure 4. Below is a snapshot of the final dashboard.

Figure 9 – Final Dashboard

Interested readers can experience the real dashboard here – https://paragkar.com/world-currency-rates-trends-with-respect-to-usd/

Thanks a lot for reading.

Recommended book list

“Graphic Data Intelligence”

“Illustrated Data Intelligence” is a popular science book that provides relevant data intelligence concepts for the docking parties, distributors and general learners of digital resources. Each concept in the book is relatively independent, and readers can use it as a tool book for retrieval, or flexibly consult relevant chapters according to their own interests.

Whether you are a professional practitioner in the field of digital intelligence, or a technical novice who just graduated and wants to enter the field, or a government or enterprise personnel who are facing digital transformation, or thousands of people living in this digital intelligence Ordinary people in society can read this book, and you will find your own answers from the hearty technical explanations and relaxed and interesting comic interpretations.

Graphical Data Smart【Picture Price Brand Review】-Jingdong JD.COM is a professional online shopping mall in China, providing you with graphic data smart price, pictures, brands, reviews, and other related information.https://item.m.jd.com/product/10067743502433.html

Wonderful review

Read the new book “Pandas1.x Practical Examples” first!

[Part 1] Using Pandas to operate the columns and rows of DataFrame

[Part 2] How Pandas sorts and counts DataFrame

[Part 3] How Pandas uses the DataFrame method chain

[Part 4] How does Pandas compare missing values and transpose directions?

[Part 5] How does DataFrame play with diverse data

[Part 6] How to conduct exploratory data analysis?

[Part 7] Using Pandas to Process Categorical Data

[Part 8] Using Pandas to Process Continuous Data

[Part 9] Using Pandas to compare continuous values and continuous columns

[Part 10] How to compare categorical values and use Pandas analysis library

WeChat search and follow “Python Learning and Research Base Camp”

Visit [IT Today’s Hot List] to discover daily technology hotspots