0_1_Reading csv.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 71,
   "id": "3e05dd10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing \n",
    "from pyspark.sql import SparkSession\n",
    "import pyspark.sql.functions as sql_func\n",
    "from pyspark.ml.feature import Imputer"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "8660d218",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating session\n",
    "spark = SparkSession.builder.appName(\"Dataframe\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "id": "d721bab4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reading csv\n",
    "spark_df = spark.read.csv(\"./data/sample.csv\", header=True, inferSchema=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "fdacad7d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Name', 'Age', 'City']"
      ]
     },
     "execution_count": 74,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Column names\n",
    "spark_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "a9271b32",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+---+--------------+\n",
      "|           Name|Age|          City|\n",
      "+---------------+---+--------------+\n",
      "|       John Doe| 25|      New York|\n",
      "|     Jane Smith| 30|   Los Angeles|\n",
      "|Michael Johnson| 35|       Chicago|\n",
      "|    Emily Brown| 28| San Francisco|\n",
      "|       John Doe| 25|      New York|\n",
      "|     Jane Smith| 30|              |\n",
      "|Michael Johnson|   |       Chicago|\n",
      "|           null| 28| San Francisco|\n",
      "+---------------+---+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "1d52fae4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(Name='John Doe', Age=' 25', City=' New York'),\n",
       " Row(Name='Jane Smith', Age=' 30', City=' Los Angeles')]"
      ]
     },
     "execution_count": 76,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark_df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "b12bbe8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Name: string (nullable = true)\n",
      " |-- Age: string (nullable = true)\n",
      " |-- City: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# info\n",
    "spark_df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "fd77ff83",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+-------+---------------+------------------+--------------+\n",
      "|summary|           Name|               Age|          City|\n",
      "+-------+---------------+------------------+--------------+\n",
      "|  count|              7|                 8|             8|\n",
      "|   mean|           null|28.714285714285715|          null|\n",
      "| stddev|           null| 3.450327796711771|          null|\n",
      "|    min|    Emily Brown|                  |              |\n",
      "|    max|Michael Johnson|                35| San Francisco|\n",
      "+-------+---------------+------------------+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Describe\n",
    "spark_df.describe().show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "12521607",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+\n",
      "|           Name|\n",
      "+---------------+\n",
      "|       John Doe|\n",
      "|     Jane Smith|\n",
      "|Michael Johnson|\n",
      "|    Emily Brown|\n",
      "|       John Doe|\n",
      "|     Jane Smith|\n",
      "|Michael Johnson|\n",
      "|           null|\n",
      "+---------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Selecting a column\n",
    "spark_df.select(\"Name\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "id": "48041811",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "8"
      ]
     },
     "execution_count": 80,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "spark_df.count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "06c8d38d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+----+\n",
      "|Name|Age|City|\n",
      "+----+---+----+\n",
      "|   1|  0|   0|\n",
      "+----+---+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Checking na values\n",
    "\n",
    "na_counts = spark_df.select([sql_func.sum(sql_func.col(column).isNull().cast(\"integer\")).alias(column) for column in spark_df.columns])\n",
    "na_counts.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "cf9743fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Converting spaces to na\n",
    "for column in spark_df.columns:\n",
    "    spark_df = spark_df.withColumn(column, sql_func.when(sql_func.col(column) == \" \", None).otherwise(sql_func.col(column)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "f67873fe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|   Los Angeles|\n",
      "|Michael Johnson|  35|       Chicago|\n",
      "|    Emily Brown|  28| San Francisco|\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|          null|\n",
      "|Michael Johnson|null|       Chicago|\n",
      "|           null|  28| San Francisco|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "cc02d836",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+---+----+\n",
      "|Name|Age|City|\n",
      "+----+---+----+\n",
      "|   1|  1|   1|\n",
      "+----+---+----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df.select([sql_func.sum(sql_func.col(column).isNull().cast(\"integer\")).alias(column) for column in spark_df.columns]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "dc6b535e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filtering na values\n",
    "spark_df_notna = spark_df.filter(sql_func.col(\"Name\").isNotNull() & sql_func.col(\"City\").isNotNull())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "276c0fd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|   Los Angeles|\n",
      "|Michael Johnson|  35|       Chicago|\n",
      "|    Emily Brown|  28| San Francisco|\n",
      "|       John Doe|  25|      New York|\n",
      "|Michael Johnson|null|       Chicago|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_notna.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "40d4dc67",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|   Los Angeles|\n",
      "|Michael Johnson|  35|       Chicago|\n",
      "|    Emily Brown|  28| San Francisco|\n",
      "|       John Doe|  25|      New York|\n",
      "|Michael Johnson|null|       Chicago|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df.filter(sql_func.col(\"Name\").isNotNull() & sql_func.col(\"City\").isNotNull()).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "78387545",
   "metadata": {},
   "outputs": [],
   "source": [
    "names = spark_df.select(\"Name\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "ca1fac2a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Adding a column using other column\n",
    "spark_df_2 = spark_df.withColumn(\"Age_1\", spark_df[\"Age\"]+2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "66a6d312",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+-----+\n",
      "|           Name| Age|          City|Age_1|\n",
      "+---------------+----+--------------+-----+\n",
      "|       John Doe|  25|      New York| 27.0|\n",
      "|     Jane Smith|  30|   Los Angeles| 32.0|\n",
      "|Michael Johnson|  35|       Chicago| 37.0|\n",
      "|    Emily Brown|  28| San Francisco| 30.0|\n",
      "|       John Doe|  25|      New York| 27.0|\n",
      "|     Jane Smith|  30|          null| 32.0|\n",
      "|Michael Johnson|null|       Chicago| null|\n",
      "|           null|  28| San Francisco| 30.0|\n",
      "+---------------+----+--------------+-----+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "e5bf022b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Name: string (nullable = true)\n",
      " |-- Age: string (nullable = true)\n",
      " |-- City: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_notna.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "e1e396c3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|   Los Angeles|\n",
      "|Michael Johnson|  35|       Chicago|\n",
      "|    Emily Brown|  28| San Francisco|\n",
      "|       John Doe|  25|      New York|\n",
      "|Michael Johnson|null|       Chicago|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# Converting Age to int\n",
    "spark_df_age_int = spark_df_notna.withColumn(\"Age\", sql_func.col(\"Age\").cast(\"integer\"))\n",
    "spark_df_age_int.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "id": "703b508c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'mean'"
      ]
     },
     "execution_count": 95,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Filling na with mean\n",
    "imputer = Imputer()\n",
    "imputer.getStrategy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "id": "3d15cb35",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Imputer_ead73a349298"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "imputer.setInputCol(\"Age\")\n",
    "imputer.setOutputCol(\"Age\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "id": "fda95321",
   "metadata": {},
   "outputs": [],
   "source": [
    "imputer_model = imputer.fit(spark_df_age_int)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "id": "d22e235e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+---+--------------+\n",
      "|           Name|Age|          City|\n",
      "+---------------+---+--------------+\n",
      "|       John Doe| 25|      New York|\n",
      "|     Jane Smith| 30|   Los Angeles|\n",
      "|Michael Johnson| 35|       Chicago|\n",
      "|    Emily Brown| 28| San Francisco|\n",
      "|       John Doe| 25|      New York|\n",
      "|Michael Johnson| 28|       Chicago|\n",
      "+---------------+---+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_mean_age = imputer_model.transform(spark_df_age_int).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "id": "ab5223cd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|  25|      New York|\n",
      "|     Jane Smith|  30|   Los Angeles|\n",
      "|Michael Johnson|  35|       Chicago|\n",
      "|    Emily Brown|  28| San Francisco|\n",
      "|       John Doe|  25|      New York|\n",
      "|Michael Johnson|null|       Chicago|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_age_int.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "id": "615bcf40",
   "metadata": {},
   "outputs": [],
   "source": [
    "name = spark_df.select([\"Name\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "id": "35b6e98f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Concatinating string to column\n",
    "spark_df_2 = spark_df.withColumn(\"Name_1\", sql_func.concat(spark_df[\"Name\"], sql_func.lit(\"_1\")))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "id": "cab6a778",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+-----------------+\n",
      "|           Name| Age|          City|           Name_1|\n",
      "+---------------+----+--------------+-----------------+\n",
      "|       John Doe|  25|      New York|       John Doe_1|\n",
      "|     Jane Smith|  30|   Los Angeles|     Jane Smith_1|\n",
      "|Michael Johnson|  35|       Chicago|Michael Johnson_1|\n",
      "|    Emily Brown|  28| San Francisco|    Emily Brown_1|\n",
      "|       John Doe|  25|      New York|       John Doe_1|\n",
      "|     Jane Smith|  30|          null|     Jane Smith_1|\n",
      "|Michael Johnson|null|       Chicago|Michael Johnson_1|\n",
      "|           null|  28| San Francisco|             null|\n",
      "+---------------+----+--------------+-----------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_2.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "47e0a163",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------+----+--------------+\n",
      "|           Name| Age|          City|\n",
      "+---------------+----+--------------+\n",
      "|       John Doe|25.0|      New York|\n",
      "|     Jane Smith|30.0|   Los Angeles|\n",
      "|Michael Johnson|35.0|       Chicago|\n",
      "|    Emily Brown|28.0| San Francisco|\n",
      "+---------------+----+--------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "spark_df_2.drop(\"Name_1\", \"Age_1\").show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0890a119",
   "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.10.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}