Python Forum
Pandas DataFrame combine rows by column value, where Date Rows are NULL
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Pandas DataFrame combine rows by column value, where Date Rows are NULL
#1
Scenerio: Parse the PDF Bank statement and transform into clean and formatted csv file.

What I've tried: I manage to parse the pdf file(tabular format) using camelot library but failed to produce the desired result in sense of formatting.

Code:

import camelot
import pandas as pd

tables = camelot.read_pdf('test.pdf', pages = '3')

for i, table in enumerate(tables):
    print(f'table_id:{i}')
    print(f'page:{table.page}')
    print(f'coordinates:{table._bbox}')

tables = camelot.read_pdf('test.pdf', flavor='stream', pages = '3')

columns = df.iloc[0]

df.columns = columns
df = df.drop(0)
df.head()

for c in df.select_dtypes('object').columns:
    df[c] = df[c].str.replace('$', '')
    df[c] = df[c].str.replace('-', '')

def convert_to_float(num):
    try:
        return float(num.replace(',',''))
    except:
        return 0

for col in ['Deposits', 'Withdrawals', 'Balance']:
    df[col] = df[col].map(convert_to_float)
My_Result:

https://ibb.co/VYLczdr

Desired_Output:

https://ibb.co/2NZby99


The logic I came up with is to move those rows up i guess n-1 if date column is NaN i don't know if this logic is right or not.Can anyone help me to sort out this properly?

I tried pandas groupby and aggregation functions but it only merging the whole data and removing NaN and duplicate dates which is not suitable because every entry is necessary.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Compare current date on calendar with date format file name Fioravanti 1 128 Mar-26-2024, 08:23 AM
Last Post: Pedroski55
  Adding PD DataFrame column bsben 2 246 Mar-08-2024, 10:46 PM
Last Post: deanhystad
  Python date format changes to date & time 1418 4 518 Jan-20-2024, 04:45 AM
Last Post: 1418
  This result object does not return rows. It has been closed automatically dawid294 5 688 Jan-10-2024, 10:55 PM
Last Post: deanhystad
  Python Alteryx QS-Passing pandas dataframe column inside SQL query where condition sanky1990 0 690 Dec-04-2023, 09:48 PM
Last Post: sanky1990
  How is pandas modifying all rows in an assignment - python-newbie question markm74 1 654 Nov-28-2023, 10:36 PM
Last Post: deanhystad
  How to insert Dashed Lines in between Rows of a tabulate output Mudassir1987 0 463 Sep-27-2023, 10:09 AM
Last Post: Mudassir1987
  how do you style data frame that has empty rows. gsaray101 0 500 Sep-08-2023, 05:20 PM
Last Post: gsaray101
  Sequential number for rows retrieved and storing the Primary UKey to the line number GYKR 2 555 Aug-22-2023, 10:14 AM
Last Post: GYKR
  pandas : problem with conditional filling of a column Xigris 2 594 Jul-22-2023, 11:44 AM
Last Post: Xigris

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020