如何使用Python从文本文件中获取表格格式数据

3
我有一个文本文件中的表格数据,我正在尝试使用Python获取这些数据,但我找不到每列之间的分隔符。请帮帮我。 提前致谢。
数据可能如下所示:
Column1           Column2         Column3            Column4
----------------------------------------------------------------------------
apple fruits      banana fruits     orange fruits    grapes fruits
mango fruits      pineapple fruits                   blackberry fruits
                  blueberry fruits  currant fruits   papaya fruits
chico fruits                        peach fruits     pear fruits

我的期望结果是以字典格式呈现。


1
你试过使用制表符(\t)了吗?另外,发布一下你用来获取数据的代码可能会有所帮助。 - Aimery
可以使用http://docs.astropy.org/en/stable/api/astropy.io.ascii.FixedWidth.html#astropy.io.ascii.FixedWidth。 - Evgeny
2个回答

1
我假设数据在每个记录中都以相同的列对齐。我将标题行和典型行分别放在两个变量中,您将从文件中读取它们。
>>> a = 'Column1           Column2             Column3             Column4'
>>> b = 'apple fruits      banana fruits       orange fruits       grapes fruits'

i是一个索引列表,初始为空,inside表示我们正在处理列名

>>> i = []
>>> inside = False

我们计算字符并检查是否在列名的开头。
>>> for n, c in enumerate(a):
...     if c == ' ':
...         inside = False
...         continue
...     if not inside:
...         inside = True
...         i.append(n)
>>> i
[0, 18, 38, 58]

我们有列开头的索引,下一列的开始位置在切片表示法中也是当前列的结束位置 --- 我们只需要最后一列的结尾,但使用切片表示法可以使用值None
>>> [b[j:k].rstrip() for j, k in zip(i,i[1:]+[None])]
['apple fruits', 'banana fruits', 'orange fruits', 'grapes fruits']

当然,您需要对输入文件中的每个数据行应用相同的索引技巧。
附注:您可能希望使用itertools.zip_longest方法,如下所示。
[... for j, k in itertools.zip_longest(i, i[1:])]

你可能希望缓存生成器以避免为每个数据行实例化它。
cached_indices = list(itertools.zip_longest(i, i[1:]))
for line in data:
    c1, c2, c3, c4 = [... for i, j in cached_indices]

我尝试着实现了我在下面评论中提出的建议,这是我最好的努力...

$ cat fetch.py
from itertools import count  # this import is necessary
from io import StringIO      # this one is needed to simulate an open file

# Your data, notice that some field in the last two lines is misaligned
data = '''\
Column1           Column2           Column3          Column4
----------------------------------------------------------------------------
apple fruits      banana fruits     orange fruits    grapes fruits
mango fruits      pineapple fruits                   blackberry fruits
                   blueberry fruits currant fruits   papaya fruits
chico fruits                        peach fruits    pear fruits
'''

f = StringIO(data) # you may have something like
                   # f = open('fruitfile.fixed')

# read the header line and skip a line                   
header = next(f).rstrip()
next(f) # skip a line

# a compact way of finding the starts of the columns
indices = [i for i, c0, c1 in zip(count(), ' '+header, header)
           if c0==' ' and c1!=' ']
# We are going to reuse zip(indices, indices[1:]+[None]), so we cache it
ranges = list(zip(indices, indices[1:]+[None]))

# we are ready for a loop on the lines of the file
for nl, line in enumerate(f, 3):
    if line == '\n': continue # don't process blank lines
    # extract the _raw_ fields from a line
    fields = [line[i:j] for i, j in ranges]
    # check that a non-all-blanks field does not start with a blank,
    # check that a field does not terminate wit anything but a space
    # or a newline
    if any((f[0]==' ' and f.rstrip()) or f[-1] not in ' \n' for f in fields):
        # signal the possibility of a misalignment
        print('Possible misalignment in line n.%d:'%nl)
        print('\t|'+header)
        print('\t|'+line.rstrip())
    # the else body is executed if all the fields are OK
    # what I do with the fields is just a possibility
    else:
        print('Data Line n.%d:'%nl)
        fields = [field.rstrip() for field in fields]
        for nf, field in enumerate(fields, 1):
            print('\tField n.%d:\t%r'%(nf, field))

$ python3 fetch.py 
Data Line n.3:
        Field n.1:      'apple fruits'
        Field n.2:      'banana fruits'
        Field n.3:      'orange fruits'
        Field n.4:      'grapes fruits'
Data Line n.4:
        Field n.1:      'mango fruits'
        Field n.2:      'pineapple fruits'
        Field n.3:      ''
        Field n.4:      'blackberry fruits'
Possible misalignment in line n.5:
        |Column1           Column2           Column3          Column4
        |                   blueberry fruits currant fruits   papaya fruits
Possible misalignment in line n.6:
        |Column1           Column2           Column3          Column4
        |chico fruits                        peach fruits    pear fruits
$ 

可能会错过一列,例如“芒果水果 菠萝水果 黑莓水果”这样的行,实际上应该有4列。 - Evgeny
@EvgenyPogrebnyak 不,我将得到['芒果水果','菠萝水果','','黑莓水果'],即列表元素中的一个(或多个)将是空字符串。 - gboffi
1
我明白了,很好!也许i可以为了可读性而重命名,我有一个类似的变量叫做starts - Evgeny
@EvgenyPogrebnyak,即使特定行为空,由于超出范围的切片始终对应于空字符串,因此我得到了空字符串。关于istarts,我会更大胆地使用column_starts,但我来自一个每个保存的位都有价值的时代...说真的,我更喜欢那些非常本地且意图几乎是自我解释的东西的较短名称。 - gboffi
谢谢大家。如果值与列标题错位,那可能会变得棘手。 - vivin
显示剩余4条评论

0

[0, 18, 38, 58] 的神奇之处在于它们是列的起始位置,这也对我的答案产生了影响,但它基于 numpy.genfromtxt()

from pathlib import Path
import pandas as pd
import numpy as np

# replicate the file
doc = """Column1           Column2             Column3             Column4
----------------------------------------------------------------------------
apple fruits      banana fruits       orange fruits       grapes fruits
mango fruits      pineapple fruits                        blackberry fruits
                  blueberry fruits    currant fruits      papaya fruits
chico fruits                          peach fruits        pear fruits"""

Path('temp.txt').write_text(doc)

# read the file    
lines = Path('temp.txt').read_text().split('\n')

# play with header to find the column widths
header = lines[0]
length = max([len(line) for line in lines])
starts = [i for i, char in enumerate(header) if char=='C'] + [length]
widths = [x-prev for x, prev in zip(starts[1:], starts[:-1])] 
assert sum(widths) == length
data = np.genfromtxt('temp.txt', dtype=None, delimiter=widths, autostrip=True,
                     encoding='utf-8')

# make pandas dataframe 
colnames = [x for x in header.split(' ') if x]
df = pd.DataFrame(data[2:], columns=colnames)

# check it is what we wanted
assert df.to_csv(index=False) == \
"""Column1,Column2,Column3,Column4
apple fruits,banana fruits,orange fruits,grapes fruits
mango fruits,pineapple fruits,,blackberry fruits
,blueberry fruits,currant fruits,papaya fruits
chico fruits,,peach fruits,pear fruits
"""

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接