Transformaciones III

In [1]:

# Spark Session
from pyspark.sql import SparkSession

spark = (
    SparkSession
    .builder
    .appName("Basic Transformation - II")
    .master("local[*]")
    .getOrCreate()
)

spark

# Spark Session from pyspark.sql import SparkSession spark = ( SparkSession .builder .appName("Basic Transformation - II") .master("local[*]") .getOrCreate() ) spark

Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
25/02/15 09:05:43 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
25/02/15 09:05:44 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
25/02/15 09:05:44 WARN Utils: Service 'SparkUI' could not bind on port 4041. Attempting port 4042.
25/02/15 09:05:44 WARN Utils: Service 'SparkUI' could not bind on port 4042. Attempting port 4043.

Out[1]:

SparkSession - in-memory

SparkContext

Spark UI

Version

v3.5.4

Master

local[*]

AppName

Basic Transformation - II

In [2]:

# Emp Data & Schema

emp_data = [
    ["001","101","John Doe","30","Male","50000","2015-01-01"],
    ["002","101","Jane Smith","25","Female","45000","2016-02-15"],
    ["003","102","Bob Brown","35","Male","55000","2014-05-01"],
    ["004","102","Alice Lee","28","Female","48000","2017-09-30"],
    ["005","103","Jack Chan","40","Male","60000","2013-04-01"],
    ["006","103","Jill Wong","32","Female","52000","2018-07-01"],
    ["007","101","James Johnson","42","Male","70000","2012-03-15"],
    ["008","102","Kate Kim","29","Female","51000","2019-10-01"],
    ["009","103","Tom Tan","33","Male","58000","2016-06-01"],
    ["010","104","Lisa Lee","27","Female","47000","2018-08-01"],
    ["011","104","David Park","38","Male","65000","2015-11-01"],
    ["012","105","Susan Chen","31","Female","54000","2017-02-15"],
    ["013","106","Brian Kim","45","Male","75000","2011-07-01"],
    ["014","107","Emily Lee","26","Female","46000","2019-01-01"],
    ["015","106","Michael Lee","37","Male","63000","2014-09-30"],
    ["016","107","Kelly Zhang","30","Female","49000","2018-04-01"],
    ["017","105","George Wang","34","Male","57000","2016-03-15"],
    ["018","104","Nancy Liu","29","Female","50000","2017-06-01"],
    ["019","103","Steven Chen","36","Male","62000","2015-08-01"],
    ["020","102","Grace Kim","32","Female","53000","2018-11-01"]
]

emp_schema = "employee_id string, department_id string, name string, age string, gender string, salary string, hire_date string"

# Emp Data & Schema emp_data = [ ["001","101","John Doe","30","Male","50000","2015-01-01"], ["002","101","Jane Smith","25","Female","45000","2016-02-15"], ["003","102","Bob Brown","35","Male","55000","2014-05-01"], ["004","102","Alice Lee","28","Female","48000","2017-09-30"], ["005","103","Jack Chan","40","Male","60000","2013-04-01"], ["006","103","Jill Wong","32","Female","52000","2018-07-01"], ["007","101","James Johnson","42","Male","70000","2012-03-15"], ["008","102","Kate Kim","29","Female","51000","2019-10-01"], ["009","103","Tom Tan","33","Male","58000","2016-06-01"], ["010","104","Lisa Lee","27","Female","47000","2018-08-01"], ["011","104","David Park","38","Male","65000","2015-11-01"], ["012","105","Susan Chen","31","Female","54000","2017-02-15"], ["013","106","Brian Kim","45","Male","75000","2011-07-01"], ["014","107","Emily Lee","26","Female","46000","2019-01-01"], ["015","106","Michael Lee","37","Male","63000","2014-09-30"], ["016","107","Kelly Zhang","30","Female","49000","2018-04-01"], ["017","105","George Wang","34","Male","57000","2016-03-15"], ["018","104","Nancy Liu","29","Female","50000","2017-06-01"], ["019","103","Steven Chen","36","Male","62000","2015-08-01"], ["020","102","Grace Kim","32","Female","53000","2018-11-01"] ] emp_schema = "employee_id string, department_id string, name string, age string, gender string, salary string, hire_date string"

In [3]:

# Create emp DataFrame

emp = spark.createDataFrame(data=emp_data, schema=emp_schema)

# Create emp DataFrame emp = spark.createDataFrame(data=emp_data, schema=emp_schema)

In [4]:

emp.printSchema()

emp.printSchema()

root
 |-- employee_id: string (nullable = true)
 |-- department_id: string (nullable = true)
 |-- name: string (nullable = true)
 |-- age: string (nullable = true)
 |-- gender: string (nullable = true)
 |-- salary: string (nullable = true)
 |-- hire_date: string (nullable = true)

In [5]:

from pyspark.sql.functions import col, cast

emp_casted = emp.select("employee_id", "name", "age", col("salary").cast("double"))

from pyspark.sql.functions import col, cast emp_casted = emp.select("employee_id", "name", "age", col("salary").cast("double"))

In [6]:

# Adding Columns
# select employee_id, name, age, salary, (salary * 0.2) as tax from emp_casted

emp_taxed = emp_casted.withColumn("tax", col("salary") * 0.2)

# Adding Columns # select employee_id, name, age, salary, (salary * 0.2) as tax from emp_casted emp_taxed = emp_casted.withColumn("tax", col("salary") * 0.2)

In [7]:

# Literals
# select employee_id, name, age, salary, tax, 1 as columnOne, 'two' as columnTwo from emp_taxed
from pyspark.sql.functions import lit

emp_new_cols = emp_taxed.withColumn("columnOne", lit(1)).withColumn("columnTwo", lit('two'))

# Literals # select employee_id, name, age, salary, tax, 1 as columnOne, 'two' as columnTwo from emp_taxed from pyspark.sql.functions import lit emp_new_cols = emp_taxed.withColumn("columnOne", lit(1)).withColumn("columnTwo", lit('two'))

In [8]:

emp_new_cols.show()

emp_new_cols.show()

+-----------+-------------+---+-------+-------+---------+---------+
|employee_id|         name|age| salary|    tax|columnOne|columnTwo|
+-----------+-------------+---+-------+-------+---------+---------+
|        001|     John Doe| 30|50000.0|10000.0|        1|      two|
|        002|   Jane Smith| 25|45000.0| 9000.0|        1|      two|
|        003|    Bob Brown| 35|55000.0|11000.0|        1|      two|
|        004|    Alice Lee| 28|48000.0| 9600.0|        1|      two|
|        005|    Jack Chan| 40|60000.0|12000.0|        1|      two|
|        006|    Jill Wong| 32|52000.0|10400.0|        1|      two|
|        007|James Johnson| 42|70000.0|14000.0|        1|      two|
|        008|     Kate Kim| 29|51000.0|10200.0|        1|      two|
|        009|      Tom Tan| 33|58000.0|11600.0|        1|      two|
|        010|     Lisa Lee| 27|47000.0| 9400.0|        1|      two|
|        011|   David Park| 38|65000.0|13000.0|        1|      two|
|        012|   Susan Chen| 31|54000.0|10800.0|        1|      two|
|        013|    Brian Kim| 45|75000.0|15000.0|        1|      two|
|        014|    Emily Lee| 26|46000.0| 9200.0|        1|      two|
|        015|  Michael Lee| 37|63000.0|12600.0|        1|      two|
|        016|  Kelly Zhang| 30|49000.0| 9800.0|        1|      two|
|        017|  George Wang| 34|57000.0|11400.0|        1|      two|
|        018|    Nancy Liu| 29|50000.0|10000.0|        1|      two|
|        019|  Steven Chen| 36|62000.0|12400.0|        1|      two|
|        020|    Grace Kim| 32|53000.0|10600.0|        1|      two|
+-----------+-------------+---+-------+-------+---------+---------+

In [9]:

# Column names with Spaces
# select employee_id as emp_id, name, age, salary, tax, columnOne, columnTwo as `Column Two` from emp_new_cols

emp_2 = emp_new_cols.withColumnRenamed("columnTwo", "Column Two")

# Column names with Spaces # select employee_id as emp_id, name, age, salary, tax, columnOne, columnTwo as `Column Two` from emp_new_cols emp_2 = emp_new_cols.withColumnRenamed("columnTwo", "Column Two")

In [10]:

# Filter data 
# select employee_id as emp_id, name, age, salary, tax, columnOne from emp_col_dropped where tax > 1000

emp_filtered = emp_2.where("tax > 10000")

# Filter data # select employee_id as emp_id, name, age, salary, tax, columnOne from emp_col_dropped where tax > 1000 emp_filtered = emp_2.where("tax > 10000")

In [11]:

emp_filtered.show()

emp_filtered.show()

+-----------+-------------+---+-------+-------+---------+----------+
|employee_id|         name|age| salary|    tax|columnOne|Column Two|
+-----------+-------------+---+-------+-------+---------+----------+
|        003|    Bob Brown| 35|55000.0|11000.0|        1|       two|
|        005|    Jack Chan| 40|60000.0|12000.0|        1|       two|
|        006|    Jill Wong| 32|52000.0|10400.0|        1|       two|
|        007|James Johnson| 42|70000.0|14000.0|        1|       two|
|        008|     Kate Kim| 29|51000.0|10200.0|        1|       two|
|        009|      Tom Tan| 33|58000.0|11600.0|        1|       two|
|        011|   David Park| 38|65000.0|13000.0|        1|       two|
|        012|   Susan Chen| 31|54000.0|10800.0|        1|       two|
|        013|    Brian Kim| 45|75000.0|15000.0|        1|       two|
|        015|  Michael Lee| 37|63000.0|12600.0|        1|       two|
|        017|  George Wang| 34|57000.0|11400.0|        1|       two|
|        019|  Steven Chen| 36|62000.0|12400.0|        1|       two|
|        020|    Grace Kim| 32|53000.0|10600.0|        1|       two|
+-----------+-------------+---+-------+-------+---------+----------+

In [12]:

# LIMIT data
# select employee_id as emp_id, name, age, salary, tax, columnOne from emp_filtered limit 5

emp_limit = emp_filtered.limit(5)

# LIMIT data # select employee_id as emp_id, name, age, salary, tax, columnOne from emp_filtered limit 5 emp_limit = emp_filtered.limit(5)

In [14]:

emp_limit.show(2)

emp_limit.show(2)

+-----------+---------+---+-------+-------+---------+----------+
|employee_id|     name|age| salary|    tax|columnOne|Column Two|
+-----------+---------+---+-------+-------+---------+----------+
|        003|Bob Brown| 35|55000.0|11000.0|        1|       two|
|        005|Jack Chan| 40|60000.0|12000.0|        1|       two|
+-----------+---------+---+-------+-------+---------+----------+
only showing top 2 rows

In [15]:

# Bonus TIP
# Add multiple columns

columns = {
    "tax" : col("salary") * 0.2 ,
    "oneNumber" : lit(1), 
    "columnTwo" : lit("two")
}

emp_final = emp.withColumns(columns)

# Bonus TIP # Add multiple columns columns = { "tax" : col("salary") * 0.2 , "oneNumber" : lit(1), "columnTwo" : lit("two") } emp_final = emp.withColumns(columns)

In [16]:

emp_final.show()

emp_final.show()

+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+
|employee_id|department_id|         name|age|gender|salary| hire_date|    tax|oneNumber|columnTwo|
+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+
|        001|          101|     John Doe| 30|  Male| 50000|2015-01-01|10000.0|        1|      two|
|        002|          101|   Jane Smith| 25|Female| 45000|2016-02-15| 9000.0|        1|      two|
|        003|          102|    Bob Brown| 35|  Male| 55000|2014-05-01|11000.0|        1|      two|
|        004|          102|    Alice Lee| 28|Female| 48000|2017-09-30| 9600.0|        1|      two|
|        005|          103|    Jack Chan| 40|  Male| 60000|2013-04-01|12000.0|        1|      two|
|        006|          103|    Jill Wong| 32|Female| 52000|2018-07-01|10400.0|        1|      two|
|        007|          101|James Johnson| 42|  Male| 70000|2012-03-15|14000.0|        1|      two|
|        008|          102|     Kate Kim| 29|Female| 51000|2019-10-01|10200.0|        1|      two|
|        009|          103|      Tom Tan| 33|  Male| 58000|2016-06-01|11600.0|        1|      two|
|        010|          104|     Lisa Lee| 27|Female| 47000|2018-08-01| 9400.0|        1|      two|
|        011|          104|   David Park| 38|  Male| 65000|2015-11-01|13000.0|        1|      two|
|        012|          105|   Susan Chen| 31|Female| 54000|2017-02-15|10800.0|        1|      two|
|        013|          106|    Brian Kim| 45|  Male| 75000|2011-07-01|15000.0|        1|      two|
|        014|          107|    Emily Lee| 26|Female| 46000|2019-01-01| 9200.0|        1|      two|
|        015|          106|  Michael Lee| 37|  Male| 63000|2014-09-30|12600.0|        1|      two|
|        016|          107|  Kelly Zhang| 30|Female| 49000|2018-04-01| 9800.0|        1|      two|
|        017|          105|  George Wang| 34|  Male| 57000|2016-03-15|11400.0|        1|      two|
|        018|          104|    Nancy Liu| 29|Female| 50000|2017-06-01|10000.0|        1|      two|
|        019|          103|  Steven Chen| 36|  Male| 62000|2015-08-01|12400.0|        1|      two|
|        020|          102|    Grace Kim| 32|Female| 53000|2018-11-01|10600.0|        1|      two|
+-----------+-------------+-------------+---+------+------+----------+-------+---------+---------+

In [ ]: