all_dupe_and_related_v2.ipynb

{
  "cells": [
    {
      "cell_type": "code",
      "execution_count": 13,
      "id": "8a531b44",
      "metadata": {
        "id": "8a531b44"
      },
      "outputs": [],
      "source": [
        "# This script finds all duplicate resources and their parent (item) or children (asset) in Academic Commons (AC).\n",
        "#\n",
        "# First created: 2022-11-10\n",
        "# Finalized: 2022-11-22\n",
        "# Version 2: 2022-12-22\n",
        "#\n",
        "# Main Processes:\n",
        "# 1) Import (a) the complete data exported from AC, and (b) a list of current duplicates in AC.\n",
        "# 2) Select items from (b) that are marked as duplicates ('Yes dupe').\n",
        "# 3) Look up bulk AC data for dupllicates' child assets.\n",
        "# 4) Output as 2 CSV, one for Hyacinth, the other one for DataCite. See descriptions below.\n",
        "#\n",
        "# Version 2:\n",
        "# To merge stats of duplicate items and assets on Hyacinth, mapping duplicate child assets to its stay-published equivalent.\n",
        "# It should be the first child asset that is:\n",
        "# (a) Published on AC ('Publish Target 1 > String Key' = academic_commons), and\n",
        "# (b) 'Asset Data > Original Filename' is not 'mets.xml', which should never be published.\n",
        "\n",
        "import pandas as pd"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 14,
      "id": "f281dc5e",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 165
        },
        "id": "f281dc5e",
        "outputId": "007e2d5a-7b10-423d-fbe4-3cbfb8fb3773"
      },
      "outputs": [],
      "source": [
        "# **Import the AC exported full dataset**\n",
        "\n",
        "df= pd.read_csv('ac_export_data.csv', dtype='string')\n",
        "\n",
        "# df.head()      # Sample data\n",
        "# print(df.columns.tolist())     # Total 8092 columns"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 15,
      "id": "871ccafc",
      "metadata": {},
      "outputs": [],
      "source": [
        "# **Import the PID list from \"Duplicates in AC\" to compare with the main list.**\n",
        "\n",
        "currentACDupe = pd.read_csv('currentPIDList.csv')\n",
        "\n",
        "# currentACPID_list.head()       # Sample data."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 16,
      "id": "1c03988e",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>PID</th>\n",
              "      <th>DOI</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>Title</th>\n",
              "      <th>Parent PID</th>\n",
              "      <th>Filename</th>\n",
              "      <th>Checksum</th>\n",
              "      <th>Published</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ac:ttdz08kpzn</td>\n",
              "      <td>doi:10.7916/d8-38h8-y303</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE 'Red Listing’ Heritage: Endan...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ac:110981</td>\n",
              "      <td>doi:10.7916/D83X8CXZ</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE 555.pdf</td>\n",
              "      <td>ac:110983</td>\n",
              "      <td>demo title for ac:110981</td>\n",
              "      <td>sha256:ac32921617195496fd19143b2c8fca95141500c...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ac:66t1g1jwwx</td>\n",
              "      <td>doi:10.7916/d8-9t26-by02</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE 617-Article Text-12318-2-10-2...</td>\n",
              "      <td>ac:xksn02v726</td>\n",
              "      <td>617-Article Text-12318-2-10-20190307.pdf</td>\n",
              "      <td>sha256:91a2b55911dc72defcfb1ee9475ebc91fa6f16c...</td>\n",
              "      <td>academic_commons</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ac:9zw3r22837</td>\n",
              "      <td>doi:10.7916/d8-32az-2n96</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE 621-Article Text-12412-2-10-2...</td>\n",
              "      <td>ac:3j9kd51c7s</td>\n",
              "      <td>621-Article Text-12412-2-10-20190329.pdf</td>\n",
              "      <td>sha256:9a63dda5b1f926f99b4c8ce170ba0db488a30ef...</td>\n",
              "      <td>academic_commons</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>ac:hx3ffbg7f7</td>\n",
              "      <td>doi:10.7916/d8-17e7-gx14</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE 719-Article Text-16086-1-10-2...</td>\n",
              "      <td>ac:f1vhhmgqrw</td>\n",
              "      <td>719-Article Text-16086-1-10-20190910 (1).pdf</td>\n",
              "      <td>sha256:f9f289e2572037d7540aacef8b41e6fcf1c86e9...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             PID                       DOI Object Type  \\\n",
              "1  ac:ttdz08kpzn  doi:10.7916/d8-38h8-y303        item   \n",
              "2      ac:110981      doi:10.7916/D83X8CXZ       asset   \n",
              "3  ac:66t1g1jwwx  doi:10.7916/d8-9t26-by02       asset   \n",
              "4  ac:9zw3r22837  doi:10.7916/d8-32az-2n96       asset   \n",
              "5  ac:hx3ffbg7f7  doi:10.7916/d8-17e7-gx14       asset   \n",
              "\n",
              "                                               Title     Parent PID  \\\n",
              "1  !DNP - DUPLICATE 'Red Listing’ Heritage: Endan...           <NA>   \n",
              "2                           !DNP - DUPLICATE 555.pdf      ac:110983   \n",
              "3  !DNP - DUPLICATE 617-Article Text-12318-2-10-2...  ac:xksn02v726   \n",
              "4  !DNP - DUPLICATE 621-Article Text-12412-2-10-2...  ac:3j9kd51c7s   \n",
              "5  !DNP - DUPLICATE 719-Article Text-16086-1-10-2...  ac:f1vhhmgqrw   \n",
              "\n",
              "                                       Filename  \\\n",
              "1                                          <NA>   \n",
              "2                      demo title for ac:110981   \n",
              "3      617-Article Text-12318-2-10-20190307.pdf   \n",
              "4      621-Article Text-12412-2-10-20190329.pdf   \n",
              "5  719-Article Text-16086-1-10-20190910 (1).pdf   \n",
              "\n",
              "                                            Checksum         Published  \n",
              "1                                               <NA>              <NA>  \n",
              "2  sha256:ac32921617195496fd19143b2c8fca95141500c...              <NA>  \n",
              "3  sha256:91a2b55911dc72defcfb1ee9475ebc91fa6f16c...  academic_commons  \n",
              "4  sha256:9a63dda5b1f926f99b4c8ce170ba0db488a30ef...  academic_commons  \n",
              "5  sha256:f9f289e2572037d7540aacef8b41e6fcf1c86e9...              <NA>  "
            ]
          },
          "execution_count": 16,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Extract only the relevent columns from the full AC data to speed up process.\n",
        "\n",
        "trimmedACData = df[['PID', '_doi', 'Digital Object Type > String Key', 'Title 1 > Sort Portion', 'Parent Digital Object 1 > PID', 'Asset Data > Original Filename', 'Asset Data > Checksum', 'Publish Target 1 > String Key']]\n",
        "trimmedACData.columns = ['PID', 'DOI', 'Object Type', 'Title', 'Parent PID', 'Filename', 'Checksum', 'Published']\n",
        "trimmedACData = trimmedACData.drop([0])     # Remove first row of element keys.\n",
        "\n",
        "trimmedACData.head()        # Sample data"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 17,
      "id": "ad9f5ea0",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Test row?</th>\n",
              "      <th>YES dupe</th>\n",
              "      <th>Ignore</th>\n",
              "      <th>Further review</th>\n",
              "      <th>delete--PID</th>\n",
              "      <th>delete--DOI</th>\n",
              "      <th>delete--first_published</th>\n",
              "      <th>Remark</th>\n",
              "      <th>keep--PID</th>\n",
              "      <th>keep--DOI</th>\n",
              "      <th>OpenRefine Dupe</th>\n",
              "      <th>NotDupes</th>\n",
              "      <th>ConfirmedDupe</th>\n",
              "      <th>OR Digital Object Type &gt; String Key</th>\n",
              "      <th>OR Title 1 &gt; Sort Portion</th>\n",
              "      <th>OR Internal Note 1 &gt; Value</th>\n",
              "      <th>OR Note 1 &gt; Type</th>\n",
              "      <th>OR Note 1 &gt; Value</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>ac:4f4qrfj6t4</td>\n",
              "      <td>doi:10.7916/d8-3b81-q793</td>\n",
              "      <td>2020-08-10T17:13:28Z</td>\n",
              "      <td>NaN</td>\n",
              "      <td>ac:kd51c5b01p</td>\n",
              "      <td>doi:10.7916/d8-vynf-tk42</td>\n",
              "      <td>True</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE Red Listing’ Heritage: Endang...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>ac:r2280gb5rw</td>\n",
              "      <td>doi:10.7916/d8-mrbt-wp10</td>\n",
              "      <td>2020-08-10T17:14:24Z</td>\n",
              "      <td>NaN</td>\n",
              "      <td>ac:kd51c5b01p</td>\n",
              "      <td>doi:10.7916/d8-vynf-tk42</td>\n",
              "      <td>True</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE Red Listing’ Heritage: Endang...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>ac:ttdz08kpzn</td>\n",
              "      <td>doi:10.7916/d8-38h8-y303</td>\n",
              "      <td>Mon Aug 10 2020 13:15:13 GMT-0400 (Eastern Day...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>ac:kd51c5b01p</td>\n",
              "      <td>doi:10.7916/d8-vynf-tk42</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE Red Listing’ Heritage: Endang...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>ac:f1vhhmgqrw</td>\n",
              "      <td>doi:10.7916/d8-ty95-3d33</td>\n",
              "      <td>2020-01-15T15:15:33Z</td>\n",
              "      <td>NaN</td>\n",
              "      <td>ac:612jm63xw2</td>\n",
              "      <td>doi:10.7916/d8-h8ee-nr45</td>\n",
              "      <td>True</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE Subacute Sclerosing Panenceph...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>This editorial was written in response to:\\n\\n...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>False</td>\n",
              "      <td>True</td>\n",
              "      <td>False</td>\n",
              "      <td>False</td>\n",
              "      <td>ac:8pk0p2ngj7</td>\n",
              "      <td>doi:10.7916/d8-ss8x-h690</td>\n",
              "      <td>2019-12-17T18:35:29Z</td>\n",
              "      <td>NaN</td>\n",
              "      <td>ac:612jm63xw2</td>\n",
              "      <td>doi:10.7916/d8-h8ee-nr45</td>\n",
              "      <td>True</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP - DUPLICATE Subacute Sclerosing Panenceph...</td>\n",
              "      <td>NaN</td>\n",
              "      <td>NaN</td>\n",
              "      <td>This editorial was written in response to:\\n\\n...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "   Test row?  YES dupe  Ignore  Further review    delete--PID  \\\n",
              "0      False      True   False           False  ac:4f4qrfj6t4   \n",
              "1      False      True   False           False  ac:r2280gb5rw   \n",
              "2      False      True   False           False  ac:ttdz08kpzn   \n",
              "3      False      True   False           False  ac:f1vhhmgqrw   \n",
              "4      False      True   False           False  ac:8pk0p2ngj7   \n",
              "\n",
              "                delete--DOI  \\\n",
              "0  doi:10.7916/d8-3b81-q793   \n",
              "1  doi:10.7916/d8-mrbt-wp10   \n",
              "2  doi:10.7916/d8-38h8-y303   \n",
              "3  doi:10.7916/d8-ty95-3d33   \n",
              "4  doi:10.7916/d8-ss8x-h690   \n",
              "\n",
              "                             delete--first_published Remark      keep--PID  \\\n",
              "0                               2020-08-10T17:13:28Z    NaN  ac:kd51c5b01p   \n",
              "1                               2020-08-10T17:14:24Z    NaN  ac:kd51c5b01p   \n",
              "2  Mon Aug 10 2020 13:15:13 GMT-0400 (Eastern Day...    NaN  ac:kd51c5b01p   \n",
              "3                               2020-01-15T15:15:33Z    NaN  ac:612jm63xw2   \n",
              "4                               2019-12-17T18:35:29Z    NaN  ac:612jm63xw2   \n",
              "\n",
              "                  keep--DOI OpenRefine Dupe NotDupes ConfirmedDupe  \\\n",
              "0  doi:10.7916/d8-vynf-tk42            True      NaN           NaN   \n",
              "1  doi:10.7916/d8-vynf-tk42            True      NaN           NaN   \n",
              "2  doi:10.7916/d8-vynf-tk42             NaN      NaN           NaN   \n",
              "3  doi:10.7916/d8-h8ee-nr45            True      NaN           NaN   \n",
              "4  doi:10.7916/d8-h8ee-nr45            True      NaN           NaN   \n",
              "\n",
              "  OR Digital Object Type > String Key  \\\n",
              "0                                item   \n",
              "1                                item   \n",
              "2                                item   \n",
              "3                                item   \n",
              "4                                item   \n",
              "\n",
              "                           OR Title 1 > Sort Portion  \\\n",
              "0  !DNP - DUPLICATE Red Listing’ Heritage: Endang...   \n",
              "1  !DNP - DUPLICATE Red Listing’ Heritage: Endang...   \n",
              "2  !DNP - DUPLICATE Red Listing’ Heritage: Endang...   \n",
              "3  !DNP - DUPLICATE Subacute Sclerosing Panenceph...   \n",
              "4  !DNP - DUPLICATE Subacute Sclerosing Panenceph...   \n",
              "\n",
              "  OR Internal Note 1 > Value OR Note 1 > Type  \\\n",
              "0                        NaN              NaN   \n",
              "1                        NaN              NaN   \n",
              "2                        NaN              NaN   \n",
              "3                        NaN              NaN   \n",
              "4                        NaN              NaN   \n",
              "\n",
              "                                   OR Note 1 > Value  \n",
              "0                                                NaN  \n",
              "1                                                NaN  \n",
              "2                                                NaN  \n",
              "3  This editorial was written in response to:\\n\\n...  \n",
              "4  This editorial was written in response to:\\n\\n...  "
            ]
          },
          "execution_count": 17,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Select rows that are marked as duplicates from the \"Duplicate in AC\" CSV.\n",
        "\n",
        "currentACDupe = currentACDupe[currentACDupe['YES dupe'] == True].reset_index(drop=True)\n",
        "\n",
        "# Create a list of duplicate PIDs.\n",
        "\n",
        "currentDupePID = currentACDupe['delete--PID'].tolist()\n",
        "\n",
        "currentACDupe.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 18,
      "id": "7ffb5a9a",
      "metadata": {
        "id": "7ffb5a9a"
      },
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>PID</th>\n",
              "      <th>DOI</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>Title</th>\n",
              "      <th>Parent PID</th>\n",
              "      <th>Filename</th>\n",
              "      <th>Checksum</th>\n",
              "      <th>Published</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>5</th>\n",
              "      <td>ac:hx3ffbg7f7</td>\n",
              "      <td>doi:10.7916/d8-17e7-gx14</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE 719-Article Text-16086-1-10-2...</td>\n",
              "      <td>ac:f1vhhmgqrw</td>\n",
              "      <td>719-Article Text-16086-1-10-20190910 (1).pdf</td>\n",
              "      <td>sha256:f9f289e2572037d7540aacef8b41e6fcf1c86e9...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>35</th>\n",
              "      <td>ac:149517</td>\n",
              "      <td>doi:10.7916/D8HH6VDV</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP - DUPLICATE hotdep.pdf</td>\n",
              "      <td>ac:149516</td>\n",
              "      <td>hotdep.pdf</td>\n",
              "      <td>sha256:ae8b0534f6d8b08a49c496fcb7a364c2d852e6c...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>63</th>\n",
              "      <td>ac:h9w0vt4bf0</td>\n",
              "      <td>doi:10.7916/d8-dky4-9y46</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP Duplicate 5402-Article Text-9484-1-10-202...</td>\n",
              "      <td>ac:3bk3j9kd83</td>\n",
              "      <td>5402-Article Text-9484-1-10-20200403.pdf</td>\n",
              "      <td>sha256:aedcdfda8a3fce63245f1063e428bc40aa5680d...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>95</th>\n",
              "      <td>ac:z34tmpg4hw</td>\n",
              "      <td>doi:10.7916/D8WQ1G7N</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP Duplicate Making Cognitive Latent Variabl...</td>\n",
              "      <td>ac:08kprr4xht</td>\n",
              "      <td>Making Cognitive Latent Variables Manifest.pdf</td>\n",
              "      <td>sha256:ca904709a2a0793fd43dacf387814ef038c34cc...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>109</th>\n",
              "      <td>ac:110917</td>\n",
              "      <td>doi:10.7916/D8BC46CV</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP Duplicate cucs-018-08.pdf</td>\n",
              "      <td>ac:110919</td>\n",
              "      <td>cucs-018-08.pdf</td>\n",
              "      <td>sha256:ae391ed01a03044e93d6e7f54a92c9e6f85f0ff...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "               PID                       DOI Object Type  \\\n",
              "5    ac:hx3ffbg7f7  doi:10.7916/d8-17e7-gx14       asset   \n",
              "35       ac:149517      doi:10.7916/D8HH6VDV       asset   \n",
              "63   ac:h9w0vt4bf0  doi:10.7916/d8-dky4-9y46       asset   \n",
              "95   ac:z34tmpg4hw      doi:10.7916/D8WQ1G7N       asset   \n",
              "109      ac:110917      doi:10.7916/D8BC46CV       asset   \n",
              "\n",
              "                                                 Title     Parent PID  \\\n",
              "5    !DNP - DUPLICATE 719-Article Text-16086-1-10-2...  ac:f1vhhmgqrw   \n",
              "35                         !DNP - DUPLICATE hotdep.pdf      ac:149516   \n",
              "63   !DNP Duplicate 5402-Article Text-9484-1-10-202...  ac:3bk3j9kd83   \n",
              "95   !DNP Duplicate Making Cognitive Latent Variabl...  ac:08kprr4xht   \n",
              "109                     !DNP Duplicate cucs-018-08.pdf      ac:110919   \n",
              "\n",
              "                                           Filename  \\\n",
              "5      719-Article Text-16086-1-10-20190910 (1).pdf   \n",
              "35                                       hotdep.pdf   \n",
              "63         5402-Article Text-9484-1-10-20200403.pdf   \n",
              "95   Making Cognitive Latent Variables Manifest.pdf   \n",
              "109                                 cucs-018-08.pdf   \n",
              "\n",
              "                                              Checksum Published  \n",
              "5    sha256:f9f289e2572037d7540aacef8b41e6fcf1c86e9...      <NA>  \n",
              "35   sha256:ae8b0534f6d8b08a49c496fcb7a364c2d852e6c...      <NA>  \n",
              "63   sha256:aedcdfda8a3fce63245f1063e428bc40aa5680d...      <NA>  \n",
              "95   sha256:ca904709a2a0793fd43dacf387814ef038c34cc...      <NA>  \n",
              "109  sha256:ae391ed01a03044e93d6e7f54a92c9e6f85f0ff...      <NA>  "
            ]
          },
          "execution_count": 18,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# If any resource in the current dupe list is a parent item, find all of its children (other assets under that parent item).\n",
        "# These new found children will join the current dupe list to be worked on.\n",
        "# New list shows: Child index - Child PID - Child Resource Type - Child Title - Parent PID\n",
        "# A parent PID can be repeated on multiple rows if having more than one child\n",
        "\n",
        "# isin() checks each value of the currentDupePID[] if exist in the parent object column in bulk AC data.\n",
        "childofDupeParent = trimmedACData[trimmedACData['Parent PID'].isin(currentDupePID)]\n",
        "#childofDupeParent.columns = ['PID', 'DOI', 'Object Type', 'Title', 'Parent PID', 'Filename', 'Published']\n",
        "\n",
        "childofDupeParent.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 19,
      "id": "ebd1fff5",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>PID</th>\n",
              "      <th>DOI</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>Title</th>\n",
              "      <th>Parent PID</th>\n",
              "      <th>Filename</th>\n",
              "      <th>Checksum</th>\n",
              "      <th>Published</th>\n",
              "      <th>Parent PID to Keep</th>\n",
              "      <th>DOI to Map to</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ac:z34tmpg4hw</td>\n",
              "      <td>doi:10.7916/D8WQ1G7N</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP Duplicate Making Cognitive Latent Variabl...</td>\n",
              "      <td>ac:08kprr4xht</td>\n",
              "      <td>Making Cognitive Latent Variables Manifest.pdf</td>\n",
              "      <td>sha256:ca904709a2a0793fd43dacf387814ef038c34cc...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>ac:193300</td>\n",
              "      <td>doi:10.7916/D8W095Q0</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ac:107680</td>\n",
              "      <td>doi:10.7916/D8VM4KQZ</td>\n",
              "      <td>asset</td>\n",
              "      <td>WP_222.pdf</td>\n",
              "      <td>ac:107682</td>\n",
              "      <td>WP_222.pdf</td>\n",
              "      <td>sha256:5f2c6ab196e5423a41945609e92c33db91e1a21...</td>\n",
              "      <td>academic_commons</td>\n",
              "      <td>ac:115911</td>\n",
              "      <td>doi:10.7916/D8G452GR</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ac:110114</td>\n",
              "      <td>doi:10.7916/D8VQ39FQ</td>\n",
              "      <td>asset</td>\n",
              "      <td>!DNP Duplicate cucs-030-99.pdf</td>\n",
              "      <td>ac:110116</td>\n",
              "      <td>cucs-030-99.pdf</td>\n",
              "      <td>sha256:85f34bc4be2ef43a85e55c70ecf14ba60038be2...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>ac:160664</td>\n",
              "      <td>doi:10.7916/D8X355T5</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ac:110417</td>\n",
              "      <td>doi:10.7916/D88D07CG</td>\n",
              "      <td>asset</td>\n",
              "      <td>358.pdf</td>\n",
              "      <td>ac:110419</td>\n",
              "      <td>demo title for ac:110417</td>\n",
              "      <td>sha256:c4823420621853bc753bd49765274bd38bf61c9...</td>\n",
              "      <td>academic_commons</td>\n",
              "      <td>ac:127054</td>\n",
              "      <td>doi:10.7916/D8XS623S</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ac:137983</td>\n",
              "      <td>doi:10.7916/D84M9GPN</td>\n",
              "      <td>asset</td>\n",
              "      <td>cucs-036-05.pdf</td>\n",
              "      <td>ac:110419</td>\n",
              "      <td>cucs-036-05.pdf</td>\n",
              "      <td>sha256:c4823420621853bc753bd49765274bd38bf61c9...</td>\n",
              "      <td>academic_commons</td>\n",
              "      <td>ac:127054</td>\n",
              "      <td>doi:10.7916/D8XS623S</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             PID                   DOI Object Type  \\\n",
              "0  ac:z34tmpg4hw  doi:10.7916/D8WQ1G7N       asset   \n",
              "1      ac:107680  doi:10.7916/D8VM4KQZ       asset   \n",
              "2      ac:110114  doi:10.7916/D8VQ39FQ       asset   \n",
              "3      ac:110417  doi:10.7916/D88D07CG       asset   \n",
              "4      ac:137983  doi:10.7916/D84M9GPN       asset   \n",
              "\n",
              "                                               Title     Parent PID  \\\n",
              "0  !DNP Duplicate Making Cognitive Latent Variabl...  ac:08kprr4xht   \n",
              "1                                         WP_222.pdf      ac:107682   \n",
              "2                     !DNP Duplicate cucs-030-99.pdf      ac:110116   \n",
              "3                                            358.pdf      ac:110419   \n",
              "4                                    cucs-036-05.pdf      ac:110419   \n",
              "\n",
              "                                         Filename  \\\n",
              "0  Making Cognitive Latent Variables Manifest.pdf   \n",
              "1                                      WP_222.pdf   \n",
              "2                                 cucs-030-99.pdf   \n",
              "3                        demo title for ac:110417   \n",
              "4                                 cucs-036-05.pdf   \n",
              "\n",
              "                                            Checksum         Published  \\\n",
              "0  sha256:ca904709a2a0793fd43dacf387814ef038c34cc...              <NA>   \n",
              "1  sha256:5f2c6ab196e5423a41945609e92c33db91e1a21...  academic_commons   \n",
              "2  sha256:85f34bc4be2ef43a85e55c70ecf14ba60038be2...              <NA>   \n",
              "3  sha256:c4823420621853bc753bd49765274bd38bf61c9...  academic_commons   \n",
              "4  sha256:c4823420621853bc753bd49765274bd38bf61c9...  academic_commons   \n",
              "\n",
              "  Parent PID to Keep         DOI to Map to  \n",
              "0          ac:193300  doi:10.7916/D8W095Q0  \n",
              "1          ac:115911  doi:10.7916/D8G452GR  \n",
              "2          ac:160664  doi:10.7916/D8X355T5  \n",
              "3          ac:127054  doi:10.7916/D8XS623S  \n",
              "4          ac:127054  doi:10.7916/D8XS623S  "
            ]
          },
          "execution_count": 19,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Mapping new parent items' PID and DOI information for the child assets found in last step.\n",
        "# The new column 'DOI to Map to' will be the DOIs that the duplicates should be redirected to.\n",
        "# Information of new DOIs comes from the current AC duplicate list.\n",
        "\n",
        "parentNewDOI = currentACDupe[['delete--PID', 'keep--PID', 'keep--DOI']]\n",
        "\n",
        "childofDupeParent = childofDupeParent.merge(parentNewDOI, how='left', left_on='Parent PID', right_on='delete--PID', sort=True)\n",
        "childofDupeParent = childofDupeParent.drop(columns=['delete--PID'])\n",
        "childofDupeParent = childofDupeParent.rename(columns={'keep--PID': 'Parent PID to Keep', 'keep--DOI': 'DOI to Map to'})\n",
        "\n",
        "childofDupeParent.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 20,
      "id": "57be86f5",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>DOI</th>\n",
              "      <th>PID</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>Title</th>\n",
              "      <th>Parent PID to Keep</th>\n",
              "      <th>DOI to Map to</th>\n",
              "      <th>Filename</th>\n",
              "      <th>Checksum</th>\n",
              "      <th>Published</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>ac:05qfttdz2r</td>\n",
              "      <td>asset</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>ac:x3ffbg79jr</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>sha256:7584d786e1f86f1b3f6bc8a0c8e709bf78d46ab...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>doi:10.7916/D81G1ZR0</td>\n",
              "      <td>ac:08kprr4xht</td>\n",
              "      <td>item</td>\n",
              "      <td>Making Cognitive Latent Variables Manifest: Di...</td>\n",
              "      <td>ac:193300</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>doi:10.7916/d8-ppp7-1s08</td>\n",
              "      <td>ac:08kprr4xkn</td>\n",
              "      <td>item</td>\n",
              "      <td>!DNP DUPLICATE “Addis Ababa Bete (Home)”: Cont...</td>\n",
              "      <td>ac:jq2bvq83gg</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>ac:0gb5mkkwkn</td>\n",
              "      <td>asset</td>\n",
              "      <td>Opening Ceremony 2007 Photo only.pdf</td>\n",
              "      <td>ac:h70rxwdbwx</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "      <td>Opening Ceremony 2007 Photo only.pdf</td>\n",
              "      <td>sha256:c271d9347489f804de3ba0d6583778d868c8926...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>ac:0gb5mkkwmb</td>\n",
              "      <td>asset</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>ac:05qfttdz2s</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>sha256:6f3481cce85978be2c344268b802f1d9d38ec50...</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                        DOI            PID Object Type  \\\n",
              "0                      <NA>  ac:05qfttdz2r       asset   \n",
              "1      doi:10.7916/D81G1ZR0  ac:08kprr4xht        item   \n",
              "2  doi:10.7916/d8-ppp7-1s08  ac:08kprr4xkn        item   \n",
              "3                      <NA>  ac:0gb5mkkwkn       asset   \n",
              "4                      <NA>  ac:0gb5mkkwmb       asset   \n",
              "\n",
              "                                               Title Parent PID to Keep  \\\n",
              "0                                           mets.xml      ac:x3ffbg79jr   \n",
              "1  Making Cognitive Latent Variables Manifest: Di...          ac:193300   \n",
              "2  !DNP DUPLICATE “Addis Ababa Bete (Home)”: Cont...      ac:jq2bvq83gg   \n",
              "3               Opening Ceremony 2007 Photo only.pdf      ac:h70rxwdbwx   \n",
              "4                                           mets.xml      ac:05qfttdz2s   \n",
              "\n",
              "                                       DOI to Map to  \\\n",
              "0  https://academiccommons.columbia.edu/doi/10.79...   \n",
              "1  https://academiccommons.columbia.edu/doi/10.79...   \n",
              "2  https://academiccommons.columbia.edu/doi/10.79...   \n",
              "3  https://academiccommons.columbia.edu/doi/10.79...   \n",
              "4  https://academiccommons.columbia.edu/doi/10.79...   \n",
              "\n",
              "                               Filename  \\\n",
              "0                              mets.xml   \n",
              "1                                  <NA>   \n",
              "2                                  <NA>   \n",
              "3  Opening Ceremony 2007 Photo only.pdf   \n",
              "4                              mets.xml   \n",
              "\n",
              "                                            Checksum Published  \n",
              "0  sha256:7584d786e1f86f1b3f6bc8a0c8e709bf78d46ab...      <NA>  \n",
              "1                                               <NA>      <NA>  \n",
              "2                                               <NA>      <NA>  \n",
              "3  sha256:c271d9347489f804de3ba0d6583778d868c8926...      <NA>  \n",
              "4  sha256:6f3481cce85978be2c344268b802f1d9d38ec50...      <NA>  "
            ]
          },
          "execution_count": 20,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Merge duplicate parents' children data to currentACDupe\n",
        "# Parent and chilren are listed together\n",
        "# Resulting list contains:\n",
        "# DOI - PID - Object Type - Title - New PID - New DOI (to map to)\n",
        "\n",
        "currentACDupSubset = currentACDupe[['delete--DOI', 'delete--PID', 'OR Digital Object Type > String Key', 'OR Title 1 > Sort Portion', 'keep--PID', 'keep--DOI']]\n",
        "currentACDupSubset.columns = ['DOI', 'PID', 'Object Type', 'Title', 'Parent PID to Keep', 'DOI to Map to']\n",
        "# currentACDupSubset\n",
        "\n",
        "allItemsMapped = pd.concat([currentACDupSubset, childofDupeParent], ignore_index=True)\n",
        "\n",
        "allItemsMapped = allItemsMapped.drop(columns=['Parent PID'])\n",
        "allItemsMapped = allItemsMapped.sort_values(by='PID')\n",
        "allItemsMapped.reset_index(inplace=True, drop=True)\n",
        "allItemsMapped = allItemsMapped.replace(r'\\r', r'', regex=True)   # Remove irrelevant new lines from values\n",
        "\n",
        "# Reformat 'keep--DOI' as actual URL\n",
        "\n",
        "allItemsMapped['DOI to Map to'] = allItemsMapped['DOI to Map to'].str.replace(r'doi:', 'https://academiccommons.columbia.edu/doi/', regex=True)\n",
        "\n",
        "#allItemsMapped.to_csv('all_dupe_and_related.csv')\n",
        "allItemsMapped.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 21,
      "id": "8b91bfe9",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>Parent PID to Keep</th>\n",
              "      <th>Dupe Asset PID</th>\n",
              "      <th>Dupe Asset Filename</th>\n",
              "      <th>Keeping Asset PID</th>\n",
              "      <th>Keeping Asset Filename</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ac:115911</td>\n",
              "      <td>ac:107680</td>\n",
              "      <td>WP_222.pdf</td>\n",
              "      <td>ac:115909</td>\n",
              "      <td>econ_0304_12.pdf</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ac:123767</td>\n",
              "      <td>ac:188164</td>\n",
              "      <td>D._bajpai_indiamdgchallenge_2005_24.pdf</td>\n",
              "      <td>ac:123768</td>\n",
              "      <td>CGSDwp24.pdf</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ac:123800</td>\n",
              "      <td>ac:188185</td>\n",
              "      <td>4._bajpai_outsourcing_2004_16.pdf</td>\n",
              "      <td>ac:123801</td>\n",
              "      <td>CGSDwp16.pdf</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ac:124645</td>\n",
              "      <td>ac:127298</td>\n",
              "      <td>WP_287.pdf</td>\n",
              "      <td>ac:124646</td>\n",
              "      <td>WP_287.pdf</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ac:125623</td>\n",
              "      <td>ac:134701</td>\n",
              "      <td>DesigningSensorsInsider.pdf</td>\n",
              "      <td>ac:125624</td>\n",
              "      <td>insider_threats.pdf</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "  Parent PID to Keep Dupe Asset PID                      Dupe Asset Filename  \\\n",
              "0          ac:115911      ac:107680                               WP_222.pdf   \n",
              "1          ac:123767      ac:188164  D._bajpai_indiamdgchallenge_2005_24.pdf   \n",
              "2          ac:123800      ac:188185        4._bajpai_outsourcing_2004_16.pdf   \n",
              "3          ac:124645      ac:127298                               WP_287.pdf   \n",
              "4          ac:125623      ac:134701              DesigningSensorsInsider.pdf   \n",
              "\n",
              "  Keeping Asset PID Keeping Asset Filename  \n",
              "0         ac:115909       econ_0304_12.pdf  \n",
              "1         ac:123768           CGSDwp24.pdf  \n",
              "2         ac:123801           CGSDwp16.pdf  \n",
              "3         ac:124646             WP_287.pdf  \n",
              "4         ac:125624    insider_threats.pdf  "
            ]
          },
          "execution_count": 21,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Version 2 new function: child asset level mapping\n",
        "# Mapping duplicate child assets to its stay-published equivalent for Hyacinth to merge the stats before removal.\n",
        "\n",
        "# Create a list of parent items to be kept.\n",
        "keepParentPID = allItemsMapped['Parent PID to Keep'].unique()\n",
        "keepParentPIDlist = keepParentPID.tolist()\n",
        "\n",
        "# Collect all child assets of each keeping parent.\n",
        "childofKeepParent = trimmedACData[trimmedACData['Parent PID'].isin(keepParentPIDlist)]\n",
        "\n",
        "# Select duplicate and keeping assets that are published on Academic Commons, and are not \"mets.xml\"\n",
        "selectedDupeChild = allItemsMapped[allItemsMapped['Object Type'] == 'asset']\n",
        "selectedDupeChild = selectedDupeChild[selectedDupeChild['Published'] == 'academic_commons']\n",
        "selectedDupeChild = selectedDupeChild[selectedDupeChild['Filename'] != 'mets.xml']\n",
        "\n",
        "# childofKeepParent = childofKeepParent[childofKeepParent['Published'] == 'academic_commons']\n",
        "# childofKeepParent = childofKeepParent[childofKeepParent['Filename'] != 'mets.xml']\n",
        "\n",
        "# Mapping duplicate child assets to its stay-published equivalent by Parent PID\n",
        "allChildMapped = selectedDupeChild.merge(childofKeepParent, how='left', left_on=['Parent PID to Keep'], right_on=['Parent PID'], sort=True)\n",
        "\n",
        "# Cleaning up duplicated rows resulted from non-1-to-1 assets mapping.\n",
        "# Cases include 1 dupe to multiple keeping assets, multiple dupes to 1 keeping asset, or multiple dupes to multiple keeping assets.\n",
        "subsetDupes = allChildMapped.duplicated(['Parent PID'], keep=False)\n",
        "\n",
        "subsetDupesCleanUp = allChildMapped[subsetDupes]\n",
        "\n",
        "# Keeping dupes that match either the filename or the checksum of a keeping asset.\n",
        "# Hyacinth's stats only counts the most downloaded assets, the rest don't matter.\n",
        "# A loop hole here is in a multiple to multiple matching, the duplicate and keeping items might share no matching files.\n",
        "# Example: The main dupe asset misses a copyright page (different checksum), and the filename different from the keeping asset.\n",
        "# Will need another parameter (e.g. download count) to do such a matching.\n",
        "\n",
        "subsetDupesCleanUp = subsetDupesCleanUp.loc[((allChildMapped['Filename_x'] == allChildMapped['Filename_y']) | (allChildMapped['Checksum_x'] == allChildMapped['Checksum_y']))]\n",
        "\n",
        "# After cleaning up duplicate matches, rejoin list of parents that have 1 child asset only.\n",
        "DupesCleaned = pd.concat([allChildMapped[~subsetDupes], subsetDupesCleanUp])\n",
        "\n",
        "# Further remove duplicates that have identical assets mapped to the same keeping asset (which still left multiple rows from last step).\n",
        "DupesCleaned.drop_duplicates(['Parent PID'], keep='first', inplace=True, ignore_index=True)\n",
        "\n",
        "# Prepare output by resetting index, selecting and renaming columns.\n",
        "DupesCleaned.sort_values(by=['Parent PID', 'PID_x'], inplace=True)\n",
        "DupesCleaned.reset_index(inplace=True, drop=True)\n",
        "DupesCleaned = DupesCleaned[['Parent PID', 'PID_x', 'Filename_x', 'PID_y', 'Filename_y']]\n",
        "DupesCleaned.columns = ['Parent PID to Keep', 'Dupe Asset PID', 'Dupe Asset Filename', 'Keeping Asset PID', 'Keeping Asset Filename']\n",
        "\n",
        "DupesCleaned.to_csv('Hyacinth_Child_Level_Mapping.csv')\n",
        "\n",
        "DupesCleaned.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 22,
      "id": "fe84e345",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>PID</th>\n",
              "      <th>DOI</th>\n",
              "      <th>Title</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>Parent PID to Keep</th>\n",
              "      <th>DOI to Map to</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>ac:05qfttdz2r</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>asset</td>\n",
              "      <td>ac:x3ffbg79jr</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>ac:08kprr4xht</td>\n",
              "      <td>doi:10.7916/D81G1ZR0</td>\n",
              "      <td>Making Cognitive Latent Variables Manifest: Di...</td>\n",
              "      <td>item</td>\n",
              "      <td>ac:193300</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>ac:08kprr4xkn</td>\n",
              "      <td>doi:10.7916/d8-ppp7-1s08</td>\n",
              "      <td>!DNP DUPLICATE “Addis Ababa Bete (Home)”: Cont...</td>\n",
              "      <td>item</td>\n",
              "      <td>ac:jq2bvq83gg</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>ac:0gb5mkkwkn</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>Opening Ceremony 2007 Photo only.pdf</td>\n",
              "      <td>asset</td>\n",
              "      <td>ac:h70rxwdbwx</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>ac:0gb5mkkwmb</td>\n",
              "      <td>&lt;NA&gt;</td>\n",
              "      <td>mets.xml</td>\n",
              "      <td>asset</td>\n",
              "      <td>ac:05qfttdz2s</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "             PID                       DOI  \\\n",
              "0  ac:05qfttdz2r                      <NA>   \n",
              "1  ac:08kprr4xht      doi:10.7916/D81G1ZR0   \n",
              "2  ac:08kprr4xkn  doi:10.7916/d8-ppp7-1s08   \n",
              "3  ac:0gb5mkkwkn                      <NA>   \n",
              "4  ac:0gb5mkkwmb                      <NA>   \n",
              "\n",
              "                                               Title Object Type  \\\n",
              "0                                           mets.xml       asset   \n",
              "1  Making Cognitive Latent Variables Manifest: Di...        item   \n",
              "2  !DNP DUPLICATE “Addis Ababa Bete (Home)”: Cont...        item   \n",
              "3               Opening Ceremony 2007 Photo only.pdf       asset   \n",
              "4                                           mets.xml       asset   \n",
              "\n",
              "  Parent PID to Keep                                      DOI to Map to  \n",
              "0      ac:x3ffbg79jr  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "1          ac:193300  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "2      ac:jq2bvq83gg  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "3      ac:h70rxwdbwx  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "4      ac:05qfttdz2s  https://academiccommons.columbia.edu/doi/10.79...  "
            ]
          },
          "execution_count": 22,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Sort and export the list to work on Hyacinth\n",
        "# The first column is PID\n",
        "\n",
        "hyacinthList = allItemsMapped[['PID', 'DOI', 'Title', 'Object Type', 'Parent PID to Keep', 'DOI to Map to']]\n",
        "\n",
        "# Export as CSV file\n",
        "hyacinthList.to_csv('all_dupe_Hyacinth.csv')\n",
        "\n",
        "hyacinthList.head()"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": 23,
      "id": "e4ed4f5b",
      "metadata": {},
      "outputs": [
        {
          "data": {
            "text/html": [
              "<div>\n",
              "<style scoped>\n",
              "    .dataframe tbody tr th:only-of-type {\n",
              "        vertical-align: middle;\n",
              "    }\n",
              "\n",
              "    .dataframe tbody tr th {\n",
              "        vertical-align: top;\n",
              "    }\n",
              "\n",
              "    .dataframe thead th {\n",
              "        text-align: right;\n",
              "    }\n",
              "</style>\n",
              "<table border=\"1\" class=\"dataframe\">\n",
              "  <thead>\n",
              "    <tr style=\"text-align: right;\">\n",
              "      <th></th>\n",
              "      <th>DOI</th>\n",
              "      <th>PID</th>\n",
              "      <th>Object Type</th>\n",
              "      <th>DOI to Map to</th>\n",
              "    </tr>\n",
              "  </thead>\n",
              "  <tbody>\n",
              "    <tr>\n",
              "      <th>0</th>\n",
              "      <td>doi:10.7916/D81G1ZR0</td>\n",
              "      <td>ac:08kprr4xht</td>\n",
              "      <td>item</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>1</th>\n",
              "      <td>doi:10.7916/d8-ppp7-1s08</td>\n",
              "      <td>ac:08kprr4xkn</td>\n",
              "      <td>item</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>2</th>\n",
              "      <td>doi:10.7916/D8VM4KQZ</td>\n",
              "      <td>ac:107680</td>\n",
              "      <td>asset</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>3</th>\n",
              "      <td>doi:10.7916/D8HQ46FR</td>\n",
              "      <td>ac:107682</td>\n",
              "      <td>item</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "    <tr>\n",
              "      <th>4</th>\n",
              "      <td>doi:10.7916/D8VQ39FQ</td>\n",
              "      <td>ac:110114</td>\n",
              "      <td>asset</td>\n",
              "      <td>https://academiccommons.columbia.edu/doi/10.79...</td>\n",
              "    </tr>\n",
              "  </tbody>\n",
              "</table>\n",
              "</div>"
            ],
            "text/plain": [
              "                        DOI            PID Object Type  \\\n",
              "0      doi:10.7916/D81G1ZR0  ac:08kprr4xht        item   \n",
              "1  doi:10.7916/d8-ppp7-1s08  ac:08kprr4xkn        item   \n",
              "2      doi:10.7916/D8VM4KQZ      ac:107680       asset   \n",
              "3      doi:10.7916/D8HQ46FR      ac:107682        item   \n",
              "4      doi:10.7916/D8VQ39FQ      ac:110114       asset   \n",
              "\n",
              "                                       DOI to Map to  \n",
              "0  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "1  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "2  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "3  https://academiccommons.columbia.edu/doi/10.79...  \n",
              "4  https://academiccommons.columbia.edu/doi/10.79...  "
            ]
          },
          "execution_count": 23,
          "metadata": {},
          "output_type": "execute_result"
        }
      ],
      "source": [
        "# Export the list to work on DataCite.\n",
        "# Those without a DOI will be removed.\n",
        "\n",
        "final_for_DataCite = allItemsMapped.dropna(subset=['DOI'])\n",
        "\n",
        "# Swapping the columns of PID and DOI\n",
        "final_for_DataCite = final_for_DataCite.reindex(columns=['DOI', 'PID', 'Object Type', 'DOI to Map to'])\n",
        "final_for_DataCite.reset_index(inplace=True, drop=True)\n",
        "\n",
        "# Export as CSV file\n",
        "final_for_DataCite.to_csv('all_dupe_Datacite.csv')\n",
        "\n",
        "final_for_DataCite.head()"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "provenance": []
    },
    "kernelspec": {
      "display_name": "Python 3.10.7 64-bit",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.10.7"
    },
    "vscode": {
      "interpreter": {
        "hash": "a0b92009e007ea74892ce71852617f5d08439bc7009f742fab30cbd093887492"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}