table extracted from pdf including header and subheader, and need to add a column based on header value

daniell251 0
Viewed 13

I need to extract table information from pdf file, it contains the header and subheader. I used python tabula package and it gives me something like this:

Header unnanmed1 unnamed2
subheader1 subheader2 subheader3
A nan nan
1 aaa bbb
2 ccc ddd
3 eee fff
B nan nan
4 ggg hhh
5 iii jjj
6 kkk lll 
    import pandas as pd
    import numpy as np

    NaN = np.nan
    df = pd.DataFrame({
        'Header': ['subheader1', 'A', 1, 2, 3, 'B', 4, 5, 6],
        'unnanmed1': ['subheader2', NaN, 'aaa', 'ccc', 'eee', NaN, 
    'ggg', 'iii', 'kkk'],
        'unnamed2': ['subheader3', NaN, 'bbb', 'ddd', 'fff', NaN, 
    'hhh', 'jjj', 'lll'],
    })

so, what I need to get is to get this result:

Header subheader1 subheader2 subheader3
A 1 aaa bbb
A 2 ccc ddd
A 3 eee fff
B 4 ggg hhh
B 5 iii jjj
B 6 kkk lll

I consider using map to do so, but still do not have a very clear clue. Thank you very much.

pdf python
1 Answers

Finally, I came up with some hard code to solve this problem. But if anyone can provide a more efficient approach, I will very much appreciate it. Thank you.

    df = df.rename(columns = {"Header": "subheader1", "unnanmed1": 
    "subheader2", "unnamed2": "subheader3"})
    df['Header'] = NaN
    df1 = df.drop([0])
    df1 = df1.reset_index(drop = True)
    
    nan_row_index = df1[df1['subheader3'].isna()]
    nan_row_index = nan_row_index.index.values
    if nan_row_index[-1] < df1.index.values[-1]:
        nan_row_index_final = np.append(nan_row_index, 
    df1.index.values[-1])
    else:
        nan_row_index_final = nan_row_index
    
    for i in range(len(nan_row_index_final)):
        if nan_row_index_final[i] == df1.index.values[-1]:
            print("out of range")
            break
        else:
            print(nan_row_index_final[i])
        for j in range(nan_row_index_final[i],nan_row_index_final[i+1]+1):
            df1.iloc[j, 3] = df1.iloc[nan_row_index_final[i],0]
    df1 = df1.dropna().reset_index(drop = True)
daniell2510
Related