Agfdhyk

I currently have following data structure in a pandas dataframe, after importing a *.txt file via read_csv:

    label   text

0   ###24293578 NaN

1   INTRO   Some text...

2   METHODS Some text...

3   METHODS Some text...

4   METHODS Some text...

5   RESULTS Some text...

6   ###24854809 NaN

7   BACKGROUND  Some text...

8   INTRO   Some text...

9   METHODS Some text...

10  METHODS Some text...

11  RESULTS Some text...

12  ###25165090 NaN

13  BACKGROUND  Some text...

14  METHODS Some text...

...

What I like to achieve is a running index for each row, retrieved from the id marked with "###":

id        label       text

24293578  INTRO       Some text...

24293578  METHODS     Some text...

24293578  ...         ...

24854809  BACKGROUND  Some text...

24854809  ...         ...

25165090  BACKGROUND  Some text...

25165090  ...         ...

I currently use following code to transform the data:

m = df['label'].str.contains("###", na=False) 

df['new'] = df['label'].where(m).ffill()

df = df[df['label'] != df['new']].copy()

df['label'] = df.pop('new').str.lstrip('#') + ' ' + df['label']

df[['id','area']] = df['label'].str.split(' ',expand=True)

df = df.drop(columns=['label'])

df

Out:

    text            id          area

1   Some text...    24293578    OBJECTIVE

...

6   Some text...    24854809    BACKGROUND

...

It does the job but I feel this isn't the best approach. Is there a way to write the code cleaner, or make it more efficient? I'm also curious, whether the a function could be directly embedded into the read_csv step.

Thank you!

Here you can do it in 3 steps:

# put in the label column into id where text is null, and strip out the #. 

# The rest will be NaN

df['id'] = df.loc[df['text'].isnull(),'label'].str.strip('#')



# forward fill in ID

df['id'].ffill(inplace=True)



# Remove the columns where text is null

df.dropna(subset=['text'], inplace=True)



>>> df

         label          text        id

1        INTRO  Some text...  24293578

2      METHODS  Some text...  24293578

3      METHODS  Some text...  24293578

4      METHODS  Some text...  24293578

5      RESULTS  Some text...  24293578

7   BACKGROUND  Some text...  24854809

8        INTRO  Some text...  24854809

9      METHODS  Some text...  24854809

10     METHODS  Some text...  24854809

11     RESULTS  Some text...  24854809

13  BACKGROUND  Some text...  25165090

14     METHODS  Some text...  25165090