Here’s the reformatted version of your code, with added sections and improved readability:

Problem

Given a DataFrame df with columns play_id, position, frame, x, and y. The goal is to transform the data into a new format where each position is a separate column, with frames as sub-columns. Empty values are kept in place.

Solution

1. Sort values: Sort the DataFrame by position, frame, and play_id columns.

df = df.sort_values(["position","frame","play_id"])

2. Set index: Set the sorted columns as the index of the DataFrame.

df = df.set_index(["position","frame","play_id"])

3. Stack values: Use the stack() method to transform each row into a single value, with coord as a suffix.

df = df.stack()

4. Reindex and unstack: Reindex the DataFrame with new column names for position, frame, play_id, and coord. Unstack the values again to create separate columns for each position.

df = df.reindex(df.index.set_names(["position","frame","play_id","coord"])).unstack([0,1,3])

5. Output: Print the resulting DataFrame.

Complete Code

import pandas as pd

# Sample data
data = """
play_id position    frame   x   y
1   A_1 1   0.1 0.1
1   A_2 1   0.1 0.1
1   B_1 1   0.1 0.1
1   A_1 2   0.1 0.1
1   A_2 2   0.1 0.1
1   B_1 2   0.1 0.1
2   A_1 1   0.1 0.1
2   B_1 1   0.1 0.1
2   B_2 1   0.1 0.1
2   A_1 2   0.1 0.1
2   B_1 2   0.1 0.1
2   B_2 2   0.1 0.1
"""

# Load data into DataFrame
df = pd.read_csv(io.StringIO(data), sep="\t")

# Transform data
df = df.sort_values(["position","frame","play_id"])
df = df.set_index(["position","frame","play_id"])
df = df.stack()
df = df.reindex(df.index.set_names(["position","frame","play_id","coord"])).unstack([0,1,3])

# Print output
print(df)

Note that I used the io.StringIO() function to load the sample data as a string. In your actual code, you can replace this with the correct way to load your data into a DataFrame.

Last modified on 2024-03-09