Du originated from Liu Lei, the grandson of Tang Yao, one of the five emperors. Since its formation, du had been widely distributed in the pre-Qin period. It ranks 53rd on the list of 100 surnames, accounting for 0.42% of the national population. Here, taking the surname du as an example, we look at the journey path of the population from the place of birth to the place of work.

Data preprocessing

Data acquisition

Firstly, the data of “Du” was extracted. The data source can be referred to the previous article

data_du=data_01[['name'.'Domicile _LNG'.'Domicile _LAT'.'Place of work _ City'.'Working place _ District/county']][data_01['name'] = ='du']
data_du=data_du[data_du['Place of work _ City']! ='Not recognized']
data_du=data_du[data_du['Working place _ District/county']! ='Not recognized']

data_du.columns=['familyname'.'birth_lng'.'birth_lat'.'work_city'.'work_district']

writer=pd.ExcelWriter('G: data analysis data ******* Study on Chinese Surnames du.xlsx')
data_du.to_excel(writer,'sheet',index=False)
writer.save()
Copy the code

View excel data for the first 20 lines below

Merge the two tables to get a table of latitude and longitude of place of birth and place of work, remove invalid data, make PivotTable, calculate the number of people in the same place of birth and place of work.

Track production

QGIS trajectory data generation

After QGIS import the national city boundary map, set the color, background, fill, lines and so on, create two point layers with ‘birthplace longitude and latitude’ and ‘workplace longitude and latitude’ respectively.

Add a track length field

Data conversion

The projection coordinate system is saved as a WGS84 geographic coordinate system, and the count value type is converted to an integer

Conversion tool

Echarts dynamic trajectory diagram implementation

By importing the exported JSON data into Echarts and adjusting the JS script, the spatial trajectory map of different forms of population rushing track can be generated, with the starting point as birthplace and the ending point as workplace.

It is also interesting that many mainland Chinese cross the Taiwan Strait to work in Taiwan’s treasure island.