CODEWITHSUNDEEP
jsonrdplyrjsonlite
pachadotdev
pachadotdev

Reputation: 3765

read nested json and convert it to tidy data

I am trying to convert a json to a tibble that follow Tidy Data principles.

The website http://pv.servelelecciones.cl/ has some nice regional data. Because I cannot provide a direct link to "Aysen region" as the url doesn't change, this is the data they are displaying:

|listapacto                          |partido  |votos|porcentaje|electo|
|------------------------------------|---------|-----|----------|------|
|H. SUMEMOS                          |         |365  |4,52%     |      |
|TODOS                               |         |113  |1,40%     |      |
|50. EDUARDO ROMO LAFOY              |IND-TODOS|69   |0,86%     |      |
|51. SARA MARTINEZ MONDELO           |IND-TODOS|44   |0,55%     |      |
|CIUDADANOS                          |         |252  |3,12%     |      |
|52. VICTOR MANUEL BORQUEZ FINCKE    |CIUD.    |53   |0,66%     |      |
|53. MARISOL LUSDEMIA PINILLA VEJAR  |CIUD.    |199  |2,47%     |      |
|K. COALICIÓN REGIONALISTA VERDE     |         |200  |2,48%     |      |
|DEMOCRACIA REGIONAL PATAGONICA      |         |200  |2,48%     |      |
|54. ELSON BORQUEZ YAÑEZ             |DRP      |59   |0,73%     |      |
|55. PEDRO ANTONIO VERGARA ROJAS     |DRP      |42   |0,52%     |      |
|56. JESSICA ANDREA TORRES BORQUEZ   |IND-DRP  |59   |0,73%     |      |
|57. TAMARA ANDREA ESPINOZA GUTIERREZ|DRP      |40   |0,50%     |      |
|N. LA FUERZA DE LA MAYORIA          |         |2.958|36,66%    |      |
|PARTIDO RADICAL SOCIALDEMOCRATA     |         |346  |4,29%     |      |
|58. JORGE CALDERON NUÑEZ            |PRSD     |346  |4,29%     |      |
|PARTIDO SOCIALISTA DE CHILE         |         |1.651|20,46%    |      |
|59. MARISOL MARTINEZ SANCHEZ        |PSCH     |1.651|20,46%    |      |
|PARTIDO COMUNISTA DE CHILE          |         |322  |3,99%     |      |
|60. ROXANA PEY TUMANOFF             |IND-PCCH |322  |3,99%     |      |
|PARTIDO POR LA DEMOCRACIA           |         |639  |7,92%     |      |
|61. RENE OSVALDO ALINCO BUSTOS      |IND-PPD  |639  |7,92%     |*     |
|O. CONVERGENCIA DEMOCRATICA         |         |2.297|28,47%    |      |
|PARTIDO DEMOCRATA CRISTIANO         |         |2.297|28,47%    |      |
|62. MIGUEL ANGEL CALISTO AGUILA     |PDC      |1.882|23,32%    |*     |
|63. CARMEN GLORIA MARTINEZ CARDENAS |PDC      |224  |2,78%     |      |
|64. RENE ANSELMO LEGUE CARDENAS     |PDC      |191  |2,37%     |      |
|P. CHILE VAMOS                      |         |1.963|24,33%    |      |
|UNION DEMOCRATA INDEPENDIENTE       |         |605  |7,50%     |      |
|65. NESTOR MERA MUÑOZ               |UDI      |605  |7,50%     |      |
|PARTIDO REGIONALISTA INDEPENDIENTE  |         |132  |1,64%     |      |
|66. PATRICIO HENRIQUEZ BARRIENTOS   |PRI      |132  |1,64%     |      |
|EVOLUCION POLITICA                  |         |365  |4,52%     |      |
|67. GEOCONDA NAVARRETE ARRATIA      |EVOP.    |365  |4,52%     |      |
|RENOVACION NACIONAL                 |         |861  |10,67%    |      |
|68. ARACELY LEUQUEN URIBE           |RN       |861  |10,67%    |*     |
|CANDIDATURA INDEPENDIENTE           |         |286  |3,54%     |      |
|69. CECILIO AGUILAR GALINDO         |IND      |286  |3,54%     |      |

I'd like to read the json data that feeds their website to obtain a tibble like the table above, and from that move to a tidy structure.

If I just try to read the data directly:

require(data.table)
require(jsonlite)
require(dplyr)

x <- fromJSON("http://www.servelelecciones.cl/data/elecciones_diputados/computo/comunas/114501.json")

y <- as_tibble(x$data)

Then I get some locked information in the last column:

> y
# A tibble: 6 x 7
  a                               b     c     d      e     f     sd                  
* <chr>                           <lgl> <chr> <chr>  <chr> <chr> <list>              
1 H. SUMEMOS                      NA    365   4,52%  4     ""    <data.frame [2 × 7]>
2 K. COALICIÓN REGIONALISTA VERDE NA    200   2,48%  4     ""    <data.frame [1 × 7]>
3 N. LA FUERZA DE LA MAYORIA      NA    2.958 36,66% 4     ""    <data.frame [4 × 7]>
4 O. CONVERGENCIA DEMOCRATICA     NA    2.297 28,47% 3     ""    <data.frame [1 × 7]>
5 P. CHILE VAMOS                  NA    1.963 24,33% 4     ""    <data.frame [4 × 7]>
6 CANDIDATURA INDEPENDIENTE       NA    286   3,54%  NA    NA    <data.frame [1 × 7]>

Of course I tried

Any help is highly welcome.

Upvotes: 0

Views: 1341

Answers (1)

pachadotdev
pachadotdev

Reputation: 3765

One possibility, instead of writing a funcion, is to use tidyr and unnest as many times as required.

In my case:

require(data.table)
require(jsonlite)
require(dplyr)
require(tidyr)

x <- fromJSON("http://www.servelelecciones.cl/data/elecciones_diputados/computo/comunas/114501.json")

y <- as_tibble(x$data)

y1 <- y %>% filter(a == "CANDIDATURA INDEPENDIENTE")
y2 <- y %>% filter(a != "CANDIDATURA INDEPENDIENTE") %>% unnest(sd)

Upvotes: 2

Related Questions