How to use apply in a Pandas dataframe slice to set values of multiple columns

Question

I am trying to use apply function to assign new values to two existing columns in a dataframe slice using a .loc query.

To reproduce - first create a dataframe:

import re
import panads as pd
data = [[1000, "MSL", "Test string"], [2000, 'AGL', 'other string'], [0, 'AGL', "xxxx SFC-10000ft MSLXXX"]]
df = pd.DataFrame(data=data, columns=['Alt', "AltType",'FreeText'])

Then create the apply function

def testapply(row):
    try:
        match = re.findall("SFC-([0-9]+)FT (MSL|AGL|FL)", row.FreeText)[0]
        return (int(match[0]), match[1])
    except:
        return (0, row.AltType)

When I run

df.loc[df['Alt']==0, ['Alt', 'AltType']] = df.loc[df['Alt']==0].apply(testapply, axis=1)

I would like to get as a result is:

     Alt AltType                 FreeText
0   1000     MSL              Test string
1   2000     AGL             other string
2  10000     MSL  xxxx SFC-10000ft MSLXXX

but what I end up getting is:

            Alt       AltType                 FreeText
0          1000           MSL              Test string
1          2000           AGL             other string
2  (10000, MSL)  (10000, MSL)  xxxx SFC-10000FT MSLXXX

Does anyone know how to make this work in one fell swoop?

I explained in my answer you need to return a list instead of tuple. the problem will be solved by adding a tolist() at the end of return (int(match[0]), match[1]) in your function — Mehdi Golzadeh
– Mehdi Golzadeh, Commented Oct 9, 2020 at 14:03

It_is_Chris · Accepted Answer · 2020-10-09 12:55:40Z

2

Just add tolist()

df.loc[df['Alt']==0, ['Alt', 'AltType']] = df.loc[df['Alt']==0].apply(testapply, axis=1).tolist()

answered Oct 9, 2020 at 12:55

It_is_Chris

14.2k3 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

crackernutter Over a year ago

Chaining tolist to the apply function is what worked for me, not converting the return value from within the apply function. as @mhDG7 specified.

wwnde · Accepted Answer · 2020-10-09 13:09:09Z

1

Use loc accessor to select relevant Alt column. Compute value by extracting digit from matching FreeText using regex

df.loc[df['Alt']==0,'Alt']=df.loc[df['Alt']==0,'FreeText'].str.extract('(\d+)')[0]



     Alt AltType                 FreeText
0   1000     MSL              Test string
1   2000     AGL             other string
2  10000     AGL  xxxx SFC-10000ft MSLXXX

answered Oct 9, 2020 at 13:09

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

1 Comment

crackernutter Over a year ago

This would technically work except in the more general case of more complicated workflows. I simplified my actual code for the purposes of this thread.

Shubham Sharma · Accepted Answer · 2020-10-09 13:10:33Z

1

Let's try Series.str.extract and use boolean indexing with loc to replace the values in columns Alt and AltType where the column Alt contains 0:

m = df['Alt'].eq(0)
df.loc[m, ['Alt', 'AltType']] = df.loc[m, 'FreeText'].str.extract(r'(?i)SFC-(\d+)FT\s(MSL|AGL|FL)').values

     Alt AltType                 FreeText
0   1000     MSL              Test string
1   2000     AGL             other string
2  10000     MSL  xxxx SFC-10000ft MSLXXX

edited Oct 9, 2020 at 13:10

answered Oct 9, 2020 at 13:00

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

1 Comment

crackernutter Over a year ago

This would technically work except in the more general case of more complicated workflows. I simplified my actual code for the purposes of this thread.

Mehdi Golzadeh · Accepted Answer · 2020-10-09 13:10:57Z

0

Only change your testapply function to this:

def testapply(row):
    try:
        match = re.findall("SFC-([0-9]+)ft (MSL|AGL|FL)", row.FreeText)[0]
        print(match)
        return (int(match[0]), match[1]).tolist()
    except:
        return (0, row.AltType)

edited Oct 9, 2020 at 13:10

answered Oct 9, 2020 at 13:05

Mehdi Golzadeh

2,5931 gold badge18 silver badges28 bronze badges

Collectives™ on Stack Overflow

How to use apply in a Pandas dataframe slice to set values of multiple columns

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related