Extract Latitude/Longitude from CSV to Excel with Pandas – Fast Method for 80k+ Rows

Fan Request Overview

A follower asked for help processing a CSV file that contains multiple latitude/longitude pairs per row, separated by commas. The goal is to split each pair into two separate columns (longitude and latitude) and add a third column for the row number.

Full Solution (Three Parts)

import pandas as pd
# 1. Read data
df = pd.read_csv("wgs84 - 副本.csv", header=None)
df.columns = ["列一", "列二"]
# 2. Process and write data
df1 = pd.DataFrame()
for index, value in enumerate(df["列一"]):
    y = value[10:-3].split(",")
    z = [[index+1] + i.strip().split(" ") for i in y]
    df1 = df1.append(z)
df1.columns = ["行号", "经度", "维度"]
# 3. Save data
df1.to_excel("经纬度.xlsx", index=None)

The script performs:

Data reading : Uses header=None because the source file lacks a header row, then assigns column names for easier handling.

Data processing : Each cell contains a string like ((lon lat, ...)). The code slices the string, splits by commas, then splits each pair by spaces after stripping extra whitespace, finally appends the result to a new DataFrame.

Data writing : Writes the resulting three‑column DataFrame to an Excel file without the default index.

Step‑by‑Step Demonstration

1. Extract a single row

x = df["列一"][0]
x

The extracted string is shown in the image below.

2. Slice and split the string

y = x[10:-3].split(",")
y

The resulting list still contains leading/trailing spaces, which are removed later with strip().

3. Combine lists with a comprehension

z = [[1] + i.strip().split(" ") for i in y]
z

4. Append to the result DataFrame

df1 = pd.DataFrame()
df1.append(z)

Processing 80k+ Rows Efficiently

After consulting an expert, the following chunk‑based code processes more than 80,000 rows in a few seconds.

import pandas as pd
import os
# Read in chunks
chunks = pd.read_csv("wgs84.csv", chunksize=10000)
last = 0
for df in chunks:
    df.dropna(how="all", inplace=True)
    result = []
    for a, b in df.values:
        if not isinstance(a, str):
            continue
        if pd.isna(b):
            b = last + 1
        elif isinstance(b, str) and not b.isdigit():
            print(a, b, last)
            continue
        for p in a[10:-3].split(","):
            result.append((b, *p.split()))
        if not pd.isna(b):
            last = int(b)
    df_out = pd.DataFrame(result, columns=["行号", "经度", "维度"])
    file = "result.csv"
    df_out.to_csv(file, index=False, mode='a', header=not os.path.exists(file), encoding="u8")

The output file contains 800,000 rows, which Excel cannot open directly, but the CSV can be further processed as needed.

Key Python Concepts Explained

1. The * operator

Placing * before a list or tuple unpacks its elements as separate positional arguments; using ** before a dictionary unpacks its key‑value pairs as named arguments.

x = "Polygon ((100.41370674 38.56406437, 100.41363237 38.56400317))"
for i, p in enumerate(x[10:-3].split(",")):
    print(p.split())          # without *
    print(*p.split())         # with *

2. isinstance() function

import numpy as np
a = 2
b = np.nan
print(isinstance(a, int))   # True
print(isinstance(a, str))   # False
print(isinstance(b, int))   # False
print(isinstance(b, float)) # True

That concludes the tutorial – try the code on your own CSV files and adapt it to your specific data format.