Relationship of 'ConnectionType' and 'NumberOfPoints' missing from CSV export

Question
1

Hello there,
I rely on your database for a data analytics project. I have a more general question regarding the style of contributions so I can decide how to process data in an automated fashion.

For example OCM-108484, Brüsseler Platz, Essen, Germany.
On your website and in the API .csv export the ‘NumberOfPoints’ is listed as 27.
However, the .csv export does not appear to contain information on how many points of each connection type are present. On the website on the other hand, this information is provided.

Now I was wondering how I can get this information into the exported database so I can work with it.
This presents a crucial problem for me right now and I’m very grateful for every response.

Warm regards
Cedric

For your information, I process the database in R.
This is the API call:
download.file("https://api.openchargemap.io/v3/poi/?output=csv&countrycode=DE&maxresults=999999","OCM_DE.csv")
These are all variables I have in my csv export:

|1|ï…ID|
|2|UUID|
|3|LocationTitle|
|4|AddressLine1|
|5|AddressLine2|
|6|Town|
|7|StateOrProvince|
|8|Postcode|
|9|Country|
|10|Latitude|
|11|Longitude|
|12|Distance|
|13|DistanceUnit|
|14|Addr_ContactTelephone1|
|15|Addr_ContactTelephone2|
|16|Addr_ContactEmail|
|17|Addr_AccessComments|
|18|Addr_GeneralComments|
|19|Addr_RelatedURL|
|20|ConnectionType|
|21|ChargerType|
|22|UsageType|
|23|NumberOfPoints|
|24|GeneralComments|
|25|DateLastConfirmed|
|26|StatusType|
|27|DateLastStatusUpdate|
|28|DateCreated|

2

You will find that the JSON export is much better than the basic CSV export, we don’t currently plan to change the CSV export as we would much rather people use the full JSON data and CSV changes can affect brittle systems that don’t expected the output to change.

A quick way to get the latest data set is to use git to clone (and subsequently pull) our data snapshot from github: https://github.com/openchargemap/ocm-data

You can then gunzip poi.json.gz and work with the entire dataset. A small sample off that data would look like: https://api.openchargemap.io/v3/poi?countrycode=DE

1 Like
3

Thank you very much for your quick answer.
Unfortunately, I’m slightly at a loss with handling the JSON data.

First, I’m retrieving the file you mentioned from GitHub:
download.file("https://github.com/openchargemap/ocm-data/raw/master/poi.json.gz","OCM_DE.json.gz")
Then I unzip the file:
gunzip("OCM_DE.json.gz", remove=FALSE)
Then I import the unzipped file into R with:
OCM_DE <- fromJSON(file = "OCM_DE.json")

Unfortunately, the resulting data in R looks awfully empty (see image below). I also tried manually downloading and extracting and only doing the import within R. Unfortunately, to no luck.
If you could give me another hint as to how I should best handle this, that would be wonderful.

Warm regards
Cedric

image
image640×898 19 KB

//EDIT:
Nevermind, using jsonlite::stream_in instead of fromJSON worked wonders.
Doing this works perfectly and provides comprehensive information as pointed out by @Christopher
For anyone trying to import the OCM database as JSON within R, please do the following:

  1. Download the JSON file:
    download.file("https://github.com/openchargemap/ocm-data/raw/master/poi.json.gz","OCM_DE.json.gz")
  2. Unzip the archive:
    gunzip("OCM_DE.json.gz", remove=FALSE)
  3. Import data into R:
    OCM_DE <- jsonlite::stream_in(file("OCM_DE.json"))
    Note: You can use ndjson::stream_in("OCM_DE.json") as pointed out here, but the result is not ideal in my opinion.

If we investigate the example from above ( OCM-108484), we notice that the connection types are stored as a nested data frame.
View(OCM_DE[OCM_DE$ID == 108484,])
To access the nested data frame, simply do the following: View(as.data.frame(OCM_DE$Connections[OCM_DE$ID == 108484]))

Thank you very much for the help provided and especially the database. It’s helping me so much for my project!

Warm regards
Cedric

2 Likes