from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("DataFrameDemo").getOrCreate()

# From list
data = [("Alice", 34), ("Bob", 28), ("Charlie", 42)]
df = spark.createDataFrame(data, ["name", "age"])
df.show()

# CSV
df = spark.read.csv("users.csv", header=True, inferSchema=True)

# JSON
df = spark.read.json("users.json")

# Parquet (columnar, optimized)
df = spark.read.parquet("users.parquet")

# With schema (better performance)
from pyspark.sql.types import StructType, StructField, StringType, IntegerType

schema = StructType([
    StructField("name", StringType(), True),
    StructField("age", IntegerType(), True)
])
df = spark.read.schema(schema).csv("users.csv")

df.select("name", "age").show()
df.select(df.name, df.age + 1).show()

df.filter(df.age > 30).show()
df.where("age > 30").show()  # SQL syntax

df.withColumn("senior", df.age > 65).show()

from pyspark.sql.functions import avg, max, min

df.groupBy("department").agg(
    avg("salary").alias("avg_salary"),
    max("salary").alias("max_salary")
).show()

df.createOrReplaceTempView("users")

result = spark.sql("""
    SELECT age, COUNT(*) as count
    FROM users
    WHERE age > 25
    GROUP BY age
    ORDER BY count DESC
""")
result.show()

df.explain(True)  # View execution plan

employees.join(departments, "dept_id", "inner").show()

from pyspark.sql.window import Window
from pyspark.sql.functions import rank

windowSpec = Window.partitionBy("department").orderBy("salary")
df.withColumn("rank", rank().over(windowSpec)).show()

df.cache()
df.count()  # Triggers caching

df.repartition(10, "department")

from pyspark.sql.functions import udf

square = udf(lambda x: x * x)
df.withColumn("squared", square(df.value)).show()