Create New Columns in Pandas DataFrames based on Existing Columns

Understanding the Task:

• You have a pandas DataFrame containing data.
• You want to create a new column where the values are derived or selected based on the values in an existing column.

Methods for Creating the New Column:

There are several ways to achieve this in pandas:

1. Direct Assignment:

   • If the new column's values can be calculated directly from the existing column using basic operations or functions, you can simply assign the expression to the new column name within square brackets [].
   • Example: Create a 'Discounted_Price' column based on a 'Price' column with a 10% discount:

   import pandas as pd
   
   data = {'Price': [100, 150, 200]}
   df = pd.DataFrame(data)
   df['Discounted_Price'] = df['Price'] * 0.9  # 10% discount
   

2. map() Function:

   • Use map() to apply a custom function that maps values from the existing column to new values in the new column.
   • Example: Create a 'Size_Category' column based on 'Shirt_Size' (S, M, L, XL):

   size_mapping = {'S': 'Small', 'M': 'Medium', 'L': 'Large', 'XL': 'Extra Large'}
   df['Size_Category'] = df['Shirt_Size'].map(size_mapping)
   

3. • Use apply() for more complex transformations that involve processing entire rows or using external libraries.
   • Example: Create a 'Grade' column based on a score range in 'Exam_Score':

   def grade_function(score):
       if score >= 90:
           return 'A'
       elif score >= 80:
           return 'B'
       else:
           return 'C'
   
   df['Grade'] = df.apply(lambda row: grade_function(row['Exam_Score']), axis=1)
   

Choosing the Right Method:

• For simple calculations, direct assignment is efficient.
• map() works well for one-to-one value mappings.
• apply() offers flexibility for complex transformations.

Additional Considerations:

• You can modify the existing column directly using techniques like transform(), but creating a new column often improves clarity and avoids modifying the original data.
• Consider using vectorized operations for efficiency whenever possible.

By understanding these methods and considerations, you can effectively create new columns based on existing data in your pandas DataFrames!

Direct Assignment (Simple Calculations):

import pandas as pd

data = {'Price': [100, 150, 200], 'Quantity': [2, 3, 1]}
df = pd.DataFrame(data)

# Create 'Total_Price' using direct calculation
df['Total_Price'] = df['Price'] * df['Quantity']

print(df)

This code first creates a DataFrame df with two columns: Price and Quantity. Then, it directly assigns the product of Price and Quantity to the new column Total_Price.

map() Function (Value Mappings):

import pandas as pd

data = {'Country_Code': ['US', 'FR', 'IN', 'UK']}
df = pd.DataFrame(data)

# Country code to full country name mapping
country_names = {'US': 'United States', 'FR': 'France', 'IN': 'India', 'UK': 'United Kingdom'}

# Create 'Country_Name' using map()
df['Country_Name'] = df['Country_Code'].map(country_names)

print(df)

This code creates a DataFrame df with a Country_Code column. It then defines a dictionary country_names for mapping codes to full names. Finally, it uses map() to apply this mapping and create the Country_Name column.

apply() Function (Complex Transformations):

import pandas as pd

data = {'Order_Amount': [120, 250, 80], 'Shipping_Cost': [10, 15, 5]}
df = pd.DataFrame(data)

# Define a function to calculate free shipping eligibility
def free_shipping(row):
    return 'Yes' if row['Order_Amount'] >= 200 else 'No'

# Create 'Free_Shipping' using apply()
df['Free_Shipping'] = df.apply(free_shipping, axis=1)

print(df)

This code creates a DataFrame df with Order_Amount and Shipping_Cost columns. It then defines a function free_shipping that checks if the order amount is greater than or equal to 200 for free shipping. Finally, it uses apply() with this function (applied to each row) to create the Free_Shipping column.

These examples demonstrate how to create new columns based on existing columns in pandas DataFrames using different methods. Choose the most appropriate approach depending on the complexity of your transformation.

List Comprehension (Simple Transformations):

• Similar to direct assignment, you can use list comprehension for concise calculations on existing columns.
• Example: Create a 'Tax' column with 8% tax on the 'Price' column:

import pandas as pd

data = {'Price': [100, 150, 200]}
df = pd.DataFrame(data)
df['Tax'] = [price * 0.08 for price in df['Price']]  # List comprehension for tax calculation

print(df)

Vectorized Operations (Efficient for Calculations):

• For calculations that can be expressed as mathematical operations, vectorized operations using NumPy can be highly efficient.
• Example: Create a 'Distance' column as the square root of 'X' and 'Y' squared (assuming Euclidean distance):

import pandas as pd
import numpy as np

data = {'X': [3, 4, 5], 'Y': [1, 2, 3]}
df = pd.DataFrame(data)
df['Distance'] = np.sqrt(df['X']**2 + df['Y']**2)  # Vectorized distance calculation

print(df)

numpy.where() (Conditional Column Creation):

• Use numpy.where() to create a new column based on conditions applied to existing columns.

import pandas as pd
import numpy as np

data = {'Exam_Score': [85, 92, 78]}
df = pd.DataFrame(data)
conditions = [df['Exam_Score'] >= 90, (df['Exam_Score'] >= 80) & (df['Exam_Score'] < 90), df['Exam_Score'] < 80]
grades = ['A', 'B', 'C']
df['Grade'] = np.where(conditions, grades, np.NAN)  # Handle missing values (optional)

print(df)

assign() Method (Functional Style):

• The assign() method provides a functional approach for creating new columns based on existing ones.
• Example: Create a 'FullName' column by concatenating 'First_Name' and 'Last_Name':

import pandas as pd

data = {'First_Name': ['Alice', 'Bob', 'Charlie'], 'Last_Name': ['Smith', 'Jones', 'Brown']}
df = pd.DataFrame(data)
df_new = df.assign(FullName=df['First_Name'] + ' ' + df['Last_Name'])  # Functional style

print(df_new)

These alternate methods offer different approaches depending on your specific needs. Consider the complexity of the transformation, efficiency requirements, and code readability when choosing the best method for your situation.