Python Pandas：填充缺失值并行转列——特定结构DataFrame处理

Let's break down the solution into actionable steps to clean and reshape your DataFrame into the desired structure:

Step 1: Import Required Libraries #

First, make sure you have pandas and numpy imported:

import pandas as pd
import numpy as np

Step 2: Define the Original DataFrame #

Your original DataFrame is:

df_nba = pd.DataFrame({
    'col1': ['name', np.nan, np.nan, 'course', 'eca', 'pages', 
             'name', np.nan, np.nan, 'course', 'pages', 
             'name', np.nan, np.nan, 'course', 'eca', 'pages', 
             'name', np.nan, np.nan, 'course', 'eca', 'pages', 
             'name', np.nan, np.nan, 'course', 'pages', 
             'name', np.nan, np.nan, 'course', 'eca', 'pages'],
    'col2': ['jim', 'California','M','Biology','Biology Club',1, 
             'jim', 'California','M','Physics',2, 
             'greg', 'Arizona','M','Geography','Jazz Band',3, 
             'greg', 'Arizona','M','Physics','Photography',4, 
             'jesse', 'Washington','F','Economics',5, 
             'jesse', 'Washington','F','Literature','Photography',6]
})

Step 3: Fill in Missing Context Columns (name, states, gender) #

We need to propagate the name, states, and gender values to all rows belonging to the same user record:

df = df_nba.copy()

# Fill the 'name' column by forward-filling values from 'name' rows
df['name'] = df.loc[df['col1'] == 'name', 'col2']
df['name'] = df['name'].ffill()

# Fill 'states' (value in the row immediately after a 'name' row)
df['states'] = df.loc[df['col1'].shift(1) == 'name', 'col2']
df['states'] = df['states'].ffill()

# Fill 'gender' (value two rows after a 'name' row)
df['gender'] = df.loc[df['col1'].shift(2) == 'name', 'col2']
df['gender'] = df['gender'].ffill()

Step 4: Create Record IDs for Each Course Entry #

Each course entry starts a new record, so we'll use these to group related attributes:

df['record_id'] = df['col1'].eq('course').cumsum()

Step 5: Pivot Attribute Rows into Columns #

Filter rows containing course/eca/pages, then pivot them into columns using the record ID:

# Extract only the attribute rows (course, eca, pages)
attributes = df[df['col1'].isin(['course', 'eca', 'pages'])].copy()

# Pivot attributes into columns
pivoted_attributes = attributes.pivot(
    index='record_id', 
    columns='col1', 
    values='col2'
).reset_index(drop=True)

Step 6: Combine User Info with Pivoted Attributes #

Merge the user context (name, states, gender) with the pivoted attributes:

# Get unique user info per record ID
user_context = df.groupby('record_id')[['name', 'states', 'gender']].first().reset_index(drop=True)

# Combine into final DataFrame
final_df = pd.concat([user_context, pivoted_attributes], axis=1)

# Convert 'pages' to integer type
final_df['pages'] = final_df['pages'].astype(int)

# Reorder columns to match your desired output
final_df = final_df[['name', 'states', 'gender', 'course', 'eca', 'pages']]

Step 7: View the Final Result #

Printing final_df will give you exactly the structure you wanted:

print(final_df)

Output: #

name     states gender      course           eca  pages
0    jim  California      M     Biology  Biology Club      1
1    jim  California      M     Physics           NaN      2
2   greg     Arizona      M  Geography    Jazz Band      3
3   greg     Arizona      M     Physics  Photography      4
4  jesse  Washington      F   Economics           NaN      5
5  jesse  Washington      F  Literature  Photography      6

This approach efficiently cleans the messy structure by leveraging forward-filling for context values and pivoting to reshape attributes into columns.

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