Issues with duplicate records

[kuriwaki]

In the data to be released, there are four five counties which include cvr_ids each with two records for President. There should be just one.

These are

• [x] San Diego #305,
• [x] Waukesha #95
• [x] Kenosha #83
• [x] Brown of WI #68
• [x] San Juan of Utah

There may be other duplicates which affect congressional races (I only looked at president). From this sample, it looks like all of these duplicates are for fringe Presidential candidates.

suppressPackageStartupMessages(
  {
    library(tidyverse)
    library(arrow)
  }
)

ds <- open_dataset("release")

# Two rows per President
duplicated <- ds  |> 
  filter(office == "US PRESIDENT") |>  
  count(state, county_name, cvr_id) |> 
  filter(n >= 2)

## examples
ds |> 
  filter(office == "US PRESIDENT") |> 
  semi_join(duplicated) |> 
  collect() |> 
  select(county_name, cvr_id, precinct, candidate) |> 
  slice_sample(n = 2, by = c(cvr_id, county_name)) |> 
  arrange(cvr_id)
#> # A tibble: 1,466 × 4
#>    county_name cvr_id precinct             candidate                 
#>    <chr>        <int> <chr>                <chr>                     
#>  1 BROWN           73 Village of Howard W5 BRIAN T CARROLL           
#>  2 BROWN           73 Village of Howard W5 BRIAN T CARROLL           
#>  3 SAN JUAN       107 Precinct 11          KANYE WEST                
#>  4 SAN JUAN       107 Precinct 11          KANYE WEST                
#>  5 SAN JUAN       350 Precinct 2MC         JOE MCHUGH ELIZABETH STORM
#>  6 SAN JUAN       350 Precinct 2MC         JOE MCHUGH ELIZABETH STORM
#>  7 BROWN          368 Village of Howard W5 BRIAN T CARROLL           
#>  8 BROWN          368 Village of Howard W5 BRIAN T CARROLL           
#>  9 SAN JUAN       480 Precinct 5BD         JOE MCHUGH ELIZABETH STORM
#> 10 SAN JUAN       480 Precinct 5BD         JOE MCHUGH ELIZABETH STORM
#> # ℹ 1,456 more rows

# which counties
duplicated |> 
  collect() |> 
  count(county_name)
#> # A tibble: 5 × 2
#>   county_name     n
#>   <chr>       <int>
#> 1 BROWN         206
#> 2 KENOSHA       120
#> 3 SAN DIEGO      12
#> 4 SAN JUAN       64
#> 5 WAUKESHA      331

# how many voters in how many precincts?
ds |> 
  filter(office == "US PRESIDENT") |> 
  semi_join(duplicated) |> 
  summarize(
    n_records_dup = n(),
    n_precincts = n_distinct(precinct),
    .by = county_name
    ) |> 
  collect()
#> # A tibble: 5 × 3
#>   county_name n_records_dup n_precincts
#>   <chr>               <int>       <int>
#> 1 SAN DIEGO             120           2
#> 2 KENOSHA               240          60
#> 3 BROWN                 412          73
#> 4 WAUKESHA              662         130
#> 5 SAN JUAN              128           9

# how many precincts are there to begin with
ds |> 
  filter(county_name %in% c("BROWN", "WAUKESHA", "KENOSHA"),
         state == "WISCONSIN") |> 
  summarize(.by = county_name, n_distinct(precinct)) |> 
  collect()
#> # A tibble: 3 × 2
#>   county_name `n_distinct(precinct)`
#>   <chr>                        <int>
#> 1 BROWN                           96
#> 2 KENOSHA                        120
#> 3 WAUKESHA                       190

^{Created on 2024-06-27 with reprex v2.1.0}

[mreece13]

All good catches, there was some inconsistent assignment of the parties in our manual classification of new parties (part of pass2). I've caught these for President and about 20 in other down-ballot contests. They should be ready for the next build.

[kuriwaki]

This is not fixed in San Juan, UT and that is producing duplicates. Should I make an adhoc fix at the end?

[mreece13]

Just fixed it for San Juan in the medsl/ data, can remove the ad-hoc fix if you want.

[kuriwaki]

Thanks. President is now all deduplicated! However, I did check the other offices now. I found the duplicates in the following. Going to reopen this issue, if these are small enough to address in this round.

I checked one county (Charlevoix), and it also seemed like fringe party voters getting two due to two spellings of a party -- e.g. NLP and NATURAL LAW)

US SENATE:

• [x] MICHIGAN ALCONA (n = 15 voters)
• [x] MICHIGAN CHARLEVOIX 29
• [x] MICHIGAN MISSAUKEE 16
• [x] NEW JERSEY GLOUCESTER 1238
• [x] NEW JERSEY SALEM 589

US HOUSE

• [x] FLORIDA (dist = 007) ORANGE 2239: Voters for fringe candidate WILLIAM R GARLINGTON in FL-07 have duplicated records, NON-PARTY AFFILIATED and UNAFFILIATED. Keep only one, probably NPA
• [x] FLORIDA (dist = 6? 11? 15?) LAKE -- cvr_ids c(100000, 200000, 300000, 400000) each are triplets for the district. Causing same issue for state house. They look like header rows that are not actual votes. You or I can simply delete these 4 cvr_ids from the dataset.
• [x] NEW JERSEY 002 GLOUCESTER 604
• [x] NEW JERSEY 002 SALEM 593
• [x] OHIO 004 SENECA 7764
• [x] TEXAS 032 COLLIN 152
• [x] UTAH 003 SAN JUAN 353

STATE SENATE

• [x] GEORGIA 003 CAMDEN 227
• [x] GEORGIA 003 CHARLTON 26
• [x] GEORGIA 003 GLYNN 609
• [x] OHIO 026 SENECA 1769

STATE HOUSE

• [x] CALIFORNIA 042 RIVERSIDE 95553
• [x] OHIO 087/088 SENECA county : 2500 voters who appear to have voted for qualified write-ins in this 40,000 voter county, all in precincts that start with TIFFIN, have two values for state house: 087 and 088. According to their detailed report, there should be NO district 87 in TIFFIN precincts. So I think we should delete only the records that are cvr_id duplicates and have district 87, but I'm not sure
• [x] GEORGIA 152/155 LEE county: Each voter has two records for State house, district 152 and 155. County does not keep election records, but Baltz data says that this small county only has one state house district -- 152. So, I recommend deleting all entries with state house district 155 (they all appear as undervotes and write-ins anyway).

[mreece13]

Can you update this list with the new MEDSL data? It should've been recently updated.

[kuriwaki]

Senates are now all fixed! A few remaining ones in the us/state house, which I researched and updated in the list above.

Lee, GA is a county I did not catch yesterday but should have. Lake, FL is also a county I did not catch yesterday but should have. I can fix downstream too. Orange, FL is from yesterday and looks a typical fringe candidate case, maybe you missed one of them Seneca, OH is from yesterday and the state house records are complicated. Not sure. We could just not release it

(more details above, I also verified these issues in the original medsl/ version)

If rerunning scripts take some time, one of us can fix it downstream as in #329 too.

[mreece13]

I believe I have resolved all of the remaining issues, and they should all be present in medsl/ in the Dropbox.

[kuriwaki]

Ohio Seneca was still giving me duplicates, but I fixed it downstream in the referenced commit above. I'll let you review the new PR, and if it looks good, I can merge.

[mreece13]

You sure there are still duplicates in Seneca, OH? I'm not getting any. Their election results also show that both 87 and 88 are districts in the county.

library(tidyverse)
library(arrow)
#> 
#> Attaching package: 'arrow'
#> The following object is masked from 'package:lubridate':
#> 
#>     duration
#> The following object is masked from 'package:utils':
#> 
#>     timestamp

open_dataset("~/Dropbox (MIT)/Research/CVR_parquet/medsl/") |> 
  filter(state == "OHIO", office == "STATE HOUSE", county_name == "SENECA") |> 
  count(cvr_id, district) |> 
  filter(n>1) |> 
  collect()
#> # A tibble: 0 × 3
#> # ℹ 3 variables: cvr_id <int>, district <chr>, n <int>

^{Created on 2024-07-08 with reprex v2.1.0}

[kuriwaki]

If you remove the district variable in your count(), you will see the duplicates. And yes, that's the tricky thing about this county -- the county includes both districts, but it can't be that a single cvr_id voted in two districts. One of them must be bug, but it's not immediately obvious which one. In the above, I explain my reasoning why I would opt to remove the 87s.