R/xtstopanel.R

In regisely/macrodata: Search and download cross country data from Quandl

# Written by Regis A. Ely (regisaely@gmail.com)

# This function takes a list of xts objects as argument and converts it
# into a panel data. It returns a data.table object.

xtstopanel <- function(list) {
  N <- length(list)

  # Drop countries not in common for all variables.
  names <- lapply(list, names)
  names <- Reduce(intersect, names)
  if (length(names) == 0) stop("Column names are not equal across variables. You need to rename them mannually according to country name.")
  if (any(length(names) != unlist(lapply(list, ncol)))) {
    warning("Some countries will be dropped, since there are countries with no values for the selected variables.")
    for (n in 1:N) {
      list[[n]] <- list[[n]][, names]
    }
  }
  
  # Create names for variables.
  varnames <- numeric(N)
  for (j in 1:N) {
    if (is.null(names(list)[j])) {
      varnames[j] <- paste0("V", j)
    } else {
      varnames[j] <- names(list)[j]
    }
  }

  if (N == 1) {

    # Use the data.table format
    x <- as.data.table(list[[1]])

    # Rename variables.
    colnames(x) <- gsub("[.]", "", colnames(x))
    colnames(x) <- c("time", paste0(varnames, colnames(x)[2:ncol(x)]))

    # Reshape data.
    data <- reshape(x, direction = "long", idvar = "time",
                    timevar = "country", varying = 2:ncol(x))

    # Create year variable and delete time.
    data <- cbind(year = as.numeric(substring(data$time, 1, 4)), data)
    data[, time := NULL]

    # Order by country.
    setcolorder(data, c("country", setdiff(names(data), "country")))
    data <- data[order(country)]
    data
  } else {

    # Merge first two variables.
    x <- lapply(list[[1]], as.data.table)
    lapply(x, setnames, 2, varnames[1])
    y <- lapply(list[[2]], as.data.table)
    lapply(y, setnames, 2, varnames[2])
    data <- mapply(merge, x, y, by = "index", all = TRUE,
                   SIMPLIFY = FALSE)

    # Merge other variables.
    if (N > 2) {
      for (i in 3:N) {
        z <- lapply(list[[i]], as.data.table)
        lapply(z, setnames, 2, varnames[i])
        data <- mapply(merge, data, z, by = "index", all = TRUE,
                       SIMPLIFY = FALSE)
      }
    }

    # Join data and rename variables.
    v.names <-  as.vector(outer(varnames, names(data), paste, sep = "."))
    data <- join_all(data, by = "index")
    colnames(data) <- c("time", v.names)

    # Reshape data.
    data <- reshape(data, direction = "long", idvar = "time",
                    timevar = "country", varying = 2:ncol(data))

    # Create year variable and delete time. 
    data <- cbind(year = as.numeric(substring(data$time, 1, 4)), data)
    data[, time := NULL]

    # Order by country.
    setcolorder(data, c("country", setdiff(names(data), "country")))
    data <- data[order(country)]
    data
  }
}