Python/Pandas：基于CSV数据集生成新变量并创建结果交叉表

Step 1: Set Up Dependencies and Sample Data #

First, let's get the basics sorted. Start by importing the necessary libraries, and create a sample CSV to test with (save this as patient_data.csv):

patient_id,score,last_visit,treatment_group
1,85,2024-01-15,A
2,62,2023-09-01,A
3,78,2024-02-20,B
4,55,2023-06-10,B
5,90,2024-03-05,A
6,68,2023-08-15,B
7,72,2023-11-30,A
8,49,2023-05-20,B

Step 2: Load Data and Derive Custom Variables #

We'll use pandas to load the CSV and generate the missing Performance and LTFU (Lost to Follow-Up) variables. Below are two approaches—pick the one that fits your workflow:

Option 1: Efficient Vectorized Operations (Great for Large Datasets)

import pandas as pd
from datetime import datetime

# Load the CSV data
df = pd.read_csv('patient_data.csv')

# Convert last_visit to datetime format (critical for LTFU logic)
df['last_visit'] = pd.to_datetime(df['last_visit'])

# Define thresholds for our variables
score_threshold = 70
ltfu_cutoff_date = datetime(2024, 1, 1)  # Patients not seen after this are LTFU

# Create Performance variable: "High" if score meets threshold, else "Low"
df['Performance'] = df['score'].apply(lambda x: 'High' if x >= score_threshold else 'Low')

# Create LTFU variable: "Yes" if last visit is before cutoff, else "No"
df['LTFU'] = df['last_visit'].apply(lambda x: 'Yes' if x < ltfu_cutoff_date else 'No')

# Check the updated DataFrame
print(df.head())

Option 2: Reusable Custom Function (Modular and Easy to Adjust)

If you want to tweak logic later or reuse this across datasets, wrap the calculations in a function:

def compute_metrics(row, score_cutoff=70, ltfu_cutoff=datetime(2024,1,1)):
    # Calculate performance status
    performance = 'High' if row['score'] >= score_cutoff else 'Low'
    # Calculate LTFU status
    ltfu_status = 'Yes' if row['last_visit'] < ltfu_cutoff else 'No'
    return pd.Series([performance, ltfu_status], index=['Performance', 'LTFU'])

# Apply the function to every row
df[['Performance', 'LTFU']] = df.apply(compute_metrics, axis=1)

Step 3: Generate the Performance Summary Cross Table #

Now let's build the cross table to summarize performance across groups and LTFU status using pd.crosstab:

# Create cross table: Treatment Group vs Performance, split by LTFU (with totals)
performance_summary = pd.crosstab(
    index=df['treatment_group'],
    columns=[df['Performance'], df['LTFU']],
    margins=True,  # Adds a "Total" row/column
    margins_name='Overall Total'
)

print(performance_summary)

Sample Output:

Performance        High       Low       Overall Total
LTFU                No Yes    No Yes                 
treatment_group                                     
A                    2   1     0   1                 4
B                    1   0     0   3                 4
Overall Total        3   1     0   4                 8

Step 4: Customize the Cross Table (Optional) #

Want percentages instead of raw counts? Use the normalize parameter:

# Cross table with row percentages (shows distribution per treatment group)
percentage_summary = pd.crosstab(
    index=df['treatment_group'],
    columns=[df['Performance'], df['LTFU']],
    normalize='index',
    margins=True
).round(2) * 100

print(percentage_summary)

Sample Output:

Performance  High       Low       Overall Total
LTFU          No  Yes    No  Yes                 
treatment_group                               
A            50.0 25.0   0.0 25.0           100.0
B            25.0  0.0   0.0 75.0           100.0
Overall Total 37.5 12.5   0.0 50.0           100.0

You can easily adjust the thresholds, logic, or cross table dimensions to match your specific dataset needs. Just modify the parameters in the function or crosstab call!

内容的提问来源于stack exchange，提问作者MGB.py