Extracting Data into Columns using pdfplumber

[arvin]

I have a pdf which has data in tabular format and has 6 columns but the columns are not separated by boundaries so when I extract the data using pdfplumber, all the data comes in one cell only and I want in separate cells.

How could I do that?

For your reference:

15/03/2021 RTGS-UTIBR52021031300662458-VIRENDER KUMAR 2,60,635.00 2,94,873.94Cr
11/03/2021 IMPS/P2A/107018040382/XXXXXXXXXX0980/trf 49,500.00 34,238.94Cr
11/03/2021 IMPS/P2A/107018771795/KINGDOMHOTELAND/trf 35,000.00 83,738.94Cr

Thanks in advance

[Pedroski55]

Looks like each line of your data is separated by a space.

If that is so, just use .split() on each line to get a list of each item of data in the line.

The list can then be written to Excel or, I remember Menator mentioned a module called tabulate which formats text data nicely!

[arvin]

I thought of the same thing, but some lines contain more data and some lines contain less.

[DPaul]

Which method do you use to extract the data from a page?
Paul

[arvin]

table = pdf.pages[0].extract_table()
df = pd.DataFrame(table[1:])

[Pedroski55]

What exactly is table? Actual text? Bytes? Or some kind of pdfplumber object?

Are column headers included in table?

[arvin]

[Pedroski55]

I'm starting to understand your problem!

Using reportlab I made a very simple pdf with 1 table of 4 rows and 5 columns. The table had borders.

The data for the table looks like this:

data= [['00', '', '02', '', '04'],
['10', '', '12', '', '14'],
['20', '', '22', '', '24'],
['30', '', '32', '', '34']]

As you can see, the columns 2 and 4 contain no data, just ''

Using this, I got the data from the table:

import pdfplumber # Parse pdf with pdfplumber.open(path2pdf + savename2) as pdf: # Get the first page of the object page = pdf.pages[0] # Get the text data of the page text = page.extract_text() # Get all the tabular data of this page tables = page.extract_tables() # Traversing table for t_index in range(len(tables)): table = tables[t_index] # Traversing each row of data for data in table: print(data)

Which gives this as output, you can see the empty values, so they should be in your pandas df as NaN, I suppose.

Output:
['00', '', '02', '', '04'] ['10', '', '12', '', '14'] ['20', '', '22', '', '24'] ['30', '', '32', '', '34']

But when I make a table with no borders, there is no table, just text:

>>> tables
[]
>>> text
'00 02 04\n11 13\n20 22 23 24\n30 31 32 34'
>>>

Now how to know which columns in which rows have nothing in them??

Think think!

[arvin]

I also tried Tabula, but I had no luck.

[Pedroski55]

Try this then look at table!

import pdfplumber import re path2pdf = '/home/pedro/myPython/reportlab_python/my_pdfs/' savename1 = "simple_table1.pdf" # no borders pdf = pdfplumber.open(path2pdf + savename1) for page in pdf.pages: print(page.extract_text()) for pdf_table in page.extract_tables(): table = [] cells = [] for row in pdf_table: if not any(row): # If a line is all empty, it is considered to be the end of a record if any(cells): table.append(cells) cells = [] elif all(row): # If the line is not empty, then this line is a new line, the last one ends if any(cells): table.append(cells) cells = [] table.append(row) else: if len(cells) == 0: cells = row else: for i in range(len(row)): if row[i] is not None: cells[i] = row[i] if cells[i] is None else cells[i] + row[i] for row in table: print([re.sub(' +', ' ', cell) if cell is not None else None for cell in row]) #print([re.sub(' +', ' ', cell) if cell is not None else ' ' for cell in row]) print('---------- Dividing line ----------') pdf.close()

Output:
>>> table [['00', '', '02', '', '04'], ['10', '', '12', '', '14'], ['20', '', '22', '', '24'], ['30', '', '32', '', '34']] >>> 

Then you can put table in pandas, I hope!