Pandas join multine row text

user_1752366 0
Viewed 77

I am reading pdf using tabula-py.

from tabula import read_pdf
pdf_path = "/home/user/test.pdf"
df = read_pdf(pdf_path, pages='all', silent='True')

Unfortunately, my pdf formatting is not consistent and the text of one of the colum is split across multiple lines as shown below:

Date        Comment      post_id
----------------------------------
03/07/20    Comment 1           1
03/07/20    Comment b 1         2
NaN         Comment b 2       NaN
NaN         Comment b 3       NaN
04/07/20    Comment c 1         3
NaN         Comment c 1       NaN

How can I format data frame to like below

Date        Comment                             post_id
-------------------------------------------------------------
03/07/20    Comment 1                                 1
03/07/20    Comment b 1 Comment b 2 Comment b 3       2
04/07/20    Comment c 1 Comment c 1                   3

few more single lines columns which are working as expected.

pandas dataframe python
1 Answers

Because there are duplicated Date values create helper Series with test non missing values with cumulative sum by Series.cumsum and pass to GroupBy.agg with aggregate GroupBy.first and join:

g = df['Date'].notna().cumsum()

df = df.groupby(g).agg({'Date':'first', 'Comment': ' '.join}).reset_index(drop=True)
print (df)
       Date                              Comment
0  03/07/20                            Comment 1
1  03/07/20  Comment b 1 Comment b 2 Comment b 3
2  04/07/20              Comment c 1 Comment c 1

For general solution for aggregate first for all columns without Comment is possible generate dictionary dynamic:

print (df.columns)
Index(['Date', 'Comment', 'post_id'], dtype='object')

g = df['Date'].notna().cumsum()

d = dict.fromkeys(df.columns.difference(['Comment']), 'first')
d['Comment'] = ' '.join
print (d)
{'Date': 'first', 'post_id': 'first', 
  'Comment': <built-in method join of str object at 0x00000000028761B0>}


df = df.groupby(g).agg(d).reset_index(drop=True)
print (df)
       Date  post_id                              Comment
0  03/07/20      1.0                            Comment 1
1  03/07/20      2.0  Comment b 1 Comment b 2 Comment b 3
2  04/07/20      3.0              Comment c 1 Comment c 1

Similar idea with overwrite Comment key in dictionary for same order of columns in output DataFrame:

print (df.columns)
Index(['Date', 'Comment', 'post_id'], dtype='object')

g = df['Date'].notna().cumsum()

d = dict.fromkeys(df.columns, 'first')
d['Comment'] = ' '.join
print (d)
{'Date': 'first', 
 'Comment': <built-in method join of str object at 0x00000000028761B0>, 
 'post_id': 'first'}

df = df.groupby(g).agg(d).reset_index(drop=True)
print (df)
       Date                              Comment  post_id
0  03/07/20                            Comment 1      1.0
1  03/07/20  Comment b 1 Comment b 2 Comment b 3      2.0
2  04/07/20              Comment c 1 Comment c 1      3.0
user_29010020
Related