如何将pandas to_string生成的字符串转回DataFrame

我通过Pandas的to_string()方法得到字符串变量reff，该字符串以\n分隔行，每行内以多个空格作为分隔符且各列右对齐。现在有分割后的字符串列表reff_split如下：

reff_split = ['                                       Name  Average Cost                                           Cuisines  Aggregate Rating    City',
 '                                Thai Garden            13                           Cafe, American, Desserts               4.2 Abilene',
 '                               Crispy Crust            54          Desserts, Bakery, Cafe, American, Seafood               3.4 Abilene',
 '                             Finger Licious            35                      Fast Food, Cafe, BBQ, Seafood               0.0 Abilene',
 '                                    Mx Corn            62                                 Tea, Cafe, Italian               3.1 Abilene',
 '                             LPK Waterfront            61            Pizza, Italian, BBQ, Fast Food, Seafood               3.7 Abilene',
 '                               Cakes Degree            18                       Cafe, Mexican, BBQ, Desserts               3.4 Abilene',
 '                             Chateau Garlic            42                 Pizza, Bakery, Desserts, Fast Food               3.0 Abilene',
 '                             Mediumwelldone            91                    Tea, French, BBQ, Cafe, Seafood               3.5 Abilene',
 '                            Biryani Express            11              Tea, Pizza, French, Bakery, Fast Food               0.0 Abilene',
 '                                     Subway            57                    Fast Food, Tea, Bakery, Italian               3.3 Abilene',
 '                       The Grand Trunk Road            80    Tea, Pizza, Bakery, BBQ, Chinese, Mediterranean               3.9 Abilene',
 "                                Bhikharam's            89                            Bakery, Pizza, Desserts               3.0 Abilene",
 '                                Pawan Foods            74                      Tea, Cafe, American, Desserts               0.0 Abilene',
 '                               Dilli Darbar            40                        Pizza, Bakery, BBQ, Seafood               2.3 Abilene',
 '                             Piyu Fast Food            88                   Mexican, Indian, Desserts, Pizza               0.0 Abilene',
 '                                Madras Cafe            54        Tea, Pizza, American, Cafe, Indian, Seafood               0.0 Abilene',
 '                                       Druk            79                       Cafe, Bakery, Pizza, Seafood               4.1 Abilene',
 '                                    Barista            88                  Fast Food, Mexican, Bakery, Pizza               3.3 Abilene',
 '                                   Mamagoto            93 Desserts, Tea, Bakery, Cafe, Indian, Mediterranean               4.1 Abilene',
 '                               Sindhi Kulfi            37                Pizza, French, BBQ, Fast Food, Cafe               3.0 Abilene',
 '                    Aggarwal Sweet & Bakers            79                          Pizza, Mediterranean, BBQ               2.9 Abilene',
 '                              Lotus Kitchen            15                                French, Bakery, BBQ               0.0 Abilene',
 '                            Inam Muradabadi            35             Tea, Seafood, Mediterranean, Fast Food               0.0 Abilene',
 "                             Domino's Pizza            89                   Bakery, Pizza, American, Seafood               0.0 Abilene",
 '                         Linx - Premier Inn            44                                 Tea, Bakery, Pizza               3.0 Abilene',
 '                              Scrummy Bites            69                          Indian, Desserts, Seafood               3.1 Abilene',
 'Vanshika Indian, Chinese, & Parantha Corner            60                                   Pizza, Fast Food               3.1 Abilene',
 '                               Otik Hotshop            82              Desserts, Tea, BBQ, Fast Food, Indian               3.1 Abilene',
 '                               Gelato Vinto            12                Seafood, Mexican, Indian, Fast Food               3.1 Abilene',
 "                                   Tomato's            21                             Tea, Cafe, Bakery, BBQ               4.1 Abilene"]

解决方案 #

因为列内容中可能包含单个空格（比如Name列的Thai Garden），而列之间的分隔是至少两个连续空格，所以可以用正则匹配多空格作为分隔符，以下两种方法都能实现转换：

方法1：正则分割每行后构建DataFrame

import pandas as pd
import re

# 匹配至少两个连续空格的正则表达式
split_pattern = re.compile(r'\s{2,}')

# 提取表头
header = split_pattern.split(reff_split[0])
# 处理所有数据行
data_rows = [split_pattern.split(row) for row in reff_split[1:]]

# 创建DataFrame
df = pd.DataFrame(data_rows, columns=header)

# 可选：转换数值列类型
df['Average Cost'] = pd.to_numeric(df['Average Cost'])
df['Aggregate Rating'] = pd.to_numeric(df['Aggregate Rating'])

# 查看结果
print(df.head())

方法2：用read_csv结合StringIO读取（更简洁）

import pandas as pd
from io import StringIO

# 将列表转回完整字符串
full_str = '\n'.join(reff_split)

# 用read_csv读取，指定分隔符为至少两个空格，使用python引擎支持正则分隔
df = pd.read_csv(StringIO(full_str), sep=r'\s{2,}', engine='python')

# 转换数值列类型
df['Average Cost'] = pd.to_numeric(df['Average Cost'])
df['Aggregate Rating'] = pd.to_numeric(df['Aggregate Rating'])

# 查看结果
print(df.head())

两种方法都能准确分割列，避免把列内容里的单个空格当成分隔符。转换后可以按需调整列的数据类型，确保数值列能正常参与计算。

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