从商铺ID列表向Pandas DataFrame添加Google Place详情遇更新异常

问题 #

我尝试为商家信息的Pandas DataFrame添加Google Place详情，但无法正确遍历并更新数据。调用现有DataFrame后，尝试匹配商铺ID对应的Place详情进行更新，代码如下：

import requests
import json
import pandas as pd

for i in range(len(textsearch_results_df)): 
    shop_id = textsearch_results_df.loc[i,"shop_place_id"]

    url = f"https://maps.googleapis.com/maps/api/place/details/json?fields=name%2Cformatted_phone_number%2Cinternational_phone_number%2Cwebsite&place_id={shop_id}&key=YOUWISHSILLYGOOSE"

    payload={}
    headers = {}

    response = requests.request("GET", url, headers=headers, data=payload)

    results =  json.loads(response.text)
    print(results)

    if str(textsearch_results_df['shop_name'][i]) == str(results['result']['name']):
        print('same')
    
        try:
            textsearch_results_df['shop_website'].update(results['result']['website'])
        except:
            textsearch_results_df['shop_website'].update('no')
        
        try:
            textsearch_results_df['shop_phone'].update(results['result']['formatted_phone_number'])
        except:
            textsearch_results_df['shop_phone'].update('no')

        try:
            textsearch_results_df['shop_int_phone'].update(results['result']['international_phone_number'])
        except:
            textsearch_results_df['shop_int_phone'].update('no') 
            
    else:
        print('nope')

接口返回结果正常，均匹配到对应商家，但DataFrame仅第一行被更新，且内容为最后一条接口返回的信息：

shop_name   shop_address    shop_website    shop_phone  shop_int_phone  shop_place_id   shop_lat    shop_lon    shop_rating shop_rating_count   shop_type   shop_place_details

0   Ray White Seminyak  Ray White Seminyak, Jalan Sunset Road No.N0.11...   https://www.balijetaime.com/    (0361) 9077090  +62 361 9077090 ChIJC4ggMCNH0i0RHe9KqqRnmYA -8.684080   115.168149  4.8 22  [real_estate_agency, point_of_interest, establ...   none

1   Bali Realty Jl. Sunset Road No.7X, Seminyak, Kec. Kuta, Ka...   none    none    none    ChIJPZ3bMKRH0i0ROaKbV-BJNww -8.683336   115.167028  4.4 38  [real_estate_agency, point_of_interest, establ...   none

2   Power Property  Jl. Braban No.12, Kerobokan Kelod, Kec. Kuta U...   none    none    none    ChIJX7xdJT1H0i0RVh148pImmpQ -8.680031   115.163218  4.8 13  [real_estate_agency, point_of_interest, establ...   none

...

58  Bali Je t'aime Villas   Jl. Pantai Berawa No.69, Canggu, Kec. Kuta Uta...   none    none    none    ChIJv7B-8iJH0i0ROilwKNLHI9o -8.662955   115.139558  4.8 59  [real_estate_agency, point_of_interest, establ...   none

请问我哪里操作错误？

错误分析与修正方案 #

核心错误 #

你误用了Pandas的update()方法：

• DataFrame.update()的作用是用另一个数据源（如Series、DataFrame）匹配索引来更新整个DataFrame/列，不是用来修改单个单元格的。
• 当你传入字符串（比如results['result']['website']）时，Pandas会自动把它转换成仅包含索引0的Series，所以每次循环都只会更新第0行的对应列，最后第0行被覆盖成最后一次请求的结果，其他行完全没被修改。

修正代码 #

把所有update()调用改成用.loc[i, 列名]直接赋值单个单元格：

import requests
import json
import pandas as pd

for i in range(len(textsearch_results_df)): 
    shop_id = textsearch_results_df.loc[i,"shop_place_id"]

    url = f"https://maps.googleapis.com/maps/api/place/details/json?fields=name%2Cformatted_phone_number%2Cinternational_phone_number%2Cwebsite&place_id={shop_id}&key=YOUWISHSILLYGOOSE"

    payload={}
    headers = {}

    response = requests.request("GET", url, headers=headers, data=payload)

    results =  json.loads(response.text)
    print(results)

    if str(textsearch_results_df['shop_name'][i]) == str(results['result']['name']):
        print('same')
    
        try:
            textsearch_results_df.loc[i, 'shop_website'] = results['result']['website']
        except KeyError:  # 明确捕获字段不存在的异常，避免隐藏其他错误
            textsearch_results_df.loc[i, 'shop_website'] = 'no'
        
        try:
            textsearch_results_df.loc[i, 'shop_phone'] = results['result']['formatted_phone_number']
        except KeyError:
            textsearch_results_df.loc[i, 'shop_phone'] = 'no'

        try:
            textsearch_results_df.loc[i, 'shop_int_phone'] = results['result']['international_phone_number']
        except KeyError:
            textsearch_results_df.loc[i, 'shop_int_phone'] = 'no' 
            
    else:
        print('nope')

额外优化建议 #

1. 避免裸except：原代码的except:会捕获所有异常（比如网络错误、JSON解析错误），建议明确捕获KeyError（对应字段不存在的情况），其他异常单独处理，方便排查问题。
2. 批量处理更高效：如果数据量较大，循环遍历行的效率较低，可以考虑把所有请求异步发送，再批量更新DataFrame，减少等待时间。

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