How do i turn a public holidays RJson into dataframe using R

Question

Hi I am trying to read the public holidays rJson file from the GOV.UK JSON file and convert into a useuable data frame but not with much success. I've used the jsonlite package to read the data as per the following :-

library(jsonlite)

holiday_json <- fromJSON("https://www.gov.uk/bank-holidays.json")

this returns a series of lists which i need to convert into a dataframe. I've tried various different solutions and they either return an error saying the dimensions of the data would be incorrect if i use as.dataframe or the data returned is not in the correct format at all(using unlist).

I want a 3 column data frame that contains the region the holiday is for i.e England,Wales,Scotland and Northern Ireland as well as what type of holiday it is and the date.

Answer 1

The first thing I find helpful when tackling a problem like this, with nested lists, is to look at the structure of the list:

> str(holiday_json)
List of 3
 $ england-and-wales:List of 2
  ..$ division: chr "england-and-wales"
  ..$ events  :'data.frame':    67 obs. of  4 variables:
  .. ..$ title  : chr [1:67] "New Year’s Day" "Good Friday" "Easter Monday" "Early May bank holiday" ...
  .. ..$ date   : chr [1:67] "2018-01-01" "2018-03-30" "2018-04-02" "2018-05-07" ...
  .. ..$ notes  : chr [1:67] "" "" "" "" ...
  .. ..$ bunting: logi [1:67] TRUE FALSE TRUE TRUE TRUE TRUE ...
 $ scotland         :List of 2
  ..$ division: chr "scotland"
  ..$ events  :'data.frame':    75 obs. of  4 variables:
  .. ..$ title  : chr [1:75] "New Year’s Day" "2nd January" "Good Friday" "Early May bank holiday" ...
  .. ..$ date   : chr [1:75] "2018-01-01" "2018-01-02" "2018-03-30" "2018-05-07" ...
  .. ..$ notes  : chr [1:75] "" "" "" "" ...
  .. ..$ bunting: logi [1:75] TRUE TRUE FALSE TRUE TRUE TRUE ...
 $ northern-ireland :List of 2
  ..$ division: chr "northern-ireland"
  ..$ events  :'data.frame':    83 obs. of  4 variables:
  .. ..$ title  : chr [1:83] "New Year’s Day" "St Patrick’s Day" "Good Friday" "Easter Monday" ...
  .. ..$ date   : chr [1:83] "2018-01-01" "2018-03-19" "2018-03-30" "2018-04-02" ...
  .. ..$ notes  : chr [1:83] "" "Substitute day" "" "" ...
  .. ..$ bunting: logi [1:83] TRUE TRUE FALSE TRUE TRUE TRUE ...

So, to put it into words, you have three sublists, with two sublists for each of those. I assume you want to pull out the events for each locality, and turn it all into one big dataframe. You can do that many ways, but maybe the easiest is using map_df():

purrr::map_df(holiday_json, as.data.frame)

Conveniently, map_df() takes the division element, and turns it into a column in the output dataframe.

Output:

             division                                             events.title
1   england-and-wales                                           New Year’s Day
2   england-and-wales                                              Good Friday
3   england-and-wales                                            Easter Monday
4   england-and-wales                                   Early May bank holiday
5   england-and-wales                                      Spring bank holiday
6   england-and-wales                                      Summer bank holiday
7   england-and-wales                                            Christmas Day
8   england-and-wales                                               Boxing Day
9   england-and-wales                                           New Year’s Day
10  england-and-wales                                              Good Friday
11  england-and-wales                                            Easter Monday
12  england-and-wales                                   Early May bank holiday
13  england-and-wales                                      Spring bank holiday
14  england-and-wales                                      Summer bank holiday
15  england-and-wales                                            Christmas Day
16  england-and-wales                                               Boxing Day
17  england-and-wales                                           New Year’s Day
18  england-and-wales                                              Good Friday
19  england-and-wales                                            Easter Monday
20  england-and-wales                          Early May bank holiday (VE day)
21  england-and-wales                                      Spring bank holiday
22  england-and-wales                                      Summer bank holiday
23  england-and-wales                                            Christmas Day
24  england-and-wales                                               Boxing Day
25  england-and-wales                                           New Year’s Day
26  england-and-wales                                              Good Friday
27  england-and-wales                                            Easter Monday
28  england-and-wales                                   Early May bank holiday
29  england-and-wales                                      Spring bank holiday
30  england-and-wales                                      Summer bank holiday
31  england-and-wales                                            Christmas Day
32  england-and-wales                                               Boxing Day
33  england-and-wales                                           New Year’s Day
34  england-and-wales                                              Good Friday
35  england-and-wales                                            Easter Monday
36  england-and-wales                                   Early May bank holiday
37  england-and-wales                                      Spring bank holiday
38  england-and-wales                            Platinum Jubilee bank holiday
39  england-and-wales                                      Summer bank holiday
40  england-and-wales Bank Holiday for the State Funeral of Queen Elizabeth II
41  england-and-wales                                               Boxing Day
42  england-and-wales                                            Christmas Day
43  england-and-wales                                           New Year’s Day
44  england-and-wales                                              Good Friday
45  england-and-wales                                            Easter Monday
46  england-and-wales                                   Early May bank holiday
47  england-and-wales      Bank holiday for the coronation of King Charles III
48  england-and-wales                                      Spring bank holiday
49  england-and-wales                                      Summer bank holiday
50  england-and-wales                                            Christmas Day
51  england-and-wales                                               Boxing Day
52  england-and-wales                                           New Year’s Day
53  england-and-wales                                              Good Friday
54  england-and-wales                                            Easter Monday
55  england-and-wales                                   Early May bank holiday
56  england-and-wales                                      Spring bank holiday
57  england-and-wales                                      Summer bank holiday
58  england-and-wales                                            Christmas Day
59  england-and-wales                                               Boxing Day
60  england-and-wales                                           New Year’s Day
61  england-and-wales                                              Good Friday
62  england-and-wales                                            Easter Monday
63  england-and-wales                                   Early May bank holiday
64  england-and-wales                                      Spring bank holiday
65  england-and-wales                                      Summer bank holiday
66  england-and-wales                                            Christmas Day
67  england-and-wales                                               Boxing Day
68           scotland                                           New Year’s Day
69           scotland                                              2nd January
70           scotland                                              Good Friday
71           scotland                                   Early May bank holiday
72           scotland                                      Spring bank holiday
73           scotland                                      Summer bank holiday
74           scotland                                          St Andrew’s Day
75           scotland                                            Christmas Day
76           scotland                                               Boxing Day
77           scotland                                           New Year’s Day
78           scotland                                              2nd January
79           scotland                                              Good Friday
80           scotland                                   Early May bank holiday
81           scotland                                      Spring bank holiday
82           scotland                                      Summer bank holiday
83           scotland                                          St Andrew’s Day
84           scotland                                            Christmas Day
85           scotland                                               Boxing Day
86           scotland                                           New Year’s Day
87           scotland                                              2nd January
88           scotland                                              Good Friday
89           scotland                          Early May bank holiday (VE day)
90           scotland                                      Spring bank holiday
91           scotland                                      Summer bank holiday
92           scotland                                          St Andrew’s Day
93           scotland                                            Christmas Day
94           scotland                                               Boxing Day
95           scotland                                           New Year’s Day
96           scotland                                              2nd January
97           scotland                                              Good Friday
98           scotland                                   Early May bank holiday
99           scotland                                      Spring bank holiday
100          scotland                                      Summer bank holiday
101          scotland                                          St Andrew’s Day
102          scotland                                            Christmas Day
103          scotland                                               Boxing Day
104          scotland                                           New Year’s Day
105          scotland                                              2nd January
106          scotland                                              Good Friday
107          scotland                                   Early May bank holiday
108          scotland                                      Spring bank holiday
109          scotland                            Platinum Jubilee bank holiday
110          scotland                                      Summer bank holiday
111          scotland Bank Holiday for the State Funeral of Queen Elizabeth II
112          scotland                                          St Andrew’s Day
113          scotland                                               Boxing Day
114          scotland                                            Christmas Day
115          scotland                                           New Year’s Day
116          scotland                                              2nd January
117          scotland                                              Good Friday
118          scotland                                   Early May bank holiday
119          scotland      Bank holiday for the coronation of King Charles III
120          scotland                                      Spring bank holiday
121          scotland                                      Summer bank holiday
122          scotland                                          St Andrew’s Day
123          scotland                                            Christmas Day
124          scotland                                               Boxing Day
125          scotland                                           New Year’s Day
126          scotland                                              2nd January
127          scotland                                              Good Friday
128          scotland                                   Early May bank holiday
129          scotland                                      Spring bank holiday
130          scotland                                      Summer bank holiday
131          scotland                                          St Andrew’s Day
132          scotland                                            Christmas Day
133          scotland                                               Boxing Day
134          scotland                                           New Year’s Day
135          scotland                                              2nd January
136          scotland                                              Good Friday
137          scotland                                   Early May bank holiday
138          scotland                                      Spring bank holiday
139          scotland                                      Summer bank holiday
140          scotland                                          St Andrew’s Day
141          scotland                                            Christmas Day
142          scotland                                               Boxing Day
143  northern-ireland                                           New Year’s Day
144  northern-ireland                                         St Patrick’s Day
145  northern-ireland                                              Good Friday
146  northern-ireland                                            Easter Monday
147  northern-ireland                                   Early May bank holiday
148  northern-ireland                                      Spring bank holiday
149  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
150  northern-ireland                                      Summer bank holiday
151  northern-ireland                                            Christmas Day
152  northern-ireland                                               Boxing Day
153  northern-ireland                                           New Year’s Day
154  northern-ireland                                         St Patrick’s Day
155  northern-ireland                                              Good Friday
156  northern-ireland                                            Easter Monday
157  northern-ireland                                   Early May bank holiday
158  northern-ireland                                      Spring bank holiday
159  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
160  northern-ireland                                      Summer bank holiday
161  northern-ireland                                            Christmas Day
162  northern-ireland                                               Boxing Day
163  northern-ireland                                           New Year’s Day
164  northern-ireland                                         St Patrick’s Day
165  northern-ireland                                              Good Friday
166  northern-ireland                                            Easter Monday
167  northern-ireland                          Early May bank holiday (VE day)
168  northern-ireland                                      Spring bank holiday
169  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
170  northern-ireland                                      Summer bank holiday
171  northern-ireland                                            Christmas Day
172  northern-ireland                                               Boxing Day
173  northern-ireland                                           New Year’s Day
174  northern-ireland                                         St Patrick’s Day
175  northern-ireland                                              Good Friday
176  northern-ireland                                            Easter Monday
177  northern-ireland                                   Early May bank holiday
178  northern-ireland                                      Spring bank holiday
179  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
180  northern-ireland                                      Summer bank holiday
181  northern-ireland                                            Christmas Day
182  northern-ireland                                               Boxing Day
183  northern-ireland                                           New Year’s Day
184  northern-ireland                                         St Patrick’s Day
185  northern-ireland                                              Good Friday
186  northern-ireland                                            Easter Monday
187  northern-ireland                                   Early May bank holiday
188  northern-ireland                                      Spring bank holiday
189  northern-ireland                            Platinum Jubilee bank holiday
190  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
191  northern-ireland                                      Summer bank holiday
192  northern-ireland Bank Holiday for the State Funeral of Queen Elizabeth II
193  northern-ireland                                               Boxing Day
194  northern-ireland                                            Christmas Day
195  northern-ireland                                           New Year’s Day
196  northern-ireland                                         St Patrick’s Day
197  northern-ireland                                              Good Friday
198  northern-ireland                                            Easter Monday
199  northern-ireland                                   Early May bank holiday
200  northern-ireland      Bank holiday for the coronation of King Charles III
201  northern-ireland                                      Spring bank holiday
202  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
203  northern-ireland                                      Summer bank holiday
204  northern-ireland                                            Christmas Day
205  northern-ireland                                               Boxing Day
206  northern-ireland                                           New Year’s Day
207  northern-ireland                                         St Patrick’s Day
208  northern-ireland                                              Good Friday
209  northern-ireland                                            Easter Monday
210  northern-ireland                                   Early May bank holiday
211  northern-ireland                                      Spring bank holiday
212  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
213  northern-ireland                                      Summer bank holiday
214  northern-ireland                                            Christmas Day
215  northern-ireland                                               Boxing Day
216  northern-ireland                                           New Year’s Day
217  northern-ireland                                         St Patrick’s Day
218  northern-ireland                                              Good Friday
219  northern-ireland                                            Easter Monday
220  northern-ireland                                   Early May bank holiday
221  northern-ireland                                      Spring bank holiday
222  northern-ireland                    Battle of the Boyne (Orangemen’s Day)
223  northern-ireland                                      Summer bank holiday
224  northern-ireland                                            Christmas Day
225  northern-ireland                                               Boxing Day
    events.date   events.notes events.bunting
1    2018-01-01                          TRUE
2    2018-03-30                         FALSE
3    2018-04-02                          TRUE
4    2018-05-07                          TRUE
5    2018-05-28                          TRUE
6    2018-08-27                          TRUE
7    2018-12-25                          TRUE
8    2018-12-26                          TRUE
9    2019-01-01                          TRUE
10   2019-04-19                         FALSE
11   2019-04-22                          TRUE
12   2019-05-06                          TRUE
13   2019-05-27                          TRUE
14   2019-08-26                          TRUE
15   2019-12-25                          TRUE
etc.