如何使用Pandas从.txt文件解析表

Question

aaa
bbb
ccc

=====================
A   B   C   D   E   F
=====================
1   2   3   4   5   6
7   8   9   1   2   3
4   5   6   7   8   9
1   2   3   4   5   6
---------------------

=====================
G     H     I     J
=====================
1           3     4
5           6     7
---------------------

=====================
K    L    M    N    O
=====================
1              2    3
4    5              6
7    8         9
---------------------

xxx
yyy
zzz

null

我现在的代码：

import pandas as pd
import itertools

df = pd.read_csv("xxx.txt", sep="\n", header=None)

# delimiters for header and end-of-table

h_dl = "=" * 21
r_dl = "-" * 21

for i in range(len(df.index)-2):

    # if loop to find lines which are table headers & convert to list    

    if (df.iloc[i].any() == h_dl) & (df.iloc[i+2].any() == h_dl):

        h = df.iloc[i+1].str.split().tolist()
        h = list(itertools.chain(*h))


        # while loop to find lines which are table rows & append to one list

        x = 3
        r = []

        while True:

            if df.iloc[i+x].any() == r_dl:
                break

            r.append(df.iloc[i+x].str.split().tolist())
            x += 1

        r = list(itertools.chain(*r))

        # create pandas dataframe with header and rows obtained above
        t = pd.DataFrame(data=r, columns=h)

此代码返回断言错误：传递了14列，传递的数据有15列。我知道这是因为对于表行，我使用的是.str.split()，默认情况下它在空格上拆分。由于有些列缺少值，因此对于第二个和htird表，表头中的元素数和表行中的元素数不匹配。我正在努力解决这个问题，因为表示缺失值的空格字符的数量对于每个表来说是不同的。

我的问题是：是否有一种方法可以解释某些列中丢失的值，以便在丢失值为null或NaN的情况下获得一个DataFrame作为输出？

Answer 1

通过使用Victor Ruiz方法，我添加了if选项来处理不同的头大小。

=^..^=

代码中的说明：

import re
import pandas as pd
import itertools

df = pd.read_csv("stack.txt", sep="\n", header=None)

# delimiters for header and end-of-table

h_dl = "=" * 21
r_dl = "-" * 21

for i in range(len(df.index)-2):

    # if loop to find lines which are table headers & convert to list
    if (df.iloc[i].any() == h_dl) & (df.iloc[i+2].any() == h_dl):

        h = df.iloc[i+1].str.split().tolist()
        h = list(itertools.chain(*h))

        # get header string
        head = df.iloc[i+1].to_string()
        # get space distance in header
        space_range = 0
        for result in re.findall('([ ]*)', head):
            if len(result) > 0:
                space_range = len(result)

        x = 3
        r = []
        while True:
            if df.iloc[i+x].any() == r_dl:
                break

            # strip line
            line = df.iloc[i+x].to_string()[5::]

            # collect items based on elements distance
            items = []
            for result in re.finditer('(\d+)([ ]*)', line):
                item, delimiter = result.groups()
                items.append(item)
                if len(delimiter) > space_range*2+1:
                    items.append('NaN')
                    items.append('NaN')
                if len(delimiter) < space_range*2+2 and len(delimiter) > space_range:
                    items.append('NaN')
            r.append([items])

            x += 1

        r = list(itertools.chain(*r))

        # create pandas dataframe with header and rows obtained above
        t = pd.DataFrame(data=r, columns=h)

   A  B  C  D  E  F
0  1  2  3  4  5  6
1  7  8  9  1  2  3
2  4  5  6  7  8  9
3  1  2  3  4  5  6

   G    H  I  J
0  1  NaN  3  4
1  5  NaN  6  7

   K    L    M    N     O
0  1  NaN  NaN    2     3
1  4    5  NaN  NaN     6
2  7    8  NaN    9  None