从SQLite导出的Python元组列表创建带指定转换的NumPy二维数组

Great question—when dealing with massive datasets, ditching Python loops for NumPy's vectorized operations is non-negotiable for speed. Here's a clean, optimized approach that leverages NumPy's native tools to get your desired result efficiently:

import numpy as np

# Your input data from SQLite
data = [(1, '12345', 1, 0, None), (1, '34567', 1, 1, None)]

# Step 1: Convert the tuple list to a NumPy array (object dtype handles mixed types initially)
arr = np.array(data, dtype=object)

# Step 2: Vectorized conversion of column 1 (string → numeric)
arr[:, 1] = arr[:, 1].astype(np.int64)

# Step 3: Vectorized handling of the last column (None → 0, else → 1)
arr[:, -1] = np.where(arr[:, -1] == None, 0, 1).astype(np.int64)

# Step 4: Cast to uniform numeric dtype (boosts performance for future operations)
transformed_data = arr.astype(np.int64)

print(transformed_data)
# Output:
# [[ 1 12345  1  0  0]
#  [ 1 34567  1  1  0]]

Why this beats Python loops: #

• Vectorized speed: All transformations run at NumPy's C-level core, avoiding the overhead of Python loop iteration. For large datasets, this translates to orders of magnitude faster execution with O(n) time complexity and minimal constant factors.
• Memory efficiency: NumPy arrays use contiguous memory blocks, which are far more efficient than Python lists for storing large numeric datasets.
• Readability: The code maps directly to your problem requirements, making it easy to debug and modify later.

Bonus tip for ultra-large datasets: #

If you're pulling data directly from SQLite, skip the Python list intermediate step entirely. Use sqlite3 cursor results with NumPy's fromiter or structured arrays to load data directly into NumPy—this cuts down on memory overhead and speeds up your pipeline even more.

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