Aggregate strings across consecutive non-NaN cells in pandas column but not across whole column

user_10889609 0
Viewed 129

I am working on an nlp problem where I have to analyze strangely formatted excel files.

There is one column with text, where each document spans multiple cells. Documents themselves are separated by empty cells. There are other columns with scores that I want to predict from the text data.

This is what it looks like

I have imported the sheets to a pandas dataframe and now I am trying to aggregate the cells belonging to each document while preserving the scores.

This is the goal state

I have started to play around with nested loops, but I feel like it is much more complicated than necessary.

How would you approach this? Each document covers a different number of cells and documents are separated by different numbers of empty cells. To make it more complicated the scores in the columns to the right are sometimes in the same row as the first and sometimes in the same row as the last cell of the corresponding document.

I would greatly appreciate your help! There must be a simple solution.

pandas document excel text python
2 Answers

Just a simple example how it could work:

import pandas as pd
# setting up the DataFrame with sample data
df = pd.DataFrame({'Document': ['This is ', 'first', None, 'This is ', 'second', `None, 'this ', 'is ', 'third'],`
                   'Score': [None, 1, None, None, 2, None, None, 3, None]})

result_df = pd.DataFrame({'Document':[], 'Score':[]})
doc = ''
for index, row in df.iterrows():
    if pd.notnull(row['Score']):
        #any not NaN value within processed document is score 
        score = row['Score']
    if row['Document']:
        #build doc string until the line is not NaN
        doc += row['Document']
    else:
        result_df = result_df.append({'Document':doc, 'Score':score}, ignore_index=True)
        doc = ''

if doc:
    #when the last line (Document) is not NaN save/print results also:
    result_df = result_df.append({'Document':doc, 'Score':score}, ignore_index=True)

Output (result_df):

Document    Score
0   This is first   1.0
1   This is second  2.0
2   This is third   3.0
user_102578100

Using @Lukas setup:

df = pd.DataFrame({'Document': ['This is ', 'first', None, 'This is ', 'second', None, 'this ', 'is ', 'third'],
                   'Score': [None, 1, None, None, 2, None, None, 3, None]})

df.groupby(df['Document'].isna().cumsum(), as_index=False)\
  .apply(lambda x : pd.Series([''.join(x['Document'].dropna()), 
                               x.loc[x['Score'].notna(), 'Score'].values[0]], 
                              index=['Document','Score']))

Output:

         Document  Score
0   This is first    1.0
1  This is second    2.0
2   this is third    3.0
user_63615310
Related