Because I don't have any programming foundation, and my goal is to climb the driving distance between more than 300 cities in China, and I only have txt from these cities at present, I encounter many difficulties today.

The first is how to get the driving distance between cities, the second is how to import excel into python, and how to establish od relations between cities (although it's embarrassing, Xiaobai always bleeds and tears...), which are the three main problems.

Of course, there are many other small problems, such as opening pychart to import pandas and itertools. Many of them are gray. Finally, my roommate reminded me that I didn't install many running packages. Looking back on the whole process, from more than 9 a.m. to 4 p.m., it was very bumpy on the whole.

with Gavin_Sunny Based on the code in the article "Python uses Baidu map to obtain the most detailed process and source code of the distance between the two places", with the help of the combinations in Python's itertools, that is, permutations and combinations, finally formed the od correspondence between the two cities, and finally got the answer. The only problem is that the operation process is very slow. I personally tested 40 cities and calculated about 800 results, It takes about eight minutes.

The initial preparations, such as applying for the ak key of Baidu open platform, are not shown in the table below, mainly using the two web APIs of location retrieval service and batch routing service.

The main codes are as follows:

# The overall source code
AK = 'NAwGuQe2WmQ7RSdvQIafcZ0Kjnv8Aldc'

# Modules to be used: pandas, requests, json, etc
import pandas as pd
import requests
import json
from itertools import combinations

#Obtain the latitude and longitude of the corresponding location
def getPosition(address):
    url = r"http://API. Map. Baidu. COM / place / V2 / search? Query = {} & Region = National & output = JSON & AK = {} ". Format(
        address,
        'NAwGuQe2WmQ7RSdvQIafcZ0Kjnv8Aldc'  
    )
    res = requests.get(url)
    json_data = json.loads(res.text)

    if json_data['status'] == 0:
        lat = json_data["results"][0]["location"]["lat"]  # latitude
        lng = json_data["results"][0]["location"]["lng"]  # longitude
    else:
        print("[ERROR] Can not find {}.".format(address))
        return "0,0", json_data["status"]
    return str(lat) + "," + str(lng), json_data["status"]

#Obtain the driving distance between the two places
def getDistance(start, end):
    url = "http://api.map.baidu.com/routematrix/v2/driving?output=json&origins={}&destinations={}&ak={}".format(
        start,
        end,
        'NAwGuQe2WmQ7RSdvQIafcZ0Kjnv8Aldc' 
    )
    res = requests.get(url)
    content = res.content
    jsonv = json.loads(str(content, "utf-8"))
    dist = jsonv["result"][0]["distance"]["value"]
    return dist

#Merge function calls
def calcDistance(startName, endName):
    start, status1 = getPosition(startName)
    end, status2 = getPosition(endName)
    if status1 == 0 and status2 == 0:
        return getDistance(start, end)
    else:
        return -1

#Calculate the distance at batch locations (select 5 cities for test)
if __name__ == "__main__":
    data = ['Beijing','Tianjin','Shanghai','Chongqing City','Aba Tibetan and Qiang Autonomous Prefecture']
    res = []
    #Calculate the non repeated arrangement and combination relationship between several cities
    for i in combinations(data,2):
        startName = i[0] #Set start and end points
        endName = i[1]
        dist = calcDistance(startName, endName)
        res.append([startName, endName, dist / 1000])
    pd.DataFrame(res).to_excel(
        "result1.xlsx",
        header=["starting point", "End", "distance"],
        index=None,
        encoding="utf-8"
    )

ps: the original code reads the starting point and ending point that have been divided, and then adds indicators such as distance. I haven't studied its usage here, and I'll update it later. Finally, there's a question. Because Baidu platform seems to have a certain limit on the amount of data that can be read every day, I'm still studying how to make python read a fixed amount of data from excel in batches (for example, 40 cities are entered each time from top to bottom). After calculating the distance between each other, it will be automatically updated and combined into a complete table for calculating the distance between cities.