CRIM_Metadata_NB 2022.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Various Routines to Harvest CRIM Metadata from Production Server"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load the data from CRIM"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json\n",
    "from pandas.io.json import json_normalize\n",
    "import os.path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "\n",
    "\n",
    "# FOR PRODUCTION DJANGO\n",
    "# rel_data = requests.get('http://crimproject.org/data/relationships/').json()\n",
    "# obs_data = requests.get('https://crimproject.org/data/observations/').json()\n",
    "people = requests.get('https://crimproject.org/data/people/').json()\n",
    "pieces = requests.get('https://crimproject.org/data/pieces/').json()\n",
    "voices = requests.get('https://crimproject.org/data/voices/').json()\n",
    "masses = requests.get('https://crimproject.org/data/masses/').json()\n",
    "# phrases = requests.get('https://crimproject.org/data/phrases/').json()\n",
    "roles = requests.get('https://crimproject.org/data/roles/').json()\n",
    "\n",
    "# For Local DJANGO\n",
    "\n",
    "# people = requests.get('http://127.0.0.1:8000/data/people/').json()\n",
    "# pieces = requests.get('http://127.0.0.1:8000/data/pieces/').json()\n",
    "# voices = requests.get('http://127.0.0.1:8000/data/voices/').json()\n",
    "# masses = requests.get('http://127.0.0.1:8000/data/masses/').json()\n",
    "# # phrases = requests.get('http://127.0.0.1:8000/data/phrases/').json()\n",
    "# roles = requests.get('http://127.0.0.1:8000/data/roles/').json()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### First Masses"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'url': 'https://crimproject.org/data/people/CRIM_Person_0001/',\n",
       " 'person_id': 'CRIM_Person_0001',\n",
       " 'name': 'Pierre Colin',\n",
       " 'name_sort': 'Colin, Pierre',\n",
       " 'name_alternate_list': '',\n",
       " 'birth_date': '15xx',\n",
       " 'death_date': '15xx',\n",
       " 'active_date': '1538–1572',\n",
       " 'role_types': [{'url': 'https://crimproject.org/data/roletypes/composer/',\n",
       "   'role_type_id': 'composer',\n",
       "   'name': 'Composer'}]}"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# This is how the CRIM Django JSON looks for Masses:\n",
    "sample = people[0]\n",
    "sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# DF based on that JSON\n",
    "masses_from_json = pd.json_normalize(masses)\n",
    "# masses_from_json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### New Heading\n",
    "\n",
    "- This is our `notebook`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "mass_list = masses_from_json['mass_id'].to_list()\n",
    "# mass_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Here we load the newly-edited CSV of Mass metadata\n",
    "# masses_data = pd.read_csv('CRIM_Mass_Django_Data.csv') \n",
    "\n",
    "mass_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQJh5BnhhOMGsGko7oGXCpLBqL4uLoetx2NlIvWL1SPPZRITOERaUpaPB23X7N70DrIF7q9_O58eout/pub?output=csv'\n",
    "masses_data = pd.read_csv(mass_csv)\n",
    "masses_data.rename(columns={\"url\": \"crim_url\"}, inplace=True)\n",
    "# masses_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# masses_data['mass_id'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'mass_id'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[0;32m~/opt/anaconda3/envs/intervals_2/lib/python3.10/site-packages/pandas/core/indexes/base.py:3080\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3079\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[0;32m-> 3080\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engine\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   3081\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[0;32mpandas/_libs/index.pyx:70\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/index.pyx:101\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:4554\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "File \u001b[0;32mpandas/_libs/hashtable_class_helper.pxi:4562\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: 'mass_id'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Cell \u001b[0;32mIn[8], line 1\u001b[0m\n\u001b[0;32m----> 1\u001b[0m new_masses \u001b[38;5;241m=\u001b[39m masses_data[\u001b[38;5;241m~\u001b[39m\u001b[43mmasses_data\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mmass_id\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[38;5;241m.\u001b[39misin(mass_list)]\n\u001b[1;32m      2\u001b[0m sample \u001b[38;5;241m=\u001b[39m new_masses\u001b[38;5;241m.\u001b[39miloc[\u001b[38;5;241m0\u001b[39m]\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/intervals_2/lib/python3.10/site-packages/pandas/core/frame.py:3024\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   3022\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mnlevels \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[1;32m   3023\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_multilevel(key)\n\u001b[0;32m-> 3024\u001b[0m indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[1;32m   3025\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[1;32m   3026\u001b[0m     indexer \u001b[38;5;241m=\u001b[39m [indexer]\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/intervals_2/lib/python3.10/site-packages/pandas/core/indexes/base.py:3082\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[0;34m(self, key, method, tolerance)\u001b[0m\n\u001b[1;32m   3080\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n\u001b[1;32m   3081\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n\u001b[0;32m-> 3082\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[1;32m   3084\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tolerance \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[1;32m   3085\u001b[0m     tolerance \u001b[38;5;241m=\u001b[39m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_convert_tolerance(tolerance, np\u001b[38;5;241m.\u001b[39masarray(key))\n",
      "\u001b[0;31mKeyError\u001b[0m: 'mass_id'"
     ]
    }
   ],
   "source": [
    "new_masses = masses_data[~masses_data['mass_id'].isin(mass_list)]\n",
    "sample = new_masses.iloc[0]\n",
    "# new_masses.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_mass_metadata_from_df(sample):\n",
    "    \n",
    "    mass_id = sample['crim_id']\n",
    "    title = sample['title']\n",
    "    remarks = ''\n",
    "    mass_data = {\"model\": \"crim.crimmass\",       \n",
    "         'fields': {'mass_id': mass_id,\n",
    "         'title': title,\n",
    "         'genre': 'mass',\n",
    "         'remarks': remarks}}\n",
    "    return mass_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'model': 'crim.crimmass',\n",
       " 'fields': {'mass_id': nan, 'title': 'Kyrie', 'genre': 'mass', 'remarks': ''}}"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# sample transformation of one mass\n",
    "\n",
    "get_mass_metadata_from_df(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run for all rows\n",
    "\n",
    "masses_json = masses_data.apply(get_mass_metadata_from_df, axis=1).to_list()\n",
    "# masses_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>url</th>\n",
       "      <th>crim_id</th>\n",
       "      <th>title</th>\n",
       "      <th>comp_name</th>\n",
       "      <th>comp_id</th>\n",
       "      <th>comp_url</th>\n",
       "      <th>date</th>\n",
       "      <th>sort_date</th>\n",
       "      <th>min_voices</th>\n",
       "      <th>max_voices</th>\n",
       "      <th>remarks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [url, crim_id, title, comp_name, comp_id, comp_url, date, sort_date, min_voices, max_voices, remarks]\n",
       "Index: []"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "## all steps at once\n",
    "\n",
    "# masses = requests.get('http://127.0.0.1:8000/data/masses/').json() # pulls JSON from CRIM\n",
    "\n",
    "masses_df = pd.json_normalize(masses) # JSON as dataframe\n",
    "\n",
    "mass_list = masses_df['mass_id'].to_list() # get the ids of pieces already in CRIM as list\n",
    "\n",
    "masses_data = pd.read_csv('https://docs.google.com/spreadsheets/d/e/2PACX-1vQiEPbv0G3t3zAD9BmtrXGomZlCUdMRJitX6cNoQ7YYJzn1DicsymCP6WOJHiJBga3c0XVHq5lp3F8z/pub?output=csv') # get the full table from Freedman's Gsheet\n",
    "\n",
    "new_masses = masses_data[~masses_data['crim_id'].isin(mass_list)] # keep ONLY Freedman items NOT in the CRIM Data\n",
    "# \n",
    "\n",
    "new_masses_json = new_masses.apply(get_mass_metadata_from_df, axis=1).to_list() # apply the json building function \n",
    "\n",
    "with open(\"new_masses.json\", \"w\") as write_file:\n",
    "    json.dump(new_masses_json, write_file, indent=4) # writes the nice JSON file"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now pieces"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pieces_from_json = pd.json_normalize(pieces)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CRIM_Piece_Django_Data.csv\n",
    "\n",
    "piece_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQzG1TYOBUmbwMdRj8hf1h1UWfKjnTmAsy5c9Q4b5UftortFaKJPxJnRXphwH8HU9tLkkfQWUBJW781/pub?output=csv'\n",
    "\n",
    "pieces_data = pd.read_csv(piece_csv) \n",
    "pieces_data['genre_title'].str.lower()\n",
    "\n",
    "# masses_data.rename(columns={\"url\": \"crim_url\"}, inplace=True)\n",
    "pieces_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sample = pieces_data.iloc[280]\n",
    "sample"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_piece_metadata_from_df(sample):\n",
    "    piece_id = sample['crim_id']\n",
    "    mass_id = sample['crim_mass_id']\n",
    "    title = sample['title']\n",
    "    genre = sample['genre_title']\n",
    "    pdf = sample['pdf_link']\n",
    "    mei = sample['mei_link']\n",
    "    remarks = ''\n",
    "    if mass_id == 'none':\n",
    "        piece_data = {\"model\": \"crim.crimpiece\",       \n",
    "         'fields': {'piece_id': piece_id,\n",
    "         'title': title,\n",
    "        'genre': genre,\n",
    "        'mei_links': mei,\n",
    "        'pdf_links': pdf,    \n",
    "         'remarks': remarks}}\n",
    "    else:\n",
    "        piece_data = {\"model\": \"crim.crimpiece\",       \n",
    "         'fields': {'piece_id': piece_id,\n",
    "         'title': title,\n",
    "        'genre': genre,\n",
    "        'mei_links': mei,\n",
    "        'pdf_links': pdf,\n",
    "        'mass': mass_id,      \n",
    "         'remarks': remarks}}\n",
    "    return piece_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "get_piece_metadata_from_df(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "piece_list = pieces_from_json['piece_id'].to_list()\n",
    "new_pieces = pieces_data[~pieces_data['crim_id'].isin(piece_list)]\n",
    "sample = new_pieces.iloc[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "pieces_json = pieces_data.apply(get_piece_metadata_from_df, axis=1).to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"pieces.json\", \"w\") as write_file:\n",
    "    json.dump(pieces_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# All Steps at once\n",
    "pieces = requests.get('https://crimproject.org/data/pieces/').json()\n",
    "\n",
    "pieces_from_json = pd.json_normalize(pieces)\n",
    "piece_list = pieces_from_json['piece_id'].to_list()\n",
    "\n",
    "piece_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vQzG1TYOBUmbwMdRj8hf1h1UWfKjnTmAsy5c9Q4b5UftortFaKJPxJnRXphwH8HU9tLkkfQWUBJW781/pub?output=csv'\n",
    "\n",
    "pieces_data = pd.read_csv(piece_csv) \n",
    "pieces_data['genre_title'].str.lower()\n",
    "\n",
    "new_pieces = pieces_data[~pieces_data['crim_id'].isin(piece_list)]\n",
    "\n",
    "new_pieces_json = new_pieces.apply(get_piece_metadata_from_df, axis=1).to_list()\n",
    "\n",
    "with open(\"new_pieces.json\", \"w\") as write_file:\n",
    "    json.dump(new_pieces_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Now Voices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>url</th>\n",
       "      <th>order</th>\n",
       "      <th>original_name</th>\n",
       "      <th>regularized_name</th>\n",
       "      <th>clef</th>\n",
       "      <th>remarks</th>\n",
       "      <th>piece.url</th>\n",
       "      <th>piece.title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://crimproject.org/data/voices/CRIM_Mass_...</td>\n",
       "      <td>1</td>\n",
       "      <td>Superius</td>\n",
       "      <td>Superius</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>Kyrie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://crimproject.org/data/voices/CRIM_Mass_...</td>\n",
       "      <td>2</td>\n",
       "      <td>Contratenor</td>\n",
       "      <td>Contratenor</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>Kyrie</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://crimproject.org/data/voices/CRIM_Mass_...</td>\n",
       "      <td>3</td>\n",
       "      <td>Tenor</td>\n",
       "      <td>Tenor</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>Kyrie</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 url  order original_name  \\\n",
       "0  https://crimproject.org/data/voices/CRIM_Mass_...      1      Superius   \n",
       "1  https://crimproject.org/data/voices/CRIM_Mass_...      2   Contratenor   \n",
       "2  https://crimproject.org/data/voices/CRIM_Mass_...      3         Tenor   \n",
       "\n",
       "  regularized_name clef remarks  \\\n",
       "0         Superius                \n",
       "1      Contratenor                \n",
       "2            Tenor                \n",
       "\n",
       "                                           piece.url piece.title  \n",
       "0  https://crimproject.org/data/pieces/CRIM_Mass_...       Kyrie  \n",
       "1  https://crimproject.org/data/pieces/CRIM_Mass_...       Kyrie  \n",
       "2  https://crimproject.org/data/pieces/CRIM_Mass_...       Kyrie  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# voices = requests.get('http://127.0.0.1:8000/data/voices/').json()\n",
    "\n",
    "\n",
    "voices_from_json = pd.json_normalize(voices)\n",
    "voices_from_json.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "voices_from_json.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_s/4t2p1z2x0yxcv068dtqj31tw0000gq/T/ipykernel_57877/517080671.py:5: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  voices_from_json['nurl'] = voices_from_json[\"url\"].str.replace(\"https://crimproject.org/data/voices/\",\"\")\n"
     ]
    }
   ],
   "source": [
    "\n",
    "# this is for local\n",
    "# voices_from_json['nurl'] = voices_from_json[\"url\"].str.replace(\"http://127.0.0.1:8000/data/voices/\",\"\")\n",
    "\n",
    "# this for production\n",
    "voices_from_json['nurl'] = voices_from_json[\"url\"].str.replace(\"https://crimproject.org/data/voices/\",\"\")\n",
    "\n",
    "voice_list = voices_from_json['nurl'].str[:-1].to_list()\n",
    "# voice_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>piece_id</th>\n",
       "      <th>voice_id</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>order</th>\n",
       "      <th>original_name</th>\n",
       "      <th>in_edition</th>\n",
       "      <th>regularized_name</th>\n",
       "      <th>clef</th>\n",
       "      <th>remarks</th>\n",
       "      <th>piece.url</th>\n",
       "      <th>piece.title</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1318</th>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>CRIM_Model_0050(2)</td>\n",
       "      <td>(</td>\n",
       "      <td>)</td>\n",
       "      <td>2</td>\n",
       "      <td>Sextus</td>\n",
       "      <td>Sextus</td>\n",
       "      <td>Sextus</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1319</th>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>CRIM_Model_0050(3)</td>\n",
       "      <td>(</td>\n",
       "      <td>)</td>\n",
       "      <td>3</td>\n",
       "      <td>Altus</td>\n",
       "      <td>Altus</td>\n",
       "      <td>Altus</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1320</th>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>CRIM_Model_0050(4)</td>\n",
       "      <td>(</td>\n",
       "      <td>)</td>\n",
       "      <td>4</td>\n",
       "      <td>Tenor</td>\n",
       "      <td>Tenor</td>\n",
       "      <td>Tenor</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1321</th>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>CRIM_Model_0050(5)</td>\n",
       "      <td>(</td>\n",
       "      <td>)</td>\n",
       "      <td>5</td>\n",
       "      <td>Quintus</td>\n",
       "      <td>Quintus</td>\n",
       "      <td>Quintus</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1322</th>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>CRIM_Model_0050(6)</td>\n",
       "      <td>(</td>\n",
       "      <td>)</td>\n",
       "      <td>6</td>\n",
       "      <td>Bassus</td>\n",
       "      <td>Bassus</td>\n",
       "      <td>Bassus</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             piece_id            voice_id Unnamed: 2 Unnamed: 3  order  \\\n",
       "1318  CRIM_Model_0050  CRIM_Model_0050(2)          (          )      2   \n",
       "1319  CRIM_Model_0050  CRIM_Model_0050(3)          (          )      3   \n",
       "1320  CRIM_Model_0050  CRIM_Model_0050(4)          (          )      4   \n",
       "1321  CRIM_Model_0050  CRIM_Model_0050(5)          (          )      5   \n",
       "1322  CRIM_Model_0050  CRIM_Model_0050(6)          (          )      6   \n",
       "\n",
       "     original_name in_edition regularized_name  clef  remarks piece.url  \\\n",
       "1318        Sextus     Sextus           Sextus   NaN      NaN       NaN   \n",
       "1319         Altus      Altus            Altus   NaN      NaN       NaN   \n",
       "1320         Tenor      Tenor            Tenor   NaN      NaN       NaN   \n",
       "1321       Quintus    Quintus          Quintus   NaN      NaN       NaN   \n",
       "1322        Bassus     Bassus           Bassus   NaN      NaN       NaN   \n",
       "\n",
       "     piece.title  \n",
       "1318         NaN  \n",
       "1319         NaN  \n",
       "1320         NaN  \n",
       "1321         NaN  \n",
       "1322         NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "voices_data = pd.read_csv('https://docs.google.com/spreadsheets/d/e/2PACX-1vQiJKCXjLtTCdXZms94NFBywrD1r4fyJFNFZmmj54W7rlfI6ubf826EorBbGe6qUPOe9uutnGUdQiNa/pub?output=csv')\n",
    "voices_data.tail(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "voices_data['voice_id'].unique"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_voices_metadata_from_df(sample):\n",
    "    piece_id = sample['piece_id']\n",
    "    voice_id = sample['voice_id']\n",
    "    order = sample['order']\n",
    "    orig_name = sample['original_name']\n",
    "    reg_name = sample['regularized_name']\n",
    "    remarks = ''\n",
    "    if orig_name == 'NaN':\n",
    "        voice_data = {'model': 'crim.crimvoice',\n",
    "            'fields': {'piece_id': piece_id,\n",
    "             'voice_id': voice_id,\n",
    "            'order': order,\n",
    "            'original_name': orig_name,\n",
    "            'regularized_name': reg_name,\n",
    "             'remarks': remarks}}\n",
    "    else:\n",
    "        voice_data = {'model': 'crim.crimvoice',\n",
    "            'fields': {'piece_id': piece_id,\n",
    "             'voice_id': voice_id,\n",
    "            'order': order,\n",
    "            'original_name': '',\n",
    "            'regularized_name': reg_name,\n",
    "             'remarks': remarks}}\n",
    "    return voice_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'model': 'crim.crimvoice',\n",
       " 'fields': {'piece_id': 'CRIM_Mass_0044_4',\n",
       "  'voice_id': 'CRIM_Mass_0044_4(2)',\n",
       "  'order': 2,\n",
       "  'original_name': '',\n",
       "  'regularized_name': 'Cantus 2',\n",
       "  'remarks': ''}}"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample = voices_data.iloc[969]\n",
    "get_voices_metadata_from_df(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_voices = voices_data[~voices_data['voice_id'].isin(voice_list)]\n",
    "# nv = new_voices.head(10)\n",
    "# nv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_voices_json = new_voices.apply(get_voices_metadata_from_df, axis=1).to_list()\n",
    "# new_voices_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(\"new_voices.json\", \"w\") as write_file:\n",
    "    json.dump(new_voices_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# tests\n",
    "\n",
    "a = voices_data.groupby('piece_id')['order'].value_counts()\n",
    "b = voices_data.groupby('piece_id')['regularized_name'].value_counts()\n",
    "print(b)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_s/4t2p1z2x0yxcv068dtqj31tw0000gq/T/ipykernel_58198/2984708587.py:7: FutureWarning: The default value of regex will change from True to False in a future version.\n",
      "  voices_from_json['nurl'] = voices_from_json[\"url\"].str.replace(\"https://crimproject.org/data/voices/\",\"\")\n"
     ]
    },
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'to_list'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Input \u001b[0;32mIn [18]\u001b[0m, in \u001b[0;36m<cell line: 15>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     11\u001b[0m voices_data \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mread_csv(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mhttps://docs.google.com/spreadsheets/d/e/2PACX-1vQiJKCXjLtTCdXZms94NFBywrD1r4fyJFNFZmmj54W7rlfI6ubf826EorBbGe6qUPOe9uutnGUdQiNa/pub?output=csv\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[1;32m     13\u001b[0m new_voices \u001b[38;5;241m=\u001b[39m voices_data[\u001b[38;5;241m~\u001b[39mvoices_data[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mvoice_id\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39misin(voice_list)]\n\u001b[0;32m---> 15\u001b[0m new_voices_json \u001b[38;5;241m=\u001b[39m \u001b[43mnew_voices\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\u001b[43mget_voices_metadata_from_df\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_list\u001b[49m()\n\u001b[1;32m     17\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28mopen\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnew_voices.json\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;28;01mas\u001b[39;00m write_file:\n\u001b[1;32m     18\u001b[0m     json\u001b[38;5;241m.\u001b[39mdump(new_voices_json, write_file, indent\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m4\u001b[39m)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/intervals/lib/python3.9/site-packages/pandas/core/generic.py:5465\u001b[0m, in \u001b[0;36mNDFrame.__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   5463\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_info_axis\u001b[38;5;241m.\u001b[39m_can_hold_identifiers_and_holds_name(name):\n\u001b[1;32m   5464\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m[name]\n\u001b[0;32m-> 5465\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mobject\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[38;5;21;43m__getattribute__\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mname\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'to_list'"
     ]
    }
   ],
   "source": [
    "# all steps at once\n",
    "\n",
    "voices = requests.get('https://crimproject.org/data/voices/').json()\n",
    "\n",
    "voices_from_json = pd.json_normalize(voices)\n",
    "\n",
    "voices_from_json['nurl'] = voices_from_json[\"url\"].str.replace(\"https://crimproject.org/data/voices/\",\"\")\n",
    "\n",
    "voice_list = voices_from_json['nurl'].str[:-1].to_list()\n",
    "\n",
    "voices_data = pd.read_csv('https://docs.google.com/spreadsheets/d/e/2PACX-1vQiJKCXjLtTCdXZms94NFBywrD1r4fyJFNFZmmj54W7rlfI6ubf826EorBbGe6qUPOe9uutnGUdQiNa/pub?output=csv')\n",
    "\n",
    "new_voices = voices_data[~voices_data['voice_id'].isin(voice_list)]\n",
    "\n",
    "new_voices_json = new_voices.apply(get_voices_metadata_from_df, axis=1).to_list()\n",
    "\n",
    "with open(\"new_voices.json\", \"w\") as write_file:\n",
    "    json.dump(new_voices_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Roles\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['url', 'person', 'role_type', 'date', 'mass', 'piece', 'treatise',\n",
       "       'source', 'remarks'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "roles_from_json = pd.json_normalize(roles)\n",
    "roles_from_json.columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "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>Unnamed: 0</th>\n",
       "      <th>crim_id</th>\n",
       "      <th>mass_id</th>\n",
       "      <th>title</th>\n",
       "      <th>full_title</th>\n",
       "      <th>person_id</th>\n",
       "      <th>person</th>\n",
       "      <th>date</th>\n",
       "      <th>role_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>mass</td>\n",
       "      <td>CRIM_Mass_0001</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Confitemini: Kyrie</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Confitemini: Kyrie</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gloria</td>\n",
       "      <td>Missa Confitemini: Gloria</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Credo</td>\n",
       "      <td>Missa Confitemini: Credo</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sanctus</td>\n",
       "      <td>Missa Confitemini: Sanctus</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_5</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Agnus Dei</td>\n",
       "      <td>Missa Confitemini: Agnus Dei</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>mass</td>\n",
       "      <td>CRIM_Mass_0002</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Vidi speciosam: Kyrie</td>\n",
       "      <td>CRIM_Person_0003</td>\n",
       "      <td>Mathieu Sohier</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0002_1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Vidi speciosam: Kyrie</td>\n",
       "      <td>CRIM_Person_0003</td>\n",
       "      <td>Mathieu Sohier</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0002_2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gloria</td>\n",
       "      <td>Missa Vidi speciosam: Gloria</td>\n",
       "      <td>CRIM_Person_0003</td>\n",
       "      <td>Mathieu Sohier</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0002_3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Credo</td>\n",
       "      <td>Missa Vidi speciosam: Credo</td>\n",
       "      <td>CRIM_Person_0003</td>\n",
       "      <td>Mathieu Sohier</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          Unnamed: 0           crim_id  \\\n",
       "0  https://crimproject.org/data/pieces/CRIM_Mass_...              mass   \n",
       "1  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_1   \n",
       "2  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_2   \n",
       "3  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_3   \n",
       "4  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_4   \n",
       "5  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_5   \n",
       "6  https://crimproject.org/data/pieces/CRIM_Mass_...              mass   \n",
       "7  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0002_1   \n",
       "8  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0002_2   \n",
       "9  https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0002_3   \n",
       "\n",
       "          mass_id      title                    full_title         person_id  \\\n",
       "0  CRIM_Mass_0001      Kyrie      Missa Confitemini: Kyrie  CRIM_Person_0001   \n",
       "1             NaN      Kyrie      Missa Confitemini: Kyrie  CRIM_Person_0001   \n",
       "2             NaN     Gloria     Missa Confitemini: Gloria  CRIM_Person_0001   \n",
       "3             NaN      Credo      Missa Confitemini: Credo  CRIM_Person_0001   \n",
       "4             NaN    Sanctus    Missa Confitemini: Sanctus  CRIM_Person_0001   \n",
       "5             NaN  Agnus Dei  Missa Confitemini: Agnus Dei  CRIM_Person_0001   \n",
       "6  CRIM_Mass_0002      Kyrie   Missa Vidi speciosam: Kyrie  CRIM_Person_0003   \n",
       "7             NaN      Kyrie   Missa Vidi speciosam: Kyrie  CRIM_Person_0003   \n",
       "8             NaN     Gloria  Missa Vidi speciosam: Gloria  CRIM_Person_0003   \n",
       "9             NaN      Credo   Missa Vidi speciosam: Credo  CRIM_Person_0003   \n",
       "\n",
       "           person  date role_type  \n",
       "0    Pierre Colin  1556  composer  \n",
       "1    Pierre Colin  1556  composer  \n",
       "2    Pierre Colin  1556  composer  \n",
       "3    Pierre Colin  1556  composer  \n",
       "4    Pierre Colin  1556  composer  \n",
       "5    Pierre Colin  1556  composer  \n",
       "6  Mathieu Sohier  1556  composer  \n",
       "7  Mathieu Sohier  1556  composer  \n",
       "8  Mathieu Sohier  1556  composer  \n",
       "9  Mathieu Sohier  1556  composer  "
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "role_data_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRcv1DLLiUZ3e9hIvYL6c3bW6ELJ5OJHlEVsqZvk9yKaARj4mR-onmGMBo8vGo4L9nLzA_8YpjuPNhq/pub?output=csv'\n",
    "roles_data = pd.read_csv(role_data_csv) \n",
    "roles_data.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['Unnamed: 0', 'crim_id', 'mass_id', 'title', 'full_title', 'person_id',\n",
       "       'person', 'date', 'role_type'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "roles_data.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "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>Unnamed: 0</th>\n",
       "      <th>crim_id</th>\n",
       "      <th>mass_id</th>\n",
       "      <th>title</th>\n",
       "      <th>full_title</th>\n",
       "      <th>person_id</th>\n",
       "      <th>person</th>\n",
       "      <th>date</th>\n",
       "      <th>role_type</th>\n",
       "      <th>role_pairs</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>mass</td>\n",
       "      <td>CRIM_Mass_0001</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Confitemini: Kyrie</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "      <td>CRIM_Person_0001composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Kyrie</td>\n",
       "      <td>Missa Confitemini: Kyrie</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "      <td>CRIM_Person_0001composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Gloria</td>\n",
       "      <td>Missa Confitemini: Gloria</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "      <td>CRIM_Person_0001composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Credo</td>\n",
       "      <td>Missa Confitemini: Credo</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "      <td>CRIM_Person_0001composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://crimproject.org/data/pieces/CRIM_Mass_...</td>\n",
       "      <td>CRIM_Mass_0001_4</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sanctus</td>\n",
       "      <td>Missa Confitemini: Sanctus</td>\n",
       "      <td>CRIM_Person_0001</td>\n",
       "      <td>Pierre Colin</td>\n",
       "      <td>1556</td>\n",
       "      <td>composer</td>\n",
       "      <td>CRIM_Person_0001composer</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1009</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CRIM_Model_0046</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Alla dolc'ombra</td>\n",
       "      <td>Alla dolc'ombra</td>\n",
       "      <td>CRIM_Person_0049</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022</td>\n",
       "      <td>editor</td>\n",
       "      <td>CRIM_Person_0049editor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1010</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CRIM_Model_0047</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Nigra sum</td>\n",
       "      <td>Nigra sum</td>\n",
       "      <td>CRIM_Person_0070</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022</td>\n",
       "      <td>editor</td>\n",
       "      <td>CRIM_Person_0070editor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1011</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CRIM_Model_0048</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Sicut lilum</td>\n",
       "      <td>Sicut lilum</td>\n",
       "      <td>CRIM_Person_0031</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022</td>\n",
       "      <td>editor</td>\n",
       "      <td>CRIM_Person_0031editor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1012</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CRIM_Model_0049</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Entre vous filles</td>\n",
       "      <td>Entre vous filles</td>\n",
       "      <td>CRIM_Person_0071</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022</td>\n",
       "      <td>editor</td>\n",
       "      <td>CRIM_Person_0071editor</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1013</th>\n",
       "      <td>NaN</td>\n",
       "      <td>CRIM_Model_0050</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Domine Dominus noster</td>\n",
       "      <td>Domine Dominus noster</td>\n",
       "      <td>CRIM_Person_0041</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022</td>\n",
       "      <td>editor</td>\n",
       "      <td>CRIM_Person_0041editor</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1014 rows × 10 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                             Unnamed: 0           crim_id  \\\n",
       "0     https://crimproject.org/data/pieces/CRIM_Mass_...              mass   \n",
       "1     https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_1   \n",
       "2     https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_2   \n",
       "3     https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_3   \n",
       "4     https://crimproject.org/data/pieces/CRIM_Mass_...  CRIM_Mass_0001_4   \n",
       "...                                                 ...               ...   \n",
       "1009                                                NaN   CRIM_Model_0046   \n",
       "1010                                                NaN   CRIM_Model_0047   \n",
       "1011                                                NaN   CRIM_Model_0048   \n",
       "1012                                                NaN   CRIM_Model_0049   \n",
       "1013                                                NaN   CRIM_Model_0050   \n",
       "\n",
       "             mass_id                  title                  full_title  \\\n",
       "0     CRIM_Mass_0001                  Kyrie    Missa Confitemini: Kyrie   \n",
       "1                NaN                  Kyrie    Missa Confitemini: Kyrie   \n",
       "2                NaN                 Gloria   Missa Confitemini: Gloria   \n",
       "3                NaN                  Credo    Missa Confitemini: Credo   \n",
       "4                NaN                Sanctus  Missa Confitemini: Sanctus   \n",
       "...              ...                    ...                         ...   \n",
       "1009             NaN        Alla dolc'ombra             Alla dolc'ombra   \n",
       "1010             NaN              Nigra sum                   Nigra sum   \n",
       "1011             NaN            Sicut lilum                 Sicut lilum   \n",
       "1012             NaN      Entre vous filles           Entre vous filles   \n",
       "1013             NaN  Domine Dominus noster       Domine Dominus noster   \n",
       "\n",
       "             person_id        person  date role_type                role_pairs  \n",
       "0     CRIM_Person_0001  Pierre Colin  1556  composer  CRIM_Person_0001composer  \n",
       "1     CRIM_Person_0001  Pierre Colin  1556  composer  CRIM_Person_0001composer  \n",
       "2     CRIM_Person_0001  Pierre Colin  1556  composer  CRIM_Person_0001composer  \n",
       "3     CRIM_Person_0001  Pierre Colin  1556  composer  CRIM_Person_0001composer  \n",
       "4     CRIM_Person_0001  Pierre Colin  1556  composer  CRIM_Person_0001composer  \n",
       "...                ...           ...   ...       ...                       ...  \n",
       "1009  CRIM_Person_0049           NaN  2022    editor    CRIM_Person_0049editor  \n",
       "1010  CRIM_Person_0070           NaN  2022    editor    CRIM_Person_0070editor  \n",
       "1011  CRIM_Person_0031           NaN  2022    editor    CRIM_Person_0031editor  \n",
       "1012  CRIM_Person_0071           NaN  2022    editor    CRIM_Person_0071editor  \n",
       "1013  CRIM_Person_0041           NaN  2022    editor    CRIM_Person_0041editor  \n",
       "\n",
       "[1014 rows x 10 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# here we combine the person and role type to make one 'word' of both of them\n",
    "# this makes matching easier\n",
    "\n",
    "roles_data['role_pairs'] = roles_data['person_id'] + roles_data['role_type']\n",
    "roles_data['crim_id'].fillna(\"mass\", inplace=True)\n",
    "roles_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_role_metadata_from_df(sample):\n",
    "    mass = sample['mass_id']\n",
    "    piece = sample['crim_id']\n",
    "    role_type = sample['role_type']\n",
    "    date = sample['date']\n",
    "    person = sample['person_id']\n",
    "    remarks = ''\n",
    "    if piece == \"mass\":\n",
    "        \n",
    "        role_data = {'model': 'crim.crimrole',       \n",
    "         'fields': {\n",
    "             'mass': mass,\n",
    "         'role_type': role_type,\n",
    "        'date': date,\n",
    "        'person': person,\n",
    "         'remarks': remarks}}\n",
    "    else:\n",
    "        role_data = {'model': 'crim.crimrole',       \n",
    "         'fields': {'piece': piece,\n",
    "         'role_type': role_type,\n",
    "        'date': date,\n",
    "        'person': person,\n",
    "         'remarks': remarks}}\n",
    "\n",
    "    \n",
    "    return role_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'model': 'crim.crimrole',\n",
       " 'fields': {'mass': 'CRIM_Mass_0049',\n",
       "  'role_type': 'composer',\n",
       "  'date': '1584',\n",
       "  'person': 'CRIM_Person_0072',\n",
       "  'remarks': ''}}"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test with one example\n",
    "\n",
    "sample = roles_data.iloc[288]\n",
    "get_role_metadata_from_df(sample)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "968    {'model': 'crim.crimrole', 'fields': {'piece':...\n",
       "dtype: object"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# filter out the roles already in Django data and apply transformation \n",
    "\n",
    "new_roles = roles_data[~roles_data['role_pairs'].isin(role_list)]\n",
    "\n",
    "new_roles_json = new_roles.apply(get_role_metadata_from_df, axis=1)\n",
    "\n",
    "new_roles_json"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'roles_json' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [58]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28mopen\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mroles.json\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;28;01mas\u001b[39;00m write_file:\n\u001b[0;32m----> 2\u001b[0m     json\u001b[38;5;241m.\u001b[39mdump(\u001b[43mroles_json\u001b[49m, write_file, indent\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m4\u001b[39m)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'roles_json' is not defined"
     ]
    }
   ],
   "source": [
    "with open(\"roles.json\", \"w\") as write_file:\n",
    "    json.dump(roles_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'to_list'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "Input \u001b[0;32mIn [101]\u001b[0m, in \u001b[0;36m<cell line: 14>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     10\u001b[0m roles_data[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mrole_pairs\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m=\u001b[39m roles_data[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mperson_id\u001b[39m\u001b[38;5;124m'\u001b[39m] \u001b[38;5;241m+\u001b[39m roles_data[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mrole_type\u001b[39m\u001b[38;5;124m'\u001b[39m]\n\u001b[1;32m     12\u001b[0m new_roles \u001b[38;5;241m=\u001b[39m roles_data[\u001b[38;5;241m~\u001b[39mroles_data[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mrole_pairs\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39misin(role_list)]\n\u001b[0;32m---> 14\u001b[0m new_roles_json \u001b[38;5;241m=\u001b[39m \u001b[43mnew_roles\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mapply\u001b[49m\u001b[43m(\u001b[49m\u001b[43mget_role_metadata_from_df\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;241;43m1\u001b[39;49m\u001b[43m)\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_list\u001b[49m()\n\u001b[1;32m     16\u001b[0m \u001b[38;5;28;01mwith\u001b[39;00m \u001b[38;5;28mopen\u001b[39m(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mnew_roles.json\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mw\u001b[39m\u001b[38;5;124m\"\u001b[39m) \u001b[38;5;28;01mas\u001b[39;00m write_file:\n\u001b[1;32m     17\u001b[0m     json\u001b[38;5;241m.\u001b[39mdump(new_roles_json, write_file, indent\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m4\u001b[39m)\n",
      "File \u001b[0;32m~/opt/anaconda3/envs/intervals/lib/python3.9/site-packages/pandas/core/generic.py:5465\u001b[0m, in \u001b[0;36mNDFrame.__getattr__\u001b[0;34m(self, name)\u001b[0m\n\u001b[1;32m   5463\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_info_axis\u001b[38;5;241m.\u001b[39m_can_hold_identifiers_and_holds_name(name):\n\u001b[1;32m   5464\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m[name]\n\u001b[0;32m-> 5465\u001b[0m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mobject\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[38;5;21;43m__getattribute__\u001b[39;49m\u001b[43m(\u001b[49m\u001b[38;5;28;43mself\u001b[39;49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mname\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'to_list'"
     ]
    }
   ],
   "source": [
    "# All Steps at once\n",
    "roles = requests.get('https://crimproject.org/data/roles/').json()\n",
    "roles_from_json = pd.json_normalize(roles)\n",
    "roles_from_json['role_pairs'] = roles_from_json['person'] + roles_from_json['role_type']\n",
    "role_list = roles_from_json['role_pairs'].to_list()\n",
    "\n",
    "role_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vRcv1DLLiUZ3e9hIvYL6c3bW6ELJ5OJHlEVsqZvk9yKaARj4mR-onmGMBo8vGo4L9nLzA_8YpjuPNhq/pub?output=csv'\n",
    "\n",
    "roles_data = pd.read_csv(role_csv) \n",
    "roles_data['role_pairs'] = roles_data['person_id'] + roles_data['role_type']\n",
    "\n",
    "new_roles = roles_data[~roles_data['role_pairs'].isin(role_list)]\n",
    "\n",
    "new_roles_json = new_roles.apply(get_role_metadata_from_df, axis=1).to_list()\n",
    "\n",
    "with open(\"new_roles.json\", \"w\") as write_file:\n",
    "    json.dump(new_roles_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Phrases"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### People\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "people_from_json = pd.json_normalize(people)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "people_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vR2MuAgy8ONgJLYoyovrS961eJCwMeXiq3GcNzSQTiNLjSuEbsfll1J2se6cjN9bviI3D07tiXIrgMO/pub?output=csv'\n",
    "\n",
    "people_data = pd.read_csv(people_csv)\n",
    "people_data.columns\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def get_person_metadata_from_df(sample):\n",
    "    person_id = sample['person_id']\n",
    "    name = sample['crim_full_name']\n",
    "    birth_date = sample['birth_date']\n",
    "    death_date = sample['death_date']\n",
    "    active_date = sample['active_date']\n",
    "    remarks = sample['viaf'],\n",
    "    person_data = {'model': 'crim.crimperson',       \n",
    "         'fields': {'person_id': person_id,\n",
    "         'name_sort': name,\n",
    "        'birth_date': birth_date,\n",
    "        'death_date': death_date,\n",
    "        'active_date': active_date,\n",
    "         'remarks': remarks}}\n",
    "    return person_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# run all steps\n",
    "\n",
    "people = requests.get('https://crimproject.org/data/people/').json()\n",
    "\n",
    "people_from_json = pd.json_normalize(people)\n",
    "people_from_json.columns\n",
    "#roles_from_json['role_pairs'] = roles_from_json['person'] + roles_from_json['role_type']\n",
    "people_list = people_from_json['person_id'].to_list()\n",
    "\n",
    "people_csv = 'https://docs.google.com/spreadsheets/d/e/2PACX-1vR2MuAgy8ONgJLYoyovrS961eJCwMeXiq3GcNzSQTiNLjSuEbsfll1J2se6cjN9bviI3D07tiXIrgMO/pub?output=csv'\n",
    "\n",
    "people_data = pd.read_csv(people_csv) \n",
    "\n",
    "\n",
    "new_people = people_data[~people_data['person_id'].isin(people_list)]\n",
    "\n",
    "new_people_json = new_people.apply(get_person_metadata_from_df, axis=1).to_list()\n",
    "\n",
    "with open(\"new_people.json\", \"w\") as write_file:\n",
    "    json.dump(new_people_json, write_file, indent=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Useful Code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['observer.name'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "mask = df['observer.name'].str.contains(\"Lorenz\")\n",
    "lorenz_list = df[mask]\n",
    "lorenz_list['piece.full_title'].value_counts()\n",
    "# fiala_list = df[mask]\n",
    "# fiala_list['piece.full_title'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "curated_obs['piece_id'].str.contains(\"Model_0013\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df[\"measures\"] = df['ema'].str.extract('(\\d+-\\d+)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}