import pandas as pd
import networkx as nx
The Excel file was generated as a query from the BIS - Bank of International Settlements
df = pd.read_excel("exposures/WEBSTATS_CIBL_1405690253612.xlsx", "Sheet1", skiprows=15)
import re
df = df[[col for col in df.columns.values if not re.match('\d\d\d\d-Q\d\.\d', col)]]
df.head()
Counterparty location | Reporting country | 2013-Q4 | 2013-Q3 | 2013-Q2 | 2013-Q1 | 2012-Q4 | 2012-Q3 | 2012-Q2 | 2012-Q1 | ... | 2010-Q2 | 2010-Q1 | 2009-Q4 | 2009-Q3 | 2009-Q2 | 2009-Q1 | 2008-Q4 | 2008-Q3 | 2008-Q2 | 2008-Q1 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 3P:All countries | 5A:All reporting countries | 24972754 | 25449148 | 25040808 | 25250972 | 25454981 | 25463002 | 25119987 | 25793807 | ... | 24702265 | 25664038 | 25726511 | 26641935 | 25901684 | 24964104 | 24964640 | 28173608 | 29808497 | 30407569 |
1 | NaN | 5E:European reporting countries | 16382374 | 16735088 | 16424728 | 16413240 | 16628204 | 16848608 | 16816716 | 17544823 | ... | 17515280 | 18533064 | 18781079 | 19469503 | 19432312 | 18779947 | 19843734 | 22765116 | 24214407 | 24727384 |
2 | NaN | 5G:Non-European countries | 8590380 | 8714060 | 8616080 | 8837732 | 8826777 | 8614394 | 8303271 | 8248984 | ... | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
3 | NaN | AT:Austria | 421402 | 435789 | 429731 | 415622 | 426664 | 428554 | 425739 | 451961 | ... | 464906 | 516150 | 523550 | 511946 | 491386 | 467771 | 492502 | 553023 | 586806 | 603162 |
4 | NaN | AU:Australia | 738742 | 703686 | 747462 | 722848 | 732388 | 693763 | 700238 | 686074 | ... | 515667 | 511566 | 514499 | 495564 | 477735 | 400309 | 425308 | 464515 | 480359 | 484506 |
5 rows × 26 columns
def clean_foreign_claims(foreign_claims):
foreign_claims.loc[:,'Counterparty location'] = foreign_claims.loc[:,'Counterparty location'].fillna(method='ffill')
foreign_claim_cp_blacklist = ['3P:All countries']
foreign_claim_rpt_blacklist = [u'5A:All reporting countries',
u'5E:European reporting countries',
u'5G:Non-European countries']
df1 = foreign_claims.ix[~foreign_claims.loc[:, 'Counterparty location'].isin(foreign_claim_cp_blacklist)]
df2 = df1.ix[~df1.loc[:, 'Reporting country'].isin(foreign_claim_rpt_blacklist)]
return df2
foreign_claims = clean_foreign_claims(df)
foreign_claims.head()
Counterparty location | Reporting country | 2013-Q4 | 2013-Q3 | 2013-Q2 | 2013-Q1 | 2012-Q4 | 2012-Q3 | 2012-Q2 | 2012-Q1 | ... | 2010-Q2 | 2010-Q1 | 2009-Q4 | 2009-Q3 | 2009-Q2 | 2009-Q1 | 2008-Q4 | 2008-Q3 | 2008-Q2 | 2008-Q1 | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
27 | AT:Austria | AU:Australia | 84 | 65 | 176 | 363 | 279 | 237 | 180 | 269 | ... | 275 | 212 | 271 | 253 | 265 | 348 | 1191 | 585 | 272 | 769 |
28 | AT:Austria | BE:Belgium | 1629 | 1093 | 1062 | 799 | 730 | 960 | 1010 | 1249 | ... | 2749 | 2918 | 3068 | 4510 | 5117 | 5426 | 5117 | 5554 | 7369 | 6814 |
29 | AT:Austria | CA:Canada | 362 | 810 | 1244 | 1428 | 1096 | 1562 | 1974 | 1427 | ... | 1081 | 1017 | 891 | 770 | 1113 | 1067 | 1358 | 1615 | 3077 | 2484 |
30 | AT:Austria | CH:Switzerland | 16965 | 8905 | 7171 | 8359 | 8570 | 8464 | 8780 | 10090 | ... | 10878 | 12136 | 11056 | 13460 | 12327 | 16220 | 13724 | 18826 | 18139 | 19114 |
31 | AT:Austria | CL:Chile | 2 | 3 | 3 | 3 | 1 | 0 | 0 | 0 | ... | 0 | 0 | 0 | 2 | 0 | 1 | 2 | 0 | 0 | 0 |
5 rows × 26 columns
g = nx.DiGraph()
for i, r in foreign_claims.iterrows():
g.add_edge(r['Reporting country'], r['Counterparty location'], attr_dict={'weight': float(r['2013-Q4'])})
The matrix shows a very strong core-periphery component. This is also in part due to the limitations of the data collection mechanism, since not every exposure is being collected.
Is is hard to make sense of it purely from the available visual representation below. Best to visualize and explore in another setup, like the program Gephi.
%matplotlib inline
nx.draw_graphviz(g) #, pos=nx.spring_layout(g) )
#
nx.draw_circular(g)
We can export the graph for analysis with other programs:
nx.write_gexf(g, "2013q4.gexf")