Python中如何将带行列索引的Turning Data转为表格并统计p/f数量

Hey there! Let's work through this problem together. We'll use pandas to reshape your data into the table format you want, handle missing positions, and calculate the counts of 'p' and 'f' for each ID. Here's a step-by-step implementation:

Step 1: Import pandas and define your data #

First, let's set up our environment and load the data you provided:

import pandas as pd

# Your original data
df = pd.DataFrame({
    'ID': ['a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'a01', 'b02', 'b02', 'b02','b02', 'b02', 'b02', 'b02', 'b02'],
    'Row': [1, 1, 1, 2, 2, 2, 3, 3, 3, 1, 1, 2, 2, 3, 3, 3, 3],
    'Col': [1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 3, 1, 3, 1, 2, 3, 3],
    'Result': ['p', 'f', 'p', 'p', 'p', 'f', 'p', 'p', 'p', 'p', 'p', 'p', 'f', 'p', 'p', 'p', 'p']
})

Step 2: Define fixed row/column ranges #

Since you mentioned row and column counts are fixed, let's extract the full set of rows and columns from the data (you can also hardcode them if you know they're always 1-3):

# Get all unique rows and columns, sorted
all_rows = sorted(df['Row'].unique())
all_cols = sorted(df['Col'].unique())

Step 3: Process each ID group #

We'll loop through each ID group, reshape the data into a table, fill missing positions with blanks, and calculate the counts:

# Group the data by ID
for id_val, group in df.groupby('ID'):
    # Pivot the group into a table, reindex to include all rows/columns, fill missing with empty string
    table = group.pivot(index='Row', columns='Col', values='Result').reindex(index=all_rows, columns=all_cols).fillna('')
    
    # Print the ID header
    print(f"ID: {id_val}")
    
    # Print each row of the table, joining values with spaces
    for _, row in table.iterrows():
        print(' '.join(row))
    
    # Calculate counts of 'p' and 'f'
    p_count = (group['Result'] == 'p').sum()
    f_count = (group['Result'] == 'f').sum()
    
    # Print the counts
    print(f"p: {p_count} f: {f_count}\n")

What this does: #

• Grouping by ID: We split the DataFrame into subsets for each unique ID.
• Pivoting and reindexing: Converts each group into a row-column table, ensuring we include all fixed rows/columns even if data is missing (filling gaps with empty strings).
• Printing the table: Iterates over each row of the pivoted table and prints the values separated by spaces.
• Counting 'p' and 'f': Uses boolean masking to sum up how many times each value appears in the group.

Output: #

When you run the code, you'll get exactly the output you requested:

ID: a01
p f p
p p f
p p p
p: 7 f: 2

ID: b02
p  p
p  f
p p p
p: 6 f: 1

This approach is flexible—if your fixed row/column ranges change later, you just need to adjust the all_rows and all_cols variables (or hardcode them like all_rows = [1,2,3] if you know they won't vary).

内容的提问来源于stack exchange，提问作者Joanne