Identifying Data Problems - Bad UUIDs/Keys

data cleaning
data exploration
bad data
Author

AJ

Published

August 18, 2026

Bad Data :(

Last time, I did the work of pulling in my found data set and getting it back to a state where I can start using it for projects, again.

As I work through data, I will continuously apply a variety of methods to check and make sure everything is looking good. You never really know where an issue is going to pop up. If my data is supposed to be tidy, that is one of the first things I check. Since I built this data set, I know how it SHOULD be structured, but when I translated it all to relational tables in MySQL, I broke apart the data and planned to use universally unique identifiers (UUID) as primary keys (PK) to link it all back together as needed. Before I went too much further, I decided to check to make sure my PK’s were still in tact. For me, the easiest way to check this is use dplyr’s group_by() + count() on the PKs in the base set, and then filter to anything that is > 1.

Before I do anything, I need to load everything up as outlined in the previous post.

# Load libraries
library(here)
library(tidyverse)
library(jsonlite)

# Load JSON Data
raw_data_dir <- here("raw_data_files")

# I am adding this "pattern =" operation here so that list.files() only looks at 
# and pulls .json files. This prevents issues in case I end up having non-json 
# raw data.
raw_data_file_names <- list.files(raw_data_dir,
                                  pattern = ".json")

# Magic loop to read in the JSON data
for (i in raw_data_file_names) {

  current_file <- fromJSON(file.path(
    raw_data_dir,
    i
  ), flatten = T)
  
  simple_name <- i |> 
    str_remove(".json")
  
  assign(simple_name, current_file)
}

Now, I group on the mainID (my UUID/PK) and count every record under that mainID. There should only be 1 record per UUID. If there are counts > 1, then my values are duplicated, and that will suck.

main_base |> 
  group_by(mainID) |> 
  count() |> 
  filter(n > 1)
# A tibble: 147 × 2
# Groups:   mainID [147]
   mainID           n
   <chr>        <int>
 1 0LG2MOY0TDA9     2
 2 0M1LCZSXB2PF     2
 3 0VPSBL9DOWCM     2
 4 0VSNH1PYDG4N     2
 5 19RVC0OQBAAL     2
 6 1GBTCR5DUAUU     2
 7 1MUF71JB23LV     2
 8 21IH1RUEQBOT     2
 9 25OWMINUBWJT     2
10 26YHIRUVYF8C     2
# ℹ 137 more rows

DAMMIT!

…

OK, so. At some point, when I translated this data to SQL, I must have accidentally assigned some records the same mainID value. I can’t recall exactly what happened, but I likely just copied and pasted a set of IDs that had already been used on a previous a set of records. I have some suspicions as to WHEN this occurred, but I want to do some digging.

I’ll create a vector (dupe_main_ids) with these duplicated UUIDs, and use that to filter to the problem records. I want to see what these records might have in common. I will start with the top and bottom 10 records, using dplyr’s slice_head() and slice_tail() functions.

dupe_main_ids <- main_base |> 
  group_by(mainID) |> 
  count() |> 
  filter(n > 1) |> 
  # Use pull() to create a vector out of a single column.
  pull(mainID)

# Slice first 10 records using the IDs in dupe_main_ids as a filter.
main_base |> 
  filter(mainID %in% dupe_main_ids) |> 
  arrange(mainID) |> 
  slice_head(n = 10)
         mainID       entity   locationID flagForDeletion
1  0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH                
2  0LG2MOY0TDA9      Hypaepa OSPGY6Y0VUL6                
3  0M1LCZSXB2PF       Tavium MQUX5CFU1NNJ                
4  0M1LCZSXB2PF  Isaura Nova X590S9QZE7WQ                
5  0VPSBL9DOWCM        Mater RFBDLM8APMI3                
6  0VPSBL9DOWCM     Orcistus WJFQ9W9SEY4R                
7  0VSNH1PYDG4N   Paleopolis D0A6EI38MYZO                
8  0VSNH1PYDG4N     Pacandus X6M1XLB49MF8                
9  19RVC0OQBAAL       Temnus HLCAJBTXUFTI                
10 19RVC0OQBAAL        Soloi OM1JXCJH8QTB                
# Slice last 10 records.
main_base |> 
  filter(mainID %in% dupe_main_ids) |> 
  arrange(mainID) |>
  slice_tail(n = 10)
         mainID       entity   locationID flagForDeletion
1  Z6RY9EEIAAXH      Himeria OZDEE3U7X2XS                
2  Z6RY9EEIAAXH       Numana T4J7ZLQY4M0Y                
3  ZFIGWBDNF5RG        Cusae VZ92V4AA1ED1                
4  ZFIGWBDNF5RG      Foligno O7LK917CGUPW                
5  ZKHST7ZWEBBV      Busiris FBZOC6XJA5YZ                
6  ZKHST7ZWEBBV       Lorium WYS2COJIIT4K                
7  ZOGWL6PFL8N7 Augustopolis 2LGSJWDRI3GM                
8  ZOGWL6PFL8N7        Sulci JPRF28EPOHAR                
9  ZY1KR1DP7ZGF       Gabala 434YSWWCWTQ9                
10 ZY1KR1DP7ZGF      Kildare    Karpathus

I arranged the output by mainID so that the identical UUIDs appear together. From what I immediately see, the duplicate ID’s at the head and tail of the data set belong to cities, towns, and villages. I say this with confidence because of how extensively I have worked with this data, and I remember breaking many of these places out into their own records - which is likely where these errors were introduced. I also notice that will the exception of Kildare, at the very end, the location ID’s appear to be intact.

Going to perform one more check…sometimes, I like to perform random spot checks using dplyr’s slice_sample() (related to slice_head/tail() functions I used above). This allows you to slice your data set to any number of random records. Each time you run the code, you get a new set. I will select 10 random records and run this code 3-4 times.

main_base |> 
  filter(mainID %in% dupe_main_ids) |> 
  slice_sample(n = 10)
         mainID     entity   locationID flagForDeletion
1  5CL3R7YKJWKM    Gortina HRUGI2UNCU9X                
2  UC645EYZQJRP  Nilopolis 2FENUGKI77M5                
3  QI8C95MTX01P  Octodurum LDQJV9WMIMKI                
4  NO8X4Q41X4EL     Hirena 9MEQILLO8UIZ                
5  HORX3L7D3N5N     Málaga RS681LGC15WD                
6  NM930AOY956U  Theudalis LOSL2U4E5LB6                
7  H6C8FODF8E7K      Atrib LAHHPAUO5BTT                
8  ER8B453EZN4X   Philippi ALHL3JVDUH87                
9  II0WN9R9NU4L Carpentras MQMUCRDOLQDZ                
10 LYZKXBQF6H3E    Lilibeo Q1MTDLVI12W0

I am pretty satisfied that these are all places.

Let’s see what happens when I connect them to the locations set. Hopefully, they connect to the right entries. I’ll use dplyr’s left_join() on the locationID (which is the PK for locations) to do this. I am only really interested in confirming that the place names line up, so I will only pull over ancientcity, ancientregion, and modernCountry. I will also spot check my records.

main_base |> 
  filter(mainID %in% dupe_main_ids) |> 
  left_join(
    locations |> 
      # Pull over only these columns.
      select(locationID,
             ancientcity,
             ancientregion,
             modernCountry),
    # Join on locationID.
    by = join_by(locationID)) |> 
  # slice_sample 5 random records at a time.
  slice_sample(n = 5)
        mainID      entity   locationID flagForDeletion
1 GBE3N0802QIL Scythopolis F3EYMFHUQ68M                
2 OMCNX80SK5BG       Elusa 0AW5GKUT61PM                
3 4PPCCWYEGEZB    Thelepte UIRQ5TK5YOQ4                
4 D1WJ33D1ZW1X      Chiusi 9WWQOPZPHB57                
5 8NIYJ369LS1P    Berenice QQXEWFRKBSAB                
                ancientcity     ancientregion modernCountry
1 Baithsan/Scythopolis/Nysa           (blank)        Israel
2                     Elusa Palaestina Tertia        Israel
3         Thelepte/Sufetula          Byzacene       Tunisia
4 Chiusi - Camars - Clusium           Etruria         Italy
5         Berenice/‘Aydhab              Egypt         Egypt

IRL, I ran that code 5 times, and it looks like the place names line up. I also did a visual check with view(), since this isn’t a lot of entries. It did appear that everything was still properly connected location-wise. This is a bit of a relief. Finding the locations for a lot of these places was one of the more time-consuming, painstaking tasks I performed. Many of these locations have changed names over the past 2 millennia, and I spent a lot of time tracking some of them down. I am pretty happy I don’t have to do that over.

Despite the fact the locations remain intact, I can’t trust these ID’s to connect to the other sets. This was confirmed when I tried to connect these them to sources. Focusing on one set of these records, you can see the issue:

main_base |> 
  as_tibble() |> 
  filter(mainID == "0LG2MOY0TDA9") |> 
    left_join(
    sources,
    by = join_by(mainID)
  ) |> 
  arrange(sourceType) |> 
  print(n = Inf)
Warning in left_join(filter(as_tibble(main_base), mainID == "0LG2MOY0TDA9"), : Detected an unexpected many-to-many relationship between `x` and `y`.
ℹ Row 1 of `x` matches multiple rows in `y`.
ℹ Row 2393 of `y` matches multiple rows in `x`.
ℹ If a many-to-many relationship is expected, set `relationship =
  "many-to-many"` to silence this warning.
# A tibble: 14 × 6
   mainID       entity       locationID   flagForDeletion sourceURL   sourceType
   <chr>        <chr>        <chr>        <chr>           <chr>       <chr>     
 1 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              12822       darmcAtHa…
 2 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              12822       darmcAtHa…
 3 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              http://www… newAdvent…
 4 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              http://www… newAdvent…
 5 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              http://www… newAdvent…
 6 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              http://www… newAdvent…
 7 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              https://ww… other     
 8 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              https://ww… other     
 9 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              https://pl… pleiades  
10 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              https://pl… pleiades  
11 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              https://ww… talbert   
12 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              https://ww… talbert   
13 0LG2MOY0TDA9 Panemotichus JWJUHI80CZOH ""              https://en… wikipedia 
14 0LG2MOY0TDA9 Hypaepa      OSPGY6Y0VUL6 ""              https://en… wikipedia

First and foremost, I get a warning that my data sets are matching multiple values on both sides, and since I did specify a left_join(), I probably wasn’t expecting or wanting that. Then, pretty clearly, I can see how all of the records are mixed together in the resulting source information. This sucks. I spent a lot of time hunting down sources for these things, too.

I am sad, sad kitty

This blog entry obviously does not show it, but I spent a couple days thinking about what to do here. Eventually, I decided that I would flag these problem records and move on. These are places, and the important parts have remained untouched via the locations. I would have been much more upset had the event, writing, or person-based records had been compromised. I have a couple ideas of how I can eventually correct this, but for right now I am spinning my wheels and not getting very far. I may have to to do some hand-correcting, but I’m not that desperate (yet!)

Let’s take a small, quick detour to flag the bad records and do some data saving.

First, I flag the duplicate mainID records using dplyr’s if_else(). If the UUID is in the dupe_main_ids vector, it is “1”, if it’s not, it is “0”. Normally, I don’t like overwriting original sets, but in this case I want this information to be part of my main_base set from here on out. I also want to remove flagForDeletion because it is an artifact that I don’t need anymore.

main_base <- main_base |> 
  mutate(dupe_main_id = if_else(mainID %in% dupe_main_ids, 1, 0)) |> 
  select(-flagForDeletion)

# Check that the flag is properly set by checking a few of the duplicate 
# mainID values from dupe_main_ids.
rando_dupe_main_ids <- sample(dupe_main_ids, 3)

# The following should always return "1" in the dupe_main_id column.
main_base |> 
  filter(mainID %in% rando_dupe_main_ids) |> 
  arrange(mainID)
        mainID                entity   locationID dupe_main_id
1 9VG4LNZFNJ9M           Helenopolis V8CDZF6FVO97            1
2 9VG4LNZFNJ9M Constantia antea Tomi XYTHFET6R3Q9            1
3 D1WJ33D1ZW1X                Sidyma QG0KIJYBQK5A            1
4 D1WJ33D1ZW1X                Chiusi 9WWQOPZPHB57            1
5 U3IASRYNNEWO                Siunia MKACZX9NATJ7            1
6 U3IASRYNNEWO               Messina UMVHUPW0OP4I            1

And since I have created a new main_base data set with a new column, I am going to save that as an .rds file. Going forward I can load it into my environment with my changes in tact. I am going to save it to a new project folder called “data_objects”.

saveRDS(main_base, file.path(
  here("data_objects"),
  "main_base.rds"
))

I bet there’s all kinds of weird stuff happening that should be looked at. So, for now I need to forget my bad UUID woes and move on with my life.