-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathtemp1.html
700 lines (671 loc) · 46.4 KB
/
temp1.html
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en"><head>
<meta charset="utf-8">
<meta name="generator" content="quarto-1.5.57">
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=yes">
<title>Untitled</title>
<style>
code{white-space: pre-wrap;}
span.smallcaps{font-variant: small-caps;}
div.columns{display: flex; gap: min(4vw, 1.5em);}
div.column{flex: auto; overflow-x: auto;}
div.hanging-indent{margin-left: 1.5em; text-indent: -1.5em;}
ul.task-list{list-style: none;}
ul.task-list li input[type="checkbox"] {
width: 0.8em;
margin: 0 0.8em 0.2em -1em; /* quarto-specific, see https://github.com/quarto-dev/quarto-cli/issues/4556 */
vertical-align: middle;
}
/* CSS for syntax highlighting */
pre > code.sourceCode { white-space: pre; position: relative; }
pre > code.sourceCode > span { line-height: 1.25; }
pre > code.sourceCode > span:empty { height: 1.2em; }
.sourceCode { overflow: visible; }
code.sourceCode > span { color: inherit; text-decoration: inherit; }
div.sourceCode { margin: 1em 0; }
pre.sourceCode { margin: 0; }
@media screen {
div.sourceCode { overflow: auto; }
}
@media print {
pre > code.sourceCode { white-space: pre-wrap; }
pre > code.sourceCode > span { display: inline-block; text-indent: -5em; padding-left: 5em; }
}
pre.numberSource code
{ counter-reset: source-line 0; }
pre.numberSource code > span
{ position: relative; left: -4em; counter-increment: source-line; }
pre.numberSource code > span > a:first-child::before
{ content: counter(source-line);
position: relative; left: -1em; text-align: right; vertical-align: baseline;
border: none; display: inline-block;
-webkit-touch-callout: none; -webkit-user-select: none;
-khtml-user-select: none; -moz-user-select: none;
-ms-user-select: none; user-select: none;
padding: 0 4px; width: 4em;
}
pre.numberSource { margin-left: 3em; padding-left: 4px; }
div.sourceCode
{ }
@media screen {
pre > code.sourceCode > span > a:first-child::before { text-decoration: underline; }
}
</style>
<script src="temp1_files/libs/clipboard/clipboard.min.js"></script>
<script src="temp1_files/libs/quarto-html/quarto.js"></script>
<script src="temp1_files/libs/quarto-html/popper.min.js"></script>
<script src="temp1_files/libs/quarto-html/tippy.umd.min.js"></script>
<script src="temp1_files/libs/quarto-html/anchor.min.js"></script>
<link href="temp1_files/libs/quarto-html/tippy.css" rel="stylesheet">
<link href="temp1_files/libs/quarto-html/quarto-syntax-highlighting.css" rel="stylesheet" id="quarto-text-highlighting-styles">
<script src="temp1_files/libs/bootstrap/bootstrap.min.js"></script>
<link href="temp1_files/libs/bootstrap/bootstrap-icons.css" rel="stylesheet">
<link href="temp1_files/libs/bootstrap/bootstrap.min.css" rel="stylesheet" id="quarto-bootstrap" data-mode="light">
</head>
<body class="fullcontent">
<div id="quarto-content" class="page-columns page-rows-contents page-layout-article">
<main class="content" id="quarto-document-content">
<header id="title-block-header" class="quarto-title-block default">
<div class="quarto-title">
<h1 class="title">Untitled</h1>
</div>
<div class="quarto-title-meta">
</div>
</header>
<section id="package-to-process-fcc-fixed-broadband-datasets" class="level1">
<h1>Package to Process FCC Fixed Broadband Datasets</h1>
<section id="installation" class="level2">
<h2 class="anchored" data-anchor-id="installation">Installation</h2>
<p>You can install the development version of processFCC like so:</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb1"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb1-1"><a href="#cb1-1" aria-hidden="true" tabindex="-1"></a><span class="fu">install.packages</span>(<span class="st">"devtools"</span>)</span>
<span id="cb1-2"><a href="#cb1-2" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(devtools)</span>
<span id="cb1-3"><a href="#cb1-3" aria-hidden="true" tabindex="-1"></a><span class="fu">install_github</span>(<span class="st">"kdmulligan/processFCC"</span>)</span>
<span id="cb1-4"><a href="#cb1-4" aria-hidden="true" tabindex="-1"></a><span class="fu">library</span>(processFCC)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
<section id="background-of-fcc-fixed-broadband-datasets" class="level2">
<h2 class="anchored" data-anchor-id="background-of-fcc-fixed-broadband-datasets">Background of FCC Fixed Broadband Datasets</h2>
<p>The goal of <code>processFCC</code> is to process the fixed broadband data sets from the <a href="https://www.fcc.gov/general/broadband-deployment-data-fcc-form-477">Federal Communications Commision (FCC)</a>. The FCC is the federal agency responsible for implementing and enforcing America’s communications laws and regulations. They regulate interstate and international communications by radio, television, wire, satellite, cable, and internet in all 50 states, D.C., and U.S. territories.</p>
<p>The FCC Broadband Data is from facilities-based broadband providers who are required to submit to the FCC biannually for any locations where they offer internet service at speeds greater than or equal to 200 kbps. Prior to 2022, this data was collected using Form 477 where information about where and what type of Internet access is provided at the census block level. However, this form was discontinued in in December 2021. Instead, broadband providers now submit information about their broadband internet access services in the broadband data collection system. Within this system, data is reported more granularity than the census block level. Although a census block level version of this data set is still available in the new format. For more information about the filing system, please reference the form resources <a href="https://www.fcc.gov/economics-analytics/industry-analysis-division/form-477-resources">website</a>.</p>
<p>Because of these changes in reporting, one of the first things you will need to do is decide which time points of the FCC fixed broadband data you wish to use and process. Dates from December 2021 and prior use the “old” format whereas dates June 2022 and beyond use the “new” format. For both formats, fixed broadband data is available at two time points per year: June and December. Use new_or_old_FCC() to check which dates are available and also the format (new or old) that should be used to process the data. The output of the function provides the available dates, the format, the function to use to process the data, and the website if you would like more information.</p>
<p>The FCC Broadband Data comes from Form 477, which facilities-based broadband providers are required to submit to the FCC biannually for any locations where they offer internet service at speeds greater than or equal to 200 kbps. Fixed providers report Census Blocks where they provide service, along with information about technology type and speeds. Thus, fixed provider data is on the Census Block level, which is the smallest unit of census geography. A column in the data indicates whether the broadband was provided to a residential or business location–the <code>processFCC</code> package only considers residential broadband. Importantly for interpretation, if a provider indicates they provide broadband to a Census Block on FCC Form 477 it does not mean every location in the Census Block has access to the broadband service. It simply means at least one location in the Census Block has access to broadband at the reported speed/technology. It is important to consider this as it may lead to over-reporting of broadband access within the FCC Fixed Broadband Deployment data. FCC data measures where there is broadband available according to internet service providers. Thus, the FCC data provides a picture of claimed availability—what may be possible or what is potentially available at the physical or technological level.</p>
<p>Because the forms are submitted biannually, data is available for June and December of each year beginning December 2014.</p>
<p>The FCC fixed broadband data sets are key to knowing where fixed broadband internet is provided however due to their size (> 8 GB) and complexity many researchers avoid using them. The data cannot be used in its raw form available from the FCC website because there are multiple rows per Census Block per unique broadband provider, technology, and speed. This means there are numerous options for measurement of broadband access which could be chosen to fit a given research question. The raw data has many nuances which can be confusing, so it is vital to understand its format before rolling it up to fit a research question. Additionally, FCC data are at the Census Block level, the smallest unit of Census Geography, which gives greater flexibility. It can be rolled up to any higher Census Geography such as Census Block Group, Census Tract, or County.</p>
<p>This package has functions to direct users to the file download URLs, put the dataset into a SQLite database, and process the data to a smaller level for both the new and old format of data. The processed form of the data is one row per specified census geography, such as census block, and counts the number of distinct internet service providers providing internet at or above the given download and upload speed thresholds, up to 5 speed thresholds combinations can be specified. It is also possible to exclude different broadband technologies or look at specific states.</p>
<section id="census-geography" class="level3">
<h3 class="anchored" data-anchor-id="census-geography">Census Geography</h3>
<p>The FCC data are reported at the Census Block level using the 15-digit FIPS code. This 15-digits FIPS code allows us to group the data to a bigger geographic level, such as Census Block group or County. The 15-digit FIPS code works as follows:</p>
<pre><code>* AABBBCCCCCCDEEE
- A: state
- B: county
- C: Census Tract
- D: Census Block Group
- E: Census Block
</code></pre>
<p>Each group is nested within the previous (e.g., Counties are nested within States, meaning FIPS county codes are unique within states, and so on for smaller census geography units). State is the largest and Census Block is the smallest.</p>
</section>
<section id="technology-codes" class="level3">
<h3 class="anchored" data-anchor-id="technology-codes">Technology Codes</h3>
<p>Within the old FCC Fixed Broadband Deployment Data there are 14 technology codes:</p>
<pre><code>- 10: Asymmetric xDSL
- 11: ADSL2, ADSL2+
- 12: VDSL
- 20: Symmetric xDSL
- 30: All other copper-wire tech
- 40: Cable Modem other
- 41: Cable Modem – DOCSIS 1, 1.1 or 2.0
- 42: Cable Modem – DOCSIS 3.0
- 43: Cable Modem – DOCSIS 3.1
- 50: Fiber to the end user
- 60: Satellite
- 70: Terrestrial Fixed Wireless
- 90: Electric Power Line
- 0: All Other</code></pre>
<p>Within the new FCC Fixed Broadband Deployment Data there are 9 technology codes:</p>
<pre><code>- 10: Copper Wire
- 40: Coaxial Cable / HFC
- 50: Optical Carrier / Fiber to the Premises
- 60: Geostationary Satellite
- 61: Non-geostationary Satellite
- 70: Unlicensed Fixed Wireless
- 71: Licensed Fixed Wireless
- 72: Licensed-by-Rule Fixed Wireless
- 0: Other</code></pre>
<p>Not all technologies are equally effective or reliable. The types of technology are not discussed here, but for more information visit <a href="https://broadbandnow.com/research">BroadbandNow</a>.</p>
<p>The option to process the FCC data without certain technologies came about because in our research projects we excluded satellite and fixed wireless technologies. This is because, according to the Fourteenth Broadband Deployment Report, the FCC broadband deployment data indicates satellite service is available nearly everywhere, however subscription rates are relatively low. The report also denotes that fixed wireless data in the FCC broadband deployment data follows trends similar to satellite, potentially signifying the deployment data overestimates availability of satellite and fixed wireless technologies. Additionally, fixed wireless and satellite technologies are inconsistent, suffering from issues such as weather interference, and delays more than other broadband technologies. These issues imply that both satellite and fixed wireless technology reporting may be especially suspect as a measure of access; in particular when those considerations are made along rural-urban lines.</p>
<p>Another reason for the flexibility allowed with technology codes is that one may wish to focus only on one type of technology, excluding all others, to see where it is available throughout the country or a state.</p>
</section>
<section id="broadband-speeds" class="level3">
<h3 class="anchored" data-anchor-id="broadband-speeds">Broadband Speeds</h3>
<p>In 2024 the speed threshold for adequate broadband service was updated to a 100 Mbps download speed and 20 Mbps upload speed, according to the FCC.</p>
<p>Currently adequate broadband service is considered a download speed of 25 Mbps and upload speed of 3 Mbps. Required speeds as technology needs continue to grow is a topic of ongoing discussion. The consensus is that the average broadband user needs higher download speeds than upload, but this may be an outdated understanding of consumer needs. The FCC Consumer Broadband Speed Guide indicates that a speed threshold of 25/3 Mbps is adequate for activities such as general usage, streaming video, video conferencing, and gaming. However, the FCC Household Broadband Guide indicates that download speeds of more than 25 Mbps may be necessary for households with moderate to high broadband use by 4 or more users or devices at a time. On the other hand, some say that 25/3 Mbps is an outdated definition of broadband and 100/10 Mbps is standard.</p>
<p>With the various speeds in mind, the processing function of this package was written to allow for flexibility of user inputted speed thresholds. 5 different thresholds can be considered in the process function which counts the number of providers providing internet at or above the threshold within the specified census geography region. One must input a vector of download speeds and a vector of upload speeds of equal length. The elements of the vectors are matched for the thresholds.</p>
</section>
</section>
<section id="using-processfcc" class="level2">
<h2 class="anchored" data-anchor-id="using-processfcc">Using <code>processFCC</code></h2>
<section id="new-fcc-data-format" class="level3">
<h3 class="anchored" data-anchor-id="new-fcc-data-format">New FCC data format</h3>
<p>To process a data time point in the “new” format you will need functions: <code>avail_new_dates()</code> and <code>rollup_new_FCC()</code>. The most efficient way to work with the new FCC data is the public data application programming interface (API). Using the API functionality avoids the laborious task of downloading each data file individually from the website.</p>
<p>To use the FCC API and the functions in this package for the “new” data format, it is necessary need to create an FCC User Registration account and then setup an API key. To create an account, go to <a href="https://broadbandmap.fcc.gov/login" class="uri">https://broadbandmap.fcc.gov/login</a> and then click “Create an account” in the bottom right of the sign-in box.</p>
<p>Once logged into your account, click on your username in the top right corner. Then select the “Manage API Access” option. From this new page, click the “Generate” button to generate a new API token. Copy the token value and save it in a separate location. You will need this key and your username in <code>avail_new_dates()</code> and <code>rollup_new_FCC()</code> to access the database. For more information on creating an API key visit the <a href="https://us-fcc.app.box.com/v/bdc-public-data-api-spec">FCC API Instructions</a>.</p>
<p>Once your account and API key is set up. The function <code>avail_new_dates()</code> can be used to see available dates in the new FCC data format.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb5"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb5-1"><a href="#cb5-1" aria-hidden="true" tabindex="-1"></a><span class="fu">avail_new_dates</span>(</span>
<span id="cb5-2"><a href="#cb5-2" aria-hidden="true" tabindex="-1"></a> <span class="at">fcc_username =</span> <span class="st">"[email protected]"</span>,</span>
<span id="cb5-3"><a href="#cb5-3" aria-hidden="true" tabindex="-1"></a> <span class="at">api_key =</span> <span class="st">"longstringofapicharacters"</span></span>
<span id="cb5-4"><a href="#cb5-4" aria-hidden="true" tabindex="-1"></a>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>If you already know what date you would like to process you can move right to using <code>rollup_new_FCC()</code>. The rollup functions count the number of unique broadband providers providing internet at or above the speed threshold combinations, up to 5 speed thresholds combinations can be specified. It is also possible to exclude specific broadband technologies or process only some states.</p>
<p>The arguments of <code>rollup_new_FCC()</code> are as follows:</p>
<ul>
<li><code>fcc_username</code>: username for existing FCC account</li>
<li><code>api_key</code>: user’s unique API key for accessing FCC data. Generated within FCC account.</li>
<li><code>get_year</code>: the year of the FCC data to process.</li>
<li><code>get_month</code>: the month of the FCC data to process: either “Jun” or “Dec”.</li>
<li><code>states</code>: A vector of the state(s) abbreviations to include in the final data. The default, NULL, includes all states and territories in the final data set.</li>
<li><code>geogr</code>: Character representation of Census geography to summarize the data set at: census block (cb), census block group (cbg), census tract (ct), county (county).</li>
<li><code>tech_exc</code>: Vector of technology codes to exclude from data when rolling up. If you do not wish to exclude any technology codes input NA or c(NA). By default, satellite technologies are excluded.</li>
<li><code>thresh_down</code>: Vector of download speeds thresholds with maximum length of 5. The vector must be the same length as <code>thresh_up</code> because elements of the vectors will be matched to count the number of internet providers at the given download/upload speed combinations.</li>
<li><code>thresh_up</code>: Vector of download speeds thresholds with maximum length of 5. The vector must be the same length as <code>thresh_down</code> because elements of the vectors will be matched to count the number of internet providers at the given download/upload speed combinations.</li>
<li><code>save_csv</code>: Logical for whether or not to save the processed data as a CSV.</li>
<li><code>wd</code>: filepath representing the working directory where the CSV should be saved. By default, this argument is set to the current working directory which is the file location in a qmd/rmd document or R project.</li>
</ul>
<p>In the following call to <code>rollup_new_FCC()</code>, the June 2022 FCC data is rolled up to the census block level for all states (<code>states = NULL</code>) excluding technology codes 60, and 70. For each census block, the processed data considers 5 threshold combinations for download/upload speeds: 25/3, 25/5, 50/5, 75/10, and 100/100 Mbps. The processed data will count the number of providers within the census block providing broadband at the threshold speeds and excluding the specified technologies. No CSV file is saved with the final dataset.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb6"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb6-1"><a href="#cb6-1" aria-hidden="true" tabindex="-1"></a><span class="fu">rollup_new_FCC</span>(</span>
<span id="cb6-2"><a href="#cb6-2" aria-hidden="true" tabindex="-1"></a> <span class="at">fcc_username =</span> <span class="st">"[email protected]"</span>,</span>
<span id="cb6-3"><a href="#cb6-3" aria-hidden="true" tabindex="-1"></a> <span class="at">api_key =</span> <span class="st">"longstringofapicharacters"</span>,</span>
<span id="cb6-4"><a href="#cb6-4" aria-hidden="true" tabindex="-1"></a> <span class="at">get_year =</span> <span class="st">"2022"</span>,</span>
<span id="cb6-5"><a href="#cb6-5" aria-hidden="true" tabindex="-1"></a> <span class="at">get_month =</span> <span class="st">"Jun"</span>,</span>
<span id="cb6-6"><a href="#cb6-6" aria-hidden="true" tabindex="-1"></a> <span class="at">states =</span> <span class="cn">NULL</span>,</span>
<span id="cb6-7"><a href="#cb6-7" aria-hidden="true" tabindex="-1"></a> <span class="at">geogr =</span> <span class="st">"cb"</span>,</span>
<span id="cb6-8"><a href="#cb6-8" aria-hidden="true" tabindex="-1"></a> <span class="at">tech_exc =</span> <span class="fu">c</span>(<span class="st">"60"</span>, <span class="st">"70"</span>),</span>
<span id="cb6-9"><a href="#cb6-9" aria-hidden="true" tabindex="-1"></a> <span class="at">thresh_down =</span> <span class="fu">c</span>(<span class="dv">25</span>, <span class="dv">25</span>, <span class="dv">50</span>, <span class="dv">100</span>, <span class="dv">100</span>),</span>
<span id="cb6-10"><a href="#cb6-10" aria-hidden="true" tabindex="-1"></a> <span class="at">thresh_up =</span> <span class="fu">c</span>(<span class="dv">3</span>, <span class="dv">5</span>, <span class="dv">10</span>, <span class="dv">10</span>, <span class="dv">100</span>),</span>
<span id="cb6-11"><a href="#cb6-11" aria-hidden="true" tabindex="-1"></a> <span class="at">save_csv =</span> <span class="cn">FALSE</span>,</span>
<span id="cb6-12"><a href="#cb6-12" aria-hidden="true" tabindex="-1"></a> <span class="at">wd =</span> <span class="fu">getwd</span>()</span>
<span id="cb6-13"><a href="#cb6-13" aria-hidden="true" tabindex="-1"></a>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
<section id="old-fcc-data-format" class="level3">
<h3 class="anchored" data-anchor-id="old-fcc-data-format">Old FCC data format</h3>
<p>If you wish to use a time point of FCC data from the “old” format you will need to use the following functions: <code>old_FCC_links()</code>, <code>csv_to_sql_db()</code>, and <code>rollup_old_FCC()</code>.</p>
<p><code>old_FCC_links()</code> returns URL(s)for the download website for the unprocessed data set of interest. If year and month are left NULL then the output data set will contain all available years and months for the old format. Set most_recent to TRUE in order to only get the link for the most recent version of the dataset(s), otherwise all available versions will be output. For example, June 2018 is one time point of the data and there may be multiple versions of one time point as the data is updated.</p>
<p>The following code results in a data set with the URLs for where to download the most recent versions of the June and December 2020 FCC data.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb7"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb7-1"><a href="#cb7-1" aria-hidden="true" tabindex="-1"></a><span class="fu">old_FCC_links</span>(<span class="at">year =</span> <span class="dv">2020</span>, <span class="at">month =</span> <span class="cn">NULL</span>, <span class="at">most_recent =</span> <span class="cn">TRUE</span>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Based on the FCC data time point you would like to process, copy the link from the <code>old_FCC_links()</code> output, go to the website, and download the US - Fixed with Satellite data set under the “Fixed Broadband Deployment Block Data” header. The code should still work if you decide to work with a state-level data set but the code is originally designed to work with the U.S. data set. Once on the website from <code>old_FCC_links()</code>, the link for the csv file may take you to dropbox. In this case you will need to click on the 3 dots icon on the top bar of the page and then click download.</p>
<p>Next, the FCC CSV file should be added to a SQLite database. To do this, first, create the database connection with <code>dbConnect()</code>. Here, we name the database fcc.sqlite. You can use <code>dbListTables()</code> to check if any tables already exist in the database if you have used the connection previously. Next, in the call to <code>csv_to_sql_db()</code>, we add the FCC data for June 2020 in Alaska to the SQLite database. The name of the table being created in the SQLite database is fcc_ak_2020_Jun, the default table name would not include the Alaska designation. In <code>csv_to_sql_db()</code>, you must set the arguments <code>csv_file</code> and <code>con</code>.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb8"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb8-1"><a href="#cb8-1" aria-hidden="true" tabindex="-1"></a>fcc_con <span class="ot"><-</span> <span class="fu">dbConnect</span>(<span class="fu">SQLite</span>(), <span class="at">dbname =</span> <span class="st">"fcc.sqlite"</span>)</span>
<span id="cb8-2"><a href="#cb8-2" aria-hidden="true" tabindex="-1"></a><span class="fu">dbListTables</span>(fcc_con)</span>
<span id="cb8-3"><a href="#cb8-3" aria-hidden="true" tabindex="-1"></a></span>
<span id="cb8-4"><a href="#cb8-4" aria-hidden="true" tabindex="-1"></a><span class="fu">csv_to_sql_db</span>(<span class="at">csv_file =</span> <span class="st">"C:/Users/kaile/Downloads/AK-Fixed-Jun2020-v2.csv"</span>, </span>
<span id="cb8-5"><a href="#cb8-5" aria-hidden="true" tabindex="-1"></a> <span class="at">con =</span> fcc_con, </span>
<span id="cb8-6"><a href="#cb8-6" aria-hidden="true" tabindex="-1"></a> <span class="at">new_tbl_name =</span> <span class="st">"fcc_ak_2020_Jun"</span>,</span>
<span id="cb8-7"><a href="#cb8-7" aria-hidden="true" tabindex="-1"></a> <span class="at">year =</span> <span class="dv">2020</span>, <span class="at">month =</span> <span class="st">"Jun"</span>,</span>
<span id="cb8-8"><a href="#cb8-8" aria-hidden="true" tabindex="-1"></a> <span class="at">pre_process_size =</span> <span class="dv">1000</span>, <span class="at">chunk_size =</span> <span class="dv">50000</span>, </span>
<span id="cb8-9"><a href="#cb8-9" aria-hidden="true" tabindex="-1"></a> <span class="at">show_progress_bar =</span> <span class="cn">TRUE</span>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>Once the data is in the SQLite database, we can then process it using <code>rollup_old_FCC()</code>. The output of this function is a data set based on the specifications from the function arguments similar to <code>rollup_new_FCC()</code>. The following call to <code>rollup_old_FCC()</code> processes the June 2020 Alaska FCC data that was just loaded to the SQLite database. The processed data is rolled up to the Census Tract level excluding technology codes 0, 60, and 70 and looks at 5 threshold combinations for download/upload speeds: 25/3, 25/5, 50/5, 75/10, and 100/100 Mbps. The processed data will count the number of providers within the census tract providing broadband at the thresholds. No CSV file is saved with the final dataset.</p>
<p>In the <code>rollup_old_FCC()</code> function, the arguments <code>con</code> and <code>table_in_con</code> are the only two differences from <code>rollup_new_FCC()</code>: <code>con</code> is a SQLite database connection and <code>table_in_con</code> is the table in the <code>con</code> database to process.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb9"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb9-1"><a href="#cb9-1" aria-hidden="true" tabindex="-1"></a>processed_dat <span class="ot"><-</span> <span class="fu">rollup_old_FCC</span>(</span>
<span id="cb9-2"><a href="#cb9-2" aria-hidden="true" tabindex="-1"></a> <span class="at">con =</span> fcc_con,</span>
<span id="cb9-3"><a href="#cb9-3" aria-hidden="true" tabindex="-1"></a> <span class="at">table_in_con =</span> <span class="st">"fcc_ak_2020_Jun"</span>,</span>
<span id="cb9-4"><a href="#cb9-4" aria-hidden="true" tabindex="-1"></a> <span class="at">year =</span> <span class="dv">2020</span>, <span class="at">month =</span> <span class="st">"Jun"</span>,</span>
<span id="cb9-5"><a href="#cb9-5" aria-hidden="true" tabindex="-1"></a> <span class="at">geogr =</span> <span class="st">"ct"</span>,</span>
<span id="cb9-6"><a href="#cb9-6" aria-hidden="true" tabindex="-1"></a> <span class="at">tech_exc =</span> <span class="fu">c</span>(<span class="st">"0"</span>, <span class="st">"60"</span>, <span class="st">"70"</span>),</span>
<span id="cb9-7"><a href="#cb9-7" aria-hidden="true" tabindex="-1"></a> <span class="at">thresh_down =</span> <span class="fu">c</span>(<span class="dv">25</span>, <span class="dv">25</span>, <span class="dv">50</span>, <span class="dv">75</span>, <span class="dv">100</span>),</span>
<span id="cb9-8"><a href="#cb9-8" aria-hidden="true" tabindex="-1"></a> <span class="at">thresh_up =</span> <span class="fu">c</span>(<span class="dv">3</span>, <span class="dv">5</span>, <span class="dv">5</span>, <span class="dv">10</span>, <span class="dv">100</span>)</span>
<span id="cb9-9"><a href="#cb9-9" aria-hidden="true" tabindex="-1"></a> )</span>
<span id="cb9-10"><a href="#cb9-10" aria-hidden="true" tabindex="-1"></a><span class="fu">head</span>(processed_dat)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
<p>If you are done working with the SQLite database, be sure to disconnect from the database with <code>dbDisconnect()</code>. You may also want to remove the sqlite, ZIP, and raw CSV files from your working directory because they are considerably large and take up a lot of space.</p>
<div class="cell">
<div class="sourceCode cell-code" id="cb10"><pre class="sourceCode r code-with-copy"><code class="sourceCode r"><span id="cb10-1"><a href="#cb10-1" aria-hidden="true" tabindex="-1"></a><span class="fu">dbDisconnect</span>(con)</span>
<span id="cb10-2"><a href="#cb10-2" aria-hidden="true" tabindex="-1"></a><span class="fu">file.remove</span>(<span class="st">"fcc.sqlite"</span>)</span></code><button title="Copy to Clipboard" class="code-copy-button"><i class="bi"></i></button></pre></div>
</div>
</section>
</section>
<section id="remarks" class="level2">
<h2 class="anchored" data-anchor-id="remarks">Remarks</h2>
<p>Once the FCC fixed broadband data is on a level where there is one row per census geography region it is much easier to work with and opens a world of possibilities, such as calculating the proportion of people within a larger census geography with access to a certain speed or making maps of the number of providers per region. For example, here is a map of the state of Iowa at the Census Block level created using the FCC data. Satellite technology was excluded in rolling up the data to a 25/3 threshold. The provider count at 25/3 Mbps was then converted to a binary variable where 1 indicates access to broadband at 25/3 and 0 indicates no access to internet at 25/3.</p>
<div class="cell" data-layout-align="center">
<div class="cell-output-display">
<div class="quarto-figure quarto-figure-center">
<figure class="figure">
<p><img src="map%20of%20iowa%20no%20sat.png" class="img-fluid quarto-figure quarto-figure-center figure-img" style="width:50.0%"></p>
</figure>
</div>
</div>
</div>
</section>
</section>
</main>
<!-- /main column -->
<script id="quarto-html-after-body" type="application/javascript">
window.document.addEventListener("DOMContentLoaded", function (event) {
const toggleBodyColorMode = (bsSheetEl) => {
const mode = bsSheetEl.getAttribute("data-mode");
const bodyEl = window.document.querySelector("body");
if (mode === "dark") {
bodyEl.classList.add("quarto-dark");
bodyEl.classList.remove("quarto-light");
} else {
bodyEl.classList.add("quarto-light");
bodyEl.classList.remove("quarto-dark");
}
}
const toggleBodyColorPrimary = () => {
const bsSheetEl = window.document.querySelector("link#quarto-bootstrap");
if (bsSheetEl) {
toggleBodyColorMode(bsSheetEl);
}
}
toggleBodyColorPrimary();
const icon = "";
const anchorJS = new window.AnchorJS();
anchorJS.options = {
placement: 'right',
icon: icon
};
anchorJS.add('.anchored');
const isCodeAnnotation = (el) => {
for (const clz of el.classList) {
if (clz.startsWith('code-annotation-')) {
return true;
}
}
return false;
}
const onCopySuccess = function(e) {
// button target
const button = e.trigger;
// don't keep focus
button.blur();
// flash "checked"
button.classList.add('code-copy-button-checked');
var currentTitle = button.getAttribute("title");
button.setAttribute("title", "Copied!");
let tooltip;
if (window.bootstrap) {
button.setAttribute("data-bs-toggle", "tooltip");
button.setAttribute("data-bs-placement", "left");
button.setAttribute("data-bs-title", "Copied!");
tooltip = new bootstrap.Tooltip(button,
{ trigger: "manual",
customClass: "code-copy-button-tooltip",
offset: [0, -8]});
tooltip.show();
}
setTimeout(function() {
if (tooltip) {
tooltip.hide();
button.removeAttribute("data-bs-title");
button.removeAttribute("data-bs-toggle");
button.removeAttribute("data-bs-placement");
}
button.setAttribute("title", currentTitle);
button.classList.remove('code-copy-button-checked');
}, 1000);
// clear code selection
e.clearSelection();
}
const getTextToCopy = function(trigger) {
const codeEl = trigger.previousElementSibling.cloneNode(true);
for (const childEl of codeEl.children) {
if (isCodeAnnotation(childEl)) {
childEl.remove();
}
}
return codeEl.innerText;
}
const clipboard = new window.ClipboardJS('.code-copy-button:not([data-in-quarto-modal])', {
text: getTextToCopy
});
clipboard.on('success', onCopySuccess);
if (window.document.getElementById('quarto-embedded-source-code-modal')) {
// For code content inside modals, clipBoardJS needs to be initialized with a container option
// TODO: Check when it could be a function (https://github.com/zenorocha/clipboard.js/issues/860)
const clipboardModal = new window.ClipboardJS('.code-copy-button[data-in-quarto-modal]', {
text: getTextToCopy,
container: window.document.getElementById('quarto-embedded-source-code-modal')
});
clipboardModal.on('success', onCopySuccess);
}
var localhostRegex = new RegExp(/^(?:http|https):\/\/localhost\:?[0-9]*\//);
var mailtoRegex = new RegExp(/^mailto:/);
var filterRegex = new RegExp('/' + window.location.host + '/');
var isInternal = (href) => {
return filterRegex.test(href) || localhostRegex.test(href) || mailtoRegex.test(href);
}
// Inspect non-navigation links and adorn them if external
var links = window.document.querySelectorAll('a[href]:not(.nav-link):not(.navbar-brand):not(.toc-action):not(.sidebar-link):not(.sidebar-item-toggle):not(.pagination-link):not(.no-external):not([aria-hidden]):not(.dropdown-item):not(.quarto-navigation-tool):not(.about-link)');
for (var i=0; i<links.length; i++) {
const link = links[i];
if (!isInternal(link.href)) {
// undo the damage that might have been done by quarto-nav.js in the case of
// links that we want to consider external
if (link.dataset.originalHref !== undefined) {
link.href = link.dataset.originalHref;
}
}
}
function tippyHover(el, contentFn, onTriggerFn, onUntriggerFn) {
const config = {
allowHTML: true,
maxWidth: 500,
delay: 100,
arrow: false,
appendTo: function(el) {
return el.parentElement;
},
interactive: true,
interactiveBorder: 10,
theme: 'quarto',
placement: 'bottom-start',
};
if (contentFn) {
config.content = contentFn;
}
if (onTriggerFn) {
config.onTrigger = onTriggerFn;
}
if (onUntriggerFn) {
config.onUntrigger = onUntriggerFn;
}
window.tippy(el, config);
}
const noterefs = window.document.querySelectorAll('a[role="doc-noteref"]');
for (var i=0; i<noterefs.length; i++) {
const ref = noterefs[i];
tippyHover(ref, function() {
// use id or data attribute instead here
let href = ref.getAttribute('data-footnote-href') || ref.getAttribute('href');
try { href = new URL(href).hash; } catch {}
const id = href.replace(/^#\/?/, "");
const note = window.document.getElementById(id);
if (note) {
return note.innerHTML;
} else {
return "";
}
});
}
const xrefs = window.document.querySelectorAll('a.quarto-xref');
const processXRef = (id, note) => {
// Strip column container classes
const stripColumnClz = (el) => {
el.classList.remove("page-full", "page-columns");
if (el.children) {
for (const child of el.children) {
stripColumnClz(child);
}
}
}
stripColumnClz(note)
if (id === null || id.startsWith('sec-')) {
// Special case sections, only their first couple elements
const container = document.createElement("div");
if (note.children && note.children.length > 2) {
container.appendChild(note.children[0].cloneNode(true));
for (let i = 1; i < note.children.length; i++) {
const child = note.children[i];
if (child.tagName === "P" && child.innerText === "") {
continue;
} else {
container.appendChild(child.cloneNode(true));
break;
}
}
if (window.Quarto?.typesetMath) {
window.Quarto.typesetMath(container);
}
return container.innerHTML
} else {
if (window.Quarto?.typesetMath) {
window.Quarto.typesetMath(note);
}
return note.innerHTML;
}
} else {
// Remove any anchor links if they are present
const anchorLink = note.querySelector('a.anchorjs-link');
if (anchorLink) {
anchorLink.remove();
}
if (window.Quarto?.typesetMath) {
window.Quarto.typesetMath(note);
}
// TODO in 1.5, we should make sure this works without a callout special case
if (note.classList.contains("callout")) {
return note.outerHTML;
} else {
return note.innerHTML;
}
}
}
for (var i=0; i<xrefs.length; i++) {
const xref = xrefs[i];
tippyHover(xref, undefined, function(instance) {
instance.disable();
let url = xref.getAttribute('href');
let hash = undefined;
if (url.startsWith('#')) {
hash = url;
} else {
try { hash = new URL(url).hash; } catch {}
}
if (hash) {
const id = hash.replace(/^#\/?/, "");
const note = window.document.getElementById(id);
if (note !== null) {
try {
const html = processXRef(id, note.cloneNode(true));
instance.setContent(html);
} finally {
instance.enable();
instance.show();
}
} else {
// See if we can fetch this
fetch(url.split('#')[0])
.then(res => res.text())
.then(html => {
const parser = new DOMParser();
const htmlDoc = parser.parseFromString(html, "text/html");
const note = htmlDoc.getElementById(id);
if (note !== null) {
const html = processXRef(id, note);
instance.setContent(html);
}
}).finally(() => {
instance.enable();
instance.show();
});
}
} else {
// See if we can fetch a full url (with no hash to target)
// This is a special case and we should probably do some content thinning / targeting
fetch(url)
.then(res => res.text())
.then(html => {
const parser = new DOMParser();
const htmlDoc = parser.parseFromString(html, "text/html");
const note = htmlDoc.querySelector('main.content');
if (note !== null) {
// This should only happen for chapter cross references
// (since there is no id in the URL)
// remove the first header
if (note.children.length > 0 && note.children[0].tagName === "HEADER") {
note.children[0].remove();
}
const html = processXRef(null, note);
instance.setContent(html);
}
}).finally(() => {
instance.enable();
instance.show();
});
}
}, function(instance) {
});
}
let selectedAnnoteEl;
const selectorForAnnotation = ( cell, annotation) => {
let cellAttr = 'data-code-cell="' + cell + '"';
let lineAttr = 'data-code-annotation="' + annotation + '"';
const selector = 'span[' + cellAttr + '][' + lineAttr + ']';
return selector;
}
const selectCodeLines = (annoteEl) => {
const doc = window.document;
const targetCell = annoteEl.getAttribute("data-target-cell");
const targetAnnotation = annoteEl.getAttribute("data-target-annotation");
const annoteSpan = window.document.querySelector(selectorForAnnotation(targetCell, targetAnnotation));
const lines = annoteSpan.getAttribute("data-code-lines").split(",");
const lineIds = lines.map((line) => {
return targetCell + "-" + line;
})
let top = null;
let height = null;
let parent = null;
if (lineIds.length > 0) {
//compute the position of the single el (top and bottom and make a div)
const el = window.document.getElementById(lineIds[0]);
top = el.offsetTop;
height = el.offsetHeight;
parent = el.parentElement.parentElement;
if (lineIds.length > 1) {
const lastEl = window.document.getElementById(lineIds[lineIds.length - 1]);
const bottom = lastEl.offsetTop + lastEl.offsetHeight;
height = bottom - top;
}
if (top !== null && height !== null && parent !== null) {
// cook up a div (if necessary) and position it
let div = window.document.getElementById("code-annotation-line-highlight");
if (div === null) {
div = window.document.createElement("div");
div.setAttribute("id", "code-annotation-line-highlight");
div.style.position = 'absolute';
parent.appendChild(div);
}
div.style.top = top - 2 + "px";
div.style.height = height + 4 + "px";
div.style.left = 0;
let gutterDiv = window.document.getElementById("code-annotation-line-highlight-gutter");
if (gutterDiv === null) {
gutterDiv = window.document.createElement("div");
gutterDiv.setAttribute("id", "code-annotation-line-highlight-gutter");
gutterDiv.style.position = 'absolute';
const codeCell = window.document.getElementById(targetCell);
const gutter = codeCell.querySelector('.code-annotation-gutter');
gutter.appendChild(gutterDiv);
}
gutterDiv.style.top = top - 2 + "px";
gutterDiv.style.height = height + 4 + "px";
}
selectedAnnoteEl = annoteEl;
}
};
const unselectCodeLines = () => {
const elementsIds = ["code-annotation-line-highlight", "code-annotation-line-highlight-gutter"];
elementsIds.forEach((elId) => {
const div = window.document.getElementById(elId);
if (div) {
div.remove();
}
});
selectedAnnoteEl = undefined;
};
// Handle positioning of the toggle
window.addEventListener(
"resize",
throttle(() => {
elRect = undefined;
if (selectedAnnoteEl) {
selectCodeLines(selectedAnnoteEl);
}
}, 10)
);
function throttle(fn, ms) {
let throttle = false;
let timer;
return (...args) => {
if(!throttle) { // first call gets through
fn.apply(this, args);
throttle = true;
} else { // all the others get throttled
if(timer) clearTimeout(timer); // cancel #2
timer = setTimeout(() => {
fn.apply(this, args);
timer = throttle = false;
}, ms);
}
};
}
// Attach click handler to the DT
const annoteDls = window.document.querySelectorAll('dt[data-target-cell]');
for (const annoteDlNode of annoteDls) {
annoteDlNode.addEventListener('click', (event) => {
const clickedEl = event.target;
if (clickedEl !== selectedAnnoteEl) {
unselectCodeLines();
const activeEl = window.document.querySelector('dt[data-target-cell].code-annotation-active');
if (activeEl) {
activeEl.classList.remove('code-annotation-active');
}
selectCodeLines(clickedEl);
clickedEl.classList.add('code-annotation-active');
} else {
// Unselect the line
unselectCodeLines();
clickedEl.classList.remove('code-annotation-active');
}
});
}
const findCites = (el) => {
const parentEl = el.parentElement;
if (parentEl) {
const cites = parentEl.dataset.cites;
if (cites) {
return {
el,
cites: cites.split(' ')
};
} else {
return findCites(el.parentElement)
}
} else {
return undefined;
}
};
var bibliorefs = window.document.querySelectorAll('a[role="doc-biblioref"]');
for (var i=0; i<bibliorefs.length; i++) {
const ref = bibliorefs[i];
const citeInfo = findCites(ref);
if (citeInfo) {
tippyHover(citeInfo.el, function() {
var popup = window.document.createElement('div');
citeInfo.cites.forEach(function(cite) {
var citeDiv = window.document.createElement('div');
citeDiv.classList.add('hanging-indent');
citeDiv.classList.add('csl-entry');
var biblioDiv = window.document.getElementById('ref-' + cite);
if (biblioDiv) {
citeDiv.innerHTML = biblioDiv.innerHTML;
}
popup.appendChild(citeDiv);
});
return popup.innerHTML;
});
}
}
});
</script>
</div> <!-- /content -->
</body></html>