Pandas处理API读取与Excel读取DataFrame时表现不一致问题排查

Hey there! Let’s break down why your direct API-to-DataFrame workflow is failing, while saving/reading via Excel works, and how to fix it without the Excel middleman.

The Core Problem #

When you transpose the DataFrame created from your API response, you’re left with a structure that looks correct on the surface, but has hidden metadata or type inconsistencies that throw off subsequent operations. Saving to Excel and reloading resets this metadata—standardizes column types, cleans up indexing, and normalizes missing values—which is why that workaround works.

Common Root Causes #

• Transposed DataFrames inherit integer-based column indices from the original row indices. Even after setting string column names, some pandas operations may still reference the underlying integer metadata.
• Missing values (None) from the API response stay as Python None instead of being converted to pandas-compatible NaN/pd.NA, which breaks aggregation or filtering logic.
• The transposed DataFrame’s internal structure isn’t fully reset, leading to unexpected behavior when accessing columns or running aggregations.

Solutions to Try #

1. Skip Transposition Altogether (Cleanest Fix) #

If your API returns data in a list (matching the order ID, Name, Tel, 1, 2, 3), directly create a row-based DataFrame instead of transposing:

import requests
import pandas as pd

res = requests.post(url, headers=headers)
data = res.json()
data_list = data['body'][0]['data']

# Directly create a single-row DataFrame with your target columns
df = pd.DataFrame([data_list], columns=['ID','Name','Tel','1','2','3'])

If the API returns a dictionary (with keys matching your target columns or needing renaming):

res = requests.post(url, headers=headers)
data_dict = res.json()['body'][0]['data']

# Convert dict to a single-row DataFrame
df = pd.DataFrame([data_dict])

# Rename columns if the API's keys don't match your targets
df = df.rename(columns={
    'api_id_key': 'ID',
    'api_name_key': 'Name',
    # Add other mappings as needed
})

2. Fix the Transposed DataFrame Structure #

If you must use transposition, explicitly reset the DataFrame’s structure to eliminate hidden metadata:

import requests
import pandas as pd

res = requests.post(url, headers=headers)
data = res.json()
data = data['body'][0]['data']

df = pd.DataFrame(data).T
# Reset index to clean up inherited integer indices
df = df.reset_index(drop=True)
# Set your target column names
df.columns=['ID','Name','Tel','1','2','3']
# Normalize missing values to pandas-compatible NA
df = df.replace({None: pd.NA})

3. Simulate Excel’s Cleaning Logic (No File Write Needed) #

If you want to replicate the Excel workaround without saving to disk, use an in-memory buffer:

import requests
import pandas as pd
from io import BytesIO

res = requests.post(url, headers=headers)
data = res.json()
data = data['body'][0]['data']

df = pd.DataFrame(data).T
# Use BytesIO to simulate saving/loading Excel
buffer = BytesIO()
df.to_excel(buffer, index=False)
buffer.seek(0)
df = pd.read_excel(buffer)
# Set your column names
df.columns=['ID','Name','Tel','1','2','3']

Verification Steps #

After applying any fix, run these checks to ensure the DataFrame is properly structured:

• Print df.columns to confirm your target column names are present (and no hidden whitespace/invalid characters).
• Check df.dtypes to ensure columns have the expected types (e.g., Tel as string/object, numeric columns as int/float).
• Test your subsequent code (like accessing df['Tel'] or running aggregations) to confirm no more errors.

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