Parsing Data From A PDF

This post will be the first in a series of posts demonstrating how I approach data wrangling problems. As an analyst in a clinical research setting I spend a great majority of my time cleaning and transforming data for analysis and thought it’d be a good idea to start documenting my approach to various types of data.

The Data

The focus of this post will be on parsing data from the attached two paged pdf file that I’ve taken from the Minnesota Transportation Alliance Membership Directory & Funding Guide and transforming it into a tidy dataset.

Based on a cursory view of the file I noticed that each entry begins with the name of the county and ends with the email. The six variables for each entry occur in the following order:

County
Name
Job title. This can be be on line three or lines three and four. Carver County is one example where the job title spans two lines
Phone number. This can be on line four or line five depending on the number of lines the job title spans
Address. This can be on lines five and six or lines six and seven depending on the number of lines the job title spans
Email address.

Load Data

library(tidyverse)
library(kableExtra)

#  each page is saved as a list element
raw_file <- pdftools::pdf_data("county_engineers.pdf")

# select the first pdf page
raw_file[[1]] %>%
  select(text, space) %>% 
  rownames_to_column() %>% 
  kable(caption = "Table 1") %>%
  kable_styling(full_width = FALSE) %>% 
  scroll_box(height = '600px')

Table 1
rowname	text	space
1	County	TRUE
2	Engineers	FALSE
3	Aitkin	TRUE
4	County	FALSE
5	John	TRUE
6	Welle	FALSE
7	County	TRUE
8	Engineer	FALSE
9		TRUE
10	927-7569	FALSE
11	1211	TRUE
12	Air	TRUE
13	Park	TRUE
14	Dr	FALSE
15	Aitkin,	TRUE
16	MN	TRUE
17	56431	FALSE
18	john.welle@co.aitkin.mn.us	FALSE
19	Brown	TRUE
20	County	FALSE
21	Wayne	TRUE
22	Stevens	FALSE
23	County	TRUE
24	Engineer	FALSE
25		TRUE
26	233-5700	FALSE
27	1901	TRUE
28	N	TRUE
29	Jefferson	TRUE
30	St	FALSE
31	New	TRUE
32	Ulm,	TRUE
33	MN	TRUE
34	56073	FALSE
35	wayne.stevens@co.brown.mn.us	FALSE
36	Clay	TRUE
37	County	FALSE
38	David	TRUE
39	Overbo	FALSE
40	County	TRUE
41	Engineer	FALSE
42		TRUE
43	299-5099	FALSE
44	2951	TRUE
45	41	TRUE
46	1/2	TRUE
47	St	TRUE
48	S	FALSE
49	Moorhead,	TRUE
50	MN	TRUE
51	56560	FALSE
52	david.overbo@co.clay.mn.us	FALSE
53	Anoka	TRUE
54	County	FALSE
55	Joe	TRUE
56	MacPherson	FALSE
57	County	TRUE
58	Engineer	FALSE
59		TRUE
60	324-3199	FALSE
61	1440	TRUE
62	Bunker	TRUE
63	Lake	TRUE
64	Blvd	TRUE
65	NW	FALSE
66	Andover,	TRUE
67	MN	TRUE
68	55304	FALSE
69	joe.macpherson@co.anoka.mn.us	FALSE
70	Carlton	TRUE
71	County	FALSE
72	JinYeene	TRUE
73	Neumann	FALSE
74	County	TRUE
75	Engineer	FALSE
76		TRUE
77	384-9150	FALSE
78	1630	TRUE
79	County	TRUE
80	Road	TRUE
81	61	FALSE
82	Carlton,	TRUE
83	MN	TRUE
84	55718	FALSE
85	jinyeene.neumann@co.carlton.mn.us	FALSE
86	Clearwater	TRUE
87	County	FALSE
88	Dan	TRUE
89	Sauve	FALSE
90	County	TRUE
91	Engineer	FALSE
92		TRUE
93	694-6132	FALSE
94	213	TRUE
95	Main	TRUE
96	Ave	TRUE
97	N	FALSE
98	Bagley,	TRUE
99	MN	TRUE
100	56621	FALSE
101	dan.sauve@co.clearwater.mn.us	FALSE
102	Becker	TRUE
103	County	FALSE
104	Jim	TRUE
105	Olson	FALSE
106	County	TRUE
107	Engineer	FALSE
108		TRUE
109	847-4463	FALSE
110	200	TRUE
111	East	TRUE
112	State	TRUE
113	St	FALSE
114	Detroit	TRUE
115	Lakes,	TRUE
116	MN	TRUE
117	56501	FALSE
118	jdolson@co.becker.mn.us	FALSE
119	Carver	TRUE
120	County	FALSE
121	Lyndon	TRUE
122	Colebrook-Robjent	FALSE
123	Public	TRUE
124	Works	TRUE
125	Director/	TRUE
126	County	FALSE
127	Engineer	FALSE
128		TRUE
129	466-5206	FALSE
130	11360	TRUE
131	Highway	TRUE
132	212	TRUE
133	Ste	TRUE
134	1	FALSE
135	Cologne,	TRUE
136	MN	TRUE
137	55322	FALSE
138	lrobjent@co.carver.mn.us	FALSE
139	Cook	TRUE
140	County	FALSE
141	Robert	TRUE
142	Hass	FALSE
143	Highway	TRUE
144	Engineer	FALSE
145		TRUE
146	387-3695	FALSE
147	609	TRUE
148	E	TRUE
149	4th	TRUE
150	Ave	FALSE
151	Grand	TRUE
152	Marais,	TRUE
153	MN	TRUE
154	55604	FALSE
155	robert.hass@co.cook.mn.us	FALSE
156	Beltrami	TRUE
157	County	FALSE
158	Bruce	TRUE
159	Hasbargen	FALSE
160	County	TRUE
161	Engineer	FALSE
162		TRUE
163	333-8173	FALSE
164	2491	TRUE
165	Adams	TRUE
166	Ave	TRUE
167	NW	FALSE
168	Bemidji,	TRUE
169	MN	TRUE
170	56601	FALSE
171	bruce.hasbargen@co.beltrami.mn.us	FALSE
172	Benton	TRUE
173	County	FALSE
174	Chris	TRUE
175	Byrd	FALSE
176	County	TRUE
177	Engineer	FALSE
178		TRUE
179	968-5051	FALSE
180	PO	TRUE
181	Box	TRUE
182	247	FALSE
183	Foley,	TRUE
184	MN	TRUE
185	56329	FALSE
186	cbyrd@co.benton.mn.us	FALSE
187	Big	TRUE
188	Stone	TRUE
189	County	FALSE
190	Todd	TRUE
191	Larson	FALSE
192	County	TRUE
193	Engineer	FALSE
194		TRUE
195	839-2594	FALSE
196	437	TRUE
197	Minnesota	TRUE
198	St	TRUE
199	N	FALSE
200	Ortonville,	TRUE
201	MN	TRUE
202	56278	FALSE
203	todd.larson@bigstonecounty.org	FALSE
204	Blue	TRUE
205	Earth	TRUE
206	County	FALSE
207	Ryan	TRUE
208	Thilges	FALSE
209	Public	TRUE
210	Works	TRUE
211	Director	FALSE
212		TRUE
213	304-4031	FALSE
214	P.O.	TRUE
215	Box	TRUE
216	8608	FALSE
217	Mankato,	TRUE
218	MN	TRUE
219	56002	FALSE
220	ryan.thilges@blueearthcountymn.gov	FALSE
221	18	FALSE
222	Cass	TRUE
223	County	FALSE
224	Darrick	TRUE
225	Anderson	FALSE
226	County	TRUE
227	Engineer	FALSE
228		TRUE
229	547-5201	FALSE
230	PO	TRUE
231	Box	TRUE
232	579	FALSE
233	Walker,	TRUE
234	MN	TRUE
235	56484	FALSE
236	darrick.anderson@co.cass.mn.us	FALSE
237	Cottonwood	TRUE
238	County	FALSE
239	Nick	TRUE
240	Klisch	FALSE
241	Public	TRUE
242	Works	TRUE
243	Director/	TRUE
244	County	FALSE
245	Engineer	FALSE
246		TRUE
247	832-8811	FALSE
248	1355	TRUE
249	9th	TRUE
250	Ave	FALSE
251	Windom,	TRUE
252	MN	TRUE
253	56101	FALSE
254	nick.klisch@co.cottonwood.mn.us	FALSE
255	Chippewa	TRUE
256	County	FALSE
257	Jeremy	TRUE
258	Gilb	FALSE
259	County	TRUE
260	Engineer	FALSE
261		TRUE
262	269-2151	FALSE
263	902	TRUE
264	N	TRUE
265	17th	TRUE
266	St	FALSE
267	Montevideo,	TRUE
268	MN	TRUE
269	56265	FALSE
270	jgilb@co.chippewa.mn.us	FALSE
271	Crow	TRUE
272	Wing	TRUE
273	County	FALSE
274	Timothy	TRUE
275	Bray	FALSE
276	County	TRUE
277	Engineer	FALSE
278		TRUE
279	822-2684	FALSE
280	16589	TRUE
281	County	TRUE
282	Road	TRUE
283	142	FALSE
284	Brainerd,	TRUE
285	MN	TRUE
286	56401	FALSE
287	tim.bray@crowwing.us	FALSE
288	Chisago	TRUE
289	County	FALSE
290	Joe	TRUE
291	Triplett	FALSE
292	Public	TRUE
293	Works	TRUE
294	Director/	TRUE
295	County	FALSE
296	Engineer	FALSE
297		TRUE
298	213-8708	FALSE
299	313	TRUE
300	N	TRUE
301	Main	TRUE
302	St	TRUE
303	Rm	TRUE
304	400	FALSE
305	Center	TRUE
306	City,	TRUE
307	MN	TRUE
308	55012	FALSE
309	joe.triplett@co.chisago.mn.us	FALSE
310	Dakota	TRUE
311	County	FALSE
312	Mark	TRUE
313	Krebsbach	FALSE
314	Transportation	TRUE
315	Director/	TRUE
316	County	FALSE
317	Engineer	FALSE
318		TRUE
319	891-7102	FALSE
320	14955	TRUE
321	Galaxie	TRUE
322	Ave	FALSE
323	Saint	TRUE
324	Paul,	TRUE
325	MN	TRUE
326	55124	FALSE
327	mark.krebsbach@co.dakota.mn.us	FALSE
328	2020-2021	TRUE
329	Membership	TRUE
330	Directory	TRUE
331	&	TRUE
332	TransportationFunding	TRUE
333	Guide	FALSE

Table 1 shows the data from page 1 of the pdf as parsed by pdftools::pdf_data. The first two rows represent the page heading and thus should be removed. When space == TRUE for row n it means that the information in row n + 1 is on the same line as row n. When space == FALSE for row n it means that the information in row n + 1 is on a new line.

Identify Unique Observations

For the purpose of this post an observation refers to a unique person along with the identifiers for that person. Notice from rows 18 and 35 in Table 1 that the email signifies the end of an observation.

Let’s create the field person_identifier which uses the email address as a unique identifier for each observation. This identifier is constructed by detecting rows with an email address and then filling NA rows, with the email address, in an upward direction from that specific row. The data can then be split on person_identifier to obtain a list that contains a dataframe for each observation.

get_unique_observation <- function(raw_file) {
  unique_observation <- raw_file %>% 
    # remove first two rows as it's the page heading
    slice(-c(1:2)) %>%
    select(text, space) %>%
    mutate(person_identifier = if_else(
      # an email ends every observation
      str_detect(text, "\\@"),
      text,
      NA_character_
    )) %>% 
    # fill up from the row that the email occurs on to identify all data relating to an observation
    fill(person_identifier, .direction = "up") %>% 
    # remove rows with NA as it does not contain data for an observation
    filter(!is.na(person_identifier)) %>% 
    # split on each observation
    split(.$person_identifier)
  
  return(unique_observation)
}

Let’s see what this list of dataframes looks like.

unique_observations <- map(raw_file, get_unique_observation) %>% 
  flatten()

unique_observations

## $`bruce.hasbargen@co.beltrami.mn.us`
## # A tibble: 16 x 3
##    text                              space person_identifier                
##    <chr>                             <lgl> <chr>                            
##  1 Beltrami                          TRUE  bruce.hasbargen@co.beltrami.mn.us
##  2 County                            FALSE bruce.hasbargen@co.beltrami.mn.us
##  3 Bruce                             TRUE  bruce.hasbargen@co.beltrami.mn.us
##  4 Hasbargen                         FALSE bruce.hasbargen@co.beltrami.mn.us
##  5 County                            TRUE  bruce.hasbargen@co.beltrami.mn.us
##  6 Engineer                          FALSE bruce.hasbargen@co.beltrami.mn.us
##  7 (218)                             TRUE  bruce.hasbargen@co.beltrami.mn.us
##  8 333-8173                          FALSE bruce.hasbargen@co.beltrami.mn.us
##  9 2491                              TRUE  bruce.hasbargen@co.beltrami.mn.us
## 10 Adams                             TRUE  bruce.hasbargen@co.beltrami.mn.us
## 11 Ave                               TRUE  bruce.hasbargen@co.beltrami.mn.us
## 12 NW                                FALSE bruce.hasbargen@co.beltrami.mn.us
## 13 Bemidji,                          TRUE  bruce.hasbargen@co.beltrami.mn.us
## 14 MN                                TRUE  bruce.hasbargen@co.beltrami.mn.us
## 15 56601                             FALSE bruce.hasbargen@co.beltrami.mn.us
## 16 bruce.hasbargen@co.beltrami.mn.us FALSE bruce.hasbargen@co.beltrami.mn.us
## 
## $`cbyrd@co.benton.mn.us`
## # A tibble: 15 x 3
##    text                  space person_identifier    
##    <chr>                 <lgl> <chr>                
##  1 Benton                TRUE  cbyrd@co.benton.mn.us
##  2 County                FALSE cbyrd@co.benton.mn.us
##  3 Chris                 TRUE  cbyrd@co.benton.mn.us
##  4 Byrd                  FALSE cbyrd@co.benton.mn.us
##  5 County                TRUE  cbyrd@co.benton.mn.us
##  6 Engineer              FALSE cbyrd@co.benton.mn.us
##  7 (320)                 TRUE  cbyrd@co.benton.mn.us
##  8 968-5051              FALSE cbyrd@co.benton.mn.us
##  9 PO                    TRUE  cbyrd@co.benton.mn.us
## 10 Box                   TRUE  cbyrd@co.benton.mn.us
## 11 247                   FALSE cbyrd@co.benton.mn.us
## 12 Foley,                TRUE  cbyrd@co.benton.mn.us
## 13 MN                    TRUE  cbyrd@co.benton.mn.us
## 14 56329                 FALSE cbyrd@co.benton.mn.us
## 15 cbyrd@co.benton.mn.us FALSE cbyrd@co.benton.mn.us
## 
## $`dan.sauve@co.clearwater.mn.us`
## # A tibble: 16 x 3
##    text                          space person_identifier            
##    <chr>                         <lgl> <chr>                        
##  1 Clearwater                    TRUE  dan.sauve@co.clearwater.mn.us
##  2 County                        FALSE dan.sauve@co.clearwater.mn.us
##  3 Dan                           TRUE  dan.sauve@co.clearwater.mn.us
##  4 Sauve                         FALSE dan.sauve@co.clearwater.mn.us
##  5 County                        TRUE  dan.sauve@co.clearwater.mn.us
##  6 Engineer                      FALSE dan.sauve@co.clearwater.mn.us
##  7 (218)                         TRUE  dan.sauve@co.clearwater.mn.us
##  8 694-6132                      FALSE dan.sauve@co.clearwater.mn.us
##  9 213                           TRUE  dan.sauve@co.clearwater.mn.us
## 10 Main                          TRUE  dan.sauve@co.clearwater.mn.us
## 11 Ave                           TRUE  dan.sauve@co.clearwater.mn.us
## 12 N                             FALSE dan.sauve@co.clearwater.mn.us
## 13 Bagley,                       TRUE  dan.sauve@co.clearwater.mn.us
## 14 MN                            TRUE  dan.sauve@co.clearwater.mn.us
## 15 56621                         FALSE dan.sauve@co.clearwater.mn.us
## 16 dan.sauve@co.clearwater.mn.us FALSE dan.sauve@co.clearwater.mn.us
## 
## $`darrick.anderson@co.cass.mn.us`
## # A tibble: 16 x 3
##    text                           space person_identifier             
##    <chr>                          <lgl> <chr>                         
##  1 18                             FALSE darrick.anderson@co.cass.mn.us
##  2 Cass                           TRUE  darrick.anderson@co.cass.mn.us
##  3 County                         FALSE darrick.anderson@co.cass.mn.us
##  4 Darrick                        TRUE  darrick.anderson@co.cass.mn.us
##  5 Anderson                       FALSE darrick.anderson@co.cass.mn.us
##  6 County                         TRUE  darrick.anderson@co.cass.mn.us
##  7 Engineer                       FALSE darrick.anderson@co.cass.mn.us
##  8 (218)                          TRUE  darrick.anderson@co.cass.mn.us
##  9 547-5201                       FALSE darrick.anderson@co.cass.mn.us
## 10 PO                             TRUE  darrick.anderson@co.cass.mn.us
## 11 Box                            TRUE  darrick.anderson@co.cass.mn.us
## 12 579                            FALSE darrick.anderson@co.cass.mn.us
## 13 Walker,                        TRUE  darrick.anderson@co.cass.mn.us
## 14 MN                             TRUE  darrick.anderson@co.cass.mn.us
## 15 56484                          FALSE darrick.anderson@co.cass.mn.us
## 16 darrick.anderson@co.cass.mn.us FALSE darrick.anderson@co.cass.mn.us
## 
## $`david.overbo@co.clay.mn.us`
## # A tibble: 17 x 3
##    text                       space person_identifier         
##    <chr>                      <lgl> <chr>                     
##  1 Clay                       TRUE  david.overbo@co.clay.mn.us
##  2 County                     FALSE david.overbo@co.clay.mn.us
##  3 David                      TRUE  david.overbo@co.clay.mn.us
##  4 Overbo                     FALSE david.overbo@co.clay.mn.us
##  5 County                     TRUE  david.overbo@co.clay.mn.us
##  6 Engineer                   FALSE david.overbo@co.clay.mn.us
##  7 (218)                      TRUE  david.overbo@co.clay.mn.us
##  8 299-5099                   FALSE david.overbo@co.clay.mn.us
##  9 2951                       TRUE  david.overbo@co.clay.mn.us
## 10 41                         TRUE  david.overbo@co.clay.mn.us
## 11 1/2                        TRUE  david.overbo@co.clay.mn.us
## 12 St                         TRUE  david.overbo@co.clay.mn.us
## 13 S                          FALSE david.overbo@co.clay.mn.us
## 14 Moorhead,                  TRUE  david.overbo@co.clay.mn.us
## 15 MN                         TRUE  david.overbo@co.clay.mn.us
## 16 56560                      FALSE david.overbo@co.clay.mn.us
## 17 david.overbo@co.clay.mn.us FALSE david.overbo@co.clay.mn.us
## 
## $`jdolson@co.becker.mn.us`
## # A tibble: 17 x 3
##    text                    space person_identifier      
##    <chr>                   <lgl> <chr>                  
##  1 Becker                  TRUE  jdolson@co.becker.mn.us
##  2 County                  FALSE jdolson@co.becker.mn.us
##  3 Jim                     TRUE  jdolson@co.becker.mn.us
##  4 Olson                   FALSE jdolson@co.becker.mn.us
##  5 County                  TRUE  jdolson@co.becker.mn.us
##  6 Engineer                FALSE jdolson@co.becker.mn.us
##  7 (218)                   TRUE  jdolson@co.becker.mn.us
##  8 847-4463                FALSE jdolson@co.becker.mn.us
##  9 200                     TRUE  jdolson@co.becker.mn.us
## 10 East                    TRUE  jdolson@co.becker.mn.us
## 11 State                   TRUE  jdolson@co.becker.mn.us
## 12 St                      FALSE jdolson@co.becker.mn.us
## 13 Detroit                 TRUE  jdolson@co.becker.mn.us
## 14 Lakes,                  TRUE  jdolson@co.becker.mn.us
## 15 MN                      TRUE  jdolson@co.becker.mn.us
## 16 56501                   FALSE jdolson@co.becker.mn.us
## 17 jdolson@co.becker.mn.us FALSE jdolson@co.becker.mn.us
## 
## $`jgilb@co.chippewa.mn.us`
## # A tibble: 16 x 3
##    text                    space person_identifier      
##    <chr>                   <lgl> <chr>                  
##  1 Chippewa                TRUE  jgilb@co.chippewa.mn.us
##  2 County                  FALSE jgilb@co.chippewa.mn.us
##  3 Jeremy                  TRUE  jgilb@co.chippewa.mn.us
##  4 Gilb                    FALSE jgilb@co.chippewa.mn.us
##  5 County                  TRUE  jgilb@co.chippewa.mn.us
##  6 Engineer                FALSE jgilb@co.chippewa.mn.us
##  7 (320)                   TRUE  jgilb@co.chippewa.mn.us
##  8 269-2151                FALSE jgilb@co.chippewa.mn.us
##  9 902                     TRUE  jgilb@co.chippewa.mn.us
## 10 N                       TRUE  jgilb@co.chippewa.mn.us
## 11 17th                    TRUE  jgilb@co.chippewa.mn.us
## 12 St                      FALSE jgilb@co.chippewa.mn.us
## 13 Montevideo,             TRUE  jgilb@co.chippewa.mn.us
## 14 MN                      TRUE  jgilb@co.chippewa.mn.us
## 15 56265                   FALSE jgilb@co.chippewa.mn.us
## 16 jgilb@co.chippewa.mn.us FALSE jgilb@co.chippewa.mn.us
## 
## $`jinyeene.neumann@co.carlton.mn.us`
## # A tibble: 16 x 3
##    text                              space person_identifier                
##    <chr>                             <lgl> <chr>                            
##  1 Carlton                           TRUE  jinyeene.neumann@co.carlton.mn.us
##  2 County                            FALSE jinyeene.neumann@co.carlton.mn.us
##  3 JinYeene                          TRUE  jinyeene.neumann@co.carlton.mn.us
##  4 Neumann                           FALSE jinyeene.neumann@co.carlton.mn.us
##  5 County                            TRUE  jinyeene.neumann@co.carlton.mn.us
##  6 Engineer                          FALSE jinyeene.neumann@co.carlton.mn.us
##  7 (218)                             TRUE  jinyeene.neumann@co.carlton.mn.us
##  8 384-9150                          FALSE jinyeene.neumann@co.carlton.mn.us
##  9 1630                              TRUE  jinyeene.neumann@co.carlton.mn.us
## 10 County                            TRUE  jinyeene.neumann@co.carlton.mn.us
## 11 Road                              TRUE  jinyeene.neumann@co.carlton.mn.us
## 12 61                                FALSE jinyeene.neumann@co.carlton.mn.us
## 13 Carlton,                          TRUE  jinyeene.neumann@co.carlton.mn.us
## 14 MN                                TRUE  jinyeene.neumann@co.carlton.mn.us
## 15 55718                             FALSE jinyeene.neumann@co.carlton.mn.us
## 16 jinyeene.neumann@co.carlton.mn.us FALSE jinyeene.neumann@co.carlton.mn.us
## 
## $`joe.macpherson@co.anoka.mn.us`
## # A tibble: 17 x 3
##    text                          space person_identifier            
##    <chr>                         <lgl> <chr>                        
##  1 Anoka                         TRUE  joe.macpherson@co.anoka.mn.us
##  2 County                        FALSE joe.macpherson@co.anoka.mn.us
##  3 Joe                           TRUE  joe.macpherson@co.anoka.mn.us
##  4 MacPherson                    FALSE joe.macpherson@co.anoka.mn.us
##  5 County                        TRUE  joe.macpherson@co.anoka.mn.us
##  6 Engineer                      FALSE joe.macpherson@co.anoka.mn.us
##  7 (763)                         TRUE  joe.macpherson@co.anoka.mn.us
##  8 324-3199                      FALSE joe.macpherson@co.anoka.mn.us
##  9 1440                          TRUE  joe.macpherson@co.anoka.mn.us
## 10 Bunker                        TRUE  joe.macpherson@co.anoka.mn.us
## 11 Lake                          TRUE  joe.macpherson@co.anoka.mn.us
## 12 Blvd                          TRUE  joe.macpherson@co.anoka.mn.us
## 13 NW                            FALSE joe.macpherson@co.anoka.mn.us
## 14 Andover,                      TRUE  joe.macpherson@co.anoka.mn.us
## 15 MN                            TRUE  joe.macpherson@co.anoka.mn.us
## 16 55304                         FALSE joe.macpherson@co.anoka.mn.us
## 17 joe.macpherson@co.anoka.mn.us FALSE joe.macpherson@co.anoka.mn.us
## 
## $`joe.triplett@co.chisago.mn.us`
## # A tibble: 22 x 3
##    text      space person_identifier            
##    <chr>     <lgl> <chr>                        
##  1 Chisago   TRUE  joe.triplett@co.chisago.mn.us
##  2 County    FALSE joe.triplett@co.chisago.mn.us
##  3 Joe       TRUE  joe.triplett@co.chisago.mn.us
##  4 Triplett  FALSE joe.triplett@co.chisago.mn.us
##  5 Public    TRUE  joe.triplett@co.chisago.mn.us
##  6 Works     TRUE  joe.triplett@co.chisago.mn.us
##  7 Director/ TRUE  joe.triplett@co.chisago.mn.us
##  8 County    FALSE joe.triplett@co.chisago.mn.us
##  9 Engineer  FALSE joe.triplett@co.chisago.mn.us
## 10 (651)     TRUE  joe.triplett@co.chisago.mn.us
## # … with 12 more rows
## 
## $`john.welle@co.aitkin.mn.us`
## # A tibble: 16 x 3
##    text                       space person_identifier         
##    <chr>                      <lgl> <chr>                     
##  1 Aitkin                     TRUE  john.welle@co.aitkin.mn.us
##  2 County                     FALSE john.welle@co.aitkin.mn.us
##  3 John                       TRUE  john.welle@co.aitkin.mn.us
##  4 Welle                      FALSE john.welle@co.aitkin.mn.us
##  5 County                     TRUE  john.welle@co.aitkin.mn.us
##  6 Engineer                   FALSE john.welle@co.aitkin.mn.us
##  7 (218)                      TRUE  john.welle@co.aitkin.mn.us
##  8 927-7569                   FALSE john.welle@co.aitkin.mn.us
##  9 1211                       TRUE  john.welle@co.aitkin.mn.us
## 10 Air                        TRUE  john.welle@co.aitkin.mn.us
## 11 Park                       TRUE  john.welle@co.aitkin.mn.us
## 12 Dr                         FALSE john.welle@co.aitkin.mn.us
## 13 Aitkin,                    TRUE  john.welle@co.aitkin.mn.us
## 14 MN                         TRUE  john.welle@co.aitkin.mn.us
## 15 56431                      FALSE john.welle@co.aitkin.mn.us
## 16 john.welle@co.aitkin.mn.us FALSE john.welle@co.aitkin.mn.us
## 
## $`lrobjent@co.carver.mn.us`
## # A tibble: 20 x 3
##    text                     space person_identifier       
##    <chr>                    <lgl> <chr>                   
##  1 Carver                   TRUE  lrobjent@co.carver.mn.us
##  2 County                   FALSE lrobjent@co.carver.mn.us
##  3 Lyndon                   TRUE  lrobjent@co.carver.mn.us
##  4 Colebrook-Robjent        FALSE lrobjent@co.carver.mn.us
##  5 Public                   TRUE  lrobjent@co.carver.mn.us
##  6 Works                    TRUE  lrobjent@co.carver.mn.us
##  7 Director/                TRUE  lrobjent@co.carver.mn.us
##  8 County                   FALSE lrobjent@co.carver.mn.us
##  9 Engineer                 FALSE lrobjent@co.carver.mn.us
## 10 (952)                    TRUE  lrobjent@co.carver.mn.us
## 11 466-5206                 FALSE lrobjent@co.carver.mn.us
## 12 11360                    TRUE  lrobjent@co.carver.mn.us
## 13 Highway                  TRUE  lrobjent@co.carver.mn.us
## 14 212                      TRUE  lrobjent@co.carver.mn.us
## 15 Ste                      TRUE  lrobjent@co.carver.mn.us
## 16 1                        FALSE lrobjent@co.carver.mn.us
## 17 Cologne,                 TRUE  lrobjent@co.carver.mn.us
## 18 MN                       TRUE  lrobjent@co.carver.mn.us
## 19 55322                    FALSE lrobjent@co.carver.mn.us
## 20 lrobjent@co.carver.mn.us FALSE lrobjent@co.carver.mn.us
## 
## $`mark.krebsbach@co.dakota.mn.us`
## # A tibble: 18 x 3
##    text                           space person_identifier             
##    <chr>                          <lgl> <chr>                         
##  1 Dakota                         TRUE  mark.krebsbach@co.dakota.mn.us
##  2 County                         FALSE mark.krebsbach@co.dakota.mn.us
##  3 Mark                           TRUE  mark.krebsbach@co.dakota.mn.us
##  4 Krebsbach                      FALSE mark.krebsbach@co.dakota.mn.us
##  5 Transportation                 TRUE  mark.krebsbach@co.dakota.mn.us
##  6 Director/                      TRUE  mark.krebsbach@co.dakota.mn.us
##  7 County                         FALSE mark.krebsbach@co.dakota.mn.us
##  8 Engineer                       FALSE mark.krebsbach@co.dakota.mn.us
##  9 (952)                          TRUE  mark.krebsbach@co.dakota.mn.us
## 10 891-7102                       FALSE mark.krebsbach@co.dakota.mn.us
## 11 14955                          TRUE  mark.krebsbach@co.dakota.mn.us
## 12 Galaxie                        TRUE  mark.krebsbach@co.dakota.mn.us
## 13 Ave                            FALSE mark.krebsbach@co.dakota.mn.us
## 14 Saint                          TRUE  mark.krebsbach@co.dakota.mn.us
## 15 Paul,                          TRUE  mark.krebsbach@co.dakota.mn.us
## 16 MN                             TRUE  mark.krebsbach@co.dakota.mn.us
## 17 55124                          FALSE mark.krebsbach@co.dakota.mn.us
## 18 mark.krebsbach@co.dakota.mn.us FALSE mark.krebsbach@co.dakota.mn.us
## 
## $`nick.klisch@co.cottonwood.mn.us`
## # A tibble: 18 x 3
##    text                            space person_identifier              
##    <chr>                           <lgl> <chr>                          
##  1 Cottonwood                      TRUE  nick.klisch@co.cottonwood.mn.us
##  2 County                          FALSE nick.klisch@co.cottonwood.mn.us
##  3 Nick                            TRUE  nick.klisch@co.cottonwood.mn.us
##  4 Klisch                          FALSE nick.klisch@co.cottonwood.mn.us
##  5 Public                          TRUE  nick.klisch@co.cottonwood.mn.us
##  6 Works                           TRUE  nick.klisch@co.cottonwood.mn.us
##  7 Director/                       TRUE  nick.klisch@co.cottonwood.mn.us
##  8 County                          FALSE nick.klisch@co.cottonwood.mn.us
##  9 Engineer                        FALSE nick.klisch@co.cottonwood.mn.us
## 10 (507)                           TRUE  nick.klisch@co.cottonwood.mn.us
## 11 832-8811                        FALSE nick.klisch@co.cottonwood.mn.us
## 12 1355                            TRUE  nick.klisch@co.cottonwood.mn.us
## 13 9th                             TRUE  nick.klisch@co.cottonwood.mn.us
## 14 Ave                             FALSE nick.klisch@co.cottonwood.mn.us
## 15 Windom,                         TRUE  nick.klisch@co.cottonwood.mn.us
## 16 MN                              TRUE  nick.klisch@co.cottonwood.mn.us
## 17 56101                           FALSE nick.klisch@co.cottonwood.mn.us
## 18 nick.klisch@co.cottonwood.mn.us FALSE nick.klisch@co.cottonwood.mn.us
## 
## $`robert.hass@co.cook.mn.us`
## # A tibble: 17 x 3
##    text                      space person_identifier        
##    <chr>                     <lgl> <chr>                    
##  1 Cook                      TRUE  robert.hass@co.cook.mn.us
##  2 County                    FALSE robert.hass@co.cook.mn.us
##  3 Robert                    TRUE  robert.hass@co.cook.mn.us
##  4 Hass                      FALSE robert.hass@co.cook.mn.us
##  5 Highway                   TRUE  robert.hass@co.cook.mn.us
##  6 Engineer                  FALSE robert.hass@co.cook.mn.us
##  7 (218)                     TRUE  robert.hass@co.cook.mn.us
##  8 387-3695                  FALSE robert.hass@co.cook.mn.us
##  9 609                       TRUE  robert.hass@co.cook.mn.us
## 10 E                         TRUE  robert.hass@co.cook.mn.us
## 11 4th                       TRUE  robert.hass@co.cook.mn.us
## 12 Ave                       FALSE robert.hass@co.cook.mn.us
## 13 Grand                     TRUE  robert.hass@co.cook.mn.us
## 14 Marais,                   TRUE  robert.hass@co.cook.mn.us
## 15 MN                        TRUE  robert.hass@co.cook.mn.us
## 16 55604                     FALSE robert.hass@co.cook.mn.us
## 17 robert.hass@co.cook.mn.us FALSE robert.hass@co.cook.mn.us
## 
## $`ryan.thilges@blueearthcountymn.gov`
## # A tibble: 17 x 3
##    text                               space person_identifier                 
##    <chr>                              <lgl> <chr>                             
##  1 Blue                               TRUE  ryan.thilges@blueearthcountymn.gov
##  2 Earth                              TRUE  ryan.thilges@blueearthcountymn.gov
##  3 County                             FALSE ryan.thilges@blueearthcountymn.gov
##  4 Ryan                               TRUE  ryan.thilges@blueearthcountymn.gov
##  5 Thilges                            FALSE ryan.thilges@blueearthcountymn.gov
##  6 Public                             TRUE  ryan.thilges@blueearthcountymn.gov
##  7 Works                              TRUE  ryan.thilges@blueearthcountymn.gov
##  8 Director                           FALSE ryan.thilges@blueearthcountymn.gov
##  9 (507)                              TRUE  ryan.thilges@blueearthcountymn.gov
## 10 304-4031                           FALSE ryan.thilges@blueearthcountymn.gov
## 11 P.O.                               TRUE  ryan.thilges@blueearthcountymn.gov
## 12 Box                                TRUE  ryan.thilges@blueearthcountymn.gov
## 13 8608                               FALSE ryan.thilges@blueearthcountymn.gov
## 14 Mankato,                           TRUE  ryan.thilges@blueearthcountymn.gov
## 15 MN                                 TRUE  ryan.thilges@blueearthcountymn.gov
## 16 56002                              FALSE ryan.thilges@blueearthcountymn.gov
## 17 ryan.thilges@blueearthcountymn.gov FALSE ryan.thilges@blueearthcountymn.gov
## 
## $`tim.bray@crowwing.us`
## # A tibble: 17 x 3
##    text                 space person_identifier   
##    <chr>                <lgl> <chr>               
##  1 Crow                 TRUE  tim.bray@crowwing.us
##  2 Wing                 TRUE  tim.bray@crowwing.us
##  3 County               FALSE tim.bray@crowwing.us
##  4 Timothy              TRUE  tim.bray@crowwing.us
##  5 Bray                 FALSE tim.bray@crowwing.us
##  6 County               TRUE  tim.bray@crowwing.us
##  7 Engineer             FALSE tim.bray@crowwing.us
##  8 (218)                TRUE  tim.bray@crowwing.us
##  9 822-2684             FALSE tim.bray@crowwing.us
## 10 16589                TRUE  tim.bray@crowwing.us
## 11 County               TRUE  tim.bray@crowwing.us
## 12 Road                 TRUE  tim.bray@crowwing.us
## 13 142                  FALSE tim.bray@crowwing.us
## 14 Brainerd,            TRUE  tim.bray@crowwing.us
## 15 MN                   TRUE  tim.bray@crowwing.us
## 16 56401                FALSE tim.bray@crowwing.us
## 17 tim.bray@crowwing.us FALSE tim.bray@crowwing.us
## 
## $`todd.larson@bigstonecounty.org`
## # A tibble: 17 x 3
##    text                           space person_identifier             
##    <chr>                          <lgl> <chr>                         
##  1 Big                            TRUE  todd.larson@bigstonecounty.org
##  2 Stone                          TRUE  todd.larson@bigstonecounty.org
##  3 County                         FALSE todd.larson@bigstonecounty.org
##  4 Todd                           TRUE  todd.larson@bigstonecounty.org
##  5 Larson                         FALSE todd.larson@bigstonecounty.org
##  6 County                         TRUE  todd.larson@bigstonecounty.org
##  7 Engineer                       FALSE todd.larson@bigstonecounty.org
##  8 (320)                          TRUE  todd.larson@bigstonecounty.org
##  9 839-2594                       FALSE todd.larson@bigstonecounty.org
## 10 437                            TRUE  todd.larson@bigstonecounty.org
## 11 Minnesota                      TRUE  todd.larson@bigstonecounty.org
## 12 St                             TRUE  todd.larson@bigstonecounty.org
## 13 N                              FALSE todd.larson@bigstonecounty.org
## 14 Ortonville,                    TRUE  todd.larson@bigstonecounty.org
## 15 MN                             TRUE  todd.larson@bigstonecounty.org
## 16 56278                          FALSE todd.larson@bigstonecounty.org
## 17 todd.larson@bigstonecounty.org FALSE todd.larson@bigstonecounty.org
## 
## $`wayne.stevens@co.brown.mn.us`
## # A tibble: 17 x 3
##    text                         space person_identifier           
##    <chr>                        <lgl> <chr>                       
##  1 Brown                        TRUE  wayne.stevens@co.brown.mn.us
##  2 County                       FALSE wayne.stevens@co.brown.mn.us
##  3 Wayne                        TRUE  wayne.stevens@co.brown.mn.us
##  4 Stevens                      FALSE wayne.stevens@co.brown.mn.us
##  5 County                       TRUE  wayne.stevens@co.brown.mn.us
##  6 Engineer                     FALSE wayne.stevens@co.brown.mn.us
##  7 (507)                        TRUE  wayne.stevens@co.brown.mn.us
##  8 233-5700                     FALSE wayne.stevens@co.brown.mn.us
##  9 1901                         TRUE  wayne.stevens@co.brown.mn.us
## 10 N                            TRUE  wayne.stevens@co.brown.mn.us
## 11 Jefferson                    TRUE  wayne.stevens@co.brown.mn.us
## 12 St                           FALSE wayne.stevens@co.brown.mn.us
## 13 New                          TRUE  wayne.stevens@co.brown.mn.us
## 14 Ulm,                         TRUE  wayne.stevens@co.brown.mn.us
## 15 MN                           TRUE  wayne.stevens@co.brown.mn.us
## 16 56073                        FALSE wayne.stevens@co.brown.mn.us
## 17 wayne.stevens@co.brown.mn.us FALSE wayne.stevens@co.brown.mn.us
## 
## $`brian.pogodzinski@co.houston.mn.us`
## # A tibble: 16 x 3
##    text                               space person_identifier                 
##    <chr>                              <lgl> <chr>                             
##  1 Houston                            TRUE  brian.pogodzinski@co.houston.mn.us
##  2 County                             FALSE brian.pogodzinski@co.houston.mn.us
##  3 Brian                              TRUE  brian.pogodzinski@co.houston.mn.us
##  4 Pogodzinski                        FALSE brian.pogodzinski@co.houston.mn.us
##  5 County                             TRUE  brian.pogodzinski@co.houston.mn.us
##  6 Engineer                           FALSE brian.pogodzinski@co.houston.mn.us
##  7 (507)                              TRUE  brian.pogodzinski@co.houston.mn.us
##  8 725-3925                           FALSE brian.pogodzinski@co.houston.mn.us
##  9 1124                               TRUE  brian.pogodzinski@co.houston.mn.us
## 10 E                                  TRUE  brian.pogodzinski@co.houston.mn.us
## 11 Washington                         TRUE  brian.pogodzinski@co.houston.mn.us
## 12 St                                 FALSE brian.pogodzinski@co.houston.mn.us
## 13 Caledonia,                         TRUE  brian.pogodzinski@co.houston.mn.us
## 14 MN                                 TRUE  brian.pogodzinski@co.houston.mn.us
## 15 55921                              FALSE brian.pogodzinski@co.houston.mn.us
## 16 brian.pogodzinski@co.houston.mn.us FALSE brian.pogodzinski@co.houston.mn.us
## 
## $`carla.stueve@hennepin.us`
## # A tibble: 15 x 3
##    text                     space person_identifier       
##    <chr>                    <lgl> <chr>                   
##  1 Hennepin                 TRUE  carla.stueve@hennepin.us
##  2 County                   FALSE carla.stueve@hennepin.us
##  3 Carla                    TRUE  carla.stueve@hennepin.us
##  4 Stueve                   FALSE carla.stueve@hennepin.us
##  5 Transportation           TRUE  carla.stueve@hennepin.us
##  6 Engineer                 FALSE carla.stueve@hennepin.us
##  7 (612)                    TRUE  carla.stueve@hennepin.us
##  8 596-0356                 FALSE carla.stueve@hennepin.us
##  9 1600                     TRUE  carla.stueve@hennepin.us
## 10 Prairie                  TRUE  carla.stueve@hennepin.us
## 11 Dr                       FALSE carla.stueve@hennepin.us
## 12 Medina,                  TRUE  carla.stueve@hennepin.us
## 13 MN                       TRUE  carla.stueve@hennepin.us
## 14 55340                    FALSE carla.stueve@hennepin.us
## 15 carla.stueve@hennepin.us FALSE carla.stueve@hennepin.us
## 
## $`chad.gramentz@co.kanabec.mn.us`
## # A tibble: 18 x 3
##    text                           space person_identifier             
##    <chr>                          <lgl> <chr>                         
##  1 Kanabec                        TRUE  chad.gramentz@co.kanabec.mn.us
##  2 County                         FALSE chad.gramentz@co.kanabec.mn.us
##  3 Chad                           TRUE  chad.gramentz@co.kanabec.mn.us
##  4 Gramentz                       FALSE chad.gramentz@co.kanabec.mn.us
##  5 Public                         TRUE  chad.gramentz@co.kanabec.mn.us
##  6 Works                          TRUE  chad.gramentz@co.kanabec.mn.us
##  7 Director/County                FALSE chad.gramentz@co.kanabec.mn.us
##  8 Engineer                       FALSE chad.gramentz@co.kanabec.mn.us
##  9 (320)                          TRUE  chad.gramentz@co.kanabec.mn.us
## 10 679-6300                       FALSE chad.gramentz@co.kanabec.mn.us
## 11 903                            TRUE  chad.gramentz@co.kanabec.mn.us
## 12 Forest                         TRUE  chad.gramentz@co.kanabec.mn.us
## 13 Ave                            TRUE  chad.gramentz@co.kanabec.mn.us
## 14 E                              FALSE chad.gramentz@co.kanabec.mn.us
## 15 Mora,                          TRUE  chad.gramentz@co.kanabec.mn.us
## 16 MN                             TRUE  chad.gramentz@co.kanabec.mn.us
## 17 55051                          FALSE chad.gramentz@co.kanabec.mn.us
## 18 chad.gramentz@co.kanabec.mn.us FALSE chad.gramentz@co.kanabec.mn.us
## 
## $`dave.reimer@co.koochiching.mn.us`
## # A tibble: 16 x 3
##    text                             space person_identifier               
##    <chr>                            <lgl> <chr>                           
##  1 Koochiching                      TRUE  dave.reimer@co.koochiching.mn.us
##  2 County                           FALSE dave.reimer@co.koochiching.mn.us
##  3 David                            TRUE  dave.reimer@co.koochiching.mn.us
##  4 Reimer                           FALSE dave.reimer@co.koochiching.mn.us
##  5 County                           TRUE  dave.reimer@co.koochiching.mn.us
##  6 Engineer                         FALSE dave.reimer@co.koochiching.mn.us
##  7 (218)                            TRUE  dave.reimer@co.koochiching.mn.us
##  8 283-1186                         FALSE dave.reimer@co.koochiching.mn.us
##  9 715                              TRUE  dave.reimer@co.koochiching.mn.us
## 10 4th                              TRUE  dave.reimer@co.koochiching.mn.us
## 11 St                               FALSE dave.reimer@co.koochiching.mn.us
## 12 International                    TRUE  dave.reimer@co.koochiching.mn.us
## 13 Falls,                           TRUE  dave.reimer@co.koochiching.mn.us
## 14 MN                               TRUE  dave.reimer@co.koochiching.mn.us
## 15 56649                            FALSE dave.reimer@co.koochiching.mn.us
## 16 dave.reimer@co.koochiching.mn.us FALSE dave.reimer@co.koochiching.mn.us
## 
## $`greg.isakson@co.goodhue.mn.us`
## # A tibble: 18 x 3
##    text                          space person_identifier            
##    <chr>                         <lgl> <chr>                        
##  1 Goodhue                       TRUE  greg.isakson@co.goodhue.mn.us
##  2 County                        FALSE greg.isakson@co.goodhue.mn.us
##  3 Gregory                       TRUE  greg.isakson@co.goodhue.mn.us
##  4 Isakson                       FALSE greg.isakson@co.goodhue.mn.us
##  5 Public                        TRUE  greg.isakson@co.goodhue.mn.us
##  6 Works                         TRUE  greg.isakson@co.goodhue.mn.us
##  7 Director/County               FALSE greg.isakson@co.goodhue.mn.us
##  8 Engineer                      FALSE greg.isakson@co.goodhue.mn.us
##  9 (651)                         TRUE  greg.isakson@co.goodhue.mn.us
## 10 385-2025                      FALSE greg.isakson@co.goodhue.mn.us
## 11 2140                          TRUE  greg.isakson@co.goodhue.mn.us
## 12 Pioneer                       TRUE  greg.isakson@co.goodhue.mn.us
## 13 Road                          FALSE greg.isakson@co.goodhue.mn.us
## 14 Red                           TRUE  greg.isakson@co.goodhue.mn.us
## 15 Wing,                         TRUE  greg.isakson@co.goodhue.mn.us
## 16 MN                            TRUE  greg.isakson@co.goodhue.mn.us
## 17 55066                         FALSE greg.isakson@co.goodhue.mn.us
## 18 greg.isakson@co.goodhue.mn.us FALSE greg.isakson@co.goodhue.mn.us
## 
## $`guy.kohlnhofer@co.dodge.mn.us`
## # A tibble: 17 x 3
##    text                          space person_identifier            
##    <chr>                         <lgl> <chr>                        
##  1 Dodge                         TRUE  guy.kohlnhofer@co.dodge.mn.us
##  2 County                        FALSE guy.kohlnhofer@co.dodge.mn.us
##  3 Guy                           TRUE  guy.kohlnhofer@co.dodge.mn.us
##  4 Kohlnhofer                    FALSE guy.kohlnhofer@co.dodge.mn.us
##  5 County                        TRUE  guy.kohlnhofer@co.dodge.mn.us
##  6 Engineer                      FALSE guy.kohlnhofer@co.dodge.mn.us
##  7 (507)                         TRUE  guy.kohlnhofer@co.dodge.mn.us
##  8 374-6694                      FALSE guy.kohlnhofer@co.dodge.mn.us
##  9 16                            TRUE  guy.kohlnhofer@co.dodge.mn.us
## 10 S.                            TRUE  guy.kohlnhofer@co.dodge.mn.us
## 11 Airport                       TRUE  guy.kohlnhofer@co.dodge.mn.us
## 12 Dr                            FALSE guy.kohlnhofer@co.dodge.mn.us
## 13 Dodge                         TRUE  guy.kohlnhofer@co.dodge.mn.us
## 14 Center,                       TRUE  guy.kohlnhofer@co.dodge.mn.us
## 15 MN                            TRUE  guy.kohlnhofer@co.dodge.mn.us
## 16 55927                         FALSE guy.kohlnhofer@co.dodge.mn.us
## 17 guy.kohlnhofer@co.dodge.mn.us FALSE guy.kohlnhofer@co.dodge.mn.us
## 
## $`jnordin@co.hubbard.mn.us`
## # A tibble: 20 x 3
##    text                     space person_identifier       
##    <chr>                    <lgl> <chr>                   
##  1 Hubbard                  TRUE  jnordin@co.hubbard.mn.us
##  2 County                   FALSE jnordin@co.hubbard.mn.us
##  3 Jed                      TRUE  jnordin@co.hubbard.mn.us
##  4 Nordin                   FALSE jnordin@co.hubbard.mn.us
##  5 County                   TRUE  jnordin@co.hubbard.mn.us
##  6 Engineer/                TRUE  jnordin@co.hubbard.mn.us
##  7 Public                   TRUE  jnordin@co.hubbard.mn.us
##  8 Works                    FALSE jnordin@co.hubbard.mn.us
##  9 Coordinator              FALSE jnordin@co.hubbard.mn.us
## 10 (218)                    TRUE  jnordin@co.hubbard.mn.us
## 11 732-7640                 FALSE jnordin@co.hubbard.mn.us
## 12 101                      TRUE  jnordin@co.hubbard.mn.us
## 13 Crocus                   TRUE  jnordin@co.hubbard.mn.us
## 14 Hill                     TRUE  jnordin@co.hubbard.mn.us
## 15 St                       FALSE jnordin@co.hubbard.mn.us
## 16 Park                     TRUE  jnordin@co.hubbard.mn.us
## 17 Rapids,                  TRUE  jnordin@co.hubbard.mn.us
## 18 MN                       TRUE  jnordin@co.hubbard.mn.us
## 19 56470                    FALSE jnordin@co.hubbard.mn.us
## 20 jnordin@co.hubbard.mn.us FALSE jnordin@co.hubbard.mn.us
## 
## $`karin.grandia@co.itasca.mn.us`
## # A tibble: 17 x 3
##    text                          space person_identifier            
##    <chr>                         <lgl> <chr>                        
##  1 Itasca                        TRUE  karin.grandia@co.itasca.mn.us
##  2 County                        FALSE karin.grandia@co.itasca.mn.us
##  3 Karin                         TRUE  karin.grandia@co.itasca.mn.us
##  4 Grandia                       FALSE karin.grandia@co.itasca.mn.us
##  5 County                        TRUE  karin.grandia@co.itasca.mn.us
##  6 Engineer                      FALSE karin.grandia@co.itasca.mn.us
##  7 (218)                         TRUE  karin.grandia@co.itasca.mn.us
##  8 327-2853                      FALSE karin.grandia@co.itasca.mn.us
##  9 123                           TRUE  karin.grandia@co.itasca.mn.us
## 10 NE                            TRUE  karin.grandia@co.itasca.mn.us
## 11 4th                           TRUE  karin.grandia@co.itasca.mn.us
## 12 St                            FALSE karin.grandia@co.itasca.mn.us
## 13 Grand                         TRUE  karin.grandia@co.itasca.mn.us
## 14 Rapids,                       TRUE  karin.grandia@co.itasca.mn.us
## 15 MN                            TRUE  karin.grandia@co.itasca.mn.us
## 16 55744                         FALSE karin.grandia@co.itasca.mn.us
## 17 karin.grandia@co.itasca.mn.us FALSE karin.grandia@co.itasca.mn.us
## 
## $`mark.daly@co.faribault.mn.us`
## # A tibble: 16 x 3
##    text                         space person_identifier           
##    <chr>                        <lgl> <chr>                       
##  1 Faribault                    TRUE  mark.daly@co.faribault.mn.us
##  2 County                       FALSE mark.daly@co.faribault.mn.us
##  3 Mark                         TRUE  mark.daly@co.faribault.mn.us
##  4 Daly                         FALSE mark.daly@co.faribault.mn.us
##  5 County                       TRUE  mark.daly@co.faribault.mn.us
##  6 Engineer                     FALSE mark.daly@co.faribault.mn.us
##  7 (507)                        TRUE  mark.daly@co.faribault.mn.us
##  8 526-4288                     FALSE mark.daly@co.faribault.mn.us
##  9 PO                           TRUE  mark.daly@co.faribault.mn.us
## 10 Box                          TRUE  mark.daly@co.faribault.mn.us
## 11 325                          FALSE mark.daly@co.faribault.mn.us
## 12 Blue                         TRUE  mark.daly@co.faribault.mn.us
## 13 Earth,                       TRUE  mark.daly@co.faribault.mn.us
## 14 MN                           TRUE  mark.daly@co.faribault.mn.us
## 15 56013                        FALSE mark.daly@co.faribault.mn.us
## 16 mark.daly@co.faribault.mn.us FALSE mark.daly@co.faribault.mn.us
## 
## $`mel.odens@kcmn.us`
## # A tibble: 21 x 3
##    text      space person_identifier
##    <chr>     <lgl> <chr>            
##  1 Kandiyohi TRUE  mel.odens@kcmn.us
##  2 County    FALSE mel.odens@kcmn.us
##  3 Mel       TRUE  mel.odens@kcmn.us
##  4 Odens     FALSE mel.odens@kcmn.us
##  5 Public    TRUE  mel.odens@kcmn.us
##  6 Works     TRUE  mel.odens@kcmn.us
##  7 Director/ TRUE  mel.odens@kcmn.us
##  8 County    FALSE mel.odens@kcmn.us
##  9 Engineer  FALSE mel.odens@kcmn.us
## 10 (320)     TRUE  mel.odens@kcmn.us
## # … with 11 more rows
## 
## $`phillip.wacholz@co.freeborn.mn.us`
## # A tibble: 19 x 3
##    text                              space person_identifier                
##    <chr>                             <lgl> <chr>                            
##  1 Freeborn                          TRUE  phillip.wacholz@co.freeborn.mn.us
##  2 County                            FALSE phillip.wacholz@co.freeborn.mn.us
##  3 Phillip                           TRUE  phillip.wacholz@co.freeborn.mn.us
##  4 Wacholz                           FALSE phillip.wacholz@co.freeborn.mn.us
##  5 Public                            TRUE  phillip.wacholz@co.freeborn.mn.us
##  6 Works                             TRUE  phillip.wacholz@co.freeborn.mn.us
##  7 Director/                         TRUE  phillip.wacholz@co.freeborn.mn.us
##  8 County                            FALSE phillip.wacholz@co.freeborn.mn.us
##  9 Engineer                          FALSE phillip.wacholz@co.freeborn.mn.us
## 10 (507)                             TRUE  phillip.wacholz@co.freeborn.mn.us
## 11 377-5188                          FALSE phillip.wacholz@co.freeborn.mn.us
## 12 3300                              TRUE  phillip.wacholz@co.freeborn.mn.us
## 13 Bridge                            TRUE  phillip.wacholz@co.freeborn.mn.us
## 14 Ave                               FALSE phillip.wacholz@co.freeborn.mn.us
## 15 Albert                            TRUE  phillip.wacholz@co.freeborn.mn.us
## 16 Lea,                              TRUE  phillip.wacholz@co.freeborn.mn.us
## 17 MN                                TRUE  phillip.wacholz@co.freeborn.mn.us
## 18 56007                             FALSE phillip.wacholz@co.freeborn.mn.us
## 19 phillip.wacholz@co.freeborn.mn.us FALSE phillip.wacholz@co.freeborn.mn.us
## 
## $`rgregg@co.fillmore.mn.us`
## # A tibble: 16 x 3
##    text                     space person_identifier       
##    <chr>                    <lgl> <chr>                   
##  1 Fillmore                 TRUE  rgregg@co.fillmore.mn.us
##  2 County                   FALSE rgregg@co.fillmore.mn.us
##  3 Ron                      TRUE  rgregg@co.fillmore.mn.us
##  4 Gregg                    FALSE rgregg@co.fillmore.mn.us
##  5 County                   TRUE  rgregg@co.fillmore.mn.us
##  6 Engineer                 FALSE rgregg@co.fillmore.mn.us
##  7 (507)                    TRUE  rgregg@co.fillmore.mn.us
##  8 765-3854                 FALSE rgregg@co.fillmore.mn.us
##  9 909                      TRUE  rgregg@co.fillmore.mn.us
## 10 Houston                  TRUE  rgregg@co.fillmore.mn.us
## 11 St                       TRUE  rgregg@co.fillmore.mn.us
## 12 NW                       FALSE rgregg@co.fillmore.mn.us
## 13 Preston,                 TRUE  rgregg@co.fillmore.mn.us
## 14 MN                       TRUE  rgregg@co.fillmore.mn.us
## 15 55965                    FALSE rgregg@co.fillmore.mn.us
## 16 rgregg@co.fillmore.mn.us FALSE rgregg@co.fillmore.mn.us
## 
## $`rheilman@highway.co.isanti.mn.us`
## # A tibble: 16 x 3
##    text                             space person_identifier               
##    <chr>                            <lgl> <chr>                           
##  1 Isanti                           TRUE  rheilman@highway.co.isanti.mn.us
##  2 County                           FALSE rheilman@highway.co.isanti.mn.us
##  3 Richard                          TRUE  rheilman@highway.co.isanti.mn.us
##  4 Heilman                          FALSE rheilman@highway.co.isanti.mn.us
##  5 County                           TRUE  rheilman@highway.co.isanti.mn.us
##  6 Engineer                         FALSE rheilman@highway.co.isanti.mn.us
##  7 (763)                            TRUE  rheilman@highway.co.isanti.mn.us
##  8 689-1870                         FALSE rheilman@highway.co.isanti.mn.us
##  9 232                              TRUE  rheilman@highway.co.isanti.mn.us
## 10 Emerson                          TRUE  rheilman@highway.co.isanti.mn.us
## 11 St                               TRUE  rheilman@highway.co.isanti.mn.us
## 12 N                                FALSE rheilman@highway.co.isanti.mn.us
## 13 Cambridge,                       TRUE  rheilman@highway.co.isanti.mn.us
## 14 MN                               TRUE  rheilman@highway.co.isanti.mn.us
## 15 55008                            FALSE rheilman@highway.co.isanti.mn.us
## 16 rheilman@highway.co.isanti.mn.us FALSE rheilman@highway.co.isanti.mn.us
## 
## $`sam.muntean@lqpco.com`
## # A tibble: 19 x 3
##    text                  space person_identifier    
##    <chr>                 <lgl> <chr>                
##  1 Lac                   TRUE  sam.muntean@lqpco.com
##  2 qui                   TRUE  sam.muntean@lqpco.com
##  3 Parle                 TRUE  sam.muntean@lqpco.com
##  4 County                FALSE sam.muntean@lqpco.com
##  5 Sam                   TRUE  sam.muntean@lqpco.com
##  6 Muntean               FALSE sam.muntean@lqpco.com
##  7 County                TRUE  sam.muntean@lqpco.com
##  8 Engineer              FALSE sam.muntean@lqpco.com
##  9 (320)                 TRUE  sam.muntean@lqpco.com
## 10 598-7252              FALSE sam.muntean@lqpco.com
## 11 422                   TRUE  sam.muntean@lqpco.com
## 12 5th                   TRUE  sam.muntean@lqpco.com
## 13 Avenue,               TRUE  sam.muntean@lqpco.com
## 14 Suite                 TRUE  sam.muntean@lqpco.com
## 15 301                   FALSE sam.muntean@lqpco.com
## 16 Madison,              TRUE  sam.muntean@lqpco.com
## 17 MN                    TRUE  sam.muntean@lqpco.com
## 18 56256                 FALSE sam.muntean@lqpco.com
## 19 sam.muntean@lqpco.com FALSE sam.muntean@lqpco.com
## 
## $`tim.stahl@co.jackson.mn.us`
## # A tibble: 15 x 3
##    text                       space person_identifier         
##    <chr>                      <lgl> <chr>                     
##  1 Jackson                    TRUE  tim.stahl@co.jackson.mn.us
##  2 County                     FALSE tim.stahl@co.jackson.mn.us
##  3 Tim                        TRUE  tim.stahl@co.jackson.mn.us
##  4 Stahl                      FALSE tim.stahl@co.jackson.mn.us
##  5 County                     TRUE  tim.stahl@co.jackson.mn.us
##  6 Engineer                   FALSE tim.stahl@co.jackson.mn.us
##  7 (507)                      TRUE  tim.stahl@co.jackson.mn.us
##  8 847-2525                   FALSE tim.stahl@co.jackson.mn.us
##  9 53053                      TRUE  tim.stahl@co.jackson.mn.us
## 10 780th                      TRUE  tim.stahl@co.jackson.mn.us
## 11 St                         FALSE tim.stahl@co.jackson.mn.us
## 12 Jackson,                   TRUE  tim.stahl@co.jackson.mn.us
## 13 MN                         TRUE  tim.stahl@co.jackson.mn.us
## 14 56143                      FALSE tim.stahl@co.jackson.mn.us
## 15 tim.stahl@co.jackson.mn.us FALSE tim.stahl@co.jackson.mn.us
## 
## $`time@co.douglas.mn.us`
## # A tibble: 18 x 3
##    text                  space person_identifier    
##    <chr>                 <lgl> <chr>                
##  1 Douglas               TRUE  time@co.douglas.mn.us
##  2 County                FALSE time@co.douglas.mn.us
##  3 Tim                   TRUE  time@co.douglas.mn.us
##  4 Erickson              FALSE time@co.douglas.mn.us
##  5 Public                TRUE  time@co.douglas.mn.us
##  6 Works                 TRUE  time@co.douglas.mn.us
##  7 Director/             TRUE  time@co.douglas.mn.us
##  8 County                FALSE time@co.douglas.mn.us
##  9 Engineer              FALSE time@co.douglas.mn.us
## 10 (320)                 TRUE  time@co.douglas.mn.us
## 11 762-2967              FALSE time@co.douglas.mn.us
## 12 526                   TRUE  time@co.douglas.mn.us
## 13 Willow                TRUE  time@co.douglas.mn.us
## 14 Dr.                   FALSE time@co.douglas.mn.us
## 15 Alexandria,           TRUE  time@co.douglas.mn.us
## 16 MN                    TRUE  time@co.douglas.mn.us
## 17 56308                 FALSE time@co.douglas.mn.us
## 18 time@co.douglas.mn.us FALSE time@co.douglas.mn.us
## 
## $`tracey.vonbargen@co.grant.mn.us`
## # A tibble: 17 x 3
##    text                            space person_identifier              
##    <chr>                           <lgl> <chr>                          
##  1 Grant                           TRUE  tracey.vonbargen@co.grant.mn.us
##  2 County                          FALSE tracey.vonbargen@co.grant.mn.us
##  3 Tracey                          TRUE  tracey.vonbargen@co.grant.mn.us
##  4 Von                             TRUE  tracey.vonbargen@co.grant.mn.us
##  5 Bargen                          FALSE tracey.vonbargen@co.grant.mn.us
##  6 County                          TRUE  tracey.vonbargen@co.grant.mn.us
##  7 Engineer                        FALSE tracey.vonbargen@co.grant.mn.us
##  8 (218)                           TRUE  tracey.vonbargen@co.grant.mn.us
##  9 685-8301                        FALSE tracey.vonbargen@co.grant.mn.us
## 10 PO                              TRUE  tracey.vonbargen@co.grant.mn.us
## 11 Box                             TRUE  tracey.vonbargen@co.grant.mn.us
## 12 1005                            FALSE tracey.vonbargen@co.grant.mn.us
## 13 Elbow                           TRUE  tracey.vonbargen@co.grant.mn.us
## 14 Lake,                           TRUE  tracey.vonbargen@co.grant.mn.us
## 15 MN                              TRUE  tracey.vonbargen@co.grant.mn.us
## 16 56531                           FALSE tracey.vonbargen@co.grant.mn.us
## 17 tracey.vonbargen@co.grant.mn.us FALSE tracey.vonbargen@co.grant.mn.us

Transform To Tidy Dataset

Now that each observation is a dataframe the individual list elements can be transformed to create a tidy data set where each observation has its own row and each variable is a column based on the following rules:

Space must never be FALSE for the first row of a dataframe. If this occurs it means that the first row does not contain information about the county and should be deleted. This is visible for the fourth list element darrick.anderson@co.cass.mn.us.
Consecutive space == FALSE must only occur on the last two rows of each dataframe. If it occurs anywhere before then space must be converted to NA for the first FALSE occurrence. Consecutive occurrences of FALSE is visible in rows 8 and 9 of list element lrobjent@co.carver.mn.us.
space == FALSE represents the end of the data for each variable. The column variable_type will be created to identify each variable. Values are then filled up to create unique groups.

get_county_engineers <- function(unique_observations) {
county_engineers <- unique_observations %>%  
    select(text, space) %>% 
    filter(!(row_number() == 1 & !space)) %>%
    mutate(space = if_else(!space, space, NA)) %>%
    mutate(lead_space = lead(space, 1)) %>%
    # if two consecutive rows, excluding the last two rows, are false convert the 
    # first row to NA
    mutate(row_to_make_na = if_else(space == lead_space & row_number() < (n() - 2) , TRUE, NA))  %>%
    mutate(space = if_else(!is.na(row_to_make_na), NA, space)) %>%
    select(-lead_space, -row_to_make_na) %>%
    group_by(space) %>% 
    mutate(variable_type = if_else(!space, 1:n(), NA_integer_)) %>% 
    ungroup() %>%
    fill(variable_type, .direction = "up") %>%
    # append row number to ensure each row has unique name for pivoting
    mutate(variable_type = case_when(
      variable_type == 1 ~ str_c("county_", row_number()),
      variable_type == 2 ~ str_c("name_", row_number()),
      variable_type == 3 ~ str_c("job_title_", row_number()),
      variable_type == 4 ~ str_c("phone_", row_number()),
      variable_type == 5 ~ str_c("street_address_", row_number()),
      variable_type == 6 ~ str_c("city_state_zip_", row_number()),
      variable_type == 7 ~ "email"
    )) %>% 
    select(text, variable_type) %>% 
    pivot_wider(names_from = variable_type, values_from = text) %>%
    unite("county", starts_with("county"), sep = " ") %>%
    mutate(county = str_remove_all(county, "County")) %>% 
    unite("name", starts_with("name"), sep = " ") %>%
    unite("title", starts_with("job"), sep = " ") %>%
    unite("phone", starts_with("phone"), sep = "-") %>%
    unite("address", matches("street|city"), sep = " ")

return(county_engineers)
}

county_engineers <- map_df(unique_observations, get_county_engineers)

county_engineers %>%
  arrange(county) %>% 
  kable() %>%
  kable_styling(full_width = FALSE) %>% 
  scroll_box(height = '600px')

county	name	title	phone	address	email
Aitkin	John Welle	County Engineer	(218)-927-7569	1211 Air Park Dr Aitkin, MN 56431	john.welle@co.aitkin.mn.us
Anoka	Joe MacPherson	County Engineer	(763)-324-3199	1440 Bunker Lake Blvd NW Andover, MN 55304	joe.macpherson@co.anoka.mn.us
Becker	Jim Olson	County Engineer	(218)-847-4463	200 East State St Detroit Lakes, MN 56501	jdolson@co.becker.mn.us
Beltrami	Bruce Hasbargen	County Engineer	(218)-333-8173	2491 Adams Ave NW Bemidji, MN 56601	bruce.hasbargen@co.beltrami.mn.us
Benton	Chris Byrd	County Engineer	(320)-968-5051	PO Box 247 Foley, MN 56329	cbyrd@co.benton.mn.us
Big Stone	Todd Larson	County Engineer	(320)-839-2594	437 Minnesota St N Ortonville, MN 56278	todd.larson@bigstonecounty.org
Blue Earth	Ryan Thilges	Public Works Director	(507)-304-4031	P.O. Box 8608 Mankato, MN 56002	ryan.thilges@blueearthcountymn.gov
Brown	Wayne Stevens	County Engineer	(507)-233-5700	1901 N Jefferson St New Ulm, MN 56073	wayne.stevens@co.brown.mn.us
Carlton	JinYeene Neumann	County Engineer	(218)-384-9150	1630 County Road 61 Carlton, MN 55718	jinyeene.neumann@co.carlton.mn.us
Carver	Lyndon Colebrook-Robjent	Public Works Director/ County Engineer	(952)-466-5206	11360 Highway 212 Ste 1 Cologne, MN 55322	lrobjent@co.carver.mn.us
Cass	Darrick Anderson	County Engineer	(218)-547-5201	PO Box 579 Walker, MN 56484	darrick.anderson@co.cass.mn.us
Chippewa	Jeremy Gilb	County Engineer	(320)-269-2151	902 N 17th St Montevideo, MN 56265	jgilb@co.chippewa.mn.us
Chisago	Joe Triplett	Public Works Director/ County Engineer	(651)-213-8708	313 N Main St Rm 400 Center City, MN 55012	joe.triplett@co.chisago.mn.us
Clay	David Overbo	County Engineer	(218)-299-5099	2951 41 1/2 St S Moorhead, MN 56560	david.overbo@co.clay.mn.us
Clearwater	Dan Sauve	County Engineer	(218)-694-6132	213 Main Ave N Bagley, MN 56621	dan.sauve@co.clearwater.mn.us
Cook	Robert Hass	Highway Engineer	(218)-387-3695	609 E 4th Ave Grand Marais, MN 55604	robert.hass@co.cook.mn.us
Cottonwood	Nick Klisch	Public Works Director/ County Engineer	(507)-832-8811	1355 9th Ave Windom, MN 56101	nick.klisch@co.cottonwood.mn.us
Crow Wing	Timothy Bray	County Engineer	(218)-822-2684	16589 County Road 142 Brainerd, MN 56401	tim.bray@crowwing.us
Dakota	Mark Krebsbach	Transportation Director/ County Engineer	(952)-891-7102	14955 Galaxie Ave Saint Paul, MN 55124	mark.krebsbach@co.dakota.mn.us
Dodge	Guy Kohlnhofer	County Engineer	(507)-374-6694	16 S. Airport Dr Dodge Center, MN 55927	guy.kohlnhofer@co.dodge.mn.us
Douglas	Tim Erickson	Public Works Director/ County Engineer	(320)-762-2967	526 Willow Dr. Alexandria, MN 56308	time@co.douglas.mn.us
Faribault	Mark Daly	County Engineer	(507)-526-4288	PO Box 325 Blue Earth, MN 56013	mark.daly@co.faribault.mn.us
Fillmore	Ron Gregg	County Engineer	(507)-765-3854	909 Houston St NW Preston, MN 55965	rgregg@co.fillmore.mn.us
Freeborn	Phillip Wacholz	Public Works Director/ County Engineer	(507)-377-5188	3300 Bridge Ave Albert Lea, MN 56007	phillip.wacholz@co.freeborn.mn.us
Goodhue	Gregory Isakson	Public Works Director/County Engineer	(651)-385-2025	2140 Pioneer Road Red Wing, MN 55066	greg.isakson@co.goodhue.mn.us
Grant	Tracey Von Bargen	County Engineer	(218)-685-8301	PO Box 1005 Elbow Lake, MN 56531	tracey.vonbargen@co.grant.mn.us
Hennepin	Carla Stueve	Transportation Engineer	(612)-596-0356	1600 Prairie Dr Medina, MN 55340	carla.stueve@hennepin.us
Houston	Brian Pogodzinski	County Engineer	(507)-725-3925	1124 E Washington St Caledonia, MN 55921	brian.pogodzinski@co.houston.mn.us
Hubbard	Jed Nordin	County Engineer/ Public Works Coordinator	(218)-732-7640	101 Crocus Hill St Park Rapids, MN 56470	jnordin@co.hubbard.mn.us
Isanti	Richard Heilman	County Engineer	(763)-689-1870	232 Emerson St N Cambridge, MN 55008	rheilman@highway.co.isanti.mn.us
Itasca	Karin Grandia	County Engineer	(218)-327-2853	123 NE 4th St Grand Rapids, MN 55744	karin.grandia@co.itasca.mn.us
Jackson	Tim Stahl	County Engineer	(507)-847-2525	53053 780th St Jackson, MN 56143	tim.stahl@co.jackson.mn.us
Kanabec	Chad Gramentz	Public Works Director/County Engineer	(320)-679-6300	903 Forest Ave E Mora, MN 55051	chad.gramentz@co.kanabec.mn.us
Kandiyohi	Mel Odens	Public Works Director/ County Engineer	(320)-235-3266-x-4105	1801 Highway 12 E Willmar, MN 56201	mel.odens@kcmn.us
Koochiching	David Reimer	County Engineer	(218)-283-1186	715 4th St International Falls, MN 56649	dave.reimer@co.koochiching.mn.us
Lac qui Parle	Sam Muntean	County Engineer	(320)-598-7252	422 5th Avenue, Suite 301 Madison, MN 56256	sam.muntean@lqpco.com