从 CSV 文件中删除新行

Question

我想删除 CSV 文件字段数据中的换行符。 SO /其他地方的多人问了同样的问题。但是，提供的解决方案是在脚本中。我正在寻找 PYTHON 或 Spark（不仅是这两种）等编程语言的解决方案，因为我有相当大的文件。

以前问过关于同一主题的问题：

• 从 CSV 文件的字符串列中删除换行符

• 用空格替换双引号之间的换行符

• 从 CSV 文件的字符串列中删除新行

• https://unix.stackexchange.com/questions/222049/how-to-detect-and-remove-newline-character-within-a-column-in-a-csv-file

我有一个大小为 ~1GB 的 CSV 文件，想删除字段数据中的换行符。 CSV 文件的架构动态变化，因此我无法对架构进行硬编码。换行符并不总是出现在逗号之前，它甚至在一个字段中随机出现。

样本数据：

 playerID,yearID,gameNum,gameName,teamName,lgID,GP,startingPos
gomezle01,1933,1,Cricket,Team1,NYA,AL,1
ferreri01,1933,2,Hockey,"This is
Team2",BOS,AL,1
gehrilo01,1933,3,"Game name is
Cricket"
,Team3,NYA,AL,1
gehrich01,1933,4,Hockey,"Here it is
Team4",DET,AL,1
dykesji01,1933,5,"Game name is
Hockey"
,"Team name
Team5",CHA,AL,1

预期输出：

 playerID,yearID,gameNum,gameName,teamName,lgID,GP,startingPos
gomezle01,1933,1,Cricket,Team1,NYA,AL,1
ferreri01,1933,2,Hockey,"This is Team2",BOS,AL,1
gehrilo01,1933,3,"Game name is Cricket" ,Team3,NYA,AL,1
gehrich01,1933,4,Hockey,"Here it is Team4",DET,AL,1
dykesji01,1933,5,"Game name is Hockey","Team name Team5",CHA,AL,1

换行符可以出现在任何字段的数据中。

编辑： 根据代码截图：

原文由 data_addict 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

如果您使用的是 pyspark ，那么我建议您使用 sparkContext 的 wholeTextFiles 函数来读取文件， 因为您的文件需要作为整个文本来读取以进行适当的解析。

使用 wholeTextFiles 阅读后，您应该通过将行尾字符替换为 进行解析，并进行一些额外的格式化，以便将整个文本分解为八个字符串的组。

 import re
rdd = sc.wholeTextFiles("path to your csv file")\
    .map(lambda x: re.sub(r'(?!(([^"]*"){2})*[^"]*$),', ' ', x[1].replace("\r\n", ",").replace(",,", ",")).split(","))\
    .flatMap(lambda x: [x[k:k+8] for k in range(0, len(x), 8)])

你应该得到输出

[u'playerID', u'yearID', u'gameNum', u'gameName', u'teamName', u'lgID', u'GP', u'startingPos']
[u'gomezle01', u'1933', u'1', u'Cricket', u'Team1', u'NYA', u'AL', u'1']
[u'ferreri01', u'1933', u'2', u'Hockey', u'"This is Team2"', u'BOS', u'AL', u'1']
[u'gehrilo01', u'1933', u'3', u'"Game name is Cricket"', u'Team3', u'NYA', u'AL', u'1']
[u'gehrich01', u'1933', u'4', u'Hockey', u'"Here it is Team4"', u'DET', u'AL', u'1']
[u'dykesji01', u'1933', u'5', u'"Game name is Hockey"', u'"Team name Team5"', u'CHA', u'AL', u'1']

如果您想将所有数组 rdd 行转换为行字符串，那么您可以添加

.map(lambda x: ", ".join(x))

你应该得到

playerID, yearID, gameNum, gameName, teamName, lgID, GP, startingPos
gomezle01, 1933, 1, Cricket, Team1, NYA, AL, 1
ferreri01, 1933, 2, Hockey, "This is Team2", BOS, AL, 1
gehrilo01, 1933, 3, "Game name is Cricket", Team3, NYA, AL, 1
gehrich01, 1933, 4, Hockey, "Here it is Team4", DET, AL, 1
dykesji01, 1933, 5, "Game name is Hockey", "Team name Team5", CHA, AL, 1

原文由 Ramesh Maharjan 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

您可以使用 re 、 pandas 和 io 模块，如下所示：

 import re
import io
import pandas as pd

with open('data.csv','r') as f:
    data = f.read()
df = pd.read_csv(io.StringIO(re.sub('"\s*\n','"',data)))

for col in df.columns: #To replace all line breaks in all textual columns
    if df[col].dtype == np.object_:
        df[col] = df[col].str.replace('\n','');

In [78]: df
Out[78]:
    playerID    yearID  gameNum gameName               teamName        lgID GP  startingPos
0   gomezle01   1933    1       Cricket                Team1           NYA  AL  1
1   ferreri01   1933    2       Hockey                 This is Team2   BOS  AL  1
2   gehrilo01   1933    3       Game name is Cricket   Team3           NYA  AL  1
3   gehrich01   1933    4       Hockey  Here it is     Team4           DET  AL  1
4   dykesji01   1933    5       Game name is Hockey    Team name Team5 CHA  AL  1

如果你想要这个 DataFrame 作为输出 CSV 文件使用：

 df.to_csv('./output.csv')

原文由 O.Suleiman 发布，翻译遵循 CC BY-SA 3.0 许可协议