# Duplicate occurrence records

**URL:** <https://discourse.gbif.org/t/duplicate-occurrence-records/3735>\
**Category:** Data Publishing\
**Created:** [December 1, 2022, 2:36am UTC](https://discourse.gbif.org/t/duplicate-occurrence-records/3735 "2022-12-01T02:36:09Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![datafixer](https://sea1.discourse-cdn.com/gbif1/user_avatar/discourse.gbif.org/datafixer/32/956_2.png) [@datafixer](https://discourse.gbif.org/u/datafixer)\
**Post date:** [December 1, 2022, 2:36am UTC](https://discourse.gbif.org/t/duplicate-occurrence-records/3735/1 "2022-12-01T02:36:09Z")

</div>

I distinguish three different kinds of duplicate Darwin Core occurrence records:

**exact** duplicates have the same entries in all fields, including unique identifiers like _occurrenceID_.  
**strict** duplicates have the same entries in all fields except those which give the record a unique ID, like the _occurrenceID_ field.  
**relaxed** duplicates have the same entries in key occurrence fields, such as taxon, coordinates, date and collector/observer.

Exact duplicates are very rare in biodiversity datasets. They probably arise from bookkeeping errors when records are compiled, or when records are converted to the Darwin Core format. Because exact duplicates are easy to find (see below), they’re easy to eliminate as well.

More common are strict duplicates. I suspect that many of these are generated when minor fields are dropped during conversion to Darwin Core. For example, suppose a CMS has these two (simplified) records:

&nbsp;&nbsp;&nbsp;&nbsp;ID | COLLDATE | COLLTOR | SITE | CABINET | DRAWER  
&nbsp;&nbsp;&nbsp;&nbsp;Leptanena depressa | 16 May 1923 | H. Murchison | Hamilton Quarry | 3BH | 15  
&nbsp;&nbsp;&nbsp;&nbsp;Leptanena depressa | 16 May 1923 | H. Murchison | Hamilton Quarry | 3BH | 16

The minor fields “CABINET” and “DRAWER” are dropped in the Darwin Core version, resulting in two strictly duplicate records:

&nbsp;&nbsp;&nbsp;&nbsp;occurrenceID | scientificName | eventDate | recordedBy | locality  
&nbsp;&nbsp;&nbsp;&nbsp;2133 | Leptanena depressa | 1923-05-16 | H. Murchison | Hamilton Quarry  
&nbsp;&nbsp;&nbsp;&nbsp;2134 | Leptanena depressa | 1923-05-16 | H. Murchison | Hamilton Quarry

Strict duplicates might also be the result of delayed identifications. Two records might have exactly the same observation or collection details, but one might have a definite ID — call it _Aus bus_ — while the other ID might be uncertain. At a later date the uncertain ID is firmed up as _Aus bus_, the two records become strict duplicates and they can be merged.

In the example shown below, there are only two strict duplicates in a ca 13000-record dataset. I’ve seen datasets containing thousands of strict duplicates, mostly the result of bookkeeping errors.

Unlike exact and strict duplicates, relaxed duplicates are quite common in Darwin Core datasets and generally don’t arise from compilation or bookkeeping problems. For example, a single plant specimen might be divided and distributed to several herbaria, or individual specimens of a certain species from a particular collecting event might be separately registered in a collection, or the same taxon+location+date+collector might come from two different collection methods (different _samplingProtocol_).

The code I use to detect duplicates relies on a dataset being **tidy** : entries must be consistent and consistently formatted. “1989-06-05” and “5/6/89” might be the same date in your mind, but they’re different for a computer routine that processes data character by character.

Similarly, “C. Darwin”, “Darwin, C.” and “Darwin, C” are different and won’t be recognised as the same by the duplicate-hunting methods described below. More not-the-same examples:

- “B. Abbott; L. Costello” vs “B. Abbott | L. Costello” in _recordedBy_
- “1989-06-05” vs "1989-06-05 " in _eventDate_ (note the trailing space in the second data item)
- “40.0618924337825” vs “40.06189” in _decimalLatitude_
- “K. Alexandrov” vs “K. Аlexandrov” (the “A” in the second data item is the Cyrillic “A”, not the Latin one)

It’s possible to write duplicate-hunting code that allows for variations like these, by first normalising all entries to a selected format. This is an awkward solution for the problem, because normalising a wide range of formats (think of dates) is very code-heavy, because automated normalisation may fail for some “pathological” entries and because the original dataset is left in its untidy state.

Tidy data is a blessing for end users, and tidying of data also makes it _much_ easier for both the data compiler and the end-user to find duplicates!

* * *

**exact** duplicates

Easily found in a BASH shell with _sort [file] | uniq -D_. It’s best to first pipe the result to a line count with _wc -l_, to avoid flooding the terminal in case there are a lot of duplicates. The following example comes from a dataset of psyllid occurrences with 1975 records and 28 fields. Only the first two exact duplicates are shown, and I’ve piped the output for clarity to [barsep](https://www.datafix.com.au/cookbook/extras3.html#2), an alias that replaces tabs with space-bar-space:

 ![shot1](https://global.discourse-cdn.com/gbif1/original/1X/77817670f706e8f034908b6cc03a80c4ac948d3b.png)

* * *

**strict** duplicates  
More information [here](https://www.datafix.com.au/cookbook/content1.html#4) and [here](https://www.datafix.com.au/BASHing/2021-10-27.html)

This and the next code recipe use GNU AWK 4 (or 5) in a BASH shell, work very fast and will easily process large numbers of records (millions) with large numbers of fields (hundreds). For more information on these and related methods, see [_A Data Cleaner’s Cookbook_](https://www.datafix.com.au/cookbook/).

The example here is **occurrence.txt** and it comes from the source archive for [this very tidy GBIF dataset](https://www.gbif.org/dataset/d2875a50-0304-469d-b19e-78dc08007931) (accessed 2022-11-30). The tab-separated file has 13742 records and 35 fields, and the fields that uniquely identify a record are _id_ and _occurrenceID_, which are fields 1 and 7, respectively. The command I used is

&nbsp;&nbsp;&nbsp;&nbsp;`awk -F"\t" 'FNR==NR {$1=$7=1; a[$0]++; next} {x=$0; $1=$7=1} a[$0]>1 {print x}' occurrence.txt occurrence.txt`

 ![shot2](https://global.discourse-cdn.com/gbif1/original/1X/b111c8f52c93fffedb2891bbb8e5d1338faa1b31.png)

The two records shown are identical apart from the _id_ and _occurrenceID_ entries “Pteridophytes\_of\_the\_Urals\_02516” and “Pteridophytes\_of\_the\_Urals\_02517”. If there are numerous strict duplicates they can be sorted afterwards to group the duplicates by their non-unique fields of choice.

* * *

**relaxed** duplicates  
More information [here](https://www.datafix.com.au/cookbook/content1.html#4) and [here](https://www.datafix.com.au/BASHing/2018-07-14.html)

This example is from a just-published [GBIF dataset](https://www.gbif.org/dataset/049b3ca8-8533-4606-8acf-7b2e2c7a74f5) (source archive accessed 2022-12-01), with 7310 records and 92 fields, and no exact or strict duplicates. The fields of interest are _scientificName_ (field 16), _decimalLatitude_ (68), _decimalLongitude_ (69), _eventDate_ (34) and _recordedBy_ (32). In the source Darwin Core archive, occurrences are in **occurrence.csv** , which I converted to tab-separated **oc1** from the horrible CSV format. A preliminary check showed that many records lacked coordinates or date or collector, so I added conditions to the AWK command that these fields should be non-empty. The core command is then

&nbsp;&nbsp;&nbsp;&nbsp;`awk -F"\t" 'FNR==NR {a[$16,$68,$69,$34,$32]++; next} $68!="" && $69!="" && $34!="" && $32!="" && a[$16,$68,$69,$34,$32]>1' oc1 oc1`

and it finds 1123 relaxed duplicates (top of screenshot below).

The command was modified to print these key fields together with _occurrenceID_ (field 7) and _catalogNumber_ (8), and the output was piped to _sort_ to sort the duplicates by taxon, coordinates, date and collector (in that order). Here are the first 10 relaxed duplicates (lower part of screenshot):

 ![shot3](https://global.discourse-cdn.com/gbif1/original/1X/69d1951153f7890cc3142137c5676a9cd98a81c9.png)

* * *

_Robert Mesibov (“datafixer”); [robert.mesibov@gmail.com](mailto:robert.mesibov@gmail.com)_

---

<div class="post-metadata">

**Author:** ![7yl4r](https://sea1.discourse-cdn.com/gbif1/user_avatar/discourse.gbif.org/7yl4r/32/666_2.png) [@7yl4r](https://discourse.gbif.org/u/7yl4r)\
**Post date:** [December 7, 2022, 6:33pm UTC](https://discourse.gbif.org/t/duplicate-occurrence-records/3735/2 "2022-12-07T18:33:18Z")

</div>

I wonder how to make these de-duplication efforts more widely available. Ideally de-duplication could be done on the backend using some QC checks like [these](https://github.com/iobis/obis-qc). However, DwC data processing features like this can also be published as standalone packages.

---

<div class="post-metadata">

**Author:** ![datafixer](https://sea1.discourse-cdn.com/gbif1/user_avatar/discourse.gbif.org/datafixer/32/956_2.png) [@datafixer](https://discourse.gbif.org/u/datafixer)\
**Post date:** [December 7, 2022, 7:39pm UTC](https://discourse.gbif.org/t/duplicate-occurrence-records/3735/3 "2022-12-07T19:39:16Z")

</div>

@7yl4r, please note that the code shown in the post does not do de-duplication. Data compilers and publishers have the responsibility to de-duplicate, or rather, to review and fix (where necessary) any duplication issues detected by the code.

Please also note that the code relies on data being tidy. If data aren’t tidy, neither the code shown above nor a rewrite in a language suitable for backend use will detect duplicates.

I’m not aware of any backend or external packages (e.g. in R or Python) that can tidy a Darwin Core dataset. The OBIS list of checks is shorter than the latest TDWG and GBIF lists of checks and all are shorter and less sophisticated than the checks routinely done for authors of Pensoft data papers, although the checks do not overlap perfectly (they complement each other). All these checks simply flag data issues which then should be (OBIS, GBIF) or have to be (Pensoft) fixed by the data compiler.

Fortunately or unfortunately, Darwin Core datasets are complex beasts that don’t lend themselves to automated checking and fixing. For this reason people (not backend routines) are the best Darwin Core [data cleaners](https://www.datafix.com.au/BASHing/2022-04-08.html). The [code recipes](https://www.datafix.com.au/cookbook/) I use are freely available on the Web and I (and now others) are happy to train others in their use.

I would be happy to discuss this further with you off-list.

---

<div class="post-metadata">

**Author:** ![system](https://global.discourse-cdn.com/gbif1/original/1X/d2525d53d815eb0c992cc44730ed8a6a1be1dbb5.png) [@system](https://discourse.gbif.org/u/system)\
**Post date:** [January 7, 2023, 5:40am UTC](https://discourse.gbif.org/t/duplicate-occurrence-records/3735/4 "2023-01-07T05:40:04Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.