Excel与Pandas读取同一份CSV文件样本数量不一致问题求助

Hey there! It’s totally normal to run into this kind of discrepancy between Excel and Pandas when loading CSVs—let’s break down the most likely culprits and how to fix them:

1. Unescaped Delimiters in Rows #

Since you’re using sep='|', if any of your data rows contain an unescaped | (like inside a text field), Pandas will misinterpret that as a column separator. This can split a single row into multiple partial rows, or mark the entire row as "bad" and skip it silently (depending on your Pandas version).

How to check:
Use the on_bad_lines parameter to get warnings about problematic rows instead of skipping them:

import pandas as pd
df = pd.read_csv("data.csv", sep='|', header=0, on_bad_lines='warn')

This will print out the line numbers that Pandas had trouble parsing—you can then open the CSV and inspect those rows to fix the unescaped | (usually by wrapping the field in quotes like "This|is a single field").

2. Inconsistent Line Endings #

Excel is pretty forgiving with mixed line endings (like \r\n vs \n), but Pandas might not parse them correctly by default. If some rows use a different line terminator, they could get merged or skipped.

Fix it by specifying the line terminator:
Try forcing Pandas to use the line ending Excel recognizes:

df = pd.read_csv("data.csv", sep='|', header=0, lineterminator='\r\n')

If that doesn’t work, you can also try lineterminator='\n' to cover Unix-style line endings.

3. Quoting/Escaping Issues #

If your CSV uses quotes to wrap fields but has inconsistent escaping (e.g., a field with a " that isn’t doubled up), Pandas might read multiple lines as a single field, making it look like rows are missing.

Fix by explicitly setting quote parameters:
First import the csv module, then define how quotes are handled:

import pandas as pd
import csv
df = pd.read_csv("data.csv", sep='|', header=0, quotechar='"', quoting=csv.QUOTE_MINIMAL)

This tells Pandas to respect quoted fields and handle escaped quotes properly.

4. Encoding Mismatches #

If your CSV uses a non-standard encoding (like gbk for Chinese text, or latin-1 for older files), Pandas might misread characters and corrupt rows, leading to silent skips.

Test different encodings:
Try specifying common encodings to see if that fixes the row count:

# Try UTF-8 first
df = pd.read_csv("data.csv", sep='|', header=0, encoding='utf-8')
# If that fails, try GBK (common for Chinese datasets)
df = pd.read_csv("data.csv", sep='|', header=0, encoding='gbk')

5. Trailing Blank Lines (Less Likely) #

Excel often ignores trailing blank lines at the end of a CSV, but Pandas should too by default (since skip_blank_lines=True is the default). But if you want to rule this out, you can disable that setting to see if the rows reappear:

df = pd.read_csv("data.csv", sep='|', header=0, skip_blank_lines=False)

If the row count jumps up, those were blank lines Excel was hiding from you.

Quick First Step #

Start with the on_bad_lines='warn' trick—it’s the fastest way to spot if delimiter issues are causing the missing rows. Once you find those problematic lines, fixing them will get your row count back in sync with Excel.

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