Python实现API响应转多列CSV及单列CSV转多列方案

Hey there! Let's break this down into two parts: first getting your API data exported correctly into a multi-column CSV with the exact headers and data types you need, then covering how to convert a single-column CSV to multi-column.

Part 1: Correctly Export API Data to Multi-Column CSV #

Your core issue right now is that the CSV export isn't structured with proper headers and columns, plus there's a mistake in how you're reading the CSV later. Let's rewrite that section of your code to fix this—using pandas makes data type management and CSV handling straightforward:

import requests
import json
import pandas as pd
from datetime import datetime

# Your existing API call code stays the same
my_data = {
    "category_ids": "948",
    "limit": "10000"
}
my_headers = {
    'Content-Type': 'application/json'
}
response = requests.post('https://newapi.zivame.com/api/v1/catalog/list', 
                         data=json.dumps(my_data), 
                         headers=my_headers)
data = response.json()
products = data.get("data").get("docs")

dataList = []
for product in products:
    valuepack = product.get("valuePackOffers", None)
    offer = []
    if valuepack:
        for v in valuepack:
            offer.append(v.get("display_name", None))
    # Append each product's data as a list entry
    dataList.append([
        product.get("sku", None),
        product.get("mainCategoryName", None),
        product["price"],
        product["specialPrice"],
        product.get("sizes", None),
        offer
    ])

# Define your desired columns and data types
columns = ["SKU", "Category_name", "price", "specialPrice", "sizes", "offers"]
data_types = {
    "SKU": object,
    "Category_name": object,
    "price": "float64",
    "specialPrice": "float64",
    "sizes": object,
    "offers": object
}

# Create DataFrame with correct columns and enforce data types
df = pd.DataFrame(dataList, columns=columns).astype(data_types)

# Export to CSV - index=False removes the extra auto-generated index column
df.to_csv('/home/arcod/Downloads/promos_zivame.csv', index=False, sep=',')

# Verify the exported data's structure and types
df_check = pd.read_csv('/home/arcod/Downloads/promos_zivame.csv')
print(df_check.dtypes)

Why this fixes your issue: #

• We explicitly set column headers so the CSV has distinct columns instead of lumping everything into one.
• Using astype() ensures each column matches your required data types exactly.
• The index=False parameter in to_csv() prevents an unnecessary index column from cluttering your output.
• Your original pd.read_csv() call had an invalid parameter ('wb' is a write mode, not a read parameter) — this version uses the correct syntax to validate your exported CSV.

Part 2: Converting a Single-Column CSV to Multi-Column #

If you already have a single-column CSV (where all data is packed into one column), how you convert it depends on how the data is formatted in that column:

Scenario 1: Single column contains comma-separated values #

If each row in the single column is a string of values separated by commas (e.g., SKU1,Category1,199.99,149.99,['S','M'],['Offer1']), you can split it directly using pandas:

import pandas as pd

# Read the single-column CSV (assuming no header row)
single_col_df = pd.read_csv('/path/to/single_col.csv', header=None, sep='\n')

# Split the single column into multiple columns using commas as delimiters
multi_col_df = single_col_df[0].str.split(',', expand=True)

# Assign your desired column names
multi_col_df.columns = ["SKU", "Category_name", "price", "specialPrice", "sizes", "offers"]

# Convert to correct data types (you may need to clean string values like [] first)
multi_col_df = multi_col_df.astype({
    "SKU": object,
    "Category_name": object,
    "price": "float64",
    "specialPrice": "float64",
    "sizes": object,
    "offers": object
})

# Export the cleaned multi-column CSV
multi_col_df.to_csv('/path/to/multi_col.csv', index=False)

Scenario 2: Single column contains Python-style list strings #

If each row is a string representation of a list (e.g., ["SKU1", "Category1", 199.99, 149.99, ["S","M"], ["Offer1"]]), use ast.literal_eval to parse the list into actual values:

import pandas as pd
import ast

# Read the single-column CSV
single_col_df = pd.read_csv('/path/to/single_col.csv', header=None, names=['data'])

# Parse each list string into an actual list, then expand into separate columns
multi_col_df = single_col_df['data'].apply(ast.literal_eval).apply(pd.Series)

# Assign column names and enforce data types
multi_col_df.columns = ["SKU", "Category_name", "price", "specialPrice", "sizes", "offers"]
multi_col_df = multi_col_df.astype({
    "SKU": object,
    "Category_name": object,
    "price": "float64",
    "specialPrice": "float64",
    "sizes": object,
    "offers": object
})

# Export the final multi-column CSV
multi_col_df.to_csv('/path/to/multi_col.csv', index=False)

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