Create new columns from current columns, keep only specific columns

pandas_columns.md

Creating New Columns & Selecting Specific Columns in Pandas

---

SETUP

import pandas as pd
import numpy as np

df = pd.DataFrame({
    'student_id':   [101, 102, 103, 104],
    'first_name':   ['Alice', 'Bob', 'Carol', 'Dave'],
    'last_name':    ['Smith', 'Jones', 'White', 'Brown'],
    'major':        ['CS', 'Math', 'CS', 'Physics'],
    'gpa':          [3.9, 2.8, 3.5, 3.1],
    'credits':      [120, 95, 110, 80],
    'us_citizen':   ['Y', 'N', 'Y', 'Y'],
    'pell':         ['N', 'Y', 'Y', 'N']
})

---

PART 1 — CREATING NEW COLUMNS

---

1. Direct Arithmetic from Existing Columns

# Simple math
df['gpa_10pt']      = df['gpa'] * 2.5
df['credits_left']  = 120 - df['credits']
df['gpa_per_credit']= round(df['gpa'] / df['credits'], 4)

---

2. Combine String Columns

# Concatenate with +
df['full_name'] = df['first_name'] + ' ' + df['last_name']

# f-string style with apply
df['label'] = df.apply(
    lambda row: f"{row['first_name']} ({row['major']})", axis=1
)

Result:

full_name	label
Alice Smith	Alice (CS)
Bob Jones	Bob (Math)

---

3. Boolean / Flag Columns

# From a condition — produces True/False
df['is_citizen']     = df['us_citizen'] == 'Y'
df['high_gpa']       = df['gpa'] >= 3.5
df['near_graduate']  = df['credits'] >= 110

# Cast to 0/1 integer
df['is_citizen_int'] = (df['us_citizen'] == 'Y').astype(int)

---

4. Conditional Column — np.where()

# np.where(condition, value_if_true, value_if_false)
df['gpa_tier'] = np.where(df['gpa'] >= 3.5, 'High', 'Low')

# Nested np.where for multiple tiers
df['standing'] = np.where(df['gpa'] >= 3.7, 'Honors',
                 np.where(df['gpa'] >= 3.0, 'Good',
                 np.where(df['gpa'] >= 2.0, 'Satisfactory',
                                            'At Risk')))

---

5. Conditional Column — pd.cut() for Binning

# Bin a continuous column into labeled ranges
df['gpa_band'] = pd.cut(
    df['gpa'],
    bins=[0, 2.0, 3.0, 3.5, 4.0],
    labels=['At Risk', 'Satisfactory', 'Good', 'Honors']
)

# Equal-sized quantile bins
df['gpa_quartile'] = pd.qcut(df['gpa'], q=4, labels=['Q1','Q2','Q3','Q4'])

---

6. Map Values to New Column — .map()

# Map from a dictionary
major_dept = {'CS': 'Engineering', 'Math': 'Sciences', 'Physics': 'Sciences'}
df['department'] = df['major'].map(major_dept)

# Map Y/N to readable labels
df['citizen_label'] = df['us_citizen'].map({'Y': 'Citizen', 'N': 'Non-Citizen'})

---

7. Group-Level Stats as a New Column — transform()

# Add group mean back to each row (no collapsing)
df['major_avg_gpa']   = df.groupby('major')['gpa'].transform('mean')
df['major_max_gpa']   = df.groupby('major')['gpa'].transform('max')
df['major_headcount'] = df.groupby('major')['gpa'].transform('count')

# Flag students above their major's average
df['above_major_avg'] = df['gpa'] > df['major_avg_gpa']

Result:

name	major	gpa	major_avg_gpa	above_major_avg
Alice	CS	3.9	3.70	True
Bob	Math	2.8	2.80	False
Carol	CS	3.5	3.70	False

---

8. Rank Column

# Rank all students by GPA (1 = highest)
df['gpa_rank'] = df['gpa'].rank(ascending=False, method='dense').astype(int)

# Rank within each major
df['rank_in_major'] = df.groupby('major')['gpa'].rank(
    ascending=False, method='dense'
).astype(int)

---

9. Rolling / Cumulative Columns

# Cumulative sum of credits (sorted first)
df = df.sort_values('student_id')
df['cumulative_credits'] = df['credits'].cumsum()

# Rolling average (window of 2)
df['rolling_avg_gpa'] = df['gpa'].rolling(window=2).mean()

---

10. Multiple New Columns with assign() — chainable

# assign() returns a new df — great for method chaining
df = df.assign(
    full_name    = df['first_name'] + ' ' + df['last_name'],
    gpa_10pt     = df['gpa'] * 2.5,
    is_citizen   = df['us_citizen'] == 'Y',
    credits_left = 120 - df['credits']
)

---

PART 2 — KEEPING SPECIFIC COLUMNS

---

11. Select Columns by Name

# Single column → returns Series
df['gpa']

# Multiple columns → returns DataFrame (double brackets)
df[['student_id', 'full_name', 'gpa', 'major']]

---

12. Keep Columns with .filter()

# Keep columns by exact name list
df.filter(items=['student_id', 'gpa', 'major'])

# Keep columns whose names contain a substring
df.filter(like='name')       # matches first_name, last_name, full_name

# Keep columns matching a regex
df.filter(regex='^gpa')      # matches gpa, gpa_10pt, gpa_rank

---

13. Drop Unwanted Columns — drop()

# Drop one column
df.drop(columns=['label'])

# Drop multiple columns
df.drop(columns=['label', 'gpa_per_credit', 'rolling_avg_gpa'])

# Drop in place
df.drop(columns=['label'], inplace=True)

---

14. Select by Data Type — select_dtypes()

# Keep only numeric columns
df.select_dtypes(include='number')

# Keep only string/object columns
df.select_dtypes(include='object')

# Keep numeric + bool
df.select_dtypes(include=['number', 'bool'])

# Exclude a type
df.select_dtypes(exclude='object')

---

15. Select Columns by Position — .iloc[]

df.iloc[:, 0]       # first column only
df.iloc[:, 0:3]     # first 3 columns
df.iloc[:, -2:]     # last 2 columns
df.iloc[:, [0,2,4]] # columns at index 0, 2, 4

---

16. Reorder Columns

# Define the exact order you want
cols = ['student_id', 'full_name', 'major', 'gpa', 'standing', 'credits']
df = df[cols]

# Move one column to the front
first_col = 'student_id'
df = df[[first_col] + [c for c in df.columns if c != first_col]]

# Move one column to the end
last_col = 'credits'
df = df[[c for c in df.columns if c != last_col] + [last_col]]

---

🧠 Quick Reference

Goal	Method
Math from columns	df['new'] = df['a'] + df['b']
Combine strings	df['a'] + ' ' + df['b']
Boolean flag	df['new'] = df['col'] == 'Y'
If/else column	np.where(condition, true_val, false_val)
Multi-tier labels	nested np.where()
Bin numeric range	pd.cut() / pd.qcut()
Map values	df['col'].map({...})
Group stat per row	groupby().transform()
Rank	df['col'].rank()
Chainable new cols	df.assign(col=...)
Keep named columns	df[['col1', 'col2']]
Keep by pattern	df.filter(like='...')
Drop columns	df.drop(columns=[...])
Keep by type	df.select_dtypes(include=...)
Reorder columns	df[custom_col_list]