python - 如何使用 python 或 scala 将非标准 csv 文件读入数据框

Question

我有一个下面的数据集采样需要使用 python 或 scala 进行处理:

FWD,13032009:09:01,10.56| FWD,13032009:10:53,11.23| FWD,13032009:15:40,23.20
SPOT,13032009:09:04,11.56| FWD,13032009:11:45,11.23| SPOT,13032009:12:30,23.20
FWD,13032009:08:01,10.56| SPOT,13032009:12:30,11.23| FWD,13032009:13:20,23.20| FWD,13032009:14:340,56.00
FWD,13032009:08:01,10.56| SPOT,13032009:12:30,11.23| FWD,13032009:13:20,23.20

每一行都将被分割成多个可以进一步分割的较小字符串。

我正在寻找一种有效的方法来生成包含以下内容的 RDD 或 Dataframe:

FWD,13032009:09:01,10.56 
FWD,13032009:10:53,11.23
FWD,13032009:15:40,23.20
SPOT,13032009:09:04,11.56
FWD,13032009:11:45,11.23
SPOT,13032009:12:30,23.20
FWD,13032009:08:01,10.56
SPOT,13032009:12:30,11.23
FWD,13032009:13:20,23.20
FWD,13032009:14:340,56.00
FWD,13032009:08:01,10.56
SPOT,13032009:12:30,11.23
FWD,13032009:13:20,23.20

请注意，效率越高越好，因为生产中的总行数可能高达百万

非常感谢。

Answer 1

假设您正在读取 csv 文件，您可以将每一行读取到列表中。展平这些值，然后将它们作为单独的行进行处理。

将文件读入列表 - 100 万行应该不会太多而难以处理:

import csv
import itertools

import pandas as pd

with open('test.csv','r') as f:
    reader = csv.reader(f, delimiter = '|')
    rows = list(reader)

从单个列表中展平和分割 - Python 标准库中优秀的 itertools 库返回一个有助于内存且高效的生成器。

flat_rows = itertools.chain.from_iterable(rows)
list_rows = [i.strip().split(',') for i in flat_rows]

嵌套列表list_rows现在为您提供一个干净且格式化的列表，如果您想创建dataframe，您可以将其发送到pandas .

list_rows
>>
[['FWD', '13032009:09:01', '10.56'],
 ['FWD', '13032009:10:53', '11.23'],
 ['FWD', '13032009:15:40', '23.20'],
 ['SPOT', '13032009:09:04', '11.56'],
 ['FWD', '13032009:11:45', '11.23'],
 ['SPOT', '13032009:12:30', '23.20'],
 ['FWD', '13032009:08:01', '10.56'],
 ['SPOT', '13032009:12:30', '11.23'],
 ['FWD', '13032009:13:20', '23.20'],
 ['FWD', '13032009:14:340', '56.00'],
 ['FWD', '13032009:08:01', '10.56'],
 ['SPOT', '13032009:12:30', '11.23'],
 ['FWD', '13032009:13:20', '23.20']]

df = pd.DataFrame(list_rows)