How to separate a JSON string into multiple columns in R

Question

I have a column with strings in dictionary format, like this:

{'district': 'Ilha do Retiro', 'city': 'RECIFE', 'state': 'PE', 'country': 'BR', 'latitude': -8.062004, 'longitude': -34.908081, 'timezone': 'Etc/GMT+3', 'zipCode': '50830000', 'streetName': 'Avenida Engenheiro Abdias de Carvalho', 'streetNumber': '365'}

I want to create one column for each key, filled with the value for each row.

I tried using separate with regex:

separate(data, address, into = c('district', 'city', 'state', 'country',
                                   'latitude', 'longitude', 'timezone',
                                   'zipCode', 'streetName', 'streetNumber'), 
         "/:.*?/,")

However this is not working, and perhaps using separate with regex is not even the best course of action for this. Any suggestions on how to do it?

score 2 · Answer 1 · answered Sep 17 '21 at 15:55

Since it is effectively JSON (though with single-quotes instead of the requisite double-quotes), we can use jsonlite:

txt <- "{'district': 'Ilha do Retiro', 'city': 'RECIFE', 'state': 'PE', 'country': 'BR', 'latitude': -8.062004, 'longitude': -34.908081, 'timezone': 'Etc/GMT+3', 'zipCode': '50830000', 'streetName': 'Avenida Engenheiro Abdias de Carvalho', 'streetNumber': '365'}"
as.data.frame(jsonlite::parse_json(gsub("'", '"', txt)))
#         district   city state country  latitude longitude  timezone  zipCode                            streetName streetNumber
# 1 Ilha do Retiro RECIFE    PE      BR -8.062004 -34.90808 Etc/GMT+3 50830000 Avenida Engenheiro Abdias de Carvalho          365

How to separate a JSON string into multiple columns in R

1 Answers1