如何使用Python的Pandas库重新整理数据

Got it, let's break this down step by step. You've got a flat string of key-value pairs representing multiple rows of market data, and you want to turn this into a structured Pandas DataFrame with columns Date, Open, High, Low, Close, S1, R1, S2, R2. Here's a straightforward approach using Pandas:

Full Working Code #

import pandas as pd

# Your raw input data as a string
raw_data = "Date ,20100423 Open ,1028.75 High ,1029.5 Low ,1026 Close ,1026 S1 ,1030.62082869339 R1 ,1033.6233971724 S2 ,1026.87917130661 R2 ,1023.8766028276 Date ,20100426 Open ,1037.75 High ,1040.5 Low ,1037 Close ,1038.75 S1 ,1043.86350963032 R1 ,1040.79138126515 S2 ,1031.63649036968 R2 ,1034.70861873485"

# Step 1: Split and clean the raw data into individual elements
elements = [item.strip() for item in raw_data.split(',')]

# Step 2: Pair keys with their corresponding values
key_value_pairs = list(zip(elements[::2], elements[1::2]))

# Step 3: Group pairs into individual row dictionaries (each row has 9 fields)
entries = []
for i in range(0, len(key_value_pairs), 9):
    row_dict = dict(key_value_pairs[i:i+9])
    entries.append(row_dict)

# Step 4: Create DataFrame and convert columns to appropriate data types
df = pd.DataFrame(entries)
# Convert numeric columns to float
numeric_cols = ['Open', 'High', 'Low', 'Close', 'S1', 'R1', 'S2', 'R2']
df[numeric_cols] = df[numeric_cols].astype(float)
# Convert Date column to datetime format
df['Date'] = pd.to_datetime(df['Date'], format='%Y%m%d')

# View the result
print(df)

Step-by-Step Explanation #

1. Clean and Split the Raw Data: We split the input string by commas, then use strip() to remove any extra whitespace around each element (since your input has spaces like Date ,20100423).
2. Pair Keys and Values: Using slicing (elements[::2] gets every even-indexed element as keys, elements[1::2] gets odd-indexed as values), we zip them into a list of (key, value) tuples.
3. Group into Rows: Each complete row has exactly 9 fields (from Date to R2), so we loop through the key-value pairs in steps of 9, converting each group into a dictionary that represents one row of data.
4. Create and Refine the DataFrame: We pass the list of row dictionaries to pd.DataFrame() to create the table. Then we convert numeric columns to float (instead of strings) and the Date column to a proper datetime type for easier time-series operations.

Result #

Running the code will give you this structured DataFrame:

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