import requests
import time
import re
import json
import pandas as pd

# Defining a request session
se = requests.session()

df = pd.DataFrame()
df = pd.read_csv(r'./county.csv',encoding="utf-8")#Read vounty-level data
df['county']

The value returned after executing the API is as follows: showLocation&&showLocation({"status":0,"result":{"location":{"lng":113.271431344459,"lat":23.135336306695},"precise":0,"confidence":20,"comprehension":100,"level":"区县"}})

def calc_ll(x):
        Post_url = "http://api.map.baidu.com/geocoding/v3/?address=" + x + "api_key" #Replace "api_key" with your own key
    Post_data = {
        'address': x
    }
    try:
        # Send a POST request, which means to submit data to a server using the HTTP POST method.
        response = se.post(Post_url, data=Post_data)
        Text = response.text.replace("'", '"').replace('/ ', '/')[27:-1]  # Response text.
        jsonValue = json.loads(Text)  # Convert it into a JSON object
        if 'result' in jsonValue:
            # Return a list of latitude and longitude
            return [jsonValue['result']['location']['lng'], jsonValue['result']['location']['lat']]
        else:
            return ['', '']
    except Exception as e:
        print(f"Error occurred: {e}")
        return ['', '']  # If an error occurs, return an empty string list.

# Apply the calc_ll function to the county names
address = df['county'].apply(calc_ll)

# Add the returned latitude and longitude to the DataFrame
df[['longitude', 'latitude']] = pd.DataFrame(address.tolist(), index=df.index)

# Save the updated DataFrame to a EXCEL file
df.to_xlsx('updated_county.xlsx', index=False,encoding='utf-8-sig')