spark_sql-readingcsvfile.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "005bbbdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "findspark.init()\n",
    "from pyspark.sql import SparkSession\n",
    "spark=SparkSession.builder.appName('casewhen').master('local[1]').getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "3d141a51",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- _c0: string (nullable = true)\n",
      " |-- _c1: string (nullable = true)\n",
      " |-- _c2: string (nullable = true)\n",
      " |-- _c3: string (nullable = true)\n",
      " |-- _c4: string (nullable = true)\n",
      " |-- _c5: string (nullable = true)\n",
      " |-- _c6: string (nullable = true)\n",
      " |-- _c7: string (nullable = true)\n",
      " |-- _c8: string (nullable = true)\n",
      " |-- _c9: string (nullable = true)\n",
      "\n",
      "+----+--------------------+--------------------+--------------------+------------------+-------------+--------------------+--------------------+---------+--------------------+\n",
      "| _c0|                 _c1|                 _c2|                 _c3|               _c4|          _c5|                 _c6|                 _c7|      _c8|                 _c9|\n",
      "+----+--------------------+--------------------+--------------------+------------------+-------------+--------------------+--------------------+---------+--------------------+\n",
      "|Year|Industry_aggregat...|Industry_code_NZSIOC|Industry_name_NZSIOC|             Units|Variable_code|       Variable_name|   Variable_category|    Value|Industry_code_ANZ...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H01|        Total income|Financial perform...|  733,258|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H04|Sales, government...|Financial perform...|  660,630|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H05|Interest, dividen...|Financial perform...|   54,342|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H07|Non-operating income|Financial perform...|   18,285|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H08|   Total expenditure|Financial perform...|  654,872|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H09|Interest and dona...|Financial perform...|   32,730|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H10|      Indirect taxes|Financial perform...|    7,509|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H11|        Depreciation|Financial perform...|   26,821|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H12|Salaries and wage...|Financial perform...|  119,387|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H13|Redundancy and se...|Financial perform...|      305|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H14|Salaries and wage...|Financial perform...|    1,756|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H19|Purchases and oth...|Financial perform...|  450,394|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H20|Non-operating exp...|Financial perform...|   16,274|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H21|      Opening stocks|Financial perform...|   68,109|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H22|      Closing stocks|Financial perform...|   69,123|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H23|Surplus before in...|Financial perform...|   79,400|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H24|        Total assets|  Financial position|2,396,143|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H25|      Current assets|  Financial position|  665,643|ANZSIC06 division...|\n",
      "|2020|             Level 1|               99999|      All industries|Dollars (millions)|          H26|Fixed tangible as...|  Financial position|  562,646|ANZSIC06 division...|\n",
      "+----+--------------------+--------------------+--------------------+------------------+-------------+--------------------+--------------------+---------+--------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df=spark.read.csv('C:\\\\Users\\\\Raju\\\\Documents\\\\Survey-2020.csv')\n",
    "df.printSchema()\n",
    "df.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "abf8e55d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- Year: string (nullable = true)\n",
      " |-- Industry_aggregation_NZSIOC: string (nullable = true)\n",
      " |-- Industry_code_NZSIOC: string (nullable = true)\n",
      " |-- Industry_name_NZSIOC: string (nullable = true)\n",
      " |-- Units: string (nullable = true)\n",
      " |-- Variable_code: string (nullable = true)\n",
      " |-- Variable_name: string (nullable = true)\n",
      " |-- Variable_category: string (nullable = true)\n",
      " |-- Value: string (nullable = true)\n",
      " |-- Industry_code_ANZSIC06: string (nullable = true)\n",
      "\n",
      "+----+---------------------------+--------------------+--------------------+------------------+-------------+-----------------------------------------------------+---------------------+---------+----------------------------------------------------------------------------------------------------------------+\n",
      "|Year|Industry_aggregation_NZSIOC|Industry_code_NZSIOC|Industry_name_NZSIOC|Units             |Variable_code|Variable_name                                        |Variable_category    |Value    |Industry_code_ANZSIC06                                                                                          |\n",
      "+----+---------------------------+--------------------+--------------------+------------------+-------------+-----------------------------------------------------+---------------------+---------+----------------------------------------------------------------------------------------------------------------+\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H01          |Total income                                         |Financial performance|733,258  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H04          |Sales, government funding, grants and subsidies      |Financial performance|660,630  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H05          |Interest, dividends and donations                    |Financial performance|54,342   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H07          |Non-operating income                                 |Financial performance|18,285   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H08          |Total expenditure                                    |Financial performance|654,872  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H09          |Interest and donations                               |Financial performance|32,730   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H10          |Indirect taxes                                       |Financial performance|7,509    |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H11          |Depreciation                                         |Financial performance|26,821   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H12          |Salaries and wages paid                              |Financial performance|119,387  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H13          |Redundancy and severance                             |Financial performance|305      |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H14          |Salaries and wages to self employed commission agents|Financial performance|1,756    |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H19          |Purchases and other operating expenses               |Financial performance|450,394  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H20          |Non-operating expenses                               |Financial performance|16,274   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H21          |Opening stocks                                       |Financial performance|68,109   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H22          |Closing stocks                                       |Financial performance|69,123   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H23          |Surplus before income tax                            |Financial performance|79,400   |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H24          |Total assets                                         |Financial position   |2,396,143|ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H25          |Current assets                                       |Financial position   |665,643  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H26          |Fixed tangible assets                                |Financial position   |562,646  |ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "|2020|Level 1                    |99999               |All industries      |Dollars (millions)|H29          |Other assets                                         |Financial position   |1,167,854|ANZSIC06 divisions A-S (excluding classes K6330, L6711, O7552, O760, O771, O772, S9540, S9601, S9602, and S9603)|\n",
      "+----+---------------------------+--------------------+--------------------+------------------+-------------+-----------------------------------------------------+---------------------+---------+----------------------------------------------------------------------------------------------------------------+\n",
      "only showing top 20 rows\n",
      "\n",
      "<class 'pyspark.sql.dataframe.DataFrame'>\n"
     ]
    }
   ],
   "source": [
    "df1=spark.read.format('csv').option('header','true').load('C:\\\\Users\\\\Raju\\\\Documents\\\\Survey-2020.csv')\n",
    "df1.printSchema()\n",
    "df1.show(truncate=0)\n",
    "print(type(df1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "f54ea4bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "text(csv,pipe delimited,tab delimited)\n",
      "avro\n",
      "parquet-columnar storage(compression)\n",
      "orc-optimised row columnar format(update/delete)\n",
      "json\n"
     ]
    }
   ],
   "source": [
    "#file formats\n",
    "print('''text(csv,pipe delimited,tab delimited)\n",
    "avro\n",
    "parquet-columnar storage(compression)\n",
    "orc-optimised row columnar format(update/delete)\n",
    "json''')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f6582dc0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----+-------------+--------------------+---------------------+\n",
      "|year|variable_code|Industry_name_NZSIOC|variable_category    |\n",
      "+----+-------------+--------------------+---------------------+\n",
      "|2020|H01          |All industries      |Financial performance|\n",
      "|2020|H04          |All industries      |Financial performance|\n",
      "|2020|H05          |All industries      |Financial performance|\n",
      "|2020|H07          |All industries      |Financial performance|\n",
      "|2020|H08          |All industries      |Financial performance|\n",
      "|2020|H09          |All industries      |Financial performance|\n",
      "|2020|H10          |All industries      |Financial performance|\n",
      "|2020|H11          |All industries      |Financial performance|\n",
      "|2020|H12          |All industries      |Financial performance|\n",
      "|2020|H13          |All industries      |Financial performance|\n",
      "|2020|H14          |All industries      |Financial performance|\n",
      "|2020|H19          |All industries      |Financial performance|\n",
      "|2020|H20          |All industries      |Financial performance|\n",
      "|2020|H21          |All industries      |Financial performance|\n",
      "|2020|H22          |All industries      |Financial performance|\n",
      "|2020|H23          |All industries      |Financial performance|\n",
      "|2020|H24          |All industries      |Financial position   |\n",
      "|2020|H25          |All industries      |Financial position   |\n",
      "|2020|H26          |All industries      |Financial position   |\n",
      "|2020|H29          |All industries      |Financial position   |\n",
      "+----+-------------+--------------------+---------------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from pyspark.sql.functions import col\n",
    "dfselect=df1.select(col('year'),col('variable_code'),col('Industry_name_NZSIOC'),col('variable_category'))\n",
    "dfselect.show(truncate=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "37b8550e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+\n",
      "|variable_category    |\n",
      "+---------------------+\n",
      "|Financial performance|\n",
      "|Financial position   |\n",
      "|Financial ratios     |\n",
      "+---------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfVarCategories=dfselect.select(col('variable_category')).distinct()\n",
    "dfVarCategories.show(truncate=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "1702a8e8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- year: string (nullable = true)\n",
      " |-- variable_code: string (nullable = true)\n",
      " |-- Industry_name_NZSIOC: string (nullable = true)\n",
      " |-- variable_category: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfselect.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "a791bb53",
   "metadata": {},
   "outputs": [],
   "source": [
    "dfcast=dfselect.withColumn('year',col('year').cast('Integer'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8bce6763",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------------------+-----------+\n",
      "|Variable_category    |Total_Value|\n",
      "+---------------------+-----------+\n",
      "|Financial performance|37377844   |\n",
      "|Financial position   |21407164   |\n",
      "|Financial ratios     |15986812   |\n",
      "+---------------------+-----------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "dfcast.groupBy(col('Variable_category')).sum('year') \\\n",
    "     .select(col('Variable_category'),col('sum(year)').alias('Total_Value')).show(truncate=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "0bb4f991",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+--------------------+---+---------+------+--------+--------------------+\n",
      "|address             |age|firstName|gender|lastName|phoneNumbers        |\n",
      "+--------------------+---+---------+------+--------+--------------------+\n",
      "|{San Diego, CA, 101}|28 |Joe      |male  |Jackson |[{7349282382, home}]|\n",
      "+--------------------+---+---------+------+--------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_json=spark.read.json('C:\\\\Users\\\\Raju\\\\Desktop\\\\sample.json',multiLine=True)\n",
    "df_json.show(truncate=0)\n",
    "d=df_json.drop('gender')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ee312bd0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+--------------------+\n",
      "|firstName|phoneNumbers        |\n",
      "+---------+--------------------+\n",
      "|Joe      |[{7349282382, home}]|\n",
      "+---------+--------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df=d.drop('age','lastName','address')\n",
    "df.show(truncate=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "bf58f836",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "root\n",
      " |-- firstName: string (nullable = true)\n",
      " |-- phoneNumbers: array (nullable = true)\n",
      " |    |-- element: struct (containsNull = true)\n",
      " |    |    |-- number: string (nullable = true)\n",
      " |    |    |-- type: string (nullable = true)\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df.printSchema()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cf54bfa6",
   "metadata": {},
   "outputs": [],
   "source": [
    "from pyspark.sql.functions import explode\n",
    "df_f=df.withColumn('phoneNumbers_flat',explode('phoneNumbers'))\n",
    "df_f=df_f.drop('phoneNumbers')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "7664d618",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+---------+------------------+\n",
      "|firstName| phoneNumbers_flat|\n",
      "+---------+------------------+\n",
      "|      Joe|{7349282382, home}|\n",
      "+---------+------------------+\n",
      "\n"
     ]
    }
   ],
   "source": [
    "df_f.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ae70028e",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}