Apache Spark by datamansam

from pyspark.sql import functions as F
# Select Columns
df.select("firstName").show() 
df.select("firstName","lastName") \
 .show()

#  split multiple array column data into rows 
df2 = df.select(df.name,explode(df.subjectandID))

#  Show all entries where age >24
df.select(df['age'] > 24).show() 

#  Show name and 0 or 1 depending on age >  or < than 30
df.select("Name", 
 F.when(df.age > 30, 1)
 .otherwise(0)) \
 .show()

# Show firstName if in the given options
df[df.firstName.isin("Jane","Boris")].collect()

# Show firstName, and lastName if lastName is Smith. 
df.select("firstName", 
 df.lastName.like("Smith"))  
 .show()

# Like also excepts wildcard matches. 
df.select("firstName", 
 df.lastName.like("%Sm"))  
 .show()


# Show firstName, and TRUE if
 df.lastName \ lastName starts with Sm

Startswith - Endswith
df.select("firstName
 .startswith("Sm")) \
 .show()


# Show last names ending in th
df.select(df.lastName.endswith("th"))\
 .show()

# Return substrings of firstName
 Substring
df.select(df.firstName.substr(1, 3) \ 
 .alias("name")) \
 .collect()

 Between
# Show  values where age is between 22 and 24
df.select(df.age.between(22, 24)) \ 
 .show() 

# Show all entries in firstName and age + 1
df.select(df["firstName"],df["age"]+ 1), .show()

import pyspark # importing the module
 
 # importing the SparkSession module
from pyspark.sql import SparkSession

 # creating a session
session = SparkSession.builder.appName('First App')
.getOrCreate()

 # calling the session variable
session

# Define the input and output formats and paths and the table name.
read_format = 'delta'
write_format = 'delta'
load_path = '/databricks-datasets/learning-spark-v2/people/people-10m.delta'
save_path = '/tmp/delta/people-10m'
table_name = 'default.people10m'

# Load the data from its source.
people = spark \
  .read \
  .format(read_format) \
  .load(load_path)

# Write the data to its target.
people.write \
  .format(write_format) \
  .save(save_path)

# Create the table.
spark.sql("CREATE TABLE " + table_name + " USING DELTA LOCATION '" + save_path + "'")

session

To select one or multiple columns the select() function works 

dataframe.select(column_name) # selecting one column
dataframe.select(column_1, column_2, .., column_N) 
# selecting many columns


dataframe.withColumn() 
To add a column the dataframe.withColumn() function 
takes two parameters

New column name to add
Existing column name to use for (not necessary if the 
new column has nothing to do with the existing column)

# adding columns in dataframe
data = data.withColumn('Age_after_3_y', data['Age']+3)


to change data type
You would also need cast() along with withColumn().
 The below statement changes the datatype from String
 to Integer for the salary column.


df.withColumn("salary",col("salary").cast("Integer")).show()


Change a value
Pass an existing column name as a first argument 
and a column as the value to be assigned as a second argument 

df.withColumn("salary",col("salary")*100).show()


Drop 
df.drop("salary") \
  .show() 


withColumnRenamed() 
rename an existing column 

df.withColumnRenamed("gender","sex") \
  .show(truncate=False)