px() parses values in PX-files with equal signs (=) incorrectly

[trygu]

https://data.ssb.no/api/pxwebapi/v2-beta/tables/03013/data?lang=no&valueCodes[Konsumgrp]=TOTAL&valueCodes[ContentsCode]=KpiIndMnd&valueCodes[Tid]=2023M04&codelist[Konsumgrp]=vs_CoiCop2016niva1&outputformat=px

This is an API-call to the next version of PxWebApi. It doesn't like Norwegian characters in the VALUES fields it seems. Saving this to example.px, and running the example command in the documentation gives me this result:

> x <- px(input = "example.px")
Error: px object: values cannot contain quotation marks

> Sys.getlocale("LC_CTYPE")
[1] "nb_NO.UTF-8"

Looking at this function which I guess it's called when reading av PX-file:

pxmake/R/px_file.R

Line 208 in 4c57906

px_from_px_file <- function(path) {

There doesn't seem to be any explicit setting of encoding hints when reading a PX-file. (this is just based on a very quick look at the source code, so I might be wrong).

[johan-ejstrud]

Hey Trygve, thanks for your detailed issue, and for trying pxmake with the new version of PxWebApi.

Using URLs in px()

Before explaining the error, I just want mention that moments ago, I pushed a change so px() can accept an URL as input, without needing to download the file first.

I just tested it with your URL and it works:

# install.packages('pak')
pak::pak("StatisticsGreenland/pxmake")

library(pxmake)

packageVersion('pxmake')
> [1] ‘0.14.2.9005’

trygve_url <- "https://data.ssb.no/api/pxwebapi/v2-beta/tables/03013/data?lang=no&valueCodes[Konsumgrp]=TOTAL&valueCodes[ContentsCode]=KpiIndMnd&valueCodes[Tid]=2023M04&codelist[Konsumgrp]=vs_CoiCop2016niva1&outputformat=px"

x <- px(trygve_url, validate = FALSE)

However, as you can see, I have added validate = FALSE which suppresses the error you are getting.

What is causing the error?

I looked a bit at it, and luckily it's not an encoding issue (those are always the worst!).

The error comes from some validation checks that pxmake performs on the PX-file. One of these checks ensures that no values contain quotation marks, since we have experienced this to cause errors with other PX tools.

However, when inspecting your PX-file I didn't find and "illegal" quotation marks.

Digging a bit deeper it turns out that pxmake parses your px-file incorrectly.

You can see it here, where part of the values for row 6-10 have been incorrectly placed in the value column, instead of code. This puts quotations marks in the value column, triggering the validation error.

> x <- px("https://data.ssb.no/api/pxwebapi/v2-beta/tables/03013/data?lang=no&valueCodes[Konsumgrp]=TOTAL&valueCodes[ContentsCode]=KpiIndMnd&valueCodes[Tid]=2023M04&codelist[Konsumgrp]=vs_CoiCop2016niva1&outputformat=px", validate = FALSE)
> x$table2
# A tibble: 13 × 4
   keyword      code                   language value                                                                     
   <chr>        <chr>                  <chr>    <chr>                                                                     
 1 SUBJECT-AREA NA                     no       "Arbeid og lønn"                                                          
 2 TITLE        NA                     no       "03013: Konsumprisindeks, etter konsumgruppe, statistikkvariabel og måned"
 3 CONTENTS     NA                     no       "03013: Konsumprisindeks,"                                                
 4 CONTVARIABLE NA                     no       "statistikkvariabel"                                                      
 5 UNITS        NA                     no       "indeks"                                                                  
 6 LAST-UPDATED Konsumprisindeks (2015 no       "100)\")=\"20250110 08:00"                                                
 7 STOCKFA      Konsumprisindeks (2015 no       "100)\")=\"A"                                                             
 8 UNITS        Konsumprisindeks (2015 no       "100)\")=\"indeks"                                                        
 9 CONTACT      Konsumprisindeks (2015 no       "100)\")=\" Konsumprisindeksen, Statistisk sentralbyrå# +47 62 88 56 34#k…
10 BASEPERIOD   Konsumprisindeks (2015 no       "100)\")=\"2015"                                                          
11 SOURCE       NA                     no       "Statistisk sentralbyrå"                                                  
12 INFOFILE     NA                     no       "None"                                                                    
13 NOTE         NA                     no       "F.o.m. 2017 er referanseår 2015=100. Endringstall beregnet fra 2015=100 

This is a bug in pxmake.

It gets confused because the codes for some of your keywords contains equal signs, which pxmake normally uses to split the left and right part of each line in a px-file.

To fix this I would have to tinker a bit with the regular expression that turns the metadata lines in the PX-file into a data frame.

pxmake/R/metadata_df.R

Lines 5 to 18 in 11bd5ce

	get_px_metadata_regex <- function() {
	paste0("(?<keyword>[[:upper:]-]+)", # Leading keyword
	"(?:\\[)?", # Maybe opening language bracket [
	"(?<language>[[:alpha:]_-]+)?", # Maybe language
	"(?:\\])?", # Maybe closing language bracket ]
	"(?:\\(\")?", # Maybe opening sub-key parentheses (
	"(?<variable>[^\"=]+)?", # Maybe sub-key
	"(?:\",\")?", # Maybe comma before cell value
	"(?<cell>[^=]+?)?(?=\\)=|=)", # Maybe cell value
	"(?:\")?", # Maybe closing " after cell value
	"(?:\"\\))?", # Maybe closing sub-key parentheses )
	"=", # definitely =
	"(?<value>[^;]*)", # Value is everything up to ending ;
	"(?:;$)?" # Maybe ;

How pxmake handles encoding

There doesn't seem to be any explicit setting of encoding hints when reading a PX-file.

Just to finish off on the encoding, you are right in that you can't explicitly set the encoding when reading a PX-file. The reason is that pxmake hopefully (🤞) handles it automatically.

Before properly opening a file, pxmake peeks into it and finds the encoding set by the CODEPAGE keyword, and then reopens the file with this encoding.

Conclusion

• You can open your file with px(validate = FALSE), but it will create an incorrect PX-file if saved.
• I have renamed the title of your issue, and tagged it as a bug.
• Fixing this would require an update to a regular expression in pxmake.

[larpSTATGL]

i did not think of that , but now, this should also be good for:

pak::pak("StatisticsGreenland/pxmake")

library(pxmake)
packageVersion('pxmake')

Danmark

url <- "https://api.statbank.dk/v1/data/bef5/PX?lang=en&valuePresentation=CodeAndValue&tid=*&fodland=*"

Irland

url <- "https://ws.cso.ie/public/api.restful/PxStat.Data.Cube_API.PxAPIv1/en/17/PME/PEA01?query=%7B%22query%22:%5B%5D,%22response%22:%7B%22format%22:%22px%22,%22pivot%22:null,%22codes%22:true%7D%7D"

pxweb2 - '503 Service Unavailable'

url <- "https://data.ssb.no/api/pxwebapi/v2-beta/tables/03013/data?lang=no&valueCodes[Konsumgrp]=TOTAL&valueCodes[ContentsCode]=KpiIndMnd&valueCodes[Tid]=2023M04&codelist[Konsumgrp]=vs_CoiCop2016niva1&outputformat=px"

x <- px(url, validate = FALSE)

title <- px_title(x)
data <- px_data(x)

[trygu]

Thank you for a thorough explanation of everything! ❤ And also thank you for making it possible to use GET urls's directly. I think that is very useful!

[trygu]

Insicednly could this be used for Parquet-files as well? If you change the output format, to Parquet, you get a parquet-file instead. For this particular example, it probably makes more sense to use px as output-format, but many sites provides parquet files served over http so reading parquet-files over the wire would be useful:

"https://data.ssb.no/api/pxwebapi/v2-beta/tables/03013/data?lang=no&valueCodes[Konsumgrp]=TOTAL&valueCodes[ContentsCode]=KpiIndMnd&valueCodes[Tid]=2023M04&codelist[Konsumgrp]=vs_CoiCop2016niva1&outputformat=parquet"

Not related to this bug, but since you postet about an enhancement here, I took the liberty to post this. 👍

Again; Thank you

[johan-ejstrud]

Insicednly could this be used for Parquet-files as well?

I didn't know it was possible to get parquet files directly from the API. Nice. 👌

The answer is yes @trygu, this should totally be possible to support. Great idea!

I have created a new issue for it: #359