database_creation.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import math\n",
    "import hashlib\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to return the SHA-1 hash of a string\n",
    "def sha_1(string):\n",
    "    return hashlib.sha1(string.encode()).hexdigest().upper()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Function to add frequency values to dataframe\n",
    "def get_total(df1, df2):\n",
    "    new_df = pd.merge(df1, df2, on='SHA_1', how='left')\n",
    "    new_df['Total'] = new_df['Total'].fillna(0)\n",
    "    new_df['Total'] = new_df['Total'].astype('int')\n",
    "    return new_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of all pwned passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# pwned_all = pd.read_csv('pwned-passwords-sha1-ordered-by-count-v5.txt', sep=':', header=None)\n",
    "# pwned_all.columns = ['SHA_1', 'Total']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all SHA-1 values to string\n",
    "# pwned_all['SHA_1'] = pwned_all['SHA_1'].astype('str')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all frequency values to int\n",
    "# pwned_all['Total'] = pwned_all['Total'].astype('int')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# pwned_all.to_csv('Datasets/pwned_all.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "555278657\n"
     ]
    }
   ],
   "source": [
    "length_all = 555278657\n",
    "print(length_all)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of passwords pwned at least 2 times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# pwned_2 = pwned_all[pwned_all['Frequency'] >= 2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# pwned_2.to_csv('Datasets/pwned_2.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "359277039 196001618\n"
     ]
    }
   ],
   "source": [
    "length_2 = 359277039\n",
    "print(length_2, (length_all-length_2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of passwords pwned at least 10 times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# pwned_10 = pwned_2[pwned_2['Frequency'] >= 10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# pwned_10.to_csv('Datasets/pwned_10.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "35682860 519595797\n"
     ]
    }
   ],
   "source": [
    "length_10 = 35682860\n",
    "print(length_10, (length_all-length_10))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of passwords pwned at least 100 times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# pwned_100 = pwned_10[pwned_10['Frequency'] >= 100]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# pwned_100.to_csv('Datasets/pwned_100.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2112819 553165838\n"
     ]
    }
   ],
   "source": [
    "length_100 = 2112819\n",
    "print(length_100, (length_all-length_100))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of 100000 most popular passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # # Create dataframe\n",
    "# popular_100000 = pd.read_csv('popular_100000.txt', sep='\\r\\t', header=None)\n",
    "# popular_100000.columns = ['Password']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all password values to string, create SHA-1 hashes\n",
    "# popular_100000['Password'] = popular_100000['Password'].astype('str')\n",
    "# popular_100000['SHA_1'] = popular_100000['Password'].apply(sha_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# popular_100000.to_csv('Datasets/popular_100000.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of 10000 most popular passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# popular_10000 = pd.read_csv('popular_10000.txt', sep='\\r\\t', header=None)\n",
    "# popular_10000.columns = ['Password']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all password values to string, create SHA-1 hashes\n",
    "# popular_10000['Password'] = popular_10000['Password'].astype('str')\n",
    "# popular_10000['SHA_1'] = popular_10000['Password'].apply(sha_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# popular_10000.to_csv('Datasets/popular_10000.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of 1000 most popular passwords"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# popular_1000 = pd.read_csv('popular_1000.txt', sep='\\r\\t', header=None)\n",
    "# popular_1000.columns = ['Password']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all password values to string, create SHA-1 hashes\n",
    "# popular_1000['Password'] = popular_1000['Password'].astype('str')\n",
    "# popular_1000['SHA_1'] = popular_1000['Password'].apply(sha_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# popular_1000.to_csv('Datasets/popular_1000.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# DF of rockyou password leak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Create dataframe\n",
    "# rockyou = pd.read_csv('rockyou.txt', sep='\\r\\t', header=None)\n",
    "# rockyou.columns = ['Password']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Convert all password values to string, create SHA-1 hashes\n",
    "# rockyou['Password'] = rockyou['Password'].astype('str')\n",
    "# rockyou['SHA_1'] = rockyou['Password'].apply(sha_1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Write to csv\n",
    "# rockyou.to_csv('Datasets/rockyou.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "14344379\n"
     ]
    }
   ],
   "source": [
    "length_rockyou = 14344379\n",
    "print(length_rockyou)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load in DFs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # All pwned passowrds\n",
    "# pwned_all = pd.read_csv('Datasets/pwned_all.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Passowrds pwned at least 2 times\n",
    "# pwned_2 = pd.read_csv('Datasets/pwned_2.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Passowrds pwned at least 10 times\n",
    "# pwned_10 = pd.read_csv('Datasets/pwned_10.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Passowrds pwned at least 100 times\n",
    "# pwned_100 = pd.read_csv('Datasets/pwned_100.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 100000 most popular passwords\n",
    "# popular_100000 = pd.read_csv('Datasets/popular_100000.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 10000 most popular passwords\n",
    "# popular_10000 = pd.read_csv('Datasets/popular_10000.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # 1000 most popular passwords\n",
    "# popular_1000 = pd.read_csv('Datasets/popular_1000.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # rockyou password leak\n",
    "# rockyou = pd.read_csv('Datasets/rockyou.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Add total pwned data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# # Add total pwned data to 100000 popular passwords (pwned at least 10 times)\n",
    "# popular_100000 = get_total(popular_100000, pwned_10)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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": 4
}