Guided Project: Storytelling Data Visualization on Exchange Rate

This Guided Project would revolve around explanatory data visualization by adopting Gestalt principles utilizing Matplotlib built-in styles, in this case 'FiveThirtyEight' style. We will be using Euro daily exchange rates between 1999 and 2021. The dataset was gathered by Daria Chemkaeva and was taken from Kaggle The data source is the European Central Bank and it gets regular updates. For this case the data was downloaded in January 2021.

In [65]:
import pandas as pd
import matplotlib.pyplot as plt
%matplotlib inline

exchange_rates = pd.read_csv('euro-daily-hist_1999_2020.csv')
exchange_rates.info()
exchange_rates.head()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5699 entries, 0 to 5698
Data columns (total 41 columns):
 #   Column                    Non-Null Count  Dtype  
---  ------                    --------------  -----  
 0   Period\Unit:              5699 non-null   object 
 1   [Australian dollar ]      5699 non-null   object 
 2   [Bulgarian lev ]          5297 non-null   object 
 3   [Brazilian real ]         5431 non-null   object 
 4   [Canadian dollar ]        5699 non-null   object 
 5   [Swiss franc ]            5699 non-null   object 
 6   [Chinese yuan renminbi ]  5431 non-null   object 
 7   [Cypriot pound ]          2346 non-null   object 
 8   [Czech koruna ]           5699 non-null   object 
 9   [Danish krone ]           5699 non-null   object 
 10  [Estonian kroon ]         3130 non-null   object 
 11  [UK pound sterling ]      5699 non-null   object 
 12  [Greek drachma ]          520 non-null    object 
 13  [Hong Kong dollar ]       5699 non-null   object 
 14  [Croatian kuna ]          5431 non-null   object 
 15  [Hungarian forint ]       5699 non-null   object 
 16  [Indonesian rupiah ]      5699 non-null   object 
 17  [Israeli shekel ]         5431 non-null   object 
 18  [Indian rupee ]           5431 non-null   object 
 19  [Iceland krona ]          3292 non-null   float64
 20  [Japanese yen ]           5699 non-null   object 
 21  [Korean won ]             5699 non-null   object 
 22  [Lithuanian litas ]       4159 non-null   object 
 23  [Latvian lats ]           3904 non-null   object 
 24  [Maltese lira ]           2346 non-null   object 
 25  [Mexican peso ]           5699 non-null   object 
 26  [Malaysian ringgit ]      5699 non-null   object 
 27  [Norwegian krone ]        5699 non-null   object 
 28  [New Zealand dollar ]     5699 non-null   object 
 29  [Philippine peso ]        5699 non-null   object 
 30  [Polish zloty ]           5699 non-null   object 
 31  [Romanian leu ]           5637 non-null   float64
 32  [Russian rouble ]         5699 non-null   object 
 33  [Swedish krona ]          5699 non-null   object 
 34  [Singapore dollar ]       5699 non-null   object 
 35  [Slovenian tolar ]        2085 non-null   object 
 36  [Slovak koruna ]          2608 non-null   object 
 37  [Thai baht ]              5699 non-null   object 
 38  [Turkish lira ]           5637 non-null   float64
 39  [US dollar ]              5699 non-null   object 
 40  [South African rand ]     5699 non-null   object 
dtypes: float64(3), object(38)
memory usage: 1.8+ MB
Out[65]:
Period\Unit: [Australian dollar ] [Bulgarian lev ] [Brazilian real ] [Canadian dollar ] [Swiss franc ] [Chinese yuan renminbi ] [Cypriot pound ] [Czech koruna ] [Danish krone ] ... [Romanian leu ] [Russian rouble ] [Swedish krona ] [Singapore dollar ] [Slovenian tolar ] [Slovak koruna ] [Thai baht ] [Turkish lira ] [US dollar ] [South African rand ]
0 2021-01-08 1.5758 1.9558 6.5748 1.5543 1.0827 7.9184 NaN 26.163 7.4369 ... 4.8708 90.8000 10.0510 1.6228 NaN NaN 36.8480 9.0146 1.2250 18.7212
1 2021-01-07 1.5836 1.9558 6.5172 1.5601 1.0833 7.9392 NaN 26.147 7.4392 ... 4.8712 91.2000 10.0575 1.6253 NaN NaN 36.8590 8.9987 1.2276 18.7919
2 2021-01-06 1.5824 1.9558 6.5119 1.5640 1.0821 7.9653 NaN 26.145 7.4393 ... 4.8720 90.8175 10.0653 1.6246 NaN NaN 36.9210 9.0554 1.2338 18.5123
3 2021-01-05 1.5927 1.9558 6.5517 1.5651 1.0803 7.9315 NaN 26.227 7.4387 ... 4.8721 91.6715 10.0570 1.6180 NaN NaN 36.7760 9.0694 1.2271 18.4194
4 2021-01-04 1.5928 1.9558 6.3241 1.5621 1.0811 7.9484 NaN 26.141 7.4379 ... 4.8713 90.3420 10.0895 1.6198 NaN NaN 36.7280 9.0579 1.2296 17.9214

5 rows × 41 columns

Data Cleaning

Main focus is on the exchange rate between the euro and the American dollar.

In [66]:
exchange_rates.rename(columns={'[US dollar ]':'US_dollar','[Indonesian rupiah ]':'ID_rupiah',
                      'Period\\Unit:': 'Time'}, 
                      inplace=True)

exchange_rates['Time'] = pd.to_datetime(exchange_rates[
    'Time'])

exchange_rates.sort_values('Time',inplace=True)
exchange_rates.reset_index(drop=True, inplace=True)
In [67]:
euro_to_dollar = exchange_rates[['Time','US_dollar']]

euro_to_dollar['US_dollar'].value_counts()
Out[67]:
-         62
1.2276     9
1.1215     8
1.1305     7
1.3373     6
          ..
1.4734     1
0.9485     1
1.3756     1
1.5705     1
1.0814     1
Name: US_dollar, Length: 3528, dtype: int64
In [68]:
# Drop all the rows where the - character appears in the US_dollar column

euro_to_dollar=euro_to_dollar[euro_to_dollar['US_dollar'] != '-']

euro_to_dollar['US_dollar']= euro_to_dollar['US_dollar'].astype(float)
euro_to_dollar.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 5637 entries, 0 to 5698
Data columns (total 2 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   Time       5637 non-null   datetime64[ns]
 1   US_dollar  5637 non-null   float64       
dtypes: datetime64[ns](1), float64(1)
memory usage: 132.1 KB
In [69]:
euro_to_dollar.head()
Out[69]:
Time US_dollar
0 1999-01-04 1.1789
1 1999-01-05 1.1790
2 1999-01-06 1.1743
3 1999-01-07 1.1632
4 1999-01-08 1.1659
In [70]:
# Visualizing the evolution of the euto-dollar exchange rate
In [71]:
plt.plot(euro_to_dollar['Time'], euro_to_dollar['US_dollar'])
plt.show()

Rolling Mean

  • As we can see, the chart above shows a lot of wiggle (coming from daily average being condensed).
  • To smoothen the line chart, we are going to plot a moving average (rolling mean) of Euro against US_dollar for 30 days rolling windows (or moving window).This is to smoothen the curve.
In [72]:
euro_to_dollar['rolling_mean'] = euro_to_dollar['US_dollar']. rolling(30). mean()
euro_to_dollar
Out[72]:
Time US_dollar rolling_mean
0 1999-01-04 1.1789 NaN
1 1999-01-05 1.1790 NaN
2 1999-01-06 1.1743 NaN
3 1999-01-07 1.1632 NaN
4 1999-01-08 1.1659 NaN
... ... ... ...
5694 2021-01-04 1.2296 1.211170
5695 2021-01-05 1.2271 1.212530
5696 2021-01-06 1.2338 1.213987
5697 2021-01-07 1.2276 1.215357
5698 2021-01-08 1.2250 1.216557

5637 rows × 3 columns

In [73]:
# Plotting different values of moving windows
In [74]:
plt.style.use('default')
plt.figure(figsize= (9,6))
plt.subplot(3,2,1)
plt.plot(euro_to_dollar['Time'], euro_to_dollar['US_dollar'])
plt.title('Original values', weight='bold')

for i, rolling_mean in zip([2,3,4,5,6], [7,30, 50, 100, 365]):
    plt.subplot(3,2,i)
    plt.plot(euro_to_dollar['Time'], euro_to_dollar['US_dollar']. rolling(rolling_mean).mean())
    plt.title('Rolling Window:' + str(rolling_mean), weight='bold')  
    
plt.tight_layout()  # Auto-adjust the padding between subplots
plt.show()

Storytelling Data Visualization

Let's see how certain currency like Indonesian Rupiah which closely tied to USD affected during the sub-prime crisis leading to Globaal Financial Crisis (GFC) back in 2007-2009. So, I am going to use the dataset to find out how Indonesian Rupiah fare against EUR and USD particularly during the 2007-2009. We may refer to the background story here and from this source.

In [75]:
# We need to rename the Indonesian Rupiah column
In [76]:
euro_to_idr= exchange_rates[['Time', 'ID_rupiah']]
euro_to_idr.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5699 entries, 0 to 5698
Data columns (total 2 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   Time       5699 non-null   datetime64[ns]
 1   ID_rupiah  5699 non-null   object        
dtypes: datetime64[ns](1), object(1)
memory usage: 89.2+ KB
In [77]:
euro_to_idr['ID_rupiah'].value_counts()
Out[77]:
-           62
12022.32     2
15149.99     2
12154.26     2
11013.16     2
            ..
9809.19      1
12850.74     1
8484.87      1
14565.23     1
15522.75     1
Name: ID_rupiah, Length: 5605, dtype: int64
In [78]:
# Removing the 'IDR_rupiah'with value '-' 

euro_to_idr=euro_to_idr. loc[euro_to_idr['ID_rupiah']!='-']
euro_to_idr['ID_rupiah'].value_counts()
Out[78]:
6755.76     2
9652.66     2
12315.00    2
12022.32    2
15177.38    2
           ..
16085.51    1
15939.20    1
9809.19     1
12850.74    1
15522.75    1
Name: ID_rupiah, Length: 5604, dtype: int64
In [79]:
#converting to float

euro_to_idr['ID_rupiah']=euro_to_idr['ID_rupiah'].astype(float)
euro_to_idr.info()
<class 'pandas.core.frame.DataFrame'>
Int64Index: 5637 entries, 0 to 5698
Data columns (total 2 columns):
 #   Column     Non-Null Count  Dtype         
---  ------     --------------  -----         
 0   Time       5637 non-null   datetime64[ns]
 1   ID_rupiah  5637 non-null   float64       
dtypes: datetime64[ns](1), float64(1)
memory usage: 132.1 KB
In [80]:
# Plotting idr rolling mean

euro_to_idr['rolling_mean']= euro_to_idr['ID_rupiah'].rolling(30).mean()
euro_to_idr.head()
Out[80]:
Time ID_rupiah rolling_mean
0 1999-01-04 9433.61 NaN
1 1999-01-05 9314.51 NaN
2 1999-01-06 9337.68 NaN
3 1999-01-07 9218.77 NaN
4 1999-01-08 9321.63 NaN
In [81]:
idr_before_GFC=euro_to_idr.copy()[(euro_to_idr['Time'].dt.year>=2005) & (euro_to_idr['Time'].dt.year<=2008)]
idr_during_GFC = euro_to_idr.copy()[(euro_to_idr['Time'].dt.year>=2008)&(euro_to_idr['Time'].dt.year<=2010)]
idr_after_GFC=euro_to_idr.copy()[(euro_to_idr['Time'].dt.year>=2010)]
In [82]:
euro_to_idr['rolling_mean'] = euro_to_idr['ID_rupiah'].rolling(30).mean()

plt.plot(euro_to_idr['Time'], euro_to_idr['rolling_mean'])
plt.show()
In [83]:
import matplotlib.style as style
from matplotlib.patches import Circle
from datetime import datetime
style.use('fivethirtyeight')

fig, ax = plt.subplots(figsize=(8,5))
ax.plot(idr_before_GFC['Time'], idr_before_GFC['rolling_mean'], lw=2, color='b')

ax.plot(idr_during_GFC['Time'], idr_during_GFC['rolling_mean'], lw=2, color='red', label= 'Indonesia GFC Period')

ax.plot(idr_after_GFC['Time'], idr_after_GFC['rolling_mean'], lw=2, color='b')

# print('ax:', ax.get_xticks()); to determine coordinate for text on the span
# print('ax:', ax.get_yticks()); to determine coordinatte for title
fig.set_dpi(80)
ax.grid(alpha=0.2)
ax.text((datetime(2005,1,1)), 18000,' Indonesian Rupiah Against Euro', size=18, weight='bold')
ax.text((datetime(2005,1,1)),17500, 'During Global Financial Crisis (GFC) 2007-2009', size= 16)
ax.text(733030,15800, 'Indonesia\nGFC-Period', size=10)
ax.set_xlabel('Years', fontsize= 12)
ax.set_ylabel('Indonesian IDR against Euro', fontsize = 12)
ax.axvspan(xmin=733042, xmax= 733773, ymax = 0.7, alpha=0.3, color='grey')
plt.show()
  • IDR was depreciated above IDR 13,000 per Euro in the year before 2008 to become IDR 15,500 during the peak of GFC
  • In the year of 2011 to 2012 IDR strengthened again at the same level as in the years prior to GFC
  • However, in the year of 2013 to 2016, IDR depreciated again. What happened?
In [84]:
fig, ax = plt.subplots(figsize=(8,5), dpi=80)
ax.plot(idr_before_GFC['Time'], idr_before_GFC['rolling_mean'], lw=2, color='b')

ax.plot(idr_during_GFC['Time'], idr_during_GFC['rolling_mean'], lw=2, color='red', label= 'Indonesia GFC Period')
ax.text(733030, 17500, 'Impact of GFC to IDR against Euro', fontsize=15, weight='bold')
ax.plot(idr_after_GFC['Time'], idr_after_GFC['rolling_mean'], lw=2, color='b')
ax.grid(alpha=0.2)
ax.set_xlabel('Years', fontsize= 12)
ax.set_ylabel('Indonesian IDR against Euro', fontsize = 12)
ax.text(733030,15800, 'Indonesia\nGFC-Period', size=10)
ax.axvspan(xmin=733042, xmax= 733773, ymax = 0.7, alpha=0.2, color='grey')
ax.text(735100, 16770, 'What happen\nin this period?', size = 10)
ax.axvspan(xmin=735150, xmax=735950, ymax=0.84, alpha=0.3, color='red')
plt.show()
  • Indonesian Rupiah currency is pegged to US Dollar. The meaning of pegging can be learned from here. Therefore,as we try to learn the effect of Euro fluctuation against Indonesian Rupiah, we should also take into consideration the fluctuation of Euro against US Dollar.
  • In the following chart, we will see how the fluctuation of Euro affect IDR and USD especially during 2014 to 2016 the period when Euro was weakening against USD because the FED started to tighten the Quantitative Easing (QE), with the potential of increasing US Interest Rate.
In [85]:
idr_2014to2016=euro_to_idr.copy()[(euro_to_idr['Time'].dt.year>=2014)&(euro_to_idr['Time'].dt.year<=2016)]
dollar_2014to2016=euro_to_dollar.copy()[(euro_to_dollar['Time'].dt.year>=2014)&(euro_to_dollar['Time'].dt.year<=2016)]

plt.figure(figsize=(16,10),dpi=70)
ax1=plt.subplot(2,2,1)
ax2=plt.subplot(2,2,2)
axes=[ax1,ax2]
for ax in axes:
    ax.grid(alpha=0.2)
    ax.set_xlabel('Years', fontsize=12)

ax1.plot(idr_2014to2016['Time'],idr_2014to2016['rolling_mean'], label="IDR", color='blue', lw=2)
ax1.set_xticklabels(['','2014','', '','', '2015','','', '', '2016'])
ax1.text(735599, 16800,'IDR Response against Euro',fontsize=14, weight='bold', alpha=0.6 )
ax1.text(735599, 16600, 'Mirror USD responses', weight= 'bold', alpha=0.6)
# print('ax1:', ax1.get_xticks())

ax2.plot(dollar_2014to2016['Time'],dollar_2014to2016['rolling_mean'], label='USD', color='red', lw=2)
ax2.set_xticklabels(['','2014', '','','', '2015','', '','', '2016'])
ax2.text(735719, 1.42, 'USD Strengthening', fontsize=14, weight='bold', alpha=0.6)
ax2.text(735719, 1.40, 'With Tightening of QE', fontsize=12, weight='bold', alpha=0.6)
ax1.text(735150, 13200, '©DATAQUEST',color='k',size = 14, weight='bold', alpha=0.7)
ax2.text(735750,0.96, 'Source:European Central Bank',color= 'k', size=14, weight='bold', alpha=0.7)
# print('ax2:', ax2.get_xticks)
plt.show()

Conclusion

  • The fluctuation of IDR looks similar with the fluctuation of USD against Euro. Althoungh during 2015, Indonesian Rupiah showed a weakening value.
  • Upon further investigation, this weakening was due to the increase of foreign debt In Indonesia with mainly US Dollar denomination
  • From the chart above, with the pegging of IDR to USD the two currency would response in similar patters against the fluctuation of the Euro.