Как конвертировать CSV в Excel в pandas без потери чисел и метаданных

Почему числа из CSV с метаданными попадают в Excel как текст и как это исправить в pandas: задаём header, конвертируем блок данных, избегаем float_format.

Преобразование CSV в Excel с помощью pandas обычно делается одной строкой — пока не вмешаются научная запись чисел и лишние метаданные. Типичный подвох в том, что значения, похожие на числа, незаметно превращаются в строки в Excel, а затем ломаются следующие шаги — например, построение графиков. Ниже — почему так происходит и как это исправить, не теряя полезные строки с метаданными в начале файла.

Минимальное воспроизведение проблемы

Функция ниже читает файлы .csv или .txt, пытается привести столбцы к числам и записывает .xlsx. На вид результат может быть корректным, но для строк ниже «шапки» с метаданными всё равно окажется текст вместо чисел.

import pandas as pds
import os
from tkinter import filedialog as fd, messagebox as mb

def export_to_xlsx():
    picked = fd.askopenfilenames(filetypes=[("Text and CSV files", "*.txt *.csv")])
    if not picked:
        return

    for src in picked:
        try:
            delim = '\t' if src.lower().endswith('.txt') else ','
            frame = pds.read_csv(src, sep=delim)

            for field in frame.columns:
                frame[field] = pds.to_numeric(frame[field], errors='ignore')

            out_path = os.path.splitext(src)[0] + '.xlsx'
            with pds.ExcelWriter(out_path, engine='openpyxl') as book:
                frame.to_excel(book, index=False, float_format="%.3f")

        except Exception as err:
            mb.showerror("Error", f"Error converting file {src}: {err}")
            return

    mb.showinfo("Success", "All files have been converted successfully!")

export_to_xlsx()

Почему так происходит

pandas охотно выводит типы столбцов, если файл начинается с чистых заголовков и строк. Например, с таким простым CSV значения в Excel будут числовыми и готовы к построению графиков:

Frequency,Random
5.0e+3,6.01e-4
5.0e+3,6.01e-4
5.0e+3,6.01e-4
5.0e+3,6.01e-4

Но если файл начинается с произвольных метаданных перед заголовком, pandas читает эти ячейки как часть таблицы и уже не может уверенно вывести числовые типы. Рассмотрим такую структуру:

!Keysignth,Easdf,MYA1234,A.04.00
!Date: Thur

BEGIN CH1_DATA
Frequency,Random
5.0e+3,6.01e-4
5.0e+3,6.01e-4
5.0e+3,6.01e-4
5.0e+3,6.01e-4

Ключевой момент: использование pds.to_numeric(..., errors='ignore') не означает «преобразуй, что получится, и пропусти остальное». Если хотя бы одно значение в столбце не парсится, преобразование для всего столбца полностью пропускается — без предупреждений. В сочетании с верхними строками это приводит к тому, что столбцы остаются типа object и записываются как текст. Числа с виду похожи на значения в научной записи, но фактически это строки, а не настоящие числа.

Есть и практическая ремарка о представлении: числа в памяти не имеют «научного» или «обычного» формата — это вопрос отображения при чтении/записи. Для построения графиков важно лишь одно: ячейки — это числа или текст.

Решение 1: указать pandas настоящую строку заголовка

Если заголовок всегда начинается после фиксированного количества строк, сообщите pandas, где находится реальная строка заголовка. Тогда всё, что выше, будет проигнорировано при построении DataFrame, и вывод числовых типов сработает как надо.

import pandas as pds
import os
from tkinter import filedialog as fd, messagebox as mb

def export_to_xlsx_fixed_header():
    picked = fd.askopenfilenames(filetypes=[("Text and CSV files", "*.txt *.csv")])
    if not picked:
        return

    for src in picked:
        try:
            delim = '\t' if src.lower().endswith('.txt') else ','
            frame = pds.read_csv(src, sep=delim, header=3)  # заголовки на строке с индексом 3

            out_path = os.path.splitext(src)[0] + '.xlsx'
            with pds.ExcelWriter(out_path, engine='openpyxl') as book:
                frame.to_excel(book, index=False)  # без float_format

        except Exception as err:
            mb.showerror("Error", f"Error converting file {src}: {err}")
            return

    mb.showinfo("Success", "All files have been converted successfully!")

Так сохраняются числовые типы, но строки с метаданными пропадают из Excel-файла, так как read_csv игнорирует всё до заголовка.

Решение 2: сохранить метаданные и конвертировать только строки с данными

Если верхние строки нужны и в Excel, загрузите файл как есть, а затем приводите к числам только блок данных. Пропустив строки с метаданными, вы избегаете прежнего «провала» конвертации всего столбца.

import pandas as pds
import os
from tkinter import filedialog as fd, messagebox as mb

def export_to_xlsx_keep_meta():
    picked = fd.askopenfilenames(filetypes=[("Text and CSV files", "*.txt *.csv")])
    if not picked:
        return

    for src in picked:
        try:
            delim = '\t' if src.lower().endswith('.txt') else ','
            frame = pds.read_csv(src, sep=delim)  # не пропускаем метаданные

            for field in frame.columns:
                frame.loc[3:, field] = pds.to_numeric(frame[field][3:])  # конвертируем начиная со строки с индексом 3

            out_path = os.path.splitext(src)[0] + '.xlsx'
            with pds.ExcelWriter(out_path, engine='openpyxl') as book:
                frame.to_excel(book, index=False)  # без float_format

        except Exception as err:
            mb.showerror("Error", f"Error converting file {src}: {err}")
            return

    mb.showinfo("Success", "All files have been converted successfully!")

В этом варианте метаданные остаются, а числовой блок становится настоящими числами, готовыми для графиков.

Решение 3: если высота блока метаданных плавает

Если число строк метаданных меняется, найдите первую числовую строку программно и конвертируйте начиная с неё. Простой эвристикой будет просканировать первый столбец до первого числового значения.

# после чтения frame через pds.read_csv(...):
first_col = frame.columns[0]
for row_idx, cell in enumerate(frame[first_col]):
    try:
        pds.to_numeric(cell)
        start_idx = row_idx
        break
    except ValueError:
        pass
else:
    print("Warning: no numeric data in the first column")
    start_idx = row_idx + 1

Затем примените числовую конвертацию к каждому столбцу, начиная с найденной строки.

for field in frame.columns:
    frame.loc[start_idx:, field] = pds.to_numeric(frame[field][start_idx:])

Этот подход предполагает, что все столбцы становятся числовыми с одной и той же строки и что после неё не встречаются нечисловые значения.

О параметре float_format

Передача float_format="%.3f" в to_excel принудительно округляет перед записью. Это может «стереть» различия между 6e-4 и 6.12e-4, превратив их в одинаковое отображаемое значение после округления. Когда данные действительно числовые, пусть Excel управляет форматами чисел по необходимости.

Зачем это важно

Диаграммы, агрегирования и формулы в Excel зависят от реальных числовых типов. Если данные хранятся как строки, графики либо не строятся, либо тихо дают неверные результаты. Корректный этап чтения — указание настоящего заголовка или конвертация только области с данными — убирает двусмысленность и сохраняет числовую целостность.

Итоги

Симптомы напоминали проблему форматирования, но корневая причина — смешанное содержимое перед заголовком в сочетании с «мягким» флагом конвертации. Надёжный путь — либо явно указать pandas, где начинается заголовок, либо переводить в числа только строки набора данных. Избегайте предварительного округления при экспорте. С этими корректировками числа в научной записи парсятся как настоящие числовые значения и предсказуемо ведут себя в Excel и на графиках.