Encoding+dataframe+columns.nbconvert.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "colab_type": "text",
    "id": "view-in-github"
   },
   "source": [
    "<a href=\"https://colab.research.google.com/github/groda/big_data/blob/master/Encoding%2Bdataframe%2Bcolumns.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "doxTgZtiazqa"
   },
   "source": [
    "<a href=\"https://github.com/groda/big_data\"><div><img src=\"https://github.com/groda/big_data/blob/master/logo_bdb.png?raw=true\" align=right width=\"90\"></div></a>\n",
    "\n",
    "# Encode columns in csv file\n",
    "<br>\n",
    "<br>\n",
    "\n",
    "\n",
    "\n",
    "I'm given a CSV file containing strings and I want to convert the characters to numeric values. I want to use different encodings of the characters on different columns or groups of columns.\n",
    "\n",
    "Let's say for instance that I have two encodings __A__ and __B__:\n",
    " - in encoding __A__ I want to encode the character `a` with the number `1`, the character `b` with `2`, and `c` with `3`\n",
    " - in encoding __B__ I want to encode the character `a` with the number `2`, the character `b` with `3`, and `c` with `1`\n",
    "\n",
    "If I use encoding __A__ to transform all columns in table\n",
    "\n",
    "| c1| c2 |\n",
    "|-----|-----|\n",
    "| a | a|\n",
    "| b | b|\n",
    "| c | b|\n",
    "\n",
    "I obtain\n",
    "\n",
    "| c1_enc| c2_enc |\n",
    "|-----|-----|\n",
    "| 1 | 1|\n",
    "| 2 | 2|\n",
    "| 3 | 2|\n",
    "\n",
    "If `col1` is encoded with __A__ and `col2` is encoded with __A__ then the table becomes\n",
    "\n",
    "| c1_enc| c2_enc |\n",
    "|-----|-----|\n",
    "| 1 | 2|\n",
    "| 2 | 3|\n",
    "| 3 | 3|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "2z76iZ3bkyLB"
   },
   "source": [
    "## Install PySpark"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:16.497904Z",
     "iopub.status.busy": "2024-05-26T08:17:16.497711Z",
     "iopub.status.idle": "2024-05-26T08:17:17.990387Z",
     "shell.execute_reply": "2024-05-26T08:17:17.989576Z"
    },
    "id": "uzgQcWAOk0Us",
    "outputId": "4c4bcb71-1d7b-4c7c-c871-298503c9e199"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: pyspark in /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages (3.4.0)\r\n",
      "Requirement already satisfied: py4j==0.10.9.7 in /opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages (from pyspark) (0.10.9.7)\r\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.0.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m24.0\u001b[0m\r\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\r\n"
     ]
    }
   ],
   "source": [
    "!pip install pyspark"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "IcTvspsLnB_j"
   },
   "source": [
    "## Download the data\n",
    "\n",
    "Retrieve the CSV file `data-1600cols.csv` and write it to the local storage."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:17.994318Z",
     "iopub.status.busy": "2024-05-26T08:17:17.993815Z",
     "iopub.status.idle": "2024-05-26T08:17:18.657855Z",
     "shell.execute_reply": "2024-05-26T08:17:18.657098Z"
    },
    "id": "J7sbQJsonF6x",
    "outputId": "2b26d8d6-2fba-4779-f4f5-84cb008e1f6a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CSV file downloaded successfully and saved at: data-1600cols.csv\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import csv\n",
    "\n",
    "def download_csv(url, save_path):\n",
    "    response = requests.get(url)\n",
    "\n",
    "    if response.status_code == 200:\n",
    "        with open(save_path, 'wb') as file:\n",
    "            file.write(response.content)\n",
    "        print(f\"CSV file downloaded successfully and saved at: {save_path}\")\n",
    "    else:\n",
    "        print(f\"Failed to download CSV file. Status code: {response.status_code}\")\n",
    "\n",
    "url = \"https://raw.githubusercontent.com/groda/big_data/master/data-1600cols.csv\"\n",
    "save_path = \"data-1600cols.csv\"\n",
    "\n",
    "download_csv(url, save_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "cMFSoMcgazqk"
   },
   "source": [
    "## Initialize Spark session\n",
    "\n",
    "SparkContext allows me to access Dataframes, change Spark configuration, cancel a job, get status of a job, etc.\n",
    "\n",
    "Load  the CSV file `data-1600cols.csv` into a Spark dataframe using the file's header as column names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:18.660917Z",
     "iopub.status.busy": "2024-05-26T08:17:18.660700Z",
     "iopub.status.idle": "2024-05-26T08:17:25.942349Z",
     "shell.execute_reply": "2024-05-26T08:17:25.941380Z"
    },
    "id": "XszooNxRazqs",
    "outputId": "0a9ed61c-990b-4beb-83c0-705d63f89ff5"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Setting default log level to \"WARN\".\n",
      "To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/05/26 08:17:20 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/hostedtoolcache/Python/3.8.18/x64/lib/python3.8/site-packages/pyspark/sql/context.py:112: FutureWarning: Deprecated in 3.0.0. Use SparkSession.builder.getOrCreate() instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql import SparkSession\n",
    "from pyspark import SparkContext\n",
    "from pyspark.sql import SQLContext\n",
    "\n",
    "spark = SparkSession \\\n",
    "            .builder \\\n",
    "            .master(\"local\") \\\n",
    "            .appName(\"Encode multiple columns\") \\\n",
    "            .getOrCreate()\n",
    "\n",
    "sqlContext = SQLContext(spark)\n",
    "df = sqlContext.read.csv(\"data-1600cols.csv\", header=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "jzhNAX3Uazqv"
   },
   "source": [
    "Check configuration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:25.948806Z",
     "iopub.status.busy": "2024-05-26T08:17:25.946834Z",
     "iopub.status.idle": "2024-05-26T08:17:25.994308Z",
     "shell.execute_reply": "2024-05-26T08:17:25.993596Z"
    },
    "id": "E8xrZgjwazqv",
    "outputId": "50f11103-74fd-4131-b27a-1eac525e0590"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('spark.master', 'local'),\n",
       " ('spark.sql.warehouse.dir',\n",
       "  'file:/home/runner/work/big_data/big_data/spark-warehouse'),\n",
       " ('spark.driver.port', '46563'),\n",
       " ('spark.executor.id', 'driver'),\n",
       " ('spark.app.id', 'local-1716711441422'),\n",
       " ('spark.app.submitTime', '1716711440474'),\n",
       " ('spark.driver.extraJavaOptions',\n",
       "  '-Djava.net.preferIPv6Addresses=false -XX:+IgnoreUnrecognizedVMOptions --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.lang.invoke=ALL-UNNAMED --add-opens=java.base/java.lang.reflect=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.base/java.net=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.util.concurrent=ALL-UNNAMED --add-opens=java.base/java.util.concurrent.atomic=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED --add-opens=java.base/sun.nio.cs=ALL-UNNAMED --add-opens=java.base/sun.security.action=ALL-UNNAMED --add-opens=java.base/sun.util.calendar=ALL-UNNAMED --add-opens=java.security.jgss/sun.security.krb5=ALL-UNNAMED -Djdk.reflect.useDirectMethodHandle=false'),\n",
       " ('spark.app.name', 'Encode multiple columns'),\n",
       " ('spark.rdd.compress', 'True'),\n",
       " ('spark.driver.host',\n",
       "  'fv-az1542-450.uwdtf5zkgycefedjt5dtsy0pzd.bx.internal.cloudapp.net'),\n",
       " ('spark.app.startTime', '1716711440649'),\n",
       " ('spark.serializer.objectStreamReset', '100'),\n",
       " ('spark.submit.pyFiles', ''),\n",
       " ('spark.submit.deployMode', 'client'),\n",
       " ('spark.ui.showConsoleProgress', 'true'),\n",
       " ('spark.executor.extraJavaOptions',\n",
       "  '-Djava.net.preferIPv6Addresses=false -XX:+IgnoreUnrecognizedVMOptions --add-opens=java.base/java.lang=ALL-UNNAMED --add-opens=java.base/java.lang.invoke=ALL-UNNAMED --add-opens=java.base/java.lang.reflect=ALL-UNNAMED --add-opens=java.base/java.io=ALL-UNNAMED --add-opens=java.base/java.net=ALL-UNNAMED --add-opens=java.base/java.nio=ALL-UNNAMED --add-opens=java.base/java.util=ALL-UNNAMED --add-opens=java.base/java.util.concurrent=ALL-UNNAMED --add-opens=java.base/java.util.concurrent.atomic=ALL-UNNAMED --add-opens=java.base/sun.nio.ch=ALL-UNNAMED --add-opens=java.base/sun.nio.cs=ALL-UNNAMED --add-opens=java.base/sun.security.action=ALL-UNNAMED --add-opens=java.base/sun.util.calendar=ALL-UNNAMED --add-opens=java.security.jgss/sun.security.krb5=ALL-UNNAMED -Djdk.reflect.useDirectMethodHandle=false')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark.sparkContext.getConf().getAll()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rS2J5wfGazqw"
   },
   "source": [
    "Check size of the dataframe (number of rows and columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:25.998483Z",
     "iopub.status.busy": "2024-05-26T08:17:25.998225Z",
     "iopub.status.idle": "2024-05-26T08:17:26.851160Z",
     "shell.execute_reply": "2024-05-26T08:17:26.850325Z"
    },
    "id": "0jeYKzP9azqw",
    "outputId": "2f4b5277-750e-4e57-8b54-fb893a704447"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows: 1000\n",
      "Number of columns: 1600\n"
     ]
    }
   ],
   "source": [
    "print('Number of rows: {}\\nNumber of columns: {}'.format(df.count(),len(df.columns)))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "a6Rq5kiMazqx"
   },
   "source": [
    "Check if the dataframe contains any nulls?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:26.854983Z",
     "iopub.status.busy": "2024-05-26T08:17:26.854475Z",
     "iopub.status.idle": "2024-05-26T08:17:28.207142Z",
     "shell.execute_reply": "2024-05-26T08:17:28.206258Z"
    },
    "id": "f-aJhYe1azqx",
    "outputId": "a7a0de89-7eb4-4eeb-e78b-00e2e44d0375"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "24/05/26 08:17:26 WARN package: Truncated the string representation of a plan since it was too large. This behavior can be adjusted by setting 'spark.sql.debug.maxToStringFields'.\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 4:>                                                          (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.where(df.V2.isNull()).collect()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "hE0dPL5qazqx"
   },
   "source": [
    "Show a couple of columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:28.210225Z",
     "iopub.status.busy": "2024-05-26T08:17:28.210008Z",
     "iopub.status.idle": "2024-05-26T08:17:28.409446Z",
     "shell.execute_reply": "2024-05-26T08:17:28.408564Z"
    },
    "id": "APnPdgpAazqy",
    "outputId": "d6ea1569-151b-4d84-9fbb-c5eefef00af7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---+\n",
      "| V1| V2| V3|\n",
      "+---+---+---+\n",
      "|  j|  n|  d|\n",
      "|  d|  n|  w|\n",
      "|  p|  h|  a|\n",
      "|  b|  h|  e|\n",
      "|  z|  x|  u|\n",
      "|  b|  e|  v|\n",
      "|  y|  t|  x|\n",
      "|  i|  r|  e|\n",
      "|  x|  e|  g|\n",
      "|  l|  j|  z|\n",
      "|  l|  v|  l|\n",
      "|  z|  n|  h|\n",
      "|  s|  m|  c|\n",
      "|  g|  m|  f|\n",
      "|  i|  p|  n|\n",
      "|  i|  f|  b|\n",
      "|  u|  n|  j|\n",
      "|  s|  o|  e|\n",
      "|  k|  y|  c|\n",
      "|  h|  b|  i|\n",
      "+---+---+---+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.select('V1','V2','V3').show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "FauIiJe1azqy"
   },
   "source": [
    "## First approach\n",
    "\n",
    "Using the `translate` function from `pyspark.sql` and adding a new column with `withColumn` at each step. Test on a small dataframe `test_df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:28.413920Z",
     "iopub.status.busy": "2024-05-26T08:17:28.413628Z",
     "iopub.status.idle": "2024-05-26T08:17:29.472769Z",
     "shell.execute_reply": "2024-05-26T08:17:29.471841Z"
    },
    "id": "4DOTSzTKazqy",
    "outputId": "80faee62-82e9-435d-a076-649b2fe29073"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+\n",
      "| c1| c2|\n",
      "+---+---+\n",
      "|  a|  a|\n",
      "|  b|  b|\n",
      "|  c|  b|\n",
      "+---+---+\n",
      "\n",
      "+---+---+------+------+\n",
      "| c1| c2|c1_enc|c2_enc|\n",
      "+---+---+------+------+\n",
      "|  a|  a|     1|     1|\n",
      "|  b|  b|     2|     2|\n",
      "|  c|  b|     3|     2|\n",
      "+---+---+------+------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import pyspark.sql.functions as f\n",
    "\n",
    "test_df = sqlContext.createDataFrame([('a', 'a'), ('b', 'b'), ('c', 'b')], ['c1', 'c2'])\n",
    "test_df.show()\n",
    "\n",
    "chars = \"abc\"\n",
    "A = \"123\" # encoding A\n",
    "B = \"231\" # encoding B\n",
    "\n",
    "\n",
    "for col_name in [\"c1\", \"c2\"]:\n",
    "    test_df = test_df.withColumn(col_name+'_enc', f.translate(f.col(col_name), \"abcd\", A))\n",
    "\n",
    "test_df.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "vNf0v2GPazqy"
   },
   "source": [
    "Try out this approach on the big dataframe, applying the function to a few columns. I define two random encodings, `encodingA` and `encodingB` and apply each encoding to two different columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:29.476511Z",
     "iopub.status.busy": "2024-05-26T08:17:29.476241Z",
     "iopub.status.idle": "2024-05-26T08:17:30.344511Z",
     "shell.execute_reply": "2024-05-26T08:17:30.343662Z"
    },
    "id": "KLEtwWtfazq0",
    "outputId": "09bd8db1-777c-4bc9-b966-7f742b426f60"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Encodings:\n",
      "abcdefghijklmnopqrstuvwxyz\n",
      "84909340662170830129865816\n",
      "03946914819742444812351068\n",
      "--------------------------\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---+---+------+------+------+------+\n",
      "| V1| V2| V3| V4|V1_enc|V2_enc|V3_enc|V4_enc|\n",
      "+---+---+---+---+------+------+------+------+\n",
      "|  j|  n|  d|  m|     6|     2|     0|     4|\n",
      "|  d|  n|  w|  y|     0|     2|     5|     6|\n",
      "|  p|  h|  a|  h|     3|     4|     8|     4|\n",
      "|  b|  h|  e|  t|     4|     4|     9|     2|\n",
      "|  z|  x|  u|  d|     6|     0|     8|     4|\n",
      "|  b|  e|  v|  j|     4|     6|     6|     1|\n",
      "|  y|  t|  x|  w|     1|     2|     8|     1|\n",
      "|  i|  r|  e|  q|     6|     8|     9|     4|\n",
      "|  x|  e|  g|  s|     8|     6|     4|     1|\n",
      "|  l|  j|  z|  h|     1|     1|     6|     4|\n",
      "|  l|  v|  l|  w|     1|     5|     1|     1|\n",
      "|  z|  n|  h|  z|     6|     2|     0|     8|\n",
      "|  s|  m|  c|  z|     2|     4|     9|     8|\n",
      "|  g|  m|  f|  j|     4|     4|     3|     1|\n",
      "|  i|  p|  n|  h|     6|     4|     0|     4|\n",
      "|  i|  f|  b|  r|     6|     9|     4|     8|\n",
      "|  u|  n|  j|  p|     8|     2|     6|     4|\n",
      "|  s|  o|  e|  f|     2|     4|     9|     9|\n",
      "|  k|  y|  c|  c|     2|     6|     9|     9|\n",
      "|  h|  b|  i|  p|     0|     3|     6|     4|\n",
      "+---+---+---+---+------+------+------+------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "import string\n",
    "import random\n",
    "\n",
    "# set a raneom seed\n",
    "random.seed(30)\n",
    "\n",
    "chars = string.ascii_lowercase\n",
    "encodingA = ''.join(random.choice(string.digits) for i in range(len(chars)))\n",
    "encodingB = ''.join(random.choice(string.digits) for i in range(len(chars)))\n",
    "\n",
    "print(\"Encodings:\")\n",
    "print(chars)\n",
    "print(encodingA)\n",
    "print(encodingB)\n",
    "print(\"-\"*26)\n",
    "new_df=df\n",
    "\n",
    "for col_name in [\"V1\", \"V3\"]:  # apply encodingA to columns V1, V3\n",
    "    new_df=new_df.withColumn(col_name+'_enc',f.translate(f.col(col_name), chars, encodingA))\n",
    "for col_name in [\"V2\", \"V4\"]:  # apply encodingB to columns V2, V4\n",
    "    new_df=new_df.withColumn(col_name+'_enc',f.translate(f.col(col_name), chars, encodingB))\n",
    "\n",
    "new_df.select(\"V1\",\"V2\",\"V3\",\"V4\", \"V1_enc\", \"V2_enc\", \"V3_enc\", \"V4_enc\").show()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "brjqW1G6azq0"
   },
   "source": [
    "Apply encodings to 4 columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:30.351598Z",
     "iopub.status.busy": "2024-05-26T08:17:30.351288Z",
     "iopub.status.idle": "2024-05-26T08:17:31.346635Z",
     "shell.execute_reply": "2024-05-26T08:17:31.345867Z"
    },
    "id": "Jx2C8uhTazq1",
    "outputId": "efd858d0-e9af-4774-e47d-d8b9724f0624"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---+---+\n",
      "| V1| V2| V3| V4|\n",
      "+---+---+---+---+\n",
      "|  6|  2|  0|  4|\n",
      "|  0|  2|  5|  6|\n",
      "|  3|  4|  8|  4|\n",
      "+---+---+---+---+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "new_df=df\n",
    "\n",
    "for col_name in [\"V1\", \"V3\"]:  # apply encodingA to columns V1, V2\n",
    "    new_df = new_df.withColumn(col_name,f.translate(f.col(col_name), chars, encodingA))\n",
    "for col_name in [\"V2\", \"V4\"]:  # apply encodingB to columns V3, V4\n",
    "    new_df = new_df.withColumn(col_name,f.translate(f.col(col_name), chars, encodingB))\n",
    "\n",
    "new_df.select(\"V1\",\"V2\",\"V3\",\"V4\").show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "bJoUhtH6azq1"
   },
   "source": [
    "Check:\n",
    "\n",
    "\n",
    "| V1 | V2 | V3 | V4\n",
    "|---|---|---|---|\n",
    "| 6 | 2 | 0 | 4 |\n",
    "| 0 | 2 | 5 | 6 |\n",
    "| 3 | 4 | 8 | 4 |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "1L6RlfYmazq1"
   },
   "source": [
    "When applying encoding to thousands of rows the previous approach is too slow. The reason is that I'm writing a new dataframe after each tranformation.\n",
    "\n",
    "Split columns in even and odd, apply two different encodings to each set of columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:31.351546Z",
     "iopub.status.busy": "2024-05-26T08:17:31.351269Z",
     "iopub.status.idle": "2024-05-26T08:17:32.098295Z",
     "shell.execute_reply": "2024-05-26T08:17:32.097594Z"
    },
    "id": "bZBw3d52azq1",
    "outputId": "5d01e70d-48e3-445c-c664-93eabb0e824a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['V2', 'V4']\n",
      "['V1', 'V3']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---+---+\n",
      "| V1| V2| V3| V4|\n",
      "+---+---+---+---+\n",
      "|  6|  2|  0|  4|\n",
      "|  0|  2|  5|  6|\n",
      "|  3|  4|  8|  4|\n",
      "+---+---+---+---+\n",
      "only showing top 3 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "cols_e = [\"V\"+str(i) for i in range(2,5,2)]\n",
    "cols_o = [\"V\"+str(i) for i in range(1,4,2)]\n",
    "\n",
    "print(cols_e)\n",
    "print(cols_o)\n",
    "\n",
    "new_df=df\n",
    "\n",
    "# works with a few columns (4 in total in this example) but too slow for thousands of columns\n",
    "for col_name in cols_o:  # apply encodingA to columns with even numbers\n",
    "    new_df=new_df.withColumn(col_name,f.translate(f.col(col_name), chars, encodingA))\n",
    "for col_name in cols_e:  # apply encodingB to odd columns\n",
    "    new_df=new_df.withColumn(col_name,f.translate(f.col(col_name), chars, encodingB))\n",
    "\n",
    "new_df.select([\"V\"+str(i) for i in range(1,5)]).show(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "7ZW9OIB4azq2"
   },
   "source": [
    "## Second approach\n",
    "Using `udf` (user-defined functions). Avoiding `withColumn` and using `select` instead."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:32.103585Z",
     "iopub.status.busy": "2024-05-26T08:17:32.103330Z",
     "iopub.status.idle": "2024-05-26T08:17:32.927491Z",
     "shell.execute_reply": "2024-05-26T08:17:32.926678Z"
    },
    "id": "XSAPE2Whazq2",
    "outputId": "cf87b281-a128-440b-a871-a2d384e34514"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['V1', 'V3', 'V5']\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---+---+---+------+------+------+\n",
      "| V1| V3| V5|V1_enc|V3_enc|V5_enc|\n",
      "+---+---+---+------+------+------+\n",
      "|  j|  d|  s|     6|     0|     2|\n",
      "|  d|  w|  l|     0|     5|     1|\n",
      "|  p|  a|  w|     3|     8|     5|\n",
      "|  b|  e|  x|     4|     9|     8|\n",
      "|  z|  u|  b|     6|     8|     4|\n",
      "|  b|  v|  u|     4|     6|     8|\n",
      "|  y|  x|  z|     1|     8|     6|\n",
      "|  i|  e|  k|     6|     9|     2|\n",
      "|  x|  g|  s|     8|     4|     2|\n",
      "|  l|  z|  l|     1|     6|     1|\n",
      "+---+---+---+------+------+------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import udf\n",
    "from pyspark.sql.types import IntegerType, StringType\n",
    "\n",
    "# define an encoding as a list of two strings of equal length\n",
    "\n",
    "o = [\"abcdefghijklmnopqrstuvwxyz\", encodingA]\n",
    "\n",
    "def enc(*a):\n",
    "    # encode string s with encoding o\n",
    "    s=a[0]\n",
    "    for i in range(len(o[0])):\n",
    "      if s==o[0][i]:\n",
    "          return o[1][i]\n",
    "    return s\n",
    "\n",
    "# create udf\n",
    "encode_udf = udf(enc, StringType())\n",
    "\n",
    "cols_o = [\"V\"+str(i) for i in range(7) if i%2==1]\n",
    "print(cols_o)\n",
    "\n",
    "(\n",
    "df.select(\"V1\",\"V3\",\"V5\",\n",
    "           encode_udf(\"V1\").alias(\"V1_enc\"),\n",
    "           encode_udf(\"V3\").alias(\"V3_enc\"),\n",
    "           encode_udf(\"V5\").alias(\"V5_enc\"))\n",
    "    .show(10)\n",
    ")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "pQs1KagOazq3"
   },
   "source": [
    "And now encode all even and odd numbered columns with `encodingA` and `encodingB`, respectively using `select`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:32.931964Z",
     "iopub.status.busy": "2024-05-26T08:17:32.931678Z",
     "iopub.status.idle": "2024-05-26T08:17:33.316152Z",
     "shell.execute_reply": "2024-05-26T08:17:33.315390Z"
    },
    "id": "lUmXnAdgazq3",
    "outputId": "3c566fbc-4de8-48ba-a60f-dd9acfd5a2f3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "|V1_enc|V3_enc|V5_enc|V7_enc|V9_enc|V11_enc|V13_enc|V15_enc|V17_enc|V19_enc|\n",
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "|     6|     0|     2|     6|     9|      8|      2|      2|      3|      6|\n",
      "|     0|     5|     1|     8|     0|      2|      9|      6|      8|      2|\n",
      "|     3|     8|     5|     4|     8|      3|      9|      0|      2|      9|\n",
      "|     4|     9|     8|     0|     9|      0|      9|      2|      8|      0|\n",
      "|     6|     8|     4|     0|     9|      2|      8|      6|      6|      6|\n",
      "|     4|     6|     8|     5|     8|      6|      5|      6|      6|      4|\n",
      "|     1|     8|     6|     0|     4|      8|      4|      5|      5|      1|\n",
      "|     6|     9|     2|     5|     8|      8|      5|      4|      0|      1|\n",
      "|     8|     4|     2|     2|     2|      2|      9|      7|      8|      0|\n",
      "|     1|     6|     1|     9|     6|      2|      6|      1|      1|      2|\n",
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# apply function to 50 columns\n",
    "new_df=df.select([encode_udf(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(1,100,2)])\n",
    "new_df.select([\"V\"+str(i)+\"_enc\" for i in range(1,21,2)]).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:33.321993Z",
     "iopub.status.busy": "2024-05-26T08:17:33.321716Z",
     "iopub.status.idle": "2024-05-26T08:17:33.765706Z",
     "shell.execute_reply": "2024-05-26T08:17:33.764974Z"
    },
    "id": "yeAi0N8Aazq3",
    "outputId": "8837f69f-b594-46b0-b5c0-79b59f594eb7"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "|V1_enc|V3_enc|V5_enc|V7_enc|V9_enc|V11_enc|V13_enc|V15_enc|V17_enc|V19_enc|\n",
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "|     6|     0|     2|     6|     9|      8|      2|      2|      3|      6|\n",
      "|     0|     5|     1|     8|     0|      2|      9|      6|      8|      2|\n",
      "|     3|     8|     5|     4|     8|      3|      9|      0|      2|      9|\n",
      "|     4|     9|     8|     0|     9|      0|      9|      2|      8|      0|\n",
      "|     6|     8|     4|     0|     9|      2|      8|      6|      6|      6|\n",
      "|     4|     6|     8|     5|     8|      6|      5|      6|      6|      4|\n",
      "|     1|     8|     6|     0|     4|      8|      4|      5|      5|      1|\n",
      "|     6|     9|     2|     5|     8|      8|      5|      4|      0|      1|\n",
      "|     8|     4|     2|     2|     2|      2|      9|      7|      8|      0|\n",
      "|     1|     6|     1|     9|     6|      2|      6|      1|      1|      2|\n",
      "+------+------+------+------+------+-------+-------+-------+-------+-------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# apply function to 100 columns\n",
    "new_df=df.select([encode_udf(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(1,201,2)])\n",
    "new_df.select([\"V\"+str(i)+\"_enc\" for i in range(1,21,2)]).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:33.769290Z",
     "iopub.status.busy": "2024-05-26T08:17:33.769004Z",
     "iopub.status.idle": "2024-05-26T08:17:34.393460Z",
     "shell.execute_reply": "2024-05-26T08:17:34.392535Z"
    },
    "id": "Cafg1tG2azq3",
    "outputId": "1e0d9cdb-cbd7-4fe3-e94b-842209f18c08"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "|V381_enc|V383_enc|V385_enc|V387_enc|V389_enc|V391_enc|V393_enc|V395_enc|V397_enc|V399_enc|\n",
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "|       4|       8|       6|       8|       9|       6|       8|       8|       6|       8|\n",
      "|       6|       4|       8|       4|       1|       6|       6|       0|       9|       6|\n",
      "|       9|       6|       8|       8|       2|       4|       0|       5|       9|       1|\n",
      "|       3|       6|       6|       6|       1|       9|       0|       0|       4|       4|\n",
      "|       6|       0|       1|       0|       1|       1|       2|       9|       2|       8|\n",
      "|       0|       4|       3|       4|       8|       4|       8|       6|       2|       1|\n",
      "|       1|       1|       8|       2|       6|       2|       1|       5|       1|       4|\n",
      "|       4|       4|       1|       0|       1|       2|       4|       8|       8|       8|\n",
      "|       2|       0|       8|       6|       8|       0|       6|       6|       7|       6|\n",
      "|       1|       4|       0|       9|       8|       6|       6|       0|       2|       9|\n",
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# apply function to 400 columns\n",
    "new_df=df.select([encode_udf(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(1,401,2)])\n",
    "new_df.select([\"V\"+str(i)+\"_enc\" for i in range(381,401,2)]).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:34.398697Z",
     "iopub.status.busy": "2024-05-26T08:17:34.397833Z",
     "iopub.status.idle": "2024-05-26T08:17:35.428421Z",
     "shell.execute_reply": "2024-05-26T08:17:35.427699Z"
    },
    "id": "F-Z8xA6Sazq3",
    "outputId": "ed44c8ff-945b-41ef-e9a1-c29ade410da3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "|V781_enc|V783_enc|V785_enc|V787_enc|V789_enc|V791_enc|V793_enc|V795_enc|V797_enc|V799_enc|\n",
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "|       2|       8|       0|       9|       9|       3|       0|       2|       6|       8|\n",
      "|       0|       9|       3|       5|       9|       9|       0|       1|       5|       9|\n",
      "|       6|       4|       8|       8|       3|       8|       5|       0|       3|       0|\n",
      "|       4|       7|       0|       6|       2|       1|       0|       6|       0|       4|\n",
      "|       3|       3|       7|       6|       8|       8|       6|       4|       0|       6|\n",
      "|       8|       8|       1|       8|       8|       4|       4|       5|       4|       2|\n",
      "|       9|       0|       8|       2|       0|       0|       6|       0|       6|       2|\n",
      "|       1|       2|       5|       6|       6|       9|       2|       7|       6|       0|\n",
      "|       0|       8|       2|       0|       8|       1|       7|       9|       8|       1|\n",
      "|       7|       0|       4|       2|       4|       8|       1|       6|       6|       4|\n",
      "+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# apply function to all odd columns\n",
    "\n",
    "new_df = df.select([encode_udf(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(1,801,2)])\n",
    "\n",
    "new_df.select([\"V\"+str(i)+\"_enc\" for i in range(781,801,2)]).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "imM1VlIGazq4"
   },
   "source": [
    "Now I want to apply different udfs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:35.431986Z",
     "iopub.status.busy": "2024-05-26T08:17:35.431708Z",
     "iopub.status.idle": "2024-05-26T08:17:37.172451Z",
     "shell.execute_reply": "2024-05-26T08:17:37.169840Z"
    },
    "id": "D8jQgRjOazq4",
    "outputId": "8932e2fd-40af-462a-993c-df91f3754059"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+------+------+------+------+--------+--------+--------+--------+--------+--------+\n",
      "|V1_enc|V2_enc|V3_enc|V4_enc|V795_enc|V796_enc|V797_enc|V798_enc|V799_enc|V800_enc|\n",
      "+------+------+------+------+--------+--------+--------+--------+--------+--------+\n",
      "|     6|     2|     0|     4|       2|       4|       6|       4|       8|       8|\n",
      "|     0|     2|     5|     6|       1|       5|       5|       6|       9|       3|\n",
      "|     3|     4|     8|     4|       0|       0|       3|       2|       0|       6|\n",
      "|     4|     4|     9|     2|       6|       4|       0|       4|       4|       8|\n",
      "|     6|     0|     8|     4|       4|       4|       0|       6|       6|       4|\n",
      "|     4|     6|     6|     1|       5|       5|       4|       9|       2|       1|\n",
      "|     1|     2|     8|     1|       0|       4|       6|       8|       2|       4|\n",
      "|     6|     8|     9|     4|       7|       9|       6|       4|       0|       1|\n",
      "|     8|     6|     4|     1|       9|       8|       8|       8|       1|       3|\n",
      "|     1|     1|     6|     4|       6|       6|       6|       3|       4|       8|\n",
      "+------+------+------+------+--------+--------+--------+--------+--------+--------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "o = [\"abcdefghijklmnopqrstuvwxyz\", encodingA]\n",
    "e = [\"abcdefghijklmnopqrstuvwxyz\", encodingB]\n",
    "\n",
    "# define two encoding functions\n",
    "\n",
    "def enc1(*a):\n",
    "    # encode string s with encoding o\n",
    "    s=a[0]\n",
    "    for i in range(len(o[0])):\n",
    "      if s==o[0][i]:\n",
    "          return o[1][i]\n",
    "    return s\n",
    "\n",
    "def enc2(*a):\n",
    "    # encode string s with encoding e\n",
    "    s=a[0]\n",
    "    for i in range(len(e[0])):\n",
    "      if s==e[0][i]:\n",
    "          return e[1][i]\n",
    "    return s\n",
    "\n",
    "# create udfs\n",
    "encode_udf1 = udf(enc1, StringType())\n",
    "encode_udf2 = udf(enc2, StringType())\n",
    "\n",
    "new_df = df.select([encode_udf1(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(1,800,2)]+\n",
    "                  [encode_udf2(\"V\"+str(i)).alias(\"V\"+str(i)+\"_enc\") for i in range(2,801,2)])\n",
    "new_df.select([\"V\"+str(i)+\"_enc\" for i in range(1,5)]+[\"V\"+str(i)+\"_enc\" for i in range(795,801)]).show(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "iG2XLoodazq4"
   },
   "source": [
    "## Export dataframe to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:37.177843Z",
     "iopub.status.busy": "2024-05-26T08:17:37.176305Z",
     "iopub.status.idle": "2024-05-26T08:17:41.745121Z",
     "shell.execute_reply": "2024-05-26T08:17:41.743957Z"
    },
    "id": "JFY5crnEazq4",
    "outputId": "92039bc9-0ff5-4896-fee3-4a8b92c6f8e7"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 17:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "saved out20240526081737.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "import time\n",
    "timestamp = time.strftime(\"%Y%m%d%H%M%S\")\n",
    "new_df.write.csv('out'+timestamp+'.csv', sep=',')\n",
    "print('saved out{}.csv'.format(timestamp))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "14QZQTn8azq4"
   },
   "source": [
    "Save to CSV with headers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:41.748308Z",
     "iopub.status.busy": "2024-05-26T08:17:41.747805Z",
     "iopub.status.idle": "2024-05-26T08:17:45.499015Z",
     "shell.execute_reply": "2024-05-26T08:17:45.498043Z"
    },
    "id": "rVhF6djCazq5",
    "outputId": "d0be0d11-1436-4aa8-a41b-af19fad15ae0"
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "[Stage 18:>                                                         (0 + 1) / 1]\r"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "saved out20240526081741.csv\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\r",
      "                                                                                \r"
     ]
    }
   ],
   "source": [
    "timestamp = time.strftime(\"%Y%m%d%H%M%S\")\n",
    "new_df.write.csv('out'+timestamp+'.csv', sep=',', header = True)\n",
    "print('saved out{}.csv'.format(timestamp))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:45.503157Z",
     "iopub.status.busy": "2024-05-26T08:17:45.502581Z",
     "iopub.status.idle": "2024-05-26T08:17:45.633550Z",
     "shell.execute_reply": "2024-05-26T08:17:45.632917Z"
    },
    "id": "wjpca67Aazq5",
    "outputId": "8b9262ab-74a6-49fd-e206-e594fb4177f1"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "out.txt\r\n",
      "\r\n",
      "out20240526081737.csv:\r\n",
      "_SUCCESS  part-00000-fce2e6a3-a015-4a7e-b6ed-b03a8e7f08e9-c000.csv\r\n",
      "\r\n",
      "out20240526081741.csv:\r\n",
      "_SUCCESS  part-00000-d922bd32-b2a1-4724-9a01-112bfae50ec7-c000.csv\r\n"
     ]
    }
   ],
   "source": [
    "!ls out*"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "rSZ204Itazq4"
   },
   "source": [
    "## Useful commands for checking system resources\n",
    "\n",
    "The `free -h` and `lscpu` commands are useful for retrieving information about system resources in a Linux environment."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "9CjM3Usor83C"
   },
   "source": [
    "The `free -h` command displays information about the system's memory usage in human-readable format. With the `-h` option the command displays sizes in a more human-readable format, using units such as megabytes (MB) and gigabytes (GB) in place of bytes."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:45.636718Z",
     "iopub.status.busy": "2024-05-26T08:17:45.636288Z",
     "iopub.status.idle": "2024-05-26T08:17:45.765147Z",
     "shell.execute_reply": "2024-05-26T08:17:45.764521Z"
    },
    "id": "V7v56l91azq4",
    "outputId": "70a1e01f-b2ce-437a-913e-fee539bc3c04"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "               total        used        free      shared  buff/cache   available\r\n",
      "Mem:            15Gi       2.0Gi       5.9Gi        24Mi       7.7Gi        13Gi\r\n",
      "Swap:          4.0Gi          0B       4.0Gi\r\n"
     ]
    }
   ],
   "source": [
    "!free -h"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "dxImTX0tsm13"
   },
   "source": [
    "The `lscpu` command displays detailed information about the CPU architecture."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "colab": {
     "base_uri": "https://localhost:8080/"
    },
    "execution": {
     "iopub.execute_input": "2024-05-26T08:17:45.768157Z",
     "iopub.status.busy": "2024-05-26T08:17:45.767738Z",
     "iopub.status.idle": "2024-05-26T08:17:45.899130Z",
     "shell.execute_reply": "2024-05-26T08:17:45.898361Z"
    },
    "id": "GgbOX557azq4",
    "outputId": "78a8c906-9ca9-41a5-d0f6-ef2a53ca602a"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Architecture:            x86_64\r\n",
      "  CPU op-mode(s):        32-bit, 64-bit\r\n",
      "  Address sizes:         48 bits physical, 48 bits virtual\r\n",
      "  Byte Order:            Little Endian\r\n",
      "CPU(s):                  4\r\n",
      "  On-line CPU(s) list:   0-3\r\n",
      "Vendor ID:               AuthenticAMD\r\n",
      "  Model name:            AMD EPYC 7763 64-Core Processor\r\n",
      "    CPU family:          25\r\n",
      "    Model:               1\r\n",
      "    Thread(s) per core:  2\r\n",
      "    Core(s) per socket:  2\r\n",
      "    Socket(s):           1\r\n",
      "    Stepping:            1\r\n",
      "    BogoMIPS:            4890.86\r\n",
      "    Flags:               fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mc\r\n",
      "                         a cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall n\r\n",
      "                         x mmxext fxsr_opt pdpe1gb rdtscp lm constant_tsc rep_go\r\n",
      "                         od nopl tsc_reliable nonstop_tsc cpuid extd_apicid aper\r\n",
      "                         fmperf pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 \r\n",
      "                         movbe popcnt aes xsave avx f16c rdrand hypervisor lahf_\r\n",
      "                         lm cmp_legacy svm cr8_legacy abm sse4a misalignsse 3dno\r\n",
      "                         wprefetch osvw topoext invpcid_single vmmcall fsgsbase \r\n",
      "                         bmi1 avx2 smep bmi2 erms invpcid rdseed adx smap clflus\r\n",
      "                         hopt clwb sha_ni xsaveopt xsavec xgetbv1 xsaves clzero \r\n",
      "                         xsaveerptr rdpru arat npt nrip_save tsc_scale vmcb_clea\r\n",
      "                         n flushbyasid decodeassists pausefilter pfthreshold v_v\r\n",
      "                         msave_vmload umip vaes vpclmulqdq rdpid fsrm\r\n",
      "Virtualization features: \r\n",
      "  Virtualization:        AMD-V\r\n",
      "  Hypervisor vendor:     Microsoft\r\n",
      "  Virtualization type:   full\r\n",
      "Caches (sum of all):     \r\n",
      "  L1d:                   64 KiB (2 instances)\r\n",
      "  L1i:                   64 KiB (2 instances)\r\n",
      "  L2:                    1 MiB (2 instances)\r\n",
      "  L3:                    32 MiB (1 instance)\r\n",
      "NUMA:                    \r\n",
      "  NUMA node(s):          1\r\n",
      "  NUMA node0 CPU(s):     0-3\r\n",
      "Vulnerabilities:         \r\n",
      "  Gather data sampling:  Not affected\r\n",
      "  Itlb multihit:         Not affected\r\n",
      "  L1tf:                  Not affected\r\n",
      "  Mds:                   Not affected\r\n",
      "  Meltdown:              Not affected\r\n",
      "  Mmio stale data:       Not affected\r\n",
      "  Retbleed:              Not affected\r\n",
      "  Spec rstack overflow:  Vulnerable: Safe RET, no microcode\r\n",
      "  Spec store bypass:     Vulnerable\r\n",
      "  Spectre v1:            Mitigation; usercopy/swapgs barriers and __user pointer\r\n",
      "                          sanitization\r\n",
      "  Spectre v2:            Mitigation; Retpolines; STIBP disabled; RSB filling; PB\r\n",
      "                         RSB-eIBRS Not affected; BHI Not affected\r\n",
      "  Srbds:                 Not affected\r\n",
      "  Tsx async abort:       Not affected\r\n"
     ]
    }
   ],
   "source": [
    "!lscpu"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "id": "ddxpdot4tSlj"
   },
   "source": [
    "In the context of distributed computing, specific values provided by the lscpu command are of particular interest:\n",
    "\n",
    "*   the number of CPUs\n",
    "*   cores per socket\n",
    "*   threads per core\n",
    "*   sockets\n",
    "\n",
    "Understanding these parameters is crucial for assessing the system's potential parallelism.\n",
    "\n",
    "Sockets represents the number of physical processors. Each processor can have one or more cores and each core can execute one or two threads concurrently.\n",
    "\n",
    "Finally, the number of CPUs indicates the total count of independent processing units within each CPU. This is the theoretical upper limit on the number of tasks that can be executed concurrently, offering valuable information for maximizing computational efficiency in distributed computing scenarios.\n",
    "\n",
    "For instance, if you have\n",
    "\n",
    "```\n",
    "Thread(s) per core:    2\n",
    "Core(s) per socket:    4\n",
    "Socket(s):             1\n",
    "```\n",
    "\n",
    "then the total number of independent processing units is\n",
    "\n",
    "$$ 1 × 4 × 2 = 8$$\n",
    "\n",
    "See also: [How many physical CPUs does my machine have?](https://superuser.com/questions/1691479/how-many-physical-cpus-does-my-machine-have).\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "colab": {
   "include_colab_link": true,
   "provenance": [],
   "toc_visible": true
  },
  "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.8.18"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}