如何将JSON条目追加到CSV对应列并解决列数据混乱问题

问题场景 #

爬取房产数据时，直接将不同结构的JSON数据追加到CSV，导致列错位，比如floorNo字段混入了户型、设施等无关数据。

原始代码 #

import pandas as pd
import requests

response = requests.get(f'https://www.magicbricks.com/mbsrp/propertySearch.html?editSearch=Y&category=S&propertyType=10002,10003,10021,10022,10001,10017,10000&bedrooms=11700,11701,11702,11703,11704,11705,11706,11707,11708,11709,11710&city=4320&page=2&groupstart=30&offset=0&maxOffset=248&sortBy=premiumRecent&postedSince=-1&pType=10002,10003,10021,10022,10001,10017,10000&isNRI=N&multiLang=en')
df = pd.json_normalize(response.json()['resultList'], max_level=0)
df.to_csv('property_data.csv', mode='a')

for i in range(3, 102):
    response = requests.get(f'https://www.magicbricks.com/mbsrp/propertySearch.html?editSearch=Y&category=S&propertyType=10002,10003,10021,10022,10001,10017,10000&bedrooms=11700,11701,11702,11703,11704,11705,11706,11707,11708,11709,11710&city=4320&page={i}&groupstart={30 * (i - 1)}&offset=0&maxOffset=248&sortBy=premiumRecent&postedSince=-1&pType=10002,10003,10021,10022,10001,10017,10000&isNRI=N&multiLang=en')
    df = pd.json_normalize(response.json()['resultList'])
    df.to_csv('property_data.csv', mode='a', header=False)

问题表现 #

运行以下代码查看floorNo列数据时，发现大量无关内容混入：

df = pd.read_csv("property_data.csv", on_bad_lines='skip')
df['floorNo'].unique()

输出示例：

array([nan, '9', '38', '12', ..., 'Freehold', 'Co-operative Society', 'Power Back Up', ...], dtype=object)

解决方案 #

核心思路是先对齐列结构，再写入CSV，避免直接追加导致的错位。提供两种实现方式：

方式一：收集所有数据后统一写入（高效推荐） #

将所有爬取到的数据存入列表，最后合并成一个DataFrame，自动对齐列，缺失值填充为NaN：

import pandas as pd
import requests

# 初始化列表存储所有页数据
all_property_data = []

# 爬取第2页
base_url = 'https://www.magicbricks.com/mbsrp/propertySearch.html?editSearch=Y&category=S&propertyType=10002,10003,10021,10022,10001,10017,10000&bedrooms=11700,11701,11702,11703,11704,11705,11706,11707,11708,11709,11710&city=4320&offset=0&maxOffset=248&sortBy=premiumRecent&postedSince=-1&pType=10002,10003,10021,10022,10001,10017,10000&isNRI=N&multiLang=en'
response = requests.get(f"{base_url}&page=2&groupstart=30")
page_data = pd.json_normalize(response.json()['resultList'], max_level=0)
all_property_data.append(page_data)

# 爬取第3-101页
for page_num in range(3, 102):
    groupstart = 30 * (page_num - 1)
    response = requests.get(f"{base_url}&page={page_num}&groupstart={groupstart}")
    page_data = pd.json_normalize(response.json()['resultList'])
    all_property_data.append(page_data)

# 合并所有数据，自动对齐列，缺失值设为NaN
final_df = pd.concat(all_property_data, ignore_index=True)

# 写入CSV
final_df.to_csv('property_data.csv', index=False)

方式二：分批读取合并后写入（适合超大数据量） #

如果数据量过大无法一次性存入内存，可以每次爬取后读取现有CSV，合并新数据再写入：

import pandas as pd
import requests
import os

csv_path = 'property_data.csv'
base_url = 'https://www.magicbricks.com/mbsrp/propertySearch.html?editSearch=Y&category=S&propertyType=10002,10003,10021,10022,10001,10017,10000&bedrooms=11700,11701,11702,11703,11704,11705,11706,11707,11708,11709,11710&city=4320&offset=0&maxOffset=248&sortBy=premiumRecent&postedSince=-1&pType=10002,10003,10021,10022,10001,10017,10000&isNRI=N&multiLang=en'

# 处理第2页
response = requests.get(f"{base_url}&page=2&groupstart=30")
new_df = pd.json_normalize(response.json()['resultList'], max_level=0)

if not os.path.exists(csv_path):
    new_df.to_csv(csv_path, index=False)
else:
    existing_df = pd.read_csv(csv_path)
    combined_df = pd.concat([existing_df, new_df], ignore_index=True)
    combined_df.to_csv(csv_path, index=False)

# 处理第3-101页
for page_num in range(3, 102):
    groupstart = 30 * (page_num - 1)
    response = requests.get(f"{base_url}&page={page_num}&groupstart={groupstart}")
    new_df = pd.json_normalize(response.json()['resultList'])
    
    existing_df = pd.read_csv(csv_path)
    combined_df = pd.concat([existing_df, new_df], ignore_index=True)
    combined_df.to_csv(csv_path, index=False)

原理说明 #

原代码直接用mode='a'追加行，当新数据的列数、列顺序与现有CSV不一致时，会导致数据错位。而pd.concat()会自动按列名匹配，缺失的列会填充NaN，确保每列数据对应正确的字段。

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