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.

Solution

We use the read.table() function to import the matrix, setting the headers and row.names parameters and disabling the checking of column names:

adjmat <- read.table(file.path("ex13", "adjacencymatrix.txt"), header = TRUE, row.names = 1, check.names = FALSE)

We can then create an igraph network from the matrix, seting the mode and weighted parameters accordingly:

library(igraph)

## 
## Attaching package: 'igraph'

## The following objects are masked from 'package:stats':
## 
##     decompose, spectrum

## The following object is masked from 'package:base':
## 
##     union

n <- graph_from_adjacency_matrix(as.matrix(adjmat), mode = "directed", weighted = T)

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.

Solution

To create a dataset with directed dyads, we need to append the trade data frame to itself, but making sure that the columns are reordered. In this solution, we do this with base R and the rbind() and subset() functions:

trade_directed <- rbind(trade, subset(trade, select = c(ccode2, ccode1, year, smoothflow2, smoothflow1, smoothtotrade)))

Since several columns are no longer required, we remove them from the dataset and retain only the country identifiers and the trade volume:

trade_directed <- subset(trade_directed, select = c(ccode1, ccode2, smoothflow1))

As a last step, we create the igraph network, but this time setting directed = T to get a directed network.

n <- graph_from_data_frame(trade_directed, directed = T, vertices = vdem)

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.

Solution

The following query produces a trade balance dataset with annual observations for countries. The two subqueries aggregate the data to compute the total values of imports per country/year (summing up smoothflow1 by ccode1 and year), and the same for exports (for this, we need to group by ccode2, which is the exporting country). The main part of the query then joins these two named subqueries and computes the trade balance.

balance_dataset <- dbGetQuery(db, "WITH
  imports AS (SELECT ccode1, year, sum(smoothflow1) AS imports_sum FROM trade GROUP BY ccode1, year),
  exports AS (SELECT ccode2, year, sum(smoothflow1) AS exports_sum FROM trade GROUP BY ccode2, year)
  SELECT ccode1, imports.year, (exports_sum - imports_sum) AS tradebalance FROM imports JOIN exports ON ccode1 = ccode2 and imports.year = exports.year")

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).

Solution

Self-referencing links are those where ccode1 and ccode2 are the same, and we count them with this query:

dbGetQuery(db, "SELECT count(*) FROM trade WHERE ccode1 = ccode2")

##   count
## 1     0

A similar comparison of the ccode1 and ccode2 identifiers helps us extract a directed network. These identifiers are unique, and each pair occurs twice in the table (once for each direction). By filtering out those pairs where one identifier is smaller than the other, we exclude the opposite direction.

undirected_network <- dbGetQuery(db, "SELECT ccode1, ccode2 FROM trade WHERE ccode1 < ccode2")