dataframe-session-2020-11-04.txt

$ cat /tmp/emps_no_header.txt
1001,alex,67000,SALES
1002,bob,24000,SALES
1003,boby,24000,SALES
1004,jane,69000,SOFTWARE
1005,betty,55000,SOFTWARE
1006,jeff,59000,SOFTWARE
1007,dara,72000,SOFTWARE
1001,al,69000,SALES
1002,bobby,24900,BUSINESS

$ ./bin/pyspark
Python 3.7.2 (v3.7.2:9a3ffc0492, Dec 24 2018, 02:44:43)
[Clang 6.0 (clang-600.0.57)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /__ / .__/\_,_/_/ /_/\_\   version 3.0.0
      /_/

Using Python version 3.7.2 (v3.7.2:9a3ffc0492, Dec 24 2018 02:44:43)
SparkSession available as 'spark'.
>>> input_path = '/tmp/emps_no_header.txt'
>>> df  = spark.read.csv(input_path)
>>> df.show()
+----+-----+-----+--------+
| _c0|  _c1|  _c2|     _c3|
+----+-----+-----+--------+
|1001| alex|67000|   SALES|
|1002|  bob|24000|   SALES|
|1003| boby|24000|   SALES|
|1004| jane|69000|SOFTWARE|
|1005|betty|55000|SOFTWARE|
|1006| jeff|59000|SOFTWARE|
|1007| dara|72000|SOFTWARE|
|1001|   al|69000|   SALES|
|1002|bobby|24900|BUSINESS|
+----+-----+-----+--------+

>>> df.count()
9
>>> df.printSchema()
root
 |-- _c0: string (nullable = true)
 |-- _c1: string (nullable = true)
 |-- _c2: string (nullable = true)
 |-- _c3: string (nullable = true)

>>> df2 = df.selectExpr("_c0 as id", "_c1 as name", "_c2 as salary", "_c3 as dept")
>>> df2.show()
+----+-----+------+--------+
|  id| name|salary|    dept|
+----+-----+------+--------+
|1001| alex| 67000|   SALES|
|1002|  bob| 24000|   SALES|
|1003| boby| 24000|   SALES|
|1004| jane| 69000|SOFTWARE|
|1005|betty| 55000|SOFTWARE|
|1006| jeff| 59000|SOFTWARE|
|1007| dara| 72000|SOFTWARE|
|1001|   al| 69000|   SALES|
|1002|bobby| 24900|BUSINESS|
+----+-----+------+--------+

>>> df2.createOrReplaceTempView("emp_table")
>>> df3 = spark.sql("SELECT * FROM emp_table WHERE id > 1002")
>>> df3.show()
+----+-----+------+--------+
|  id| name|salary|    dept|
+----+-----+------+--------+
|1003| boby| 24000|   SALES|
|1004| jane| 69000|SOFTWARE|
|1005|betty| 55000|SOFTWARE|
|1006| jeff| 59000|SOFTWARE|
|1007| dara| 72000|SOFTWARE|
+----+-----+------+--------+

>>> df3.printSchema()
root
 |-- id: string (nullable = true)
 |-- name: string (nullable = true)
 |-- salary: string (nullable = true)
 |-- dept: string (nullable = true)

>>> df4 = df2.filter(df2.id > 1002)
>>> df4.show()
+----+-----+------+--------+
|  id| name|salary|    dept|
+----+-----+------+--------+
|1003| boby| 24000|   SALES|
|1004| jane| 69000|SOFTWARE|
|1005|betty| 55000|SOFTWARE|
|1006| jeff| 59000|SOFTWARE|
|1007| dara| 72000|SOFTWARE|
+----+-----+------+--------+

>>> df5 = spark.sql("SELECT id, salary  FROM emp_table WHERE id > 1002")
>>> df5.show()
+----+------+
|  id|salary|
+----+------+
|1003| 24000|
|1004| 69000|
|1005| 55000|
|1006| 59000|
|1007| 72000|
+----+------+

>>>
>>> df6 = spark.sql("SELECT name, salary FROM emp_table WHERE salary > 55000 ORDER BY salary")
>>> df6.show()
+----+------+
|name|salary|
+----+------+
|jeff| 59000|
|alex| 67000|
|jane| 69000|
|  al| 69000|
|dara| 72000|
+----+------+

>>> df6 = spark.sql("SELECT name, salary FROM emp_table WHERE salary > 55000 ORDER BY salary DESC")
>>> df6.show()
+----+------+
|name|salary|
+----+------+
|dara| 72000|
|  al| 69000|
|jane| 69000|
|alex| 67000|
|jeff| 59000|
+----+------+

>>> df7 = spark.sql("SELECT dept, COUNT(*) as count FROM emp_table GROUP BY dept")
>>> df7.show()
+--------+-----+
|    dept|count|
+--------+-----+
|   SALES|    4|
|BUSINESS|    1|
|SOFTWARE|    4|
+--------+-----+

>>>