Question: I'll preface by saying I have a solution, but it feels overly complicated and inefficient and I'm looking to improve on it. I have a series inside a dataframe that looks like: >>> df = pd.DataFrame({'Distance':[None,None,'13',None,'38.12','5NW','1N,3SW,8.3E',None,'2,7']}) >>> df Distance 0 None 1 None 2 13 3 None 4 38.12 5 5NW 6 1N,3,8.3E 7 None 8 2,7  The non-None values are always strings, and each string is a comma-separated list of distances, each of which might have a fractional part and might not be followed by a cardinal direction. Cardinal directions are always represented as one of the following: N E S W NE NW SE SW  My current goal is to split this column into three:  A column for the greatest distance, represented as a float (I want to do math on it later) A column for the cardinal direction associated with the greatest value, if present A column for any remaining distances/directions, represented as a list of strings  So, for the input above, the desired output would be something like:  Distance Direction Additional_Values 0 NaN None None 1 NaN None None 2 13.00 None None 3 NaN None None 4 38.12 None None 5 5.00 NW None 6 8.30 E [1N, 3SW] 7 NaN None None 8 7.00 None [2]  So far I'm doing it like this: >>> helper = df['Distance'].str.extractall(r'(?P<distance>[\d\.]+)(?P<direction>[NESW]*)') #Get each distance + possible direction and explode to one row per value / value pair >>> helper['distance'] = helper['distance'].astype(float) #Convert distances to float >>> helper.index.set_names('orig_id',level=0,inplace=True) #Give the level 0 index a name for convenience >>> helper distance direction orig_id match 2 0 13.00 NaN 4 0 38.12 NaN 5 0 5.00 NW 6 0 1.00 N 1 8.30 E 2 3.00 SW 8 0 2.00 NaN 1 7.00 NaN >>> helperMax = helper.sort_values('distance').reset_index().drop_duplicates('orig_id',keep='last') #Get maximum distance for each index from the original df >>> helperMax = helperMax.sort_values('orig_id').set_index(['orig_id','match']) #Convert columns back to MultiIndex >>> helperMax distance direction orig_id match 2 0 13.00 NaN 4 0 38.12 NaN 5 0 5.00 NW 6 1 8.30 E 8 1 7.00 NaN >>> helperRemaining = helper.drop(index=helperMax.index) #Get remaining value pairs by dropping indices of max values >>> helperRemaining['concat'] = helperRemaining['distance'].astype(str) + helperRemaining['direction'].fillna('') #Concat value pairs back into strings >>> helperRemaining = helperRemaining.groupby('orig_id')['concat'].apply(list) #Group strings into lists >>> helperRemaining = helperRemaining.reindex(df.index) #Make index match the starting df >>> helperRemaining 0 NaN 1 NaN 2 NaN 3 NaN 4 NaN 5 NaN 6 [1.0N, 3.0SW] 7 NaN 8 [2.0] >>> helperMax = helperMax.droplevel('match').reindex(df.index) #Make helperMax index match the starting df >>> df['Distance'] = helperMax['distance'] #Replace original df column with max values >>> # Insert Direction column (df actually has many columns and order matters) >>> df.insert( >>> df.columns.get_loc('Distance') + 1, >>> 'Direction', >>> helperMax['direction'] >>> ) >>> # Insert Additional_Values column >>> df.insert( >>> df.columns.get_loc('Direction') + 1, >>> 'Additional_Values', >>> helperRemaining >>> ) >>> df Distance Direction Additional_Values 0 NaN NaN NaN 1 NaN NaN NaN 2 13.00 NaN NaN 3 NaN NaN NaN 4 38.12 NaN NaN 5 5.00 NW NaN 6 8.30 E [1.0N, 3.0SW] 7 NaN NaN NaN 8 7.00 NaN [2.0]  This gets an acceptable result, but it's a lot of steps to get there. What can I do to condense this into something less complicated, and hopefully speed it up along the way? Sidenote: I know the first three lines of my process immediately produce output in First Normal Form, and thus they'd better fit with data management best practices. Unfortunately, for compatibility reasons I can only have one table. Additionally: A column must be a single data type, and can contain lists of varying length as long as all elements across all lists are of the same data type. If there's a better way to format my table that fits those constraints and allows me to do math on the max distance, I'm all ears. Edit to clarify: When the max value occurs more than once, I want to get exactly one of them and send the rest to Additional_Values. Direction doesn't matter for this selection (aside from keeping any directions paired with their associated distances); I'm okay with e.g. sorting purely by distance and getting whichever one happens to be last.

Answer: Try: import re df = pd.DataFrame( {"Distance": [None, None, "13", None, "38.12", "5NW", "1N,3SW,8.3E", None, "2,7"]} ) pat = re.compile(r"^\d+\.?\d*") mask = df["Distance"].isna() x = ( df.loc[~mask, "Distance"] .apply( lambda x: sorted( x.split(","), key=lambda k: float(pat.search(k).group(0)), reverse=True, ), ) .to_frame() ) x["Distance2"] = x["Distance"].str[0].str.extract(r"^(\d+\.?\d*)") x["Direction"] = x["Distance"].str[0].str.extract(r"([A-Z]*)$").replace("", np.nan) x["Additional_Values"] = x["Distance"].str[1:].apply(lambda x: x if x else np.nan) x = x.reindex(df.index) print(x)  Prints:  Distance Distance2 Direction Additional_Values 0 NaN NaN NaN NaN 1 NaN NaN NaN NaN 2 [13] 13 NaN NaN 3 NaN NaN NaN NaN 4 [38.12] 38.12 NaN NaN 5 [5NW] 5 NW NaN 6 [8.3E, 3SW, 1N] 8.3 E [3SW, 1N] 7 NaN NaN NaN NaN 8 [7, 2] 7 NaN [2]