Snippet to create ICP series with WHO NHE figures at PPPs for AIC and GDP

make_icp_data.R

require(dplyr)
require(janitor)
require(reshape2)
require(countrycode)


icp_raw = read.csv("ext_data/WorldBank_icp_2011.csv", stringsAsFactors = F) %>%
  filter(
     Country.Code != ''   # remove blank lines and notes at end of file 
  ) %>% 
  transmute(
    year = 2011,
    # named to be consistent with OECD variable
    location = Country.Code,  
    country_name = Country.Name,
    # remove leading numbers for expenditure categories
    category = str_replace(Classification.Name,'^\\d+\\. ',''), 
    series = Series.Name,
    value = Value
  )

icp_country_names = icp_raw %>%
  select(location, country_name) %>%
  unique()

# Get expenditures in NCU and convert values from billions to millions
icp_exp = icp_raw %>%
  filter(
    series == 'EXPENDITURES (National currency units, billions)'
  ) %>%
  transmute(
    year,
    location,
    category,
    # billions -> millions
    value_ncu = value * 10^3 
  )

# read in World Bank's 2011 NHE estimates in millions of NCU
who_est = read.csv("who_2011_nhe_estimates.csv",stringsAsFactors = F) %>%
  transmute(
    year = 2011,
    location = countrycode(Countries,"country.name","iso3c"),
    category = 'WHO total health expenditures',
    value_ncu = value_2011
  ) %>%
  filter(
    location != 'LBR' # remove liberia -- bad data
  )


# calculate NCU to real per capita multiplier using relationship between AIC and GDP values
icp_mult = icp_raw %>%
  filter( 
    category %in% c(
      'Gross domestic product',
      'Actual individual consumption'
    ) 
  ) %>%
  dcast( location + year + category ~ series ) %>%
  clean_names() %>%
  transmute(
    year,
    location,
    reference_ppp = case_when(
      category == 'Actual individual consumption' ~ 'aic',
      category == 'Gross domestic product' ~ 'gdp'
    ),
    # calculate multiplier assuming NCU values are in millions
    multiplier = 
      real_expenditures_per_capita_us / 
      (expenditures_national_currency_units_billions * 10^3) 
  )

# extract per capita volume index rows
#    like their "real expenditures per capita' series these are 
#    adjusted by the category specific PPPs!
icp_vol = icp_raw %>%
  filter(
    series == 'INDEX OF REAL EXPENDITURES PER CAPITA (World=100)'
  ) %>%
  transmute(
    year,
    location,
    measure = 'volume_index',
    category,
    value
  )

# get reference PPPs (AIC and GDP)
icp_ppp_refs = icp_raw %>%
  filter(
    series == 'PPPs (US$=1)',
    category %in% c(
      'Gross domestic product',
      'Actual individual consumption'
    ) 
  ) %>%
  transmute(
    year,
    location,
    ref_cat = case_when(
      category == 'Actual individual consumption' ~ 'aic',
      category == 'Gross domestic product' ~ 'gdp'
    ),
    ref_ppp = value
  )
  
# get prices relative to references: AIC and GDP
icp_rel_price = icp_raw %>%
  filter(
    series == 'PPPs (US$=1)'
  ) %>%
  merge(icp_ppp_refs,by=c('location','year'),all=T) %>%
  transmute(
    year,
    location,
    measure = paste('rel_price_',ref_cat,sep=''),
    category,
    value = ( value - ref_ppp ) / ref_ppp
  )

#
# 1) merge WHO and ICP expenditures in NCU 
# 2) bind rows volume series from ICP
# 3) bind rows of relative prices series (derived from ICP PPPs)
# 4) include measure variable specifying what kind of row it is
# 5) return ALL categories in wide format
icp_out_all = bind_rows(icp_exp,who_est) %>%
  merge(icp_mult,by=c('location','year')) %>%
  transmute(
    year,
    location,
    measure = paste('at_',reference_ppp,'_ppp',sep=''),
    category,
    value = value_ncu * multiplier
  ) %>%
  # merge rows for volume
  bind_rows(icp_vol,icp_rel_price) %>%
  dcast( location + year + measure ~ category ) %>%
  clean_names() %>%
  # merge country name rows
  merge(icp_country_names,by=c('location'))

# return key categories and in a specific order
icp_out_subset = icp_out_all %>%
  transmute(
    location,
    year,
    measure,
    country_name,
    gross_domestic_product,
    actual_individual_consumption,
    food_and_nonalcoholic_beverages,
    alcoholic_beverages_tobacco_and_narcotics,
    clothing_and_footwear,
    housing_water_electricity_gas_and_other_fuels,
    furnishings_household_equipment_and_maintenance,
    health,
    transport,
    communication,
    recreation_and_culture,
    education,
    restaurants_and_hotels,
    miscellaneous_goods_and_services,
    net_purchases_abroad,
    individual_consumption_expenditure_by_households,
    individual_consumption_expenditure_by_government,
    gross_fixed_capital_formation,
    machinery_and_equipment,
    construction,
    domestic_absorption,
    individual_consumption_expenditure_by_households_without_housing,
    who_total_health_expenditures
  )


# clean up objects we don't need outside of scope
rm(
  icp_raw,
  icp_mult,
  icp_exp,
  who_est,
  icp_country_names,
  icp_vol,
  icp_rel_price,
  icp_ppp_refs
)

# show table structure
str(icp_out_subset,vec.len=2)

# show available measures
unique(icp_out_subset$measure)