Exercises for Chapter 13

Exercise 1: Importing Adjacency Matrices in R/igraph

The file adjmatrix.txt in the repository for these exercises contains the directed trade network for 2014 as an adjacency matrix. Take a look at the file and try to understand its structure. Since some networks are still represented in this format, let us see how we can import it to R and create an igraph network. The read.table() function is useful here. There are some important points to consider: The matrix rows and columns are labeled, so you need to set the appropriate parameters such that the function recognizes these labels correctly. Also, R does not allow column names that start with numbers, so the function attempts to fix this by preceding them with an X. This should be disabled.

Once the matrix is imported, use the appropriate function from igraph to create a network from it. The function accepts an R matrix (use as.matrix() to convert it), and make sure to set the mode of the network (directed/undirected) correctly. Also, since the matrix entries contain the bilateral trade volumes, you need to tell igraph to treat them as weights.

Exercise 2: A Directed Network in igraph

For this exercise, make sure to import the trade and vdem datasets again, using the code from the book chapter:

trade <- read.csv(file.path("ch13", "trade.csv.gz"), na.strings = "-9")
trade <- subset(trade, !is.na(smoothtotrade) & smoothtotrade > 0)
trade <- subset(trade, year == 2014 & smoothtotrade >= 100)
vdem <- read.csv(file.path("ch13", "vdem.csv"))
vdem <- subset(vdem, year == 2014 & !is.na(cowcode))
vdem <- subset(vdem, select = c("cowcode", "country_name", "year", "v2x_polyarchy", "e_regiongeo"))
trade <- subset(trade, ccode1 %in% vdem$cowcode & ccode2 %in% vdem$cowcode)

In the chapter, we created an undirected network from the datasets. In this exercise, we want to preserve the original structure of the data, and work with directed dyads. Given the original trade dataset, how can we modify it such that igraph can create a directed network from it? Note that this requires every pair of countries to be present twice in the data, once for each direction. Hint: The trick that we used to do the same for PostgreSQL may be helpful here.

For the following two exercises, we use again the database networkdata. We import the trade dataset again and convert it to a proper directed network, using the code from the book chapter:

library(RPostgres)

db <- dbConnect(Postgres(),
  dbname = "networkdata",
  user = "postgres",
  password = "pgpasswd"
)

trade <- read.csv(file.path("ch13", "trade.csv.gz"), na.strings = "-9")
dbWriteTable(db, "trade", trade)
dbExecute(db, "INSERT INTO trade (ccode1, ccode2, year, smoothflow1) SELECT ccode2, ccode1, year, smoothflow2 FROM trade")

## [1] 798157

dbExecute(db, "ALTER TABLE trade DROP COLUMN smoothflow2, DROP COLUMN smoothtotrade")

## [1] 0

dbExecute(db, "DELETE FROM trade WHERE smoothflow1 IS NULL OR smoothflow1 = 0")

## [1] 793173

Exercise 3: Creating a Balance of Trade Dataset in PostgreSQL

In this exercise, our aim is to aggregate the trade data such that we get a dataset with annual estimates of the trade balance for individual countries, that is, the difference between the value of exports and the value of imports. For your SQL query, it is useful to rely on subqueries (temporary tables) defined with the WITH keyword (see the chapter). One of these subqueries should produce annual values of imports for the countries in the sample, the other subquery should do the same for exports. In the main part of the query, you can then join these temporary tables to produce the final dataset.

Exercise 4: Working with Directed Networks in PostgreSQL

In this exercise, we use PostgreSQL to do some basic checks on our network, and to convert it to an undirected format. Recall that our database treats the imported datasets as simple tables, without “knowing” that they contain network data. This is why unlike the igraph library, it does not perform basic validity checks on the data. Let us do a simple check ourselves. How can you test whether the network contains self-referencing links, i.e. links from one node to itself? Next, can you modify this approach to export a simple (longitudinal) directed network from the database (for simplicity, we omit the trade volume and only focus on pairs of countries that are connected).