将混乱的CSV文件解析为Pandas DataFrame的最佳方法

Question

我想要将Matlab代码迁移到Python代码。虽然我对Python不熟悉，但我仍在尝试解析一个CSV文件。

我的意图是解析一个结构类似于以下示例的CSV文件：

SENSORID;DATESMPL;TRE;ISRC
FQBI-000-001;08/01/2020 13:56:00;-10.0956;0.03662119
LAMBDAS;1550;1551;1552;1553;1554
REFERENCE;6961.058824;6959.564706;6959.423529;6960.988235;6961.788235
1;166;164;162;138;162
2;146;152;161;143;142
3;138;147;150;133;124
4;134;120;158;145;133
5;135;157;135;139;137

预期结果（在Python数据帧上）：

    SENSORID         DATESMPL           TRE       ISRC     1550  1551  1552  1553  1554
0 FQBI-000-001  08/01/2020 13:56:00  -10.0956  0.03662119   166  164   162   138   162
1 FQBI-000-001  08/01/2020 13:56:00  -10.0956  0.03662119   146  152   161   143   142
2 FQBI-000-001  08/01/2020 13:56:00  -10.0956  0.03662119   138  147   150   133   124
3 FQBI-000-001  08/01/2020 13:56:00  -10.0956  0.03662119   134  120   158   145   133
4 FQBI-000-001  08/01/2020 13:56:00  -10.0956  0.03662119   135  157   135   139   137

参考行将被丢弃。对于每个实际的测量数据行（以1到5的整数开头），SENSORID、DATESMPL、TRE和ISRC的值必须被复制。

当然，我要解析的实际CSV文件比我的示例要大得多，即LAMBDA从1550到1850，有255个测量行（每个文件约250 KB）。

为了使事情更加简单，我最终将不得不导入多达10,000个这些文件并将它们存储在一个唯一的DataFrame中。

使用Matlab，我可以使用textscan函数来解析这些文件，并将数据存储在提供统计工具箱的数据集对象中。导入这些文件的10000个可以在不到10分钟的时间内完成，这对于这种情况是可以接受的。

在Python下完成这项任务的最佳方法是什么？

看起来有很多方法可以做到这一点：

- 将文件内容作为字符串读取到列表中 - 使用NumPy数组或简单地使用 - 使用DataFrame.read_csv() 但我不确定哪一种方法是最有效的。

我真的希望能够保持与Matlab相近（或更好，当然）的性能。

- Xav59130

---

请分享你使用了什么代码，以及它们的结果如何？pd.read_csv()非常灵活，可能会有所帮助。 - aayush_malik

很让人惊奇的是Matlab能够在没有任何预处理的情况下解析这样的CSV文件，每个文件是否包含单个 lambdas、fqbi 和 reference 或者多个？ - Umar.H

@Datanovice：抱歉如果有误导，我正在Matlab下使用textscan，但我需要4个调用和一些处理。第一个调用是获取标题的第一行（SENSORID，...），第二个调用是相关信息（FQBI-000-001，...），第三个调用是获取波长列表（1550，...），最后一个调用是读取所有剩余行（实际测量行）。 - Xav59130

读取csv文件是问题的一部分。另一部分是如何处理10000个文件并避免在构建唯一的DataFrame时复制数据。LAMBDAS行对于所有文件是否相同？所有数据行是否都包含255个测量值？ - FredrikHedman

Answer 1

我想提供一个更快的解决方案，因为你提到性能很重要。这个方法的执行速度比Code_Different的解决方案快每个文件5-10倍，但对于更大的文件，你需要自行测试。

def parse(file):
       columns = []
       #general_values = [] # use this if the meta data columns are different
       column_values = ['SENSORID', 'DATESMPL', 'TRE', 'ISRC']
       measurement_values = []

       with open('tmp.csv', "r") as f:
              for index, row in enumerate(f):
                     if index > 3:  # test for measurement rows first as you will do it most often
                            measurement_values.append(row[:-1].split(';')[1:])
                     # uncomment next elif-clause if the meta data column names differ per file
                     #elif index == 0:  # first row -> SENSORID;DATESMPL;TRE;ISRC
                     #       columns += row[:-1].split(';')  # get rid of newline and split
                     elif index == 1:  # second row -> meta data
                            general_values = row[:-1].split(';') # get rid of newline and split
                     elif index == 2:  # fourth row  -> Lambdas as column names
                            columns += row[:-1].split(';')[1:]  # get rid of newline, split and delete 'LAMBDAS'

       df_array = [columns]
       for measurement in measurement_values:
              df_array.append(general_values + measurement)
       return pd.DataFrame(df_array)

df = parse('tmp.csv')

- Cribber

Answer 2

你需要对文件进行两次解析：一次获取元数据，另一次获取数据。然后将这两个数据框合并在一起：

path = '/path/to/file.txt'
meta = pd.read_csv(path, sep=';', nrows=1)
data = pd.read_csv(path, sep=';', skiprows=[0,1,3]).drop(columns='LAMBDAS')

# Limit the `fillna` to the columns in `meta`
df = pd.concat([meta, data], axis=1)
df[meta.columns] = df[meta.columns].fillna(method='ffill')

# If you are sure `data` has no NaN
df = pd.concat([meta, data], axis=1).fillna(method='ffill')

- Code Different