> ## Documentation Index
> Fetch the complete documentation index at: https://rajanand.org/llms.txt
> Use this file to discover all available pages before exploring further.

# Spark: withColumn function

The `withColumn()` command in Spark is used to add a new column to a DataFrame or replace an existing column with a new value. This is particularly useful when you need to transform or derive new columns based on existing ones.

***

## 1. **Syntax**

**PySpark:**

```python theme={"system"}
df.withColumn(colName, colExpression)
```

**Spark SQL:**

```sql theme={"system"}
SELECT *, expression AS new_column FROM table_name;
```

## 2. **Parameters**

* **colName:** The name of the new or existing column.
* **colExpression:** The expression to compute the values for the new column. This can be a column operation, a function, or a literal value.

## 3. **Return Type**

* Returns a new DataFrame with the added or replaced column.

## 4. **Examples**

### 1: Adding a New Column

**PySpark:**

```python theme={"system"}
from pyspark.sql import SparkSession
from pyspark.sql.functions import lit

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

data = [("Anand", 25), ("Bala", 30), ("Kavitha", 28), ("Raj", 35)]
columns = ["Name", "Age"]

df = spark.createDataFrame(data, columns)

# Adding a new column 'Country' with a constant value 'India'
df_with_country = df.withColumn("Country", lit("India"))
df_with_country.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT *, 'India' AS Country FROM people;
```

**Output:**

```
+-------+---+-------+
|   Name|Age|Country|
+-------+---+-------+
|  Anand| 25|  India|
|   Bala| 30|  India|
|Kavitha| 28|  India|
|    Raj| 35|  India|
+-------+---+-------+
```

### 2: Replacing an Existing Column

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import col

# Replacing the 'Age' column with 'Age' + 5
df_with_age_increased = df.withColumn("Age", col("Age") + 5)
df_with_age_increased.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, Age + 5 AS Age FROM people;
```

**Output:**

```
+-------+---+
|   Name|Age|
+-------+---+
|  Anand| 30|
|   Bala| 35|
|Kavitha| 33|
|    Raj| 40|
+-------+---+
```

### 3: Adding a Derived Column

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import when

# Adding a new column 'AgeGroup' based on 'Age'
df_with_age_group = df.withColumn("AgeGroup", 
                                  when(col("Age") < 30, "Young")
                                  .when((col("Age") >= 30) & (col("Age") < 40), "Middle-aged")
                                  .otherwise("Senior"))
df_with_age_group.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, Age,
       CASE 
           WHEN Age < 30 THEN 'Young'
           WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'
           ELSE 'Senior'
       END AS AgeGroup
FROM people;
```

**Output:**

```
+-------+---+------------+
|   Name|Age|    AgeGroup|
+-------+---+------------+
|  Anand| 25|       Young|
|   Bala| 30| Middle-aged|
|Kavitha| 28|       Young|
|    Raj| 35| Middle-aged|
+-------+---+------------+
```

### 4: Adding a Column with a Computed Value

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import expr

# Adding a new column 'BirthYear' computed as 2023 - Age
df_with_birth_year = df.withColumn("BirthYear", expr("2023 - Age"))
df_with_birth_year.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, Age, 2023 - Age AS BirthYear FROM people;
```

**Output:**

```
+-------+---+---------+
|   Name|Age|BirthYear|
+-------+---+---------+
|  Anand| 25|     1998|
|   Bala| 30|     1993|
|Kavitha| 28|     1995|
|    Raj| 35|     1988|
+-------+---+---------+
```

### 5: Adding Multiple Columns

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import lit, expr

# Adding multiple columns in a single transformation
df_with_multiple_columns = df.withColumn("Country", lit("India")) \
                            .withColumn("BirthYear", expr("2023 - Age")) \
                            .withColumn("AgeGroup", 
                                        when(col("Age") < 30, "Young")
                                        .when((col("Age") >= 30) & (col("Age") < 40), "Middle-aged")
                                        .otherwise("Senior"))
df_with_multiple_columns.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, Age, 'India' AS Country, 2023 - Age AS BirthYear,
       CASE 
           WHEN Age < 30 THEN 'Young'
           WHEN Age >= 30 AND Age < 40 THEN 'Middle-aged'
           ELSE 'Senior'
       END AS AgeGroup
FROM people;
```

**Output:**

```
+-------+---+-------+---------+------------+
|   Name|Age|Country|BirthYear|    AgeGroup|
+-------+---+-------+---------+------------+
|  Anand| 25|  India|     1998|       Young|
|   Bala| 30|  India|     1993| Middle-aged|
|Kavitha| 28|  India|     1995|       Young|
|    Raj| 35|  India|     1988| Middle-aged|
+-------+---+-------+---------+------------+
```

### 6: Adding a Column with a Random Value

**PySpark:**

```python theme={"system"}
from pyspark.sql.functions import rand

# Adding a new column 'RandomValue' with random values
df_with_random = df.withColumn("RandomValue", rand())
df_with_random.show()
```

**Spark SQL:**

```sql theme={"system"}
SELECT Name, Age, RAND() AS RandomValue FROM people;
```

**Output:**

```
+-------+---+-------------------+
|   Name|Age|        RandomValue|
+-------+---+-------------------+
|  Anand| 25| 0.1234567890123456|
|   Bala| 30| 0.6543210987654321|
|Kavitha| 28| 0.9876543210123456|
|    Raj| 35| 0.4567890123456789|
+-------+---+-------------------+
```

## 5. **Common Use Cases**

* Adding new features or derived columns for machine learning models.
* Transforming existing columns (e.g., converting units, normalizing values).
* Adding metadata or constant values to rows.

## 6. **Performance Considerations**

* Adding multiple columns in a single transformation can be more efficient than adding them one by one.
* Use appropriate indexing and partitioning strategies to optimize operations on large datasets.

### 7. Key Takeaways

1. The `withColumn()` command is used to add a new column or replace an existing column in a DataFrame.
2. It allows you to create new columns using constants, expressions, or transformations on existing columns.
3. Adding multiple columns in a single transformation is more efficient than adding them one by one.
4. In Spark SQL, similar transformations can be achieved using `SELECT` with expressions and `CASE` statements.
5. Works efficiently on large datasets when combined with proper partitioning and indexing strategies.