Unnest IMF DOT Data into a data frame

Question

I used the imfr package to download IMF Direction of Trade (DOT) data for specific countries for the time period 1970-2020. I'm able to extract the data so that it looks like this (Albania = 'AL' as the example here.[data image][1] [1]: https://i.stack.imgur.com/oXVOD.png

How do I unnnest the 'Obs' so that I have a data frame of annual bilateral trade data between Albania and the Counterpart_Areas?

Thank you!

dput(head(EXP_AL1970_2020_data)) structure(list(@FREQ = c("A", "A", "A", "A", "A", "A"), @REF_AREA = c("AL", "AL", "AL", "AL", "AL", "AL"), @INDICATOR = c("TXG_FOB_USD", "TXG_FOB_USD", "TXG_FOB_USD", "TXG_FOB_USD", "TXG_FOB_USD", "TXG_FOB_USD" ), @COUNTERPART_AREA = c("EC", "SO", "MX", "BY", "HR", "ID" ), @UNIT_MULT = c("6", "6", "6", "6", "6", "6"), @TIME_FORMAT = c("P1Y", "P1Y", "P1Y", "P1Y", "P1Y", "P1Y"), Obs = list(list(@TIME_PERIOD = "2013", @OBS_VALUE = "0.01998"), list(@TIME_PERIOD = "2017", @OBS_VALUE = "1.181912"), structure(list(@TIME_PERIOD = c("1987", "1988", "1989", "1990", "1994", "2004", "2005", "2006", "2008", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"), @OBS_VALUE = c("0.0049999999999999", "0.131", "0.00199999999999988", "0.037", "0.348751188924508", "0.000396", "0.000156", "0.008208", "0.00114", "0.047808", "0.013344", "0.02184", "0.015036", "0.184554", "0.008467", "2.33989", "0.532913", "0.582315"), @OBS_STATUS = c("e", "e", "e", "e", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 18L)), structure(list( @TIME_PERIOD = c("2005", "2010", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"), @OBS_VALUE = c("0.002436", "0.01134", "1.838688", "0.294108", "0.071556", "0.00784", "0.72113", "0.081031", "0.033331", "0.052027")), class = "data.frame", row.names = c(NA, 10L)), structure(list(@TIME_PERIOD = c("1993", "1994", "1995", "1996", "1997", "1998", "1999", "2000", "2001", "2002", "2003", "2004", "2005", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"), @OBS_VALUE = c("1.23061849357012", "0.667911367879663", "2.13921626796839", "1.43255073617191", "5.9320331581053", "1.24208895773401", "0.384921021590075", "1.480835", "0.04974", "0.159391", "0.316497", "0.402769", "0.182419", "2.232246", "0.984952", "2.240131", "1.235558", "2.422898", "3.061001", "3.770467", "3.597173", "3.251577", "3.868998", "7.592541", "7.552596", "9.463396", "11.18564", "17.738573")), class = "data.frame", row.names = c(NA, 28L)), structure(list(@TIME_PERIOD = c("1983", "1987", "1990", "1991", "1992", "1993", "2000", "2004", "2006", "2007", "2008", "2009", "2010", "2011", "2012", "2013", "2014", "2015", "2016", "2017", "2018", "2019", "2020"), @OBS_VALUE = c("0.0909090909090909", "2.18181818181818", "0.0316590909090909", "0.00013727272727264", "0.0165872727272728", "3.55272504592774", "0.023424", "0.00948", "0.002892", "0.000696", "0.097872", "0.021216", "0.072492", "0.173352", "0.412392", "0.49854", "0.40212", "0.068712", "0.04928", "0.000813", "0.00065", "0.108844", "0.006609"), @OBS_STATUS = c("e", "e", "e", "e", "e", NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 23L)))), row.names = c(NA, 6L), class = "data.frame")

Answer 1

Some elements in the Obs columns are list. We may convert it to tibble and then unnest

library(dplyr)
library(purrr)
library(tibble)
library(tidyr)
EXP_AL1970_2020_data %>%
    mutate(Obs = map(Obs, ~ if(is.list(.x)) as_tibble(.x) else .x)) %>%
    unnest(Obs)

-output

# A tibble: 81 × 9
   `@FREQ` `@REF_AREA` `@INDICATOR` `@COUNTERPART_AREA` `@UNIT_MULT` `@TIME_FORMAT` `@TIME_PERIOD` `@OBS_VALUE`        `@OBS_STATUS`
   <chr>   <chr>       <chr>        <chr>               <chr>        <chr>          <chr>          <chr>               <chr>        
 1 A       AL          TXG_FOB_USD  EC                  6            P1Y            2013           0.01998             <NA>         
 2 A       AL          TXG_FOB_USD  SO                  6            P1Y            2017           1.181912            <NA>         
 3 A       AL          TXG_FOB_USD  MX                  6            P1Y            1987           0.0049999999999999  e            
 4 A       AL          TXG_FOB_USD  MX                  6            P1Y            1988           0.131               e            
 5 A       AL          TXG_FOB_USD  MX                  6            P1Y            1989           0.00199999999999988 e            
 6 A       AL          TXG_FOB_USD  MX                  6            P1Y            1990           0.037               e            
 7 A       AL          TXG_FOB_USD  MX                  6            P1Y            1994           0.348751188924508   <NA>         
 8 A       AL          TXG_FOB_USD  MX                  6            P1Y            2004           0.000396            <NA>         
 9 A       AL          TXG_FOB_USD  MX                  6            P1Y            2005           0.000156            <NA>         
10 A       AL          TXG_FOB_USD  MX                  6            P1Y            2006           0.008208            <NA>         
# … with 71 more rows