053 wikidata čištění 2.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "5f9e73ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "842c4314",
   "metadata": {},
   "outputs": [],
   "source": [
    "from ast import literal_eval"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c6320fe3",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_json(os.path.join('wikidata','wikidata_raw.json'))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bedfb773",
   "metadata": {},
   "source": [
    "## Statistiky"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "ca8be151",
   "metadata": {},
   "outputs": [],
   "source": [
    "sloupce = df.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "6130b6a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:5: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
      "C:\\Users\\micha\\AppData\\Local\\Temp\\ipykernel_23164\\54388433.py:6: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n"
     ]
    },
    {
     "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>sloupec</th>\n",
       "      <th>vyplneno</th>\n",
       "      <th>nevyplneno</th>\n",
       "      <th>ruznych_hodnot</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>interpret_hlavni</td>\n",
       "      <td>1615</td>\n",
       "      <td>0</td>\n",
       "      <td>1615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>id</td>\n",
       "      <td>1615</td>\n",
       "      <td>0</td>\n",
       "      <td>1593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>stazeno</td>\n",
       "      <td>1615</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>nazev_wiki</td>\n",
       "      <td>1608</td>\n",
       "      <td>7</td>\n",
       "      <td>1586</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>velikost</td>\n",
       "      <td>1598</td>\n",
       "      <td>17</td>\n",
       "      <td>125</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>popis_en</td>\n",
       "      <td>1583</td>\n",
       "      <td>32</td>\n",
       "      <td>1034</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>misto_puvodu</td>\n",
       "      <td>1213</td>\n",
       "      <td>402</td>\n",
       "      <td>685</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>zeme_puvodu</td>\n",
       "      <td>1211</td>\n",
       "      <td>404</td>\n",
       "      <td>72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>zanry</td>\n",
       "      <td>3308</td>\n",
       "      <td>492</td>\n",
       "      <td>388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>zacatek_pusobeni</td>\n",
       "      <td>1114</td>\n",
       "      <td>501</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>popis_cs</td>\n",
       "      <td>1063</td>\n",
       "      <td>552</td>\n",
       "      <td>565</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>gender</td>\n",
       "      <td>952</td>\n",
       "      <td>663</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>spotify_id</td>\n",
       "      <td>947</td>\n",
       "      <td>668</td>\n",
       "      <td>933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>datum_narozeni</td>\n",
       "      <td>917</td>\n",
       "      <td>698</td>\n",
       "      <td>873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>obcanstvi</td>\n",
       "      <td>971</td>\n",
       "      <td>724</td>\n",
       "      <td>73</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>nastroje</td>\n",
       "      <td>1349</td>\n",
       "      <td>908</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>oceneni</td>\n",
       "      <td>2140</td>\n",
       "      <td>1198</td>\n",
       "      <td>732</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>zalozeni</td>\n",
       "      <td>386</td>\n",
       "      <td>1229</td>\n",
       "      <td>123</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>hlas</td>\n",
       "      <td>211</td>\n",
       "      <td>1414</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>datum_umrti</td>\n",
       "      <td>70</td>\n",
       "      <td>1545</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>nabozenstvi</td>\n",
       "      <td>73</td>\n",
       "      <td>1550</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>konec_pusobeni</td>\n",
       "      <td>62</td>\n",
       "      <td>1553</td>\n",
       "      <td>29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>orientace</td>\n",
       "      <td>59</td>\n",
       "      <td>1561</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>etnicita</td>\n",
       "      <td>61</td>\n",
       "      <td>1568</td>\n",
       "      <td>36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>zanik</td>\n",
       "      <td>15</td>\n",
       "      <td>1600</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>spotify_odberatele</td>\n",
       "      <td>11</td>\n",
       "      <td>1604</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               sloupec  vyplneno  nevyplneno  ruznych_hodnot\n",
       "0     interpret_hlavni      1615           0            1615\n",
       "1                   id      1615           0            1593\n",
       "2              stazeno      1615           0               1\n",
       "3           nazev_wiki      1608           7            1586\n",
       "6             velikost      1598          17             125\n",
       "4             popis_en      1583          32            1034\n",
       "15        misto_puvodu      1213         402             685\n",
       "16         zeme_puvodu      1211         404              72\n",
       "7                zanry      3308         492             388\n",
       "10    zacatek_pusobeni      1114         501              84\n",
       "5             popis_cs      1063         552             565\n",
       "14              gender       952         663               8\n",
       "24          spotify_id       947         668             933\n",
       "8       datum_narozeni       917         698             873\n",
       "17           obcanstvi       971         724              73\n",
       "20            nastroje      1349         908              88\n",
       "21             oceneni      2140        1198             732\n",
       "12            zalozeni       386        1229             123\n",
       "19                hlas       211        1414              20\n",
       "9          datum_umrti        70        1545              67\n",
       "22         nabozenstvi        73        1550              28\n",
       "11      konec_pusobeni        62        1553              29\n",
       "23           orientace        59        1561               9\n",
       "18            etnicita        61        1568              36\n",
       "13               zanik        15        1600              13\n",
       "25  spotify_odberatele        11        1604              11"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "statistiky = []\n",
    "for s in sloupce:\n",
    "    statka = {}\n",
    "    statka['sloupec'] = s\n",
    "    statka['vyplneno'] = len(df.explode(s)[~df[s].isnull()])\n",
    "    statka['nevyplneno'] = len(df.explode(s)[df[s].isnull()])\n",
    "    statka['ruznych_hodnot'] = df.explode(s)[s].nunique()\n",
    "    statistiky.append(statka)\n",
    "statistiky = pd.DataFrame(statistiky)\n",
    "statistiky.sort_values(by='nevyplneno')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe42a602",
   "metadata": {},
   "source": [
    "## Čištění"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "c8944913",
   "metadata": {},
   "outputs": [],
   "source": [
    "ceskove = df[(df['zeme_puvodu'].isnull()) & (df['popis_cs'].str.contains('česk'))]['interpret_hlavni'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "594569ea",
   "metadata": {},
   "outputs": [],
   "source": [
    "slovaci = ['Kontrafakt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "e4a809e0",
   "metadata": {},
   "outputs": [],
   "source": [
    "puvod_nejisty = ['Welshly Arms',\n",
    " 'Bodyrockers',\n",
    " 'Junge Junge',\n",
    " 'Karin Ann',\n",
    " 'Flipsyde',\n",
    " 'The Faim',\n",
    " 'D.H.T.',\n",
    " 'Banners',\n",
    " 'Bodybangers',\n",
    " 'Justin Jesso',\n",
    " 'Jc Stewart',\n",
    " 'Desmod',\n",
    " 'Novaspace',\n",
    " 'Djane Housekat',\n",
    " 'Vinylshakerz',\n",
    " 'Pascal Letoublon',\n",
    " 'Rat City',\n",
    " 'Yall',\n",
    " 'Younotus',\n",
    " 'Spankers',\n",
    " 'Sam And The Womp',\n",
    " 'Thxsomch',\n",
    " 'Hi-Tack',\n",
    " 'Gold 1',\n",
    " 'Beatfreakz',\n",
    " 'Dony',\n",
    " 'Dimitri Vegas',\n",
    " 'Fragma',\n",
    " 'Klangkarussell',\n",
    " 'Aloha From Hell',\n",
    " 'Karmah',\n",
    " 'Aquagen',\n",
    " 'Cian Ducrot',\n",
    " 'Renforshort',\n",
    " 'Dave Ramone',\n",
    " 'Cover Drive',\n",
    " 'Beach Weather',\n",
    " 'Urban Cone',\n",
    " 'Avantasia',\n",
    " 'Ari Abdul',\n",
    " 'Bpm',\n",
    " 'Surfaces',\n",
    " 'Boston Bun',\n",
    " 'Switch Disco',\n",
    " 'Hosh',\n",
    " 'Jodie Harsh',\n",
    " 'Public',\n",
    " 'Ocean Park Standoff',\n",
    " 'Nicky Your',\n",
    " 'Nitty',\n",
    " 'Fiedlerski',\n",
    " 'Shouse',\n",
    " 'Clou',\n",
    " 'Momo',\n",
    " 'Gladiator',\n",
    " 'James Harries',\n",
    " 'Fellas',\n",
    " 'Five O´Clock Tea',\n",
    " 'Rebels',\n",
    " 'Hematom',\n",
    " 'Highland',\n",
    " 'Atmo Music']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "d9fcda82",
   "metadata": {},
   "outputs": [],
   "source": [
    "for x in df[(df['zeme_puvodu'].isnull())]['interpret_hlavni'].to_list():\n",
    "    if x not in puvod_nejisty:\n",
    "        ceskove.append(x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "137dc3fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['interpret_hlavni'].isin(ceskove),\"zeme_puvodu\"]=\"Česko\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "520c4aa5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['interpret_hlavni'].isin(slovaci),\"zeme_puvodu\"]=\"Slovensko\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "1779ae4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Římská říše',\"zeme_puvodu\"]=\"Spojené království\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "0b0afeac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Clou',\n",
       " 'James Harries',\n",
       " 'Shouse',\n",
       " 'Momo',\n",
       " 'Rebels',\n",
       " 'Fellas',\n",
       " 'Highland',\n",
       " 'Welshly Arms',\n",
       " 'Junge Junge',\n",
       " 'Bodyrockers',\n",
       " 'Flipsyde',\n",
       " 'Karin Ann',\n",
       " 'Banners',\n",
       " 'The Faim',\n",
       " 'Djane Housekat',\n",
       " 'D.H.T.',\n",
       " 'Novaspace',\n",
       " 'Jc Stewart',\n",
       " 'Justin Jesso',\n",
       " 'Bodybangers',\n",
       " 'Dimitri Vegas',\n",
       " 'Desmod',\n",
       " 'Karmah',\n",
       " 'Pascal Letoublon',\n",
       " 'Rat City',\n",
       " 'Klangkarussell',\n",
       " 'Beatfreakz',\n",
       " 'Dony',\n",
       " 'Beach Weather',\n",
       " 'Fragma',\n",
       " 'Vinylshakerz',\n",
       " 'Thxsomch',\n",
       " 'Hi-Tack',\n",
       " 'Sam And The Womp',\n",
       " 'Aloha From Hell',\n",
       " 'Yall',\n",
       " 'Gold 1',\n",
       " 'Aquagen',\n",
       " 'Spankers',\n",
       " 'Younotus',\n",
       " 'Urban Cone',\n",
       " 'Renforshort',\n",
       " 'Switch Disco',\n",
       " 'Surfaces',\n",
       " 'Public',\n",
       " 'Ari Abdul',\n",
       " 'Avantasia',\n",
       " 'Cian Ducrot',\n",
       " 'Bpm',\n",
       " 'Boston Bun',\n",
       " 'Nitty',\n",
       " 'Nicky Your',\n",
       " 'Ocean Park Standoff',\n",
       " 'Hosh',\n",
       " 'Jodie Harsh',\n",
       " 'Fiedlerski',\n",
       " 'Gladiator',\n",
       " 'Five O´Clock Tea',\n",
       " 'Hematom',\n",
       " 'Atmo Music']"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[(df['zeme_puvodu'].isnull())]['interpret_hlavni'].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "168381bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "smazat_narozeni = ['Gold 1','Momo','Shaun Baker']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "4ad064c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['interpret_hlavni'].isin(smazat_narozeni),\"datum_narozeni\"]=None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "410751af",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "1592d7bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "def ctyrcislo(x):\n",
    "    try:\n",
    "        return re.search('\\d\\d\\d\\d',x).group()\n",
    "    except:\n",
    "        return None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "1cffe1a1",
   "metadata": {},
   "outputs": [],
   "source": [
    "data = ['konec_pusobeni','zacatek_pusobeni','zalozeni','datum_narozeni','datum_umrti']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "43ebdfbe",
   "metadata": {},
   "outputs": [],
   "source": [
    "for d in data:\n",
    "    df[d] = df[d].apply(lambda x: ctyrcislo(str(x)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d63381cc",
   "metadata": {},
   "source": [
    "## Kontrola x žebříčky"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "c93f9579",
   "metadata": {},
   "outputs": [],
   "source": [
    "cz = pd.read_csv(os.path.join(\"data\",\"CZ_RADIO_TOP_100_tydenni.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "c6a207a8",
   "metadata": {},
   "outputs": [],
   "source": [
    "cz = cz.merge(df, on='interpret_hlavni', how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "1347d6c6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "interpret_hlavni\n",
       "Airfare                    99\n",
       "Light                      89\n",
       "Timmy White                85\n",
       "Nea                        79\n",
       "Robin Mood                 76\n",
       "Za100Let                   75\n",
       "Team                       73\n",
       "Le Monde                   65\n",
       "Hodiny                     64\n",
       "Joy Vance                  59\n",
       "Matej Smutný               57\n",
       "K´ Naan                    55\n",
       "Popcorn Drama              55\n",
       "Guru Josh Project          54\n",
       "Mcclarnon Liz              54\n",
       "Lenka                      53\n",
       "Rahman A.R.                52\n",
       "Sparks Jordin              52\n",
       "2Choice                    50\n",
       "Blige Mary J.              50\n",
       "Avidan Asaf                49\n",
       "Digital Fever              49\n",
       "Noel Sak                   49\n",
       "Clockclock                 48\n",
       "Linakaro                   47\n",
       "Rodan                      45\n",
       "Teló Michel                43\n",
       "Buena Mutya                40\n",
       "The Carsleepers            40\n",
       "Mena Maria                 39\n",
       "Pam Rabbit                 38\n",
       "Samson Sidney              38\n",
       "Forks Jasper               36\n",
       "Rowland Kelly              36\n",
       "Tribbs                     36\n",
       "Óčko Allstars              36\n",
       "Gabriela                   35\n",
       "Tisdale Ashley             34\n",
       "Andrews Michael            33\n",
       "Declan J Donovan           33\n",
       "Nykki                      33\n",
       "Elly                       32\n",
       "Ingrosso Sebastian         32\n",
       "Andy Cermak                31\n",
       "Avener                     31\n",
       "Cab Cris                   31\n",
       "Gym Class Heros            31\n",
       "One Night Band             31\n",
       "Deaf Heart                 30\n",
       "Lota Jana                  30\n",
       "Mccoy Travie               30\n",
       "Naim Yael                  30\n",
       "Hansard Glen               29\n",
       "Sinclair Didier            29\n",
       "Strumbellas                29\n",
       "Felix  Jaehn               28\n",
       "Pokora Matt                28\n",
       "Webbe Simon                28\n",
       "Alžběta                    27\n",
       "Avicii Vs. Nicky Romero    26\n",
       "dtype: int64"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cz[cz['velikost'].isnull()].groupby('interpret_hlavni').size().nlargest(60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "412eaca0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "interpret_hlavni\n",
       "Welshly Arms         170\n",
       "Flipsyde             163\n",
       "Atmo Music           118\n",
       "Junge Junge          107\n",
       "Jc Stewart           102\n",
       "Clou                 101\n",
       "Airfare               99\n",
       "D.H.T.                93\n",
       "Light                 89\n",
       "Novaspace             85\n",
       "Timmy White           85\n",
       "Karmah                83\n",
       "Robin Mood            76\n",
       "Za100Let              75\n",
       "Banners               72\n",
       "Shouse                72\n",
       "Le Monde              65\n",
       "Hodiny                64\n",
       "Joy Vance             59\n",
       "James Harries         58\n",
       "Matej Smutný          57\n",
       "K´ Naan               55\n",
       "Popcorn Drama         55\n",
       "Guru Josh Project     54\n",
       "Mcclarnon Liz         54\n",
       "Lenka                 53\n",
       "Rahman A.R.           52\n",
       "Sparks Jordin         52\n",
       "2Choice               50\n",
       "Blige Mary J.         50\n",
       "Avidan Asaf           49\n",
       "Digital Fever         49\n",
       "Noel Sak              49\n",
       "Clockclock            48\n",
       "Fiedlerski            48\n",
       "Linakaro              47\n",
       "The Faim              47\n",
       "Rodan                 45\n",
       "Younotus              45\n",
       "Teló Michel           43\n",
       "Buena Mutya           40\n",
       "The Carsleepers       40\n",
       "Hi-Tack               39\n",
       "Mena Maria            39\n",
       "Pam Rabbit            38\n",
       "Samson Sidney         38\n",
       "Bodyrockers           36\n",
       "Forks Jasper          36\n",
       "Karin Ann             36\n",
       "Rowland Kelly         36\n",
       "Tribbs                36\n",
       "Óčko Allstars         36\n",
       "Gabriela              35\n",
       "Beatfreakz            34\n",
       "Cian Ducrot           34\n",
       "Tisdale Ashley        34\n",
       "Andrews Michael       33\n",
       "Declan J Donovan      33\n",
       "Nykki                 33\n",
       "Elly                  32\n",
       "dtype: int64"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cz[(cz['zeme_puvodu'].isnull())].groupby('interpret_hlavni').size().nlargest(60)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "528a6a82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "interpret_hlavni\n",
       "Shaun Baker         227\n",
       "Banners              72\n",
       "Fiedlerski           48\n",
       "Billy Mack           29\n",
       "Lexter               25\n",
       "Justin Jesso         23\n",
       "Clare Alex           20\n",
       "Robert Grace         15\n",
       "Thomas Rob           13\n",
       "Lily Moore           11\n",
       "Roch Jean            10\n",
       "Kelvin Jones          9\n",
       "Boston Bun            8\n",
       "Gold 1                8\n",
       "Tom Ferry             7\n",
       "Ari Abdul             5\n",
       "Clayton Adam          5\n",
       "Luude                 3\n",
       "Holly Rae             2\n",
       "Ca$Hanova Bulhar      1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cz[(cz['velikost'] == 'člověk') & (cz['datum_narozeni'].isnull())].groupby('interpret_hlavni').size().nlargest(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "7b3dd14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "b100 = pd.read_csv(os.path.join(\"data_b100\",\"b100.csv\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "id": "3ae68242",
   "metadata": {},
   "outputs": [],
   "source": [
    "b100 = b100.merge(df, on='interpret_hlavni', how = 'outer')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "b7ae0e82",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "interpret_hlavni\n",
       "Tim McGraw                    828\n",
       "Elvis Presley                 818\n",
       "Keith Urban                   717\n",
       "Rod Stewart                   682\n",
       "Stevie Wonder                 665\n",
       "Jason Aldean                  656\n",
       "Whitney Houston               654\n",
       "Aretha Franklin               630\n",
       "Rascal Flatts                 627\n",
       "R. Kelly                      625\n",
       "P!nk                          620\n",
       "Brad Paisley                  618\n",
       "Blake Shelton                 611\n",
       "The Beatles                   608\n",
       "Chicago                       607\n",
       "Diana Ross                    597\n",
       "Billy Joel                    595\n",
       "The Rolling Stones            585\n",
       "Carrie Underwood              565\n",
       "George Strait                 553\n",
       "Neil Diamond                  553\n",
       "Daryl Hall John Oates         540\n",
       "Mary J. Blige                 537\n",
       "The Beach Boys                531\n",
       "Toby Keith                    531\n",
       "Marvin Gaye                   526\n",
       "Bee Gees                      516\n",
       "Dionne Warwick                491\n",
       "The Temptations               476\n",
       "Dierks Bentley                475\n",
       "Kool                          469\n",
       "Smokey Robinson               445\n",
       "DJ Khaled                     430\n",
       "Janet Jackson                 429\n",
       "Olivia Newton-John            427\n",
       "Heart                         426\n",
       "Cher                          420\n",
       "Donna Summer                  418\n",
       "Gladys Knight And The Pips    415\n",
       "Alan Jackson                  400\n",
       "The 4 Seasons                 390\n",
       "Barry Manilow                 389\n",
       "The Pointer Sisters           387\n",
       "Lil Baby                      386\n",
       "James Brown                   375\n",
       "Lady Antebellum               374\n",
       "Bobby Vinton                  369\n",
       "Gloria Estefan                369\n",
       "The Isley Brothers            368\n",
       "Four Tops                     367\n",
       "Thomas Rhett                  362\n",
       "Brooks                        360\n",
       "Bob Seger                     359\n",
       "Fleetwood Mac                 355\n",
       "Commodores                    354\n",
       "Journey                       352\n",
       "Faith Hill                    351\n",
       "Linda Ronstadt                342\n",
       "Genesis                       341\n",
       "Lionel Richie                 340\n",
       "dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b100[b100['velikost'].isnull()].groupby('interpret_hlavni').size().nlargest(60)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8af5a53b",
   "metadata": {},
   "source": [
    "## Země původu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "id": "84f26121",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "zeme_puvodu\n",
       "Česko                                        538\n",
       "Spojené státy americké                       421\n",
       "Spojené království                           203\n",
       "Německo                                       42\n",
       "Kanada                                        38\n",
       "Slovensko                                     38\n",
       "Švédsko                                       33\n",
       "Austrálie                                     27\n",
       "Nizozemsko                                    24\n",
       "Francie                                       22\n",
       "Itálie                                        15\n",
       "Norsko                                         9\n",
       "Švýcarsko                                      8\n",
       "Belgie                                         7\n",
       "Finsko                                         7\n",
       "Irsko                                          7\n",
       "Jižní Korea                                    6\n",
       "Braniborské markrabství                        5\n",
       "Polsko                                         5\n",
       "Španělsko                                      5\n",
       "Dánsko                                         4\n",
       "Rakousko                                       4\n",
       "Spojené království Velké Británie a Irska      4\n",
       "Izrael                                         3\n",
       "Jamajka                                        3\n",
       "Japonsko                                       3\n",
       "Jihoafrická republika                          3\n",
       "Kazachstán                                     3\n",
       "Kolumbie                                       3\n",
       "Libanon                                        3\n",
       "Nový Zéland                                    3\n",
       "Rumunsko                                       3\n",
       "Rusko                                          3\n",
       "Valašské knížectví                             3\n",
       "Alžírsko                                       2\n",
       "Barbados                                       2\n",
       "Brazílie                                       2\n",
       "Filipíny                                       2\n",
       "Island                                         2\n",
       "Konžská demokratická republika                 2\n",
       "Kosovo                                         2\n",
       "Mexiko                                         2\n",
       "Moskevské knížectví                            2\n",
       "Nigérie                                        2\n",
       "Svatá říše římská                              2\n",
       "Trinidad a Tobago                              2\n",
       "České knížectví                                2\n",
       "Argentina                                      1\n",
       "Arménie                                        1\n",
       "Britské impérium                               1\n",
       "dtype: int64"
      ]
     },
     "execution_count": 66,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('zeme_puvodu').size().nlargest(50)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "24b3834a",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['J. Cole', 'Alice Merton']"
      ]
     },
     "execution_count": 67,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['zeme_puvodu'] == 'Svatá říše římská']['interpret_hlavni'].drop_duplicates().to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "5c5c52d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Moskevské knížectví',\"zeme_puvodu\"]=\"Rusko\"\n",
    "df.loc[df['zeme_puvodu'] == 'Ruské impérium',\"zeme_puvodu\"]=\"Rusko\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "401ef5e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Braniborské markrabství',\"zeme_puvodu\"]=\"Německo\"\n",
    "df.loc[df['zeme_puvodu'] == 'Svatá říše římská',\"zeme_puvodu\"]=\"Německo\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "id": "159fc59e",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'České knížectví',\"zeme_puvodu\"]=\"Česko\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "b6719957",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Německá demokratická republika',\"zeme_puvodu\"]=\"Německo\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "4aeaf5d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Spojené království Velké Británie a Irska','zeme_puvodu']=\"Spojené království\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "9893a453",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[df['zeme_puvodu'] == 'Valašské knížectví',\"zeme_puvodu\"]=\"Rumunsko\"\n",
    "df.loc[df['zeme_puvodu'] == 'Rumunské království',\"zeme_puvodu\"]=\"Rumunsko\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5efe950e",
   "metadata": {},
   "source": [
    "## Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "5adbc502",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df.sort_values(by='interpret_hlavni')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "bffce5dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_json(os.path.join('wikidata','wikidata_raw2.json'), orient=\"records\")\n",
    "df.to_csv(os.path.join('wikidata','wikidata_raw2.csv'), index=False)"
   ]
  }
 ],
 "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": 5
}