Hello my intention is to calculate the Government Revenue for all countries in the World converted from local currencies using a 5 Year Median of the Exchange Rate.

The data source for the Government Revenue as a % of GDP is from IMF website:https://www.imf.org/external/datamapper/rev@FPP/. For GDP in local currencies and Exchange Rate the data source is the “NY.GDP.MKTP.CN” and “PA.NUS.ATLS” from the WDI R package.

The main issue I am having is that the IMF spreadsheet is having large amount of empty values for countries (“no data”) and its formatted differently to the WDI data-frame containing the GDP and EX values. Also the data set goes much further back for the IMF spreadsheet(1810 verses 1960 for WDI).

I would like to combine the two data-frames into one with the formatting being similar to the WDI data-frame. After doing so I would like to multiply the Government Revenue Share with GDP in Local Currency Units to get the Government Revenue in local currencies.

Then after doing so multiply the Government Revenue LCU with the 5 Year Median Exchange Rate so that I can compare Government Revenues of the various countries in the World.

Below is the code.

Thanks,

Anon9001.

# Installing and loading required packages.
install.packages(“dplyr”)
install.packages(“WDI”)
install.packages(“readxl”)
library(dplyr)
library(WDI)
library(readxl)

# Getting GDP Local currencies and Exchange Rate from WDI.

 GDP_EX <- WDI(indicator=c("NY.GDP.MKTP.CN","PA.NUS.ATLS")) |>
   
# Removing values of GDP and EX for Regions. 
    filter(!(iso2c %in% c("XD","OE","V4","Z7","ZT","XF","XO","XP","Z4","XC","V3","XT","XU","4E","T4","V2","XN","T2","ZJ","XJ","T7","B8","ZQ","7E","1A","T5","ZF","T6","ZG","XQ","T3","XE","ZH","ZI","S3","F1","XL","XM","XY","S4","S2","V1","XG","8S","S1","XI","EU")))  |>

 na.omit(GDP_EX) |>

 # Taking 5 Year Median of Exchange Rates.

 mutate(halfdecade = floor(year/5)*5) |>
 mutate(EX_median = median(PA.NUS.ATLS), .by = c(country,halfdecade)) |>
 filter(n() == 5, .by = c(country, halfdecade)) 

# Importing IMF Excel sheet into R.

 GovRevofGDP <- read_excel("C:/Users/INSERT_NAME/Downloads/imf-dm-export-20240528.xls", 
    sheet = "rev")

WDI Dataframe
IMF Spreadsheet Dataframe