Python处理JSON转DataFrame：列名映射与排序技术求助

Hey there! Let's fix those two issues you're facing with your DataFrame from the API response. I'll walk you through renaming the c1/c2 columns to their proper business names and reordering them in logical numeric order.

Step 1: Extract Column Name Mapping #

First, we'll pull the column name mappings directly from the report_header section of your JSON response. This gives us a dictionary that links each cXX key to its human-readable name:

import json
import pandas as pd

# Your existing code to load JSON data
json_data = json.loads(res.read())

# Extract the report header to create our column mapping
report_header = json_data['data'][0]['report_header']
col_mapping = {col: info['name'] for col, info in report_header.items()}

This creates a mapping like:

{'c11': 'VENDOR_RECORD', 'c10': 'VENDOR_ID', ..., 'c1': 'RECORD_NO'}

Step 2: Rename Columns in Your DataFrame #

Next, we'll use this mapping to replace the generic cXX column names with their proper labels:

# Load the report rows into DataFrame (your existing code)
data = pd.json_normalize(json_data['data'], record_path=['report_row'])

# Rename columns using our mapping
data_renamed = data.rename(columns=col_mapping)

Step 3: Reorder Columns Correctly #

Right now, your columns are ordered c1, c10, c11... because string sorting treats "10" as coming before "2". We'll fix this by sorting the original cXX keys by their numeric suffix, then mapping those to the new column names:

# Sort the original cXX columns by their numeric part (1,2,3...12 instead of 1,10,11...)
sorted_c_cols = sorted(report_header.keys(), key=lambda x: int(x[1:]))

# Map the sorted cXX keys to their new human-readable names
sorted_col_names = [col_mapping[col] for col in sorted_c_cols]

# Reorder the DataFrame to use this sorted column list
data_final = data_renamed[sorted_col_names]

# Print the final result
print(data_final)

Final Output #

You'll now get a clean, properly ordered DataFrame:

RECORD_NO REF_RECORD_NO SOV_LINEITEM_NO REF_ITEM PROJECTNUMBER PROJECTNAME           TITLE CONTRACT_NO  STATUS VENDOR_ID VENDOR_RECORD VENDOR_NAME
0  CON-0000001                        1                     P-0037  Project ABC  Build IT System  Contract 123  Pending       71   VEN-0000001    Microsoft
1  CON-0000002                      1.1                     P-0037  Project ABC  Build IT System  Contract XYZ Approved       72   VEN-0000002      Google

Full Combined Code #

Here's the complete snippet to copy-paste:

import json
import pandas as pd

# Load API response data
json_data = json.loads(res.read())

# Extract column name mapping from report_header
report_header = json_data['data'][0]['report_header']
col_mapping = {col: info['name'] for col, info in report_header.items()}

# Load and normalize report rows
data = pd.json_normalize(json_data['data'], record_path=['report_row'])

# Rename columns to human-readable names
data_renamed = data.rename(columns=col_mapping)

# Sort columns in logical numeric order
sorted_c_cols = sorted(report_header.keys(), key=lambda x: int(x[1:]))
sorted_col_names = [col_mapping[col] for col in sorted_c_cols]
data_final = data_renamed[sorted_col_names]

# Print the cleaned DataFrame
print(data_final)

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