data-prep.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "fb4eded6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pathlib import Path\n",
    "\n",
    "from hashlib import sha1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "cc149696",
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_hash(email):\n",
    "    return sha1(email.lower().encode('utf-8')).hexdigest()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "97a14fbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "mapping = {\n",
    "    'Email Address': 'email',\n",
    "    'Email address': 'email',\n",
    "    'Your email (the same you used for signing up)': 'email',\n",
    "    'How much time (in hours) did you spend on watching lectures and reading?': 'time_lectures',\n",
    "    'How much time (in hours) did you spend on homework?': 'time_homework',\n",
    "    'How much time (in hours) did you spend working on this project?': 'time_project',\n",
    "    'How much time (in hours) did you spend evaluating this project?': 'time_evaluate',\n",
    "    'Hash for the project you evaluate': 'hash_other',\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "6d817427",
   "metadata": {},
   "outputs": [],
   "source": [
    "courses = [\"de-zoomcamp-2022\", \"mlops-zoomcamp-2022\", \"ml-zoomcamp-2023\",\n",
    "           \"de-zoomcamp-2023\", \"mlops-zoomcamp-2023\", \"ml-zoomcamp-2022\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "ba369123",
   "metadata": {},
   "outputs": [],
   "source": [
    "prefix = Path(\"C:/Users/alexe/git/zoomcamp-scoring/\")\n",
    "data_out = Path('.') / 'data'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "0bcf1945",
   "metadata": {},
   "outputs": [],
   "source": [
    "columns = {\"time_lectures\", \"time_homework\", \"time_project\", \"time_evaluate\", \"hash_other\"}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "id": "c28bc33d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "no data for C:\\Users\\alexe\\git\\zoomcamp-scoring\\mlops-zoomcamp-2022\\data\\raw\\raw-leaderboard.csv. columns: Index(['Timestamp', 'email', 'Your name', 'LinkedIn', 'Github',\n",
      "       'Link to your project', 'Other links',\n",
      "       'Anything else you'd like to add to your record on the leaderboard?'],\n",
      "      dtype='object')\n",
      "no data for C:\\Users\\alexe\\git\\zoomcamp-scoring\\de-zoomcamp-2023\\data\\raw\\raw-leaderboard.csv. columns: Index(['Timestamp', 'email', 'Your name', 'LinkedIn', 'Github',\n",
      "       'Link to your project', 'Other links',\n",
      "       'Anything else you'd like to add to your record on the leaderboard?'],\n",
      "      dtype='object')\n",
      "no data for C:\\Users\\alexe\\git\\zoomcamp-scoring\\ml-zoomcamp-2022\\data\\raw\\competition.csv. columns: Index(['Timestamp', 'email', 'Team name on the leaderboard', 'Score',\n",
      "       'Learning in public links',\n",
      "       'I confirm that I filled in the form correctly'],\n",
      "      dtype='object')\n",
      "no data for C:\\Users\\alexe\\git\\zoomcamp-scoring\\ml-zoomcamp-2022\\data\\raw\\raw-leaderboard.csv. columns: Index(['Timestamp', 'email', 'Your name', 'LinkedIn', 'Github',\n",
      "       'Link to your project', 'Other links',\n",
      "       'Anything else you'd like to add to your record on the leaderboard?'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "for course in courses:\n",
    "    data_folder = prefix / course / 'data' / 'raw'\n",
    "    files = list(data_folder.glob('*.csv'))\n",
    "\n",
    "    course_data_out = data_out / course\n",
    "    course_data_out.mkdir(parents=True, exist_ok=True)\n",
    "    \n",
    "    for f in files: \n",
    "        df = pd.read_csv(f)\n",
    "\n",
    "        df = df.rename(columns=mapping)\n",
    "        existing_columns = list(set(df.columns) & columns)\n",
    "        \n",
    "        if len(existing_columns) == 0:\n",
    "            print(f'no data for {f}. columns: {df.columns}')\n",
    "            continue\n",
    "\n",
    "        df = df [[\"email\"] + existing_columns]\n",
    "\n",
    "        df.email = df.email.str.lower().str.strip()\n",
    "\n",
    "        if 'eval' in f.name:\n",
    "            df = df.drop_duplicates(subset=['email', 'hash_other'], keep='last')\n",
    "            del df['hash_other']\n",
    "        else:\n",
    "            df = df.drop_duplicates(subset=['email'], keep='last')\n",
    "\n",
    "        df = df.reset_index(drop=True)\n",
    "\n",
    "        df.email = df.email + '_salt'\n",
    "        df['email'] = df.email.apply(compute_hash)\n",
    "        \n",
    "        f_out = course_data_out / f.name\n",
    "        df.to_csv(f_out, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "46f38b37",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d06f0d5b",
   "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}