How do I extract mixed elements from a long list in pandas/ python

user_11496486 0
Viewed 51

I've the following long list in a dataframe that needs cleaning: df=pd.DataFrame({'Points': ['Team A v Team Bn\n13 Jan 20:00 +147 st\nScoreO (1.5)\n1.142\nScoreU (1.5)\n5.50\nScoreO (2.5)\n1.48\nScoreU (2.5)\n2.65\nScoreO (3.5)\n2.15\nScoreU (3.5)\n1.666\nScoreO (4.5)\n3.60\nScoreU (4.5)\n1.285\nScoreO (5.5)\n6.50\nScoreU (5.5)\n1.10']})

df=pd.DataFrame({'Points':
['Team A v Team Bn\n13 Jan 20:00 +147 st\nScoreO (1.5)\n1.142\nScoreU (1.5)\n5.50\nScoreO (2.5)\n1.48\nScoreU (2.5)\n2.65\nScoreO (3.5)\n2.15\nScoreU (3.5)\n1.666\nScoreO (4.5)\n3.60\nScoreU (4.5)\n1.285\nScoreO (5.5)\n6.50\nScoreU (5.5)\n1.10']})

Desired results: 1.48\n2.65

So from the list I want to extract the actual float score inside (2.5) for both ScoreO & ScoreU to achieve the above objective.

I've tried slicing but I'm far from my goal hence will not attempt to show my dismal failure (be kind).

pandas python-3.x regex
4 Answers

You can try str.extractall with Score(?:O|U) \(2\.5\)\n([0-9.]+), where Score(?:O|U) \(2\.5\)\n will match ScoreO (2.5) or ScoreU (2.5) plus a new line, and ([0-9.])+ captures the score at the next line:

df.Points.str.extractall('Score(?:O|U) \(2\.5\)\n([0-9.]+)').unstack(level=1)

#           0      
# match     0     1
# 0      1.48  2.65

If you need to join the results:

df.Points.str.extractall('Score(?:O|U) \(2\.5\)\n([0-9.]+)')
  .groupby(level=0)
  .agg('\n'.join)

#            0
#0  1.48\n2.65
user_49834500

First of all, know your input. Your actual string looks like

Team A v Team Bn
13 Jan 20:00 +147 st
ScoreO (1.5)
1.142
ScoreU (1.5)
5.50
ScoreO (2.5)
1.48
ScoreU (2.5)
2.65
ScoreO (3.5)
2.15
ScoreU (3.5)
1.666
ScoreO (4.5)
3.60
ScoreU (4.5)
1.285
ScoreO (5.5)
6.50
ScoreU (5.5)
1.10

Now, you want to get a float numeric value that is located on lines below the lines equal to ScoreO (2.5) and ScoreU (2.5).

Thus, you can set the context with either Score[OU] \(2\.5\)\n or just \(2\.5\)\n since this is enough to find the context you need to start matching from, and then you may either use (.+) (you already know the value you need is a whole line) or \d+\.\d+ (since you know it is a float value).

So, you may use either of the two solutions below:

>>> df['Points'].str.findall(r'Score[OU] \(2\.5\)\n(\d+\.\d+)').str.join('\n')
0    1.48\n2.65
>>> df['Points'].str.findall(r'\(2\.5\)\n(.+)').str.join('\n')
0    1.48\n2.65

See the regex demo

user_38329700

I was working on that when the other solution was posted but still, here is something that can be useful. You can convert this into dataframe, for later-on selection of other scores (e.g. 3.5)

>>> pat=r'ScoreO.+\((?P<ScoreO>\d+[.]\d+)\)\n(\d+[.]\d+)\nScoreU.+\((?P<ScoreU>\d+[.]\d+)\)\n(\d+[.]\d+)'
>>> df = df.Points.str.extractall(pat).droplevel(1)
>>> df.columns=['Score0','result1','ScoreU','result2']
>>> df 
  Score0 result1 ScoreU result2
0    1.5   1.142    1.5    5.50
0    2.5    1.48    2.5    2.65
0    3.5    2.15    3.5   1.666
0    4.5    3.60    4.5   1.285
0    5.5    6.50    5.5    1.10
user_64150800

You can covert the dataframe into a list, split it by lines, and iterate through the smaller strings to find your data:

def findScores (df, search_term = "2.5"):
    lst = df.to_numpy().tolist() #converts df into list
    lst = lst[0][0].split("\n") #makes a list of strings
    result = ['ScoreO', 'ScoreU']
    for i in range(len(lst)):
        if lst[i] == f'ScoreO ({search_term})':
            result[0] = lst[i+1]
        elif lst[i] == f'ScoreU ({search_term})':
            result[1] = lst[i + 1]
    return result
user_91620000
Related