Create Dataframe from lists with different sizes, keeping the data matching

Question

I have lists of different lengths with data extracted from a PDF.

Each value and code is part (is inside) of a respective note:

cliente = ['12345','15432']
nota = ['21/0576750-3', '20/063859-1']
codigo = [86, 6052,5031,1038,1025]
valor = [100.45, 200.34, 450.10, 150.58, 42.30]

I want to turn this data into a dataframe like this:

Note: The challenge is to be able to create relationships one for many in order to keep the correspondence between the data of the lines.

I tried the following code, but the table loses the reference of the line data.

tabela = list(zip(cliente,nota,codigo,valor)
df = pd.DataFrame(tabela, columns = ['Cliente', 'Nota', 'Código','Valor'])
df.to_excel('d:/nome.xlsx', index=False)

Could anyone help? Thank you!

Answer 1

Guys, I was able to solve the issue of creating Dataframe without losing the indexing of column information!

The solution was to use the Collections - namedtuple library

This function allows the creation of a tuple with items that work like a dictionary, maintaining the relationship between the keys and the extracted values.

Follow the code below:

import pandas as pd
import re
from collections import namedtuple


formulario = namedtuple('Formulário', 'cliente nota codigo valor')


padraoCliente = re.compile('[0-9]{2}.[0-9]{3}.[0-9]{3}\/[0-9]{4}-[0-9]{2}')
padraoNota = re.compile('Número da nota')
padraoCodigoValor = re.compile('([0-9]{4})\s(\.?[0-9]*\.?[0-9]*\.?[0-9]*,[0-9]{2})')

extracao = ocr.getTexto('arquivo.pdf')

linha_items = []

for linha in extracao.split('\n'):
    if padraoCliente.match(linha):
        cliente = linha.split()
        cliente = ' '.join(cliente)

    if padraoNota.match(linha):
        nota = linha.split()
        

    linha = padraoCodigoValor.search(linha)
    if linha:
        codigo = linha.group(1)
        valor = linha.group(2)
        
        linha_items.append(formulario(cliente nota codigo valor))

df = df = pd.DataFrame(linha_items, columns=['Número do cliente','Número da Nota', 'Código do produto', 'Valor do Produto'])

I learned the procedure by watching this video: Youtube: Use Python to Extract Invoice Lines from a semistructured PDF AP Report

Answer 2

you can do this with the following code:

pd.DataFrame({'Cliente': pd.Series(cliente), 'Nota': pd.Series(nota), 'Código': pd.Series(codigo), 'Valor': pd.Series(valor)})

Exit:

  Cliente          Nota  Código   Valor
0   12345  21/0576750-3      86  100.45
1   15432   20/063859-1    6052  200.34
2     NaN           NaN    5031  450.10
3     NaN           NaN    1038  150.58
4     NaN           NaN    1025   42.30

The only "problem" is that you will have to see how it will handle the repetition of the client code and the note.