src/EBI_analysis/preprocess_metadata.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "# EBI Metadata - Preprocessing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2831557 entries, 0 to 2831556\n",
      "Data columns (total 51 columns):\n",
      " #   Column                Dtype  \n",
      "---  ------                -----  \n",
      " 0   accession             object \n",
      " 1   altitude              float64\n",
      " 2   base_count            int64  \n",
      " 3   bio_material          float64\n",
      " 4   cell_line             float64\n",
      " 5   cell_type             float64\n",
      " 6   collected_by          object \n",
      " 7   collection_date       object \n",
      " 8   country               object \n",
      " 9   cultivar              float64\n",
      " 10  culture_collection    object \n",
      " 11  dataclass             object \n",
      " 12  description           object \n",
      " 13  dev_stage             float64\n",
      " 14  ecotype               float64\n",
      " 15  environmental_sample  bool   \n",
      " 16  first_public          object \n",
      " 17  germline              bool   \n",
      " 18  haplotype             float64\n",
      " 19  host                  object \n",
      " 20  identified_by         object \n",
      " 21  isolate               object \n",
      " 22  isolation_source      object \n",
      " 23  keywords              object \n",
      " 24  lab_host              object \n",
      " 25  last_updated          object \n",
      " 26  location              object \n",
      " 27  mating_type           float64\n",
      " 28  metagenome_source     float64\n",
      " 29  mol_type              object \n",
      " 30  organelle             float64\n",
      " 31  plasmid               float64\n",
      " 32  sample_accession      object \n",
      " 33  scientific_name       object \n",
      " 34  sequence_md5          object \n",
      " 35  sequence_version      int64  \n",
      " 36  serotype              float64\n",
      " 37  serovar               float64\n",
      " 38  sex                   float64\n",
      " 39  specimen_voucher      float64\n",
      " 40  strain                object \n",
      " 41  study_accession       object \n",
      " 42  sub_species           float64\n",
      " 43  sub_strain            float64\n",
      " 44  submitted_sex         float64\n",
      " 45  tax_division          object \n",
      " 46  tax_id                int64  \n",
      " 47  tissue_lib            float64\n",
      " 48  tissue_type           float64\n",
      " 49  topology              object \n",
      " 50  variety               float64\n",
      "dtypes: bool(2), float64(22), int64(3), object(24)\n",
      "memory usage: 1.0+ GB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "metadata_ebi = pd.read_csv('../../data/results_sequence_tsv.txt',\n",
    "                           sep='\\t',\n",
    "                           dtype={'collected_by': object,\n",
    "                                  'collection_date': object,\n",
    "                                  'culture_collection': object,\n",
    "                                  'identified_by': object,\n",
    "                                  'isolate': object,\n",
    "                                  'isolation_source': object,\n",
    "                                  'keywords': object,\n",
    "                                  'lab_host': object,\n",
    "                                  'location': object,\n",
    "                                  'sample_accession': object,\n",
    "                                  'strain': object,\n",
    "                                  'study_accession': object})\n",
    "\n",
    "metadata_ebi.info() # 51 columns, 2.8 mio entries"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "## Remove Empty Columns"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "accession                     0\naltitude                2831557\nbase_count                    0\nbio_material            2831557\ncell_line               2831557\ncell_type               2831557\ncollected_by            2748277\ncollection_date           66411\ncountry                     462\ncultivar                2831557\nculture_collection      2831556\ndataclass                     0\ndescription                   0\ndev_stage               2831557\necotype                 2831557\nenvironmental_sample          0\nfirst_public                  0\ngermline                      0\nhaplotype               2831557\nhost                        451\nidentified_by           2831543\nisolate                 1285827\nisolation_source        1566587\nkeywords                1932985\nlab_host                2831507\nlast_updated                  0\nlocation                2828006\nmating_type             2831557\nmetagenome_source       2831557\nmol_type                      0\norganelle               2831557\nplasmid                 2831557\nsample_accession         935723\nscientific_name               0\nsequence_md5                  0\nsequence_version              0\nserotype                2831557\nserovar                 2831557\nsex                     2831557\nspecimen_voucher        2831557\nstrain                  2831400\nstudy_accession          900293\nsub_species             2831557\nsub_strain              2831557\nsubmitted_sex           2831557\ntax_division                  0\ntax_id                        0\ntissue_lib              2831557\ntissue_type             2831557\ntopology                      0\nvariety                 2831557\ndtype: int64"
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Show how many NaN values each col has\n",
    "metadata_ebi.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "['altitude',\n 'bio_material',\n 'cell_line',\n 'cell_type',\n 'cultivar',\n 'dev_stage',\n 'ecotype',\n 'haplotype',\n 'mating_type',\n 'metagenome_source',\n 'organelle',\n 'plasmid',\n 'serotype',\n 'serovar',\n 'sex',\n 'specimen_voucher',\n 'sub_species',\n 'sub_strain',\n 'submitted_sex',\n 'tissue_lib',\n 'tissue_type',\n 'variety']"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract names of cols containing only NaN values\n",
    "nan_cols = [i for i in metadata_ebi.columns if metadata_ebi[i].isnull().sum() == len(metadata_ebi)]\n",
    "nan_cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "accession                     0\nbase_count                    0\ncollected_by            2748277\ncollection_date           66411\ncountry                     462\nculture_collection      2831556\ndataclass                     0\ndescription                   0\nenvironmental_sample          0\nfirst_public                  0\ngermline                      0\nhost                        451\nidentified_by           2831543\nisolate                 1285827\nisolation_source        1566587\nkeywords                1932985\nlab_host                2831507\nlast_updated                  0\nlocation                2828006\nmol_type                      0\nsample_accession         935723\nscientific_name               0\nsequence_md5                  0\nsequence_version              0\nstrain                  2831400\nstudy_accession          900293\ntax_division                  0\ntax_id                        0\ntopology                      0\ndtype: int64"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Reduce data frame to relevant 29 cols (6 columns do still contain a lot of NaN values)\n",
    "metadata_ebi_relevant_cols = metadata_ebi.drop(nan_cols, axis=1)\n",
    "metadata_ebi_relevant_cols.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Rename column and fill NaN values with empty strings\n",
    "metadata_ebi_relevant_cols.rename({'country': 'country_and_region'}, axis=1, inplace=True)\n",
    "metadata_ebi_relevant_cols['country_and_region'] = metadata_ebi_relevant_cols['country_and_region'].fillna('')\n",
    "\n",
    "# Save as CSV\n",
    "metadata_ebi_relevant_cols.to_csv('../../data/metadata_EBI_relevant_cols.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Data Cleaning"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 2831557 entries, 0 to 2831556\n",
      "Data columns (total 29 columns):\n",
      " #   Column                Dtype \n",
      "---  ------                ----- \n",
      " 0   accession             object\n",
      " 1   base_count            int64 \n",
      " 2   collected_by          object\n",
      " 3   collection_date       object\n",
      " 4   country_and_region    object\n",
      " 5   culture_collection    object\n",
      " 6   dataclass             object\n",
      " 7   description           object\n",
      " 8   environmental_sample  bool  \n",
      " 9   first_public          object\n",
      " 10  germline              bool  \n",
      " 11  host                  object\n",
      " 12  identified_by         object\n",
      " 13  isolate               object\n",
      " 14  isolation_source      object\n",
      " 15  keywords              object\n",
      " 16  lab_host              object\n",
      " 17  last_updated          object\n",
      " 18  location              object\n",
      " 19  mol_type              object\n",
      " 20  sample_accession      object\n",
      " 21  scientific_name       object\n",
      " 22  sequence_md5          object\n",
      " 23  sequence_version      int64 \n",
      " 24  strain                object\n",
      " 25  study_accession       object\n",
      " 26  tax_division          object\n",
      " 27  tax_id                int64 \n",
      " 28  topology              object\n",
      "dtypes: bool(2), int64(3), object(24)\n",
      "memory usage: 588.7+ MB\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv('../../data/metadata_EBI_relevant_cols.csv',\n",
    "                 dtype={'collected_by': object,\n",
    "                        'collection_date': object,\n",
    "                        'culture_collection': object,\n",
    "                        'identified_by': object,\n",
    "                        'isolate': object,\n",
    "                        'isolation_source': object,\n",
    "                        'keywords': object,\n",
    "                        'lab_host': object,\n",
    "                        'location': object,\n",
    "                        'sample_accession': object,\n",
    "                        'strain': object,\n",
    "                        'study_accession': object})\n",
    "df.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# TODO: Which columns are not relevant for further analysis?\n",
    "cols_to_drop = ['scientific_name', # all 'SARS-CoV-2'\n",
    "                'sequence_md5', # MD5 checksum should have to further meaning as it is a hash value\n",
    "                'sequence_version', # nearly all version 1\n",
    "                'study_accession', # name of study accession should have no influence on biases or does it encode e.g. country?\n",
    "                'location', # same as country?\n",
    "                'environmental_sample' #?\n",
    "]"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Keywords"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "purposeofsampling:baselinesurveillance        894872\npurposeofsampling:targetedefforts               1658\npuposeofsampling:baselinesurveillance           1092\npurposeofsampling:targeted_sequencing            611\npurpose_of_sequencing:baselinesurveillance       214\npurposeofsampling=targetedefforts                122\npurposeofsampling=baselinesurveillance             3\nName: keywords, dtype: int64"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Cleanup keyword column\n",
    "df['keywords'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "purpose_of_sampling:baseline_surveillance    896181\npurpose_of_sampling:targeted_efforts           2391\nName: keywords, dtype: int64"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['keywords'].replace(['purposeofsampling:baselinesurveillance', 'puposeofsampling:baselinesurveillance', 'purpose_of_sequencing:baselinesurveillance', 'purposeofsampling=baselinesurveillance'], 'purpose_of_sampling:baseline_surveillance', inplace=True)\n",
    "df['keywords'].replace(['purposeofsampling:targetedefforts', 'purposeofsampling=targetedefforts', 'purposeofsampling:targeted_sequencing'], 'purpose_of_sampling:targeted_efforts', inplace=True)\n",
    "df['keywords'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Collection Date"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "data": {
      "text/plain": "Timestamp('2012-10-19 00:00:00')"
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check if start date of observations is useful\n",
    "df['collection_date'] = pd.to_datetime(df['collection_date'])\n",
    "df['collection_date'].min()  # -> first COVID-19 infections were in 2019, thus 2012 must be typo"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "array(['2019-12-26T00:00:00.000000000', '2019-12-31T00:00:00.000000000',\n       '2019-12-01T00:00:00.000000000', '2019-12-30T00:00:00.000000000',\n       '2019-12-23T00:00:00.000000000', '2019-12-18T00:00:00.000000000',\n       '2019-11-27T00:00:00.000000000', '2019-12-11T00:00:00.000000000',\n       '2019-12-05T00:00:00.000000000', '2019-09-12T00:00:00.000000000',\n       '2019-10-23T00:00:00.000000000', '2019-10-19T00:00:00.000000000',\n       '2019-10-22T00:00:00.000000000', '2019-10-17T00:00:00.000000000',\n       '2019-10-12T00:00:00.000000000', '2019-11-22T00:00:00.000000000',\n       '2019-12-15T00:00:00.000000000'], dtype='datetime64[ns]')"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Check which other dates cannot exist\n",
    "df[df['collection_date'].dt.year < 2020]['collection_date'].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [],
   "source": [
    "# Remove same \"2012\"-typo for 23 samples in collection date by exchanging with \"2021\"\n",
    "df['collection_date'].replace('2012-10-19', '2021-10-19', inplace=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "source": [
    "## Data Enrichment\n",
    "\n",
    "### Gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Homo sapiens                                                       2812197\nhomo sapiens                                                          2397\nHomo sapiens; FEMALE                                                  1987\nHomo sapiens; MALE                                                    1758\nHomo sapiens; UNKNOWN                                                  151\n                                                                    ...   \nHomo sapiens; Female, age 21,Asian;White,Not Hispanic or Latino          1\nHomo sapiens; Female, age 20,Asian,Not Hispanic or Latino                1\nHomo sapiens; Female, age 64,White,Not Hispanic or Latino                1\nHomo sapiens; Female, age 55,White,Not Hispanic or Latino                1\nHomo sapiens; age 440                                                    1\nName: host, Length: 1402, dtype: int64"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['host'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "def get_gender(host_value):\n",
    "    if any([substring in host_value.lower() for substring in ['female', 'femle', 'gender: f']]):\n",
    "        return 'female'\n",
    "    if any([substring in host_value.lower() for substring in ['male', 'gender: m']]):\n",
    "        return 'male'\n",
    "    else:\n",
    "        return 'unknown'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "unknown    2817207\nfemale        7611\nmale          6739\nName: gender, dtype: int64"
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add gender column\n",
    "df['gender'] = df.apply(lambda row: get_gender(str(row['host'])), axis=1)\n",
    "df['gender'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": "Homo sapiens                           2812197\nhomo sapiens                              2397\nHomo sapiens; UNKNOWN                      151\nNeovison vison                              62\nHomo sapiens; age 19                        55\nMink                                        54\nHomo sapiens; age 21                        40\nHomo sapiens; age 20                        38\nHomo sapiens; age 18                        35\nHomo sapiens; age 50                        35\nHomo sapiens; age 49                        29\nHomo sapiens; age 22                        28\nHomo sapiens; age 47                        27\nHomo sapiens; age 37                        25\nHomo sapiens; age 59                        24\nHomo sapiens; age 38                        23\nHomo sapiens; age 58                        23\nHomo sapiens; age 40                        23\nHomo sapiens; age 60                        23\nHomo sapiens; age 56                        22\nHomo sapiens; age 42                        21\nHomo sapiens; age 51                        21\nHomo sapiens; age 24                        21\nHomo sapiens; age 48                        21\nHomo sapiens; age 31                        21\nHomo sapiens; age 29                        20\nHomo sapiens; age 53                        20\nHomo sapiens; age 41                        20\nHomo sapiens; age 36                        20\nHomo sapiens; age 26                        20\nHomo sapiens; age 46                        20\nFelis catus                                 20\nHomo sapiens; age 32                        20\nHomo sapiens; age 44                        19\nHomo sapiens; age 16                        19\nHomo sapiens; age 75                        19\nHomo sapiens; age 54                        19\nHomo sapiens; age 52                        19\nHomo sapiens; age 67                        18\nHomo sapiens; age 57                        18\nHomo sapiens; age 30                        18\nHomo sapiens; age 15                        18\nHomo sapiens; age 71                        17\nHomo sapiens; age 39                        17\nHomo sapiens; age 35                        17\nHomo sapiens; age 55                        17\nHomo sapiens; Nasal Swab                    17\nHomo sapiens; age 64                        16\nHomo sapiens; age 65                        16\nHomo sapiens; age 23                        16\nHomo sapiens; age 43                        16\ncat                                         16\nHomo sapiens; age 68                        16\nHomo sapiens; age 74                        16\nHomo sapiens; age 70                        15\nHomo sapiens; age 14                        15\nHomo sapiens; age 45                        15\nHomo sapiens; age 61                        15\nHomo sapiens; age 63                        14\nHomo sapiens; age 33                        14\nUSA: Louisiana                              14\nHomo sapiens; age 72                        14\nHomo sapiens; age 34                        13\nHomo sapiens; age 62                        13\nMustela lutreola                            13\nHomo sapiens; age 25                        13\nHomo sapiens; age 11                        13\nOdocoileus virginianus                      13\nHomo sapiens; age 17                        12\nHomo sapiens; age 73                        12\nHomo sapiens; age 7                         12\nHomo sapiens; age 12                        12\nHomo sapiens; age 28                        12\nHomo sapiens; age 8                         12\nHomo sapiens; age 9                         11\nHomo sapiens; age 4                         11\nHomo sapiens; age 77                        11\nHomo sapiens; age 69                        11\nHomo sapiens; age 79                        10\nHomo sapiens; age 66                        10\nPanthera tigris                             10\nHomo sapiens; Unknown                       10\nHomo sapiens; age 27                         9\nHomo sapiens; age 81                         9\nHomo sapiens; age 80                         9\nHomo sapiens; Nasopharyngeal Swab            9\nHomo sapiens; age 13                         9\nHomo sapiens; age 1                          8\nHomo sapiens; age 5                          8\nHomo sapiens; age 6                          8\nHomo sapiens; age 91                         8\nHomo sapiens; age 3                          8\nPanthera leo                                 8\nHomo sapiens; age 92                         8\nHuman; 30 year old M                         8\nHuman; 24 year old M                         8\ndog                                          8\nHomo sapiens; age 76                         8\nHomo sapiens; age 10                         7\nHomo sapiens; age 83                         7\nHomo sapiens; age 82                         7\nHuman; 27 year old M                         6\nHomo sapiens; age 84                         6\nHuman; 60 year old M                         6\nHomo sapiens; age 78                         6\nHuman; 52 year old M                         6\nCanis lupus familiaris                       6\nHuman; 25 year old M                         5\nHuman; 22 year old M                         5\nHuman; 40 year old M                         5\nMesocricetus auratus                         5\nHuman; 36 year old M                         5\nHuman; 48 year old M                         5\nHuman; 42 year old M                         5\nHuman; 31 year old M                         4\nHomo sapiens; age 86                         4\nHomo sapiens; age 88                         4\nHuman; 20 year old M                         4\nHuman; 33 year old M                         4\nHuman; 29 year old M                         4\nHuman; 32 year old M                         4\nHuman; 35 year old M                         4\nHomo sapiens; age 2                          3\nHuman; 37 year old M                         3\nHuman; 55 year old M                         3\nHuman; 95 year old F                         3\nHuman; 45 year old M                         3\nHuman; 44 year old M                         3\nHuman; 51 year old M                         3\nHuman; 38 year old M                         3\nHuman; 26 year old M                         3\nHuman; 83 year old F                         3\nHuman; 56 year old M                         3\nHomo sapiens; age 87                         3\nHuman; 41 year old M                         3\nHuman; 40 year old F                         2\nHuman; 19 year old M                         2\nHuman; 84 year old F                         2\nHuman; 58 year old M                         2\nHuman; 81 year old M                         2\nHuman; 43 year old M                         2\nHuman; 46 year old M                         2\nHuman; 81 year old F                         2\nHuman; 26 year old F                         2\nHuman; 28 year old F                         2\nHuman; 50 year old F                         2\nHuman; 23 year old M                         2\nHomo sapiens; asymptomatic                   2\nHuman; 53 year old F                         2\nnot provided                                 2\nHomo sapiens; age 95                         2\nHomo sapiens; age 93                         2\nHuman                                        2\nAsiatic Lion                                 2\nhuamn                                        2\nFerret                                       2\nGorilla gorilla gorilla                      2\nHuman; 21 year old M                         2\nHomo sapiens; unknown sex, age37             2\nHomo sapiens; age 89                         2\nHuman; 28 year old M                         2\nHomo                                         2\nHuman; 34 year old M                         2\nHuman; 45 year old F                         2\nHuman; 29 year old F                         2\nHuman; 22 year old F                         2\nHuman; 74 year old F                         2\nHuman; 53 year old M                         2\nVero E6                                      2\nHuman; 66 year old M                         2\ncanine                                       2\nHomo sapiens; symptomatic                    2\nFelis catus; Domestic Shorthair              2\nHomo sapiens; age 1 day                      2\nPanthera tigris jacksoni                     2\nHomo sapiens; age 85                         2\nHomo sapiens; age 90                         2\nHuman; 65 year old M                         1\nHuman; 62 year old M                         1\nHuman; 24 year old F                         1\nHuman; 39 year old M                         1\nHuman; 67 year old M                         1\nHomo sapiens; hospitalized patient           1\nPanthera leo persica                         1\nPrionailurus bengalensis euptilurus          1\nHuman; 38 year old                           1\nHuman; 47 year old                           1\nHuman; 43 year old                           1\nHuman; 41 year old                           1\nHuman; 33 year old                           1\nHuman; 28 year old                           1\nHuman; 21 year old F                         1\nHuman; 41 year old F                         1\nHuman; 16 year old M                         1\nHuman; 38 year old F                         1\nHuman; 33 year old F                         1\nHuman; 86 year old M                         1\nHuman; 50 year old M                         1\nHuman; 47 year old M                         1\nHuman; 59 year old M                         1\nHuman; 70 year old F                         1\nHuman; 77 year old F                         1\nHuman; 80 year old M                         1\nHuman; 54 year old M                         1\nHuman; 25 year old F                         1\nHuman; 90 year old F                         1\nHuman; 31 year old F                         1\nHuman; 15 year old F                         1\nHuman; 55 year old F                         1\nHuman; 79 year old F                         1\nHuman; 76 year old F                         1\nHuman; 62 year old F                         1\nHomo sapiens; age 0                          1\nHuman; 73 year old M                         1\nHomo sapiens; unknown sex, age18             1\nHomo sapiens; age 10 mos                     1\nHomo sapiens; age 23 MOS                     1\nHomo sapiens; age 440                        1\nHomo sapiens; age 8MOS                       1\nfeline                                       1\nChlorocebus sabaeus                          1\nHomo sapiens; unknown, age40                 1\nHomo sapiens; , age34                        1\nHomo sapiens; age25                          1\nHomo sapiens; unknown sex, age63             1\nHomo sapiens; unknown sex, age2              1\nHuman; 71 year old F                         1\nHomo sapiens; unknown sex, age10             1\nHomo sapiens; unknown sex, age47             1\nHomosapeins                                  1\nHomo sapiens; age Unknown                    1\nHomo sapiens; unknown sex, age8              1\nHomo sapiens; unknown sex, age72             1\nHomo sapiens; unknown sex, age1              1\nHomo sapiens; unknown sex, age28             1\nunknown                                      1\nVero E6 cells                                1\nHomo sapiens; age 25 days                    1\nHomo sapiens; age 3 mos                      1\nHomo sapiens; age 9 mos                      1\nHuman; 32 year old                           1\nHuman; 75 year old M                         1\nHuman; 58 year old F                         1\nHuman; 64 year old F                         1\nHuman; 57 year old F                         1\nHuman; 63 year old M                         1\nHuman; 65 year old F                         1\nHuman; 69 year old F                         1\nHuman; 74 year old M                         1\nHuman; 49 year old M                         1\nHuman; 44 year old F                         1\nHuman; 32 year old F                         1\nHuman; 79 year old M                         1\nHuman; 51 year old F                         1\nHuman; 30 year old F                         1\nHuman; 59 year old F                         1\nHuman; 88 year old M                         1\nHuman; 36 year old                           1\nHuman; 40 year old                           1\nHuman; 26 year old                           1\nHuman; 89 year old                           1\nHuman; 35 year old                           1\nHuman; 61 year old M                         1\nName: host, dtype: int64"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Verify if all gender descriptions are catched\n",
    "df_unknown_gender = df[df['gender'] == 'unknown']\n",
    "\n",
    "pd.set_option(\"display.max_rows\", None)\n",
    "df_unknown_gender['host'].value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Age"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   },
   "execution_count": 1
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [],
   "source": [
    "# Manually fix typos in \"host\" column\n",
    "df['host'] = df['host'].replace(['agee'], ['age'], regex=True)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "def get_age(host_value):\n",
    "    if 'age' in host_value.lower():\n",
    "        if 'younger than' in host_value.lower():\n",
    "            # example: \"age Younger Than 15\"\n",
    "            return np.nan\n",
    "        elif 'day' in host_value.lower():\n",
    "            age_in_days = int(host_value[(host_value.lower().find('day') - 3): (host_value.lower().find('day') - 1)])\n",
    "            # example: \"age 42 day/s\"\n",
    "            return round(age_in_days / 365, 2)\n",
    "        elif 'months' in host_value.lower():\n",
    "            age_in_months = int(\n",
    "                host_value[(host_value.lower().find('months') - 3): (host_value.lower().find('months') - 1)])\n",
    "            # example: \"age 6 months\"\n",
    "            return round(age_in_months / 12, 2)\n",
    "        elif 'mos' in host_value.lower():\n",
    "            if host_value[host_value.lower().find('mos') - 1] == ' ':\n",
    "                age_in_months = int(\n",
    "                    host_value[(host_value.lower().find('mos') - 3): (host_value.lower().find('mos') - 1)])\n",
    "                # example: \"age 6 mos/MOS\"\n",
    "                return round(age_in_months / 12, 2)\n",
    "            else:\n",
    "                age_in_months = int(host_value[(host_value.lower().find('mos') - 2): (host_value.lower().find('mos'))])\n",
    "                # example: \"age 6MOS\"\n",
    "                return round(age_in_months / 12, 2)\n",
    "        elif host_value[host_value.lower().find('age') + 3] == ' ':\n",
    "            if 'age unknown' in host_value.lower():\n",
    "                # example: \"age Unknown\"\n",
    "                return np.nan\n",
    "            else:\n",
    "                # example: \"age 42\"\n",
    "                return host_value[(host_value.lower().find('age') + 4): (host_value.lower().find('age') + 6)]\n",
    "        elif host_value[host_value.lower().find('age') + 3] == ':':\n",
    "            if host_value[host_value.lower().find('age') + 4] == ' ':\n",
    "                # example: \"age: 42\"\n",
    "                return host_value[(host_value.lower().find('age') + 5): (host_value.lower().find('age') + 7)]\n",
    "            else:\n",
    "                # example: \"age:42\"\n",
    "                return host_value[(host_value.lower().find('age') + 4): (host_value.lower().find('age') + 6)]\n",
    "        else:\n",
    "            # example: \"age42\"\n",
    "            return host_value[(host_value.lower().find('age') + 3): (host_value.lower().find('age') + 5)]\n",
    "    elif 'year old' in host_value.lower():\n",
    "        # example: \"42 year old\"\n",
    "        return host_value[(host_value.lower().find('year old') - 3): (host_value.lower().find('year old') - 1)]\n",
    "    else:\n",
    "        return np.nan"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [],
   "source": [
    "# Add age column (measured in years)\n",
    "df['age'] = df.apply(lambda row: get_age(str(row['host'])), axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Country"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "outputs": [
    {
     "data": {
      "text/plain": "USA                   1389589\nUnited Kingdom        1075204\nGermany                210968\nSwitzerland             91095\nAustralia               13311\nSlovakia                 9548\nIceland                  5365\nBahrain                  3304\nMexico                   2949\nKenya                    2709\nNew Zealand              2427\nIndia                    2062\nEstonia                  1681\nFrance                   1618\nNetherlands              1562\nPoland                   1219\nSpain                    1184\nThailand                 1106\nSaudi Arabia             1090\nEgypt                    1071\nBrazil                   1018\nJapan                     942\nLebanon                   931\nPakistan                  827\nBangladesh                666\nSeychelles                541\nnan                       462\nChina                     423\nIran                      416\nChile                     383\nNigeria                   383\nRussia                    360\nHong Kong                 347\nItaly                     333\nPuerto Rico               304\nGhana                     297\nDjibouti                  290\nIraq                      287\nTunisia                   278\nLiechtenstein             265\nAustria                   261\nMalawi                    233\nPeru                      183\nCanada                    170\nSerbia                    153\nIsrael                    125\nSierra Leone               99\nGreece                     98\nZimbabwe                   98\nUzbekistan                 88\nPhilippines                84\nTurkey                     78\nMalaysia                   77\nTaiwan                     63\nWest Bank                  63\nMyanmar                    61\nArmenia                    48\nLibya                      46\nArgentina                  43\nMali                       42\nVenezuela                  40\nHungary                    36\nSouth Korea                35\nUganda                     29\nCzech Republic             29\nTimor-Leste                29\nJordan                     28\nFinland                    28\nPortugal                   27\nUruguay                    25\nEthiopia                   22\nGeorgia                    21\nKazakhstan                 18\nGabon                      17\nMorocco                    17\nComoros                    17\nDenmark                    15\nGuinea                     13\nMalta                      12\nBenin                      12\nNepal                      11\nTogo                       11\nViet Nam                   11\nSweden                     10\nSomalia                    10\nGuatemala                  10\nDominican Republic          9\nColombia                    9\nIndonesia                   9\nJamaica                     8\nSri Lanka                   6\nBelgium                     6\nQatar                       6\nGambia                      6\nLesotho                     5\nSouth Africa                5\nEcuador                     4\nBelize                      4\nGuam                        3\nSingapore                   3\nBelarus                     2\nCambodia                    2\nRomania                     2\nCuba                        2\nPalau                       2\nCameroon                    1\nCroatia                     1\nZambia                      1\nName: country, dtype: int64"
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Extract country from \"country_and_region\" column\n",
    "df['country_and_region'] = df['country_and_region'].astype(str)\n",
    "df['country'] = [country_and_region.split(':')[0] for country_and_region in df['country_and_region']]\n",
    "df['country'].value_counts()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "outputs": [
    {
     "data": {
      "text/plain": "array(['Japan', 'United Kingdom', 'Spain', 'Switzerland', 'France',\n       'Poland', 'Estonia', 'Philippines', 'nan', 'Bangladesh', 'China',\n       'Sweden', 'Netherlands', 'Germany', 'Zimbabwe', 'Italy',\n       'Thailand', 'USA', 'Australia', 'India', 'Finland', 'South Korea',\n       'Malaysia', 'Taiwan', 'Belgium', 'Nepal', 'Hong Kong', 'Brazil',\n       'Viet Nam', 'Iran', 'Nigeria', 'Georgia', 'Pakistan', 'Colombia',\n       'Peru', 'Israel', 'Iraq', 'Tunisia', 'Singapore', 'South Africa',\n       'Turkey', 'Greece', 'Serbia', 'Sri Lanka', 'Czech Republic',\n       'Puerto Rico', 'Kazakhstan', 'Uzbekistan', 'Guam', 'Uruguay',\n       'Jamaica', 'Russia', 'Egypt', 'Morocco', 'Kenya', 'Timor-Leste',\n       'Saudi Arabia', 'Benin', 'Chile', 'New Zealand', 'Mexico',\n       'Bahrain', 'Zambia', 'Lebanon', 'Jordan', 'Belize', 'Guatemala',\n       'Sierra Leone', 'Cuba', 'Ghana', 'Venezuela', 'Denmark', 'Myanmar',\n       'Libya', 'Malta', 'Gabon', 'Romania', 'West Bank', 'Ecuador',\n       'Canada', 'Cambodia', 'Mali', 'Dominican Republic', 'Argentina',\n       'Portugal', 'Cameroon', 'Austria', 'Hungary', 'Belarus',\n       'Ethiopia', 'Indonesia', 'Gambia', 'Togo', 'Armenia', 'Uganda',\n       'Djibouti', 'Somalia', 'Qatar', 'Guinea', 'Croatia', 'Seychelles',\n       'Slovakia', 'Palau', 'Comoros', 'Malawi', 'Liechtenstein',\n       'Iceland', 'Lesotho'], dtype=object)"
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['country'].unique()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Number of Inhabitants"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "outputs": [],
   "source": [
    "import json\n",
    "\n",
    "df['country'] = df['country'].replace(np.nan, 'Unknown')\n",
    "\n",
    "# Load number of inhabitants per country (taken from Wikipedia)\n",
    "with open('../helper_dicts/country_inhabitants.json') as f:\n",
    "    country_inhabitants_map = json.load(f)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "outputs": [
    {
     "data": {
      "text/plain": "count    2.830840e+06\nmean     1.967880e+08\nstd      1.385421e+08\nmin      1.790700e+04\n25%      6.708100e+07\n50%      8.312928e+07\n75%      3.314493e+08\nmax      1.418120e+09\nName: n_inhabitants, dtype: float64"
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Add column with number of inhabitants\n",
    "df['n_inhabitants'] = df['country'].map(country_inhabitants_map)\n",
    "df['n_inhabitants'].describe()"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "### Region"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%% md\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "\n",
    "def get_region(country_and_region):\n",
    "    if country_and_region.split(':', 1) != [country_and_region]:\n",
    "        if country_and_region.split(':', 1)[1][0] == ' ':\n",
    "            # Remove whitespace in front of region\n",
    "            return country_and_region.split(':', 1)[1][1:]\n",
    "        else:\n",
    "            return country_and_region.split(':', 1)[1]\n",
    "    else:\n",
    "        # Insert NaN if only country is available in \"country_and_region\" column\n",
    "        return np.nan\n",
    "\n",
    "# Add region column\n",
    "df['region'] = df.apply(lambda row: get_region(str(row['country_and_region'])), axis=1)"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "outputs": [],
   "source": [
    "# TODO: Derive race from \"host\" column and add as new column"
   ],
   "metadata": {
    "collapsed": false,
    "pycharm": {
     "name": "#%%\n"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "df.to_csv('../../data/metadata_EBI_preprocessed.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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}