Ambiguous values for the variable 'time' in bop_iip6_q

[rideofyourlife]

Hi.

It seems that the variable 'time' has unambiguous values assigned to every quarter. By this I mean every quarter has a value of "XXXX-01-01" instead of c("XXXX-01-01", "XXXX-04-01", "XXXX-07-01", "XXXX-10-01"). I wrote a code:

df <- get_eurostat("bop_iip6_q",
                   filters = list(stk_flow = c("A_LE", "L_LE", "N_LE"),
                                  sector10 = "S1",
                                  partner = "WRL_REST",
                                  currency = "MIO_EUR",
                                  sectpart = "S1",
                                  bop_item = c("FA", "FA__D__F", "FA__P__F", "FA__O__F", "FA__F__F7", "FA__R__F", "FA_FNED", "FA_FGED"))
                   ) %>%
  filter (time >= as.Date("2004-01-01"),
          !is.na(values),
          geo %in% c("EA19", "BE", "BG", "CZ", "DK", "DE", "IE", "EL", "ES", "FR", "IT", "HU", "NL", "AT", "PL", "PT", "RO", "SK", "FI", "SE", "NO", "CH", "UK", "TR"),
          freq == "Q") %>%
  select (-freq, -sector10, -partner, -currency, -sectpart) %>%
  mutate (values = values / 1000)

This results in four values to every observation. I think this needs to be fixed.

[pitkant]

@rideofyourlife Thank you for reporting. It seems that the internal function for time_format = "date" or time_format = "date_last" are not working properly and we will aim to investigate what is the root cause here and fix this quickly.

[pitkant]

Ok, the reason seems to be this:

The raw table has a freq column that indicates that all times are in the quarterly format "YYYY-QN" but actually there are also yearly rows:

# A tibble: 257,040 × 10
   freq  currency bop_item sector10 sectpart stk_flow partner  geo       time    values
   <chr> <chr>    <chr>    <chr>    <chr>    <chr>    <chr>    <chr>     <chr>    <dbl>
 1 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1991        NA
 2 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1991-Q1     NA
 3 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1991-Q2     NA
 4 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1991-Q3     NA
 5 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1991-Q4     NA
 6 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1992        NA
 7 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1992-Q1     NA
 8 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1992-Q2     NA
 9 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1992-Q3     NA
10 Q     MIO_EUR  FA       S1       S1       A_LE     WRL_REST EU27_2020 1992-Q4     NA
# ℹ 257,030 more rows

Therefore adding select_time = "Q" parameter to get_eurostat() won't help here.

What you could do, as a temporary solution, is this:

Return the time column in raw, unprocessed format:

df <- get_eurostat("bop_iip6_q",
                   time_format = "raw",
                   filters = list(stk_flow = c("A_LE", "L_LE", "N_LE"),
                                  sector10 = "S1",
                                  partner = "WRL_REST",
                                  currency = "MIO_EUR",
                                  sectpart = "S1",
                                  bop_item = c("FA", "FA__D__F", "FA__P__F", "FA__O__F", "FA__F__F7", "FA__R__F", "FA_FNED", "FA_FGED"))
)

Remove rows that are not quarterly:

# Factors into characters
df$time <- as.character(df$time)

df <- df %>% 
    filter(nchar(time) != 4)

# Back to factors
df$time <- as.factor(df$time)

Use eurotime2date function that would have been used:

df$time <- eurostat::eurotime2date(df$time)

Then business as usual:

df <- df %>%
  filter (time >= as.Date("2004-01-01"),
          !is.na(values),
          geo %in% c("EA19", "BE", "BG", "CZ", "DK", "DE", "IE", "EL", "ES", "FR", "IT", "HU", "NL", "AT", "PL", "PT", "RO", "SK", "FI", "SE", "NO", "CH", "UK", "TR"),
          freq == "Q") %>%
  select (-freq, -sector10, -partner, -currency, -sectpart) %>%
  mutate (values = values / 1000)

[pitkant]

Note to self: This problem is due to the difference between how freq data is represented in the different sources: in tsv files it is explicitly states whereas in json files it should be deduced from the format of the date.

Example tsv with different time periods: https://ec.europa.eu/eurostat/api/dissemination/sdmx/2.1/data/avia_gonc?format=TSV

Example json: https://ec.europa.eu/eurostat/api/dissemination/statistics/1.0/data/avia_gonc?untilTimePeriod=2010&format=JSON&lang=en

Fortunately datasets with multiple different time frequencies are quite rare but this is an urgent issue to fix.