如何使用Python将单列文本文件解析为表格？

Question

如何使用Python将单列文本文件解析为表格？

4

我是新来的StackOverflow用户，但我在这个网站上找到了很多答案。我也是一个编程新手，所以我想加入并最终成为这个社区的一部分——从一个问题开始，这个问题已经困扰了我几个小时。

我登录到一个网站，并抓取b标签内的大段文本，将其转换为适当的表格。生成的Output.txt的布局如下:

BIN                   STATUS                                                   
8FHA9D8H 82HG9F     RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS          


INVENTORY CODE:   FPBC   *SOUP CANS LENTILS                                 

BIN                   STATUS                                                   
HA8DHW2H HD0138     RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS          
8SHDNADU 00A123     #2956- INVALID STOCK COUPON CODE (MISSING).          
93827548 096DBR     RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS

有许多页面都包含相同的区块，但我需要将它们组合成一个实际的表格，如下所示：

      BIN               INV CODE                          STATUS                                                   
HA8DHW2HHD0138     FPBC-*SOUP CANS LENTILS    RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS          
8SHDNADU00A123     FPBC-*SOUP CANS LENTILS    #2956- INVALID STOCK COUPON CODE (MISSING).          
93827548096DBR     FPBC-*SOUP CANS LENTILS    RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS          
8FHA9D8H82HG9F   SSXR-98-20LM NM CORN CREAM  RECEIVED SUCCESSFULLY AWAITING STOCKING PROCESS

基本上，这个例子中所有的单独文本块都将成为这个表格的一部分，并且inv代码将重复显示其Bin值。我尝试过解析这些数据（使用了Pandas / bs / openpyxl / csv writer），但我不得不承认它们有点令人尴尬，因为我找不到任何关于这个特定问题的信息。还有没有好心人可以帮帮我？ :)

（另外，我正在使用Python 2.7）

- Patrick O

3个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Burgler · Answer 1

一个简单的自定义解析器，像下面这样做应该就可以了。

from __future__ import print_function



def parse_body(s):
    line_sep = '\n'
    getting_bins = False
    inv_code = ''
    for l in s.split(line_sep):
        if l.startswith('INVENTORY CODE:') and not getting_bins:
            inv_data = l.split()
            inv_code = inv_data[2] + '-' + ' '.join(inv_data[3:])
        elif l.startswith('INVENTORY CODE:') and getting_bins:
            print("unexpected inventory code while reading bins:", l)
        elif l.startswith('BIN') and l.endswith('MESSAGE'):
            getting_bins = True
        elif getting_bins == True and l:
            bin_data = l.split()
            # need to add exception handling here to make sure:
            # 1) we have an inv_code
            # 2) bin_data is at least 3 items big (assuming two for
            #    bin_id and at least one for message)
            # 3) maybe some constraint checking to ensure that we have
            #    a valid instance of an inventory code and bin id
            bin_id = ''.join(bin_data[0:2])
            message = ' '.join(bin_data[2:])
            # we now have a bin, an inv_code, and a message to add to our table
            print(bin_id.ljust(20), inv_code.ljust(30), message, sep='\t')
        elif getting_bins == True and not l:
            # done getting bins for current inventory code
            getting_bins = False
            inv_code = ''

- Jan · Answer 2

一个相对复杂的问题，但这可能能为你提供一些启示：

import re, pandas as pd
from pandas import DataFrame

rx = re.compile(r'''
                (?:INVENTORY\  CODE:)\s*
                (?P<inv>.+\S)
                [\s\S]+?
                ^BIN.+[\n\r]
                (?P<bin_msg>(?:(?!^\ ).+[\n\r])+)
                ''', re.MULTILINE | re.VERBOSE)

string = your_string_here

# set up the dataframe
df = DataFrame(columns = ['BIN', 'INV', 'MESSAGE'])

for match in rx.finditer(string):
    inv = match.group('inv')
    bin_msg_raw = match.group('bin_msg').split("\n")

    rxbinmsg = re.compile(r'^(?P<bin>(?:(?!\ {2}).)+)\s+(?P<message>.+\S)\s*$', re.MULTILINE)
    for item in bin_msg_raw:
        for m in rxbinmsg.finditer(item):
            # append it to the dataframe
            df.loc[len(df.index)] = [m.group('bin'), inv, m.group('message')]

print(df)

说明

它查找 INVENTORY CODE 并设置组 (inv 和 bin_msg) 以便在 afterwork() 中进行进一步处理 (注意: 如果您只有一行 bin/msg，那么在此之后拆分组会更容易)。
然后，它拆分了 bin 和 msg 部分，并将所有内容附加到 df 对象中。

- Amit · Answer 3

我有一个编写用于网站抓取的代码，可能会对你有所帮助。基本上你需要做的是在网页上右键点击进入html，并尝试找到你要查找的表格标签，然后使用模块（我正在使用beautiful soup）提取信息。我正在创建一个json，因为我需要将其存储到mongodb中，你也可以创建表格。

#! /usr/bin/python

import sys
import requests
import re
from BeautifulSoup import BeautifulSoup
import pymongo

def req_and_parsing():
        url2 = 'http://businfo.dimts.in/businfo/Bus_info/EtaByRoute.aspx?ID='

        list1 = ['534UP','534DOWN']
        for Route  in list1:
                final_url = url2 + Route
                #r = requests.get(final_url)
                #parsing_file(r.text,Route)

        outdict = []
        outdict = [parsing_file( requests.get(url2+Route).text,Route)  for Route in list1 ]
        print outdict
        conn = f_connection()
        for i in range(len(outdict)):
                insert_records(conn,outdict[i])


def parsing_file(txt,Route):
        soup = BeautifulSoup(txt)
        table = soup.findAll("table",{"id" : "ctl00_ContentPlaceHolder1_GridView2"})
        #trtags = table[0].findAll('tr')

        tdlist = []
        trtddict = {}
        """
        for trtag in trtags:
                print 'print trtag- ' ,  trtag.text
                tdtags =  trtag.findAll('td')
                for tdtag in tdtags:
                        print tdtag.text
        """

        divtags = soup.findAll("span",{"id":"ctl00_ContentPlaceHolder1_ErrorLabel"})

        for divtag in divtags:
        for divtag in divtags:
                print "div tag - " , divtag.text
                if  divtag.text ==  "Currently no bus is running on this route" or "This is not a cluster (orange bus) route":
                        print "Page not displayed Errored with below meeeage for Route-", Route," , " , divtag.text
                        sys.exit()

        trtags = table[0].findAll('tr')
        for trtag in trtags:
                tdtags =  trtag.findAll('td')
                if len(tdtags) == 2:
                        trtddict[tdtags[0].text] = sub_colon(tdtags[1].text)
        return trtddict


def sub_colon(tag_str):
        return re.sub(';',',',tag_str)

def f_connection():
        try:
                conn=pymongo.MongoClient()
                print "Connected successfully!!!"
        except pymongo.errors.ConnectionFailure, e:
                print "Could not connect to MongoDB: %s" % e
        return conn

def insert_records(conn,stop_dict):
        db = conn.test
        print db.collection_names()
        mycoll = db.stopsETA
        mycoll.insert(stop_dict)

if __name__ == "__main__":
        req_and_parsing()