Extracting maximum number from DataFrame of strings (and some NaN values)

January 18, 2024

Look at the DataFrame:

import pandas as pd
import numpy as np
data=pd.DataFrame(['random 15 numbers 128 and 12 letters','12-5','page 65'],columns=['text'])

I want to extract all numbers from the strings and write the maximum number into a new column. I achieved that with this code:

data['list']=data['text'].str.extractall('(\d+)').unstack().values.tolist()
data['max']=data['list'].apply(lambda row:max([int(x) for x in row if x is not np.nan]))

This results in this DataFrame:

First question: Is there a more elegant way to do that?

My actual problem: My code works only if there is no NaN value in my original DataFrame. How would you adapt the code in that case? The result should be a NaN column for each NaN value with the correct index. Replace the data defining line by the following to make the problem appear:

data=pd.DataFrame(['random 15 numbers 128 and 12 letters','12-5','page 65',np.nan],columns=['text'])

Additionally I want to deal the code with entrys which are not NaN but strings without a number. In that case the intermediate list should be empty and the last row should be NaN (this last thing is easy to achive by manipulating the last line).

>Solution :

Don’t use a list as intermediate, directly go with a groupby.max:

data['max'] = (data['text']
              .str.extractall('(\d+)')[0]
              .astype(int)
              .groupby(level=0).max()
              )

Output:

                                   text    max
0  random 15 numbers 128 and 12 letters  128.0
1                                  12-5   12.0
2                               page 65   65.0
3                                   NaN    NaN

If you need both the list and the max:

g = (data['text']
     .str.extractall('(\d+)')[0]
     .astype(int)
     .groupby(level=0)
     )

data['list'] = g.agg(list)
data['max'] = g.max()

Output:

                                   text           list    max
0  random 15 numbers 128 and 12 letters  [15, 128, 12]  128.0
1                                  12-5        [12, 5]   12.0
2                               page 65           [65]   65.0
3                                   NaN            NaN    NaN