如何在字符数后使用列分隔符从 txt 文件创建 csv 文件"x"

Question

我有一个txt文件，如下所示：

MT0111500000000 Anniston-Oxford-Jacksonville, AL Metropolitan Statistical Area
MT0112220000000 Auburn-Opelika, AL Metropolitan Statistical Area  
MT0113820000000 Birmingham-Hoover, AL Metropolitan Statistical Area

我需要从中创建一个csv文件，我对此没有什么经验，但一直在学习和做，尽管可能效率不高。

我现在的问题是，当我使用panda时，它是在"&"；。我需要的是列分隔符在左边的代码后面；MT0113820000000"；，尽管代码确实发生了变化，但它们的长度都是相同的。

提前谢谢，我知道这是个很难回答的问题。

这是我目前的代码：

import pandas as pd
dataframe1 = pd.read_csv("C:/Users/andre/Desktop/bea_api_test/python-bureau-economic-analysis-api-client/testttt/output.txt")  
dataframe1.to_csv('output_.csv', index = None)

输出：

COLUMN 1                                COLUMN 2
MT0111500000000 Anniston-Oxford-Jacksonville     | AL Metropolitan Statistical Area

Answer 1

或者，使用上面注释中提到的read_fwf：

from io import StringIO
import pandas as pd
testdata = '''
MT0111500000000 Anniston-Oxford-Jacksonville, AL Metropolitan Statistical Area
MT0112220000000 Auburn-Opelika, AL Metropolitan Statistical Area
MT0113820000000 Birmingham-Hoover, AL Metropolitan Statistical Area
'''
buff = StringIO(testdata)
df = pd.read_fwf(buff, header=None, colspecs=[(0, 15), (16, 64 * 1024)])
print(df.to_csv(index=False, columns=[0, 1], header=['COLUMN1', 'COLUMN2']))

Answer 2

您可以在第一次出现空白时拆分数据：

data = pd.read_table("data.txt", squeeze = True, header = None).str.split(" ", 1)
df = pd.DataFrame(data.tolist(), columns = ["column1", "column2"])
df.to_csv("df.csv")

Answer 3

这不是CSV，我看不出有什么方便的方法可以说服read_csv做正确的事情。幸运的是，这里似乎有一条简单的规则。第一个空格之前的东西，然后是后面的东西。str.split就是这样做的。

import pandas as pd
from pathlib import Path
#in_file = Path("C:/Users/andre/Desktop/bea_api_test/python-bureau-economic-analysis-api-client/testttt/output.txt")
in_file = Path("test.txt")
out_file = in_file.with_name(in_file.stem + "_").with_suffix(".csv")
# test data
open(in_file, "w").write("""
MT0111500000000 Anniston-Oxford-Jacksonville, AL Metropolitan Statistical Area
MT0112220000000 Auburn-Opelika, AL Metropolitan Statistical Area  
MT0113820000000 Birmingham-Hoover, AL Metropolitan Statistical Area""")

# convert to csv
pd.DataFrame([line.strip().split(" ",1) for line in open(in_file)],
columns=["COLUMN1", "COLUMN2"]).to_csv(out_file, index=None, headr=False)

# visual verification
print(open(out_file).read())

输出

MT0111500000000,"Anniston-Oxford-Jacksonville, AL Metropolitan Statistical Area"
MT0112220000000,"Auburn-Opelika, AL Metropolitan Statistical Area"
MT0113820000000,"Birmingham-Hoover, AL Metropolitan Statistical Area"

在这个例子中，我立即编写csv，这样数据帧就会自动从内存中删除。您也可以使用CSV模块，一次写入一行。这将使用更少的内存，因为它不必在内存中保存整个文件。由于csv是标准python库的一部分，因此不存在对pandas的外部依赖。添加一点文件名处理

import csv
from pathlib import Path
#in_file = Path("C:/Users/andre/Desktop/bea_api_test/python-bureau-economic-analysis-api-client/testttt/output.txt")
in_file = Path("test.txt")
out_file = in_file.with_name(in_file.stem + "_").with_suffix(".csv")
# test data
open(in_file, "w").write("""
MT0111500000000 Anniston-Oxford-Jacksonville, AL Metropolitan Statistical Area
MT0112220000000 Auburn-Opelika, AL Metropolitan Statistical Area  
MT0113820000000 Birmingham-Hoover, AL Metropolitan Statistical Area""")
# convert to csv
with open(in_file) as infp, open(out_file, "w") as outfp:
writer = csv.writer(outfp)
writer.writerows(line.strip().split(" ",1) for line in infp)
# visual verification
print(open(out_file).read())