在pandas读取ucs之前对数据文件进行预处理

Question

我使用SAP的数据输出，但它既不是CSV，因为它不引用包含分隔符的字符串，也不是固定宽度的，因为它有多字节字符。这是一种“固定宽度”的字符。在

为了将它放入pandaps，我当前读取了该文件，获取了分隔符的位置，将分隔符周围的每一行切分，然后将其保存到一个适当的CSV中，我可以毫不费力地读取该文件。在

我看到pandas read_csv可以得到一个文件缓冲区。如果不保存csv文件，如何将流直接传递给它？我要做发电机吗？我能得到吗csv.writer.writerow输出而不给它一个文件句柄？在

这是我的代码：

import pandas as pd

caminho= r'C:\Users\user\Documents\SAP\Tests\\'
arquivo = "ExpComp_01.txt"
tipo_dado = {"KEY_GUID":"object", "DEL_IND":"object", "HDR_GUID":"object", , "PRICE":"object", "LEADTIME":"int16", "MANUFACTURER":"object", "LOAD_TIME":"object", "APPR_TIME":"object", "SEND_TIME":"object", "DESCRIPTION":"object"} 

def desmembra(linha, limites):
    # This functions receives each delimiter's index and cuts around it
    posicao=limites[0]    
    for limite in limites[1:]:
        yield linha[posicao+1:limite]
        posicao=limite

def pre_processa(arquivo):
    import csv
    import os
    # Translates SAP output in standard CSV
    with open(arquivo,"r", encoding="mbcs") as entrada, open(arquivo[:-3] +
    "csv", "w", newline="", encoding="mbcs") as saida:
        escreve=csv.writer(saida,csv.QUOTE_MINIMAL, delimiter=";").writerow
        for line in entrada:
            # Find heading
            if line[0]=="|":
                delimitadores = [x for x, v in enumerate(line) if v == '|']
                if line[-2] != "|": 
                    delimitadores.append(None)
                cabecalho_teste=line[:50]
                escreve([campo.strip() for campo in desmembra(line,delimitadores)])
                break
        for line in entrada:
            if line[0]=="|" and line[:50]!=cabecalho_teste:
                escreve([campo.strip() for campo in desmembra(line, delimitadores)])

pre_processa(caminho+arquivo)       
dados = pd.read_csv(caminho + arquivo[:-3] + "csv", sep=";",
                    header=0, encoding="mbcs", dtype=tipo_dado)

---

另外，如果您可以分享最佳实践： 我有奇怪的日期时间字符串，如20.120.813.132.432，我可以使用

^{pr2}$

我不能为它编写解析器，因为我用不同的字符串格式存储日期。指定一个转换器在导入过程中执行该操作会更快，还是让pandas在最后按列执行？ 我对代码99999999有一个类似的问题，我必须给99.999.999添加点。我不知道我是写一个转换器，还是等到导入后再做df.replace

编辑——示例数据：

---

|                        KEY_GUID|DEL_IND|                        HDR_GUID|Prod_CD |DESCRIPTION                      |      PRICE|LEADTIME|MANUFACTURER|          LOAD_TIME|APPR_TIME     |          SEND_TIME|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|000427507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123636|Vneráéíoaeot.sadot.m             |     29,55 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.157 |
|000527507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123643|Tnerasodaeot|sadot.m             |    122,91 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.141 |
|0005DB50112F9E69E10000000A1D2028|       |384BB350BF56315DE20062700D627978|75123676|Dnerasodáeot.sadot.m             |252.446,99 |3       |POLAND      |20.121.226.175.640 |20121226183608|20.121.222.000.015 |
|000627507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123652|Pner|sodaeot.sadot.m             |    657,49 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.128 |
|000727507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|        |Rnerasodaeot.sadot.m             |    523,63 |30      |            |20.120.813.132.432 |20120813132929|20.120.707.010.119 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|                        KEY_GUID|DEL_IND|                        HDR_GUID|Prod_CD |DESCRIPTION                      |      PRICE|LEADTIME|MANUFACTURER|          LOAD_TIME|APPR_TIME     |          SEND_TIME|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------   |000827507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123603|Inerasodéeot.sadot.m             |  2.073,63 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.127 |
|000927507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123662|Ane|asodaeot.sadot.m             |      0,22 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.135 |
|000A27507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123626|Pneraíodaeot.sadot.m             |    300,75 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.140 |
|000B27507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|        |Aneraéodaeot.sadot.m             |      1,19 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.131 |
|000C27507E64FB29E2006281548EB186|       |4C1AD7E25DC50D61E10000000A19FF83|75123613|Cnerasodaeot.sadot.m             |     30,90 |30      |            |20.120.813.132.432 |20120813132929|20.120.505.010.144 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

我将处理其他具有其他字段的表。都是这样的。我只能相信标题中的分隔符。我也可能在数据中有重复的标题。看起来像是一份打印出来的材料。在

Answer 1

如果您想在不先写入CSV的情况下构建一个数据帧，那么就不需要了 需要pd.read_csv。可以使用io.BytesIO或 cString.StringIO要写入类似于内存中文件的对象，它不会 转换iterable值的意义（如desmembra(line, delimitadores)） 只需使用pd.read_csv重新解析它。在

相反，使用pd.DataFrame更直接，因为pd.DataFrame可以接受行数据的迭代器。在

使用纯Python逐个操作值通常不是最快的方法。一般来说，在整列上使用Pandas函数更快。因此，我将首先将arquivo解析为字符串的数据帧，然后使用Pandas函数将列后处理为正确的数据类型和值。在

---

import pandas as pd
import os
import csv
import io

caminho = r'C:\Users\u5en\Documents\SAP\Testes\\'
arquivo = os.path.join(caminho, "ExpComp_01.txt")
arquivo_csv = os.path.splitext(arquivo)[0] + '.csv'

def desmembra(linha, limites):
    # This functions receives each delimiter's index and cuts around it
    return [linha[limites[i]+1:limites[i+1]].strip()
            for i in range(len(limites[:-1]))]

def pre_processa(arquivo, enc):
    # Translates SAP output into an iterator of lists of strings
    with io.open(arquivo, "r", encoding=enc) as entrada:
        for line in entrada:
            # Find heading
            if line[0] == "|":
                delimitadores = [x for x, v in enumerate(line) if v == '|']
                if line[-2] != "|": 
                    delimitadores.append(None)
                cabecalho_teste = line[:50]
                yield desmembra(line, delimitadores)
                break
        for line in entrada:
            if line[0] == "|" and line[:50] != cabecalho_teste:
                yield desmembra(line, delimitadores)                

def post_process(dados):
    dados['LEADTIME'] = dados['LEADTIME'].astype('int16')
    for col in ('SEND_TIME', 'LOAD_TIME', 'PRICE'):
        dados[col] = dados[col].str.replace(r'.', '')
    for col in ('SEND_TIME', 'LOAD_TIME', 'APPR_TIME'):
        dados[col] = pd.to_datetime(dados[col], format="%Y%m%d%H%M%S")
    return dados

enc = 'mbcs'  
saida = pre_processa(arquivo, enc)
header = next(saida)
dados = pd.DataFrame(saida, columns=header)
dados = post_process(dados)
print(dados)

收益率

^{pr2}$