Using function in Pandas to split text -- reasons not to use apply

Question

Can this be rewritten as a function?

df2['AB18t'] = df2['AB18'].apply(lambda x: x.split(":")[0])
df2['AB18n'] = df2['AB18'].apply(lambda x: x.split(":")[1]).astype(int)
df2['AB18n'] = np.where(df2['AB18t'] == "Ab", df2['AB18n'] ,-df2['AB18n'])
df2['AB18t'] = np.where(df2['AB18t'] == "Ab", 1 ,0)

Tried

def getTextNum(x):
    df2['AB18t'] = df2['AB18'].apply(lambda x: x.split(":")[0])
    df2['AB18n'] = df2['AB18'].apply(lambda x: x.split(":")[1]).astype(int)
    df2['AB18n'] = np.where(df2['AB18t'] == "Ab", df2['AB18n'] ,-df2['AB18n'])
    df2['AB18t'] = np.where(df2['AB18t'] == "Ab", 1 ,0)


df2['AB18'].apply(getTextNum)

EDITs... form1

0     Blw:001
1      Ab:008
2      Ab:007
3      Ab:006
4      Ab:005
5      Ab:004
6      Ab:003
7      Ab:002
8      Ab:001
9     Blw:001
10     Ab:001
11    Blw:002
12    Blw:001
13     Ab:001
14    Blw:002
Name: AB18, dtype: object

form2:::

0     B:Ab:048
1     B:Ab:047
2     B:Ab:046
3     B:Ab:045
4     B:Ab:044
5     B:Ab:043
6     B:Ab:042
7     B:Ab:041
8     B:Ab:040
9     B:Ab:039
10    B:Ab:038
11    B:Ab:037
12    B:Ab:036
13    B:Ab:035
14    B:Ab:034
Name: SLT, dtype: object

What is reason for `apply`? Vector solution is better and faster. — jezrael, May 02 '16 at 15:29
How would rewrite using Vector --- no lambdas! I am splitting values as txt and number — Merlin, May 02 '16 at 15:31

Alexander · Answer 1 · 2016-05-02T16:30:47.887

1

def foo(df):
    data = df['AB18'].str.split(":")
    df['AB18t'] = [row[-2] for row in data]
    df['AB18n'] = [row[-1] for row in data]
    df['AB18n'] = np.where(df['AB18t'] == "Ab", df['AB18n'].astype(int), -df['AB18n'].astype(int))
    df['AB18t'] = np.where(df['AB18t'] == "Ab", 1 ,0)


df = pd.DataFrame({'AB18': ['Ab:18', 'Ac:20', 'B:Ab:20']})

>>> foo(df)
      AB18  AB18t  AB18n
0    Ab:18      1     18
1    Ac:20      0    -20
2  B:Ab:20      1     20

edited May 02 '16 at 16:30

answered May 02 '16 at 15:33

Alexander

105,104
32
201
196

@jezrael. A return value is not required. It is an inplace modification of the dataframe. – Alexander May 02 '16 at 15:51
@Merlin Could you please provide a few rows of sample data? – Alexander May 02 '16 at 15:58

jezrael · Accepted Answer · 2016-05-03T05:57:00.590

For me works str.split with indexing with str:

print df2
    AB18    b  c     d
0   Ab:1  1.0  7  M024
1   Ab:0  2.0  9  M024
2  125:1  5.0  0  M024
3  127:0  7.0  4  M025
4  129:1  NaN  2  M024

def getTextNum(df2):
    ser = df2['AB18'].str.split(":")
    df2['AB18t'] = ser.str[0]
    df2['AB18n'] = ser.str[1].astype(int)
    df2['AB18n'] = np.where(df2['AB18t'] == "Ab", df2['AB18n'] ,-df2['AB18n'])
    df2['AB18t'] = np.where(df2['AB18t'] == "Ab", 1 ,0)
    return df2

print getTextNum(df2)

    AB18    b  c     d  AB18t  AB18n
0   Ab:1  1.0  7  M024      1      1
1   Ab:0  2.0  9  M024      1      0
2  125:1  5.0  0  M024      0     -1
3  127:0  7.0  4  M025      0      0
4  129:1  NaN  2  M024      0     -1

Vectorized methods.

EDIT: You can use function getTextNum with input column (Serie) (e.g. df2['AB18']) and return new DataFrame:

def getTextNum(col):
    ser   = col.str.split(":")
    text  = np.where(ser.str[0] == "Ab", 1 ,0)
    num   = np.where(ser.str[0] == "Ab", ser.str[1].astype(int) ,-ser.str[1].astype(int))
    return pd.DataFrame({'Text':text,'Num':num}, columns= ['Text','Num'])

print getTextNum(df2['AB18'])
   AB18n  AB18t
0      1      1
1      0      1
2     -1      0
3      0      0
4     -1      0

df2[['AB18t', 'AB18n']] = getTextNum(df2['AB18'])

EDIT1:

More generalize solution - I count list from back - last column is indexing by [-1] and last previous is [-2]:

print df2
       AB18       SLT
0   Blw:001  B:Ab:048
1    Ab:008  B:Ab:047
2    Ab:007  B:Ab:046
3    Ab:006  B:Ab:045
4    Ab:005  B:Ab:044
5    Ab:004  B:Ab:043
6    Ab:003  B:Ab:042
7    Ab:002  B:Ab:041
8    Ab:001  B:Ab:040
9   Blw:001  B:Ab:039
10   Ab:001  B:Ab:038
11  Blw:002  B:Ab:037
12  Blw:001  B:Ab:036
13   Ab:001  B:Ab:035
14  Blw:002  B:Ab:034

def getTextNum(df, col):
    ser   = df[col].str.split(":")
    text  = np.where(ser.str[-2] == "Ab", 1, 0)
    num   = np.where(ser.str[-2] == "Ab", ser.str[-1].astype(int),-ser.str[-1].astype(int))
    df[df[col].name + 't'] = text
    df[df[col].name + 'n'] = num   
    return df

#parameters - name of DataFrame, name of column in DataFrame
getTextNum(df2, 'SLT')
print df2
       AB18       SLT  SLTt  SLTn
0   Blw:001  B:Ab:048     1    48
1    Ab:008  B:Ab:047     1    47
2    Ab:007  B:Ab:046     1    46
3    Ab:006  B:Ab:045     1    45
4    Ab:005  B:Ab:044     1    44
5    Ab:004  B:Ab:043     1    43
6    Ab:003  B:Ab:042     1    42
7    Ab:002  B:Ab:041     1    41
8    Ab:001  B:Ab:040     1    40
9   Blw:001  B:Ab:039     1    39
10   Ab:001  B:Ab:038     1    38
11  Blw:002  B:Ab:037     1    37
12  Blw:001  B:Ab:036     1    36
13   Ab:001  B:Ab:035     1    35
14  Blw:002  B:Ab:034     1    34

getTextNum(df2, 'AB18')
print df2
       AB18       SLT  AB18t  AB18n
0   Blw:001  B:Ab:048      0     -1
1    Ab:008  B:Ab:047      1      8
2    Ab:007  B:Ab:046      1      7
3    Ab:006  B:Ab:045      1      6
4    Ab:005  B:Ab:044      1      5
5    Ab:004  B:Ab:043      1      4
6    Ab:003  B:Ab:042      1      3
7    Ab:002  B:Ab:041      1      2
8    Ab:001  B:Ab:040      1      1
9   Blw:001  B:Ab:039      0     -1
10   Ab:001  B:Ab:038      1      1
11  Blw:002  B:Ab:037      0     -2
12  Blw:001  B:Ab:036      0     -1
13   Ab:001  B:Ab:035      1      1
14  Blw:002  B:Ab:034      0     -2

Please check solution. I think vector solution is better and faster as `apply`. — jezrael, May 02 '16 at 15:50
Why dont use [`apply`](http://stackoverflow.com/q/12829428/2901002) - it is slow. I think the best is add timing. Give me a time, I add some timing. — jezrael, May 02 '16 at 16:05
No need for timing work --- :-) ....apply plus lambda cant be faster than directly working on elements. — Merlin, May 02 '16 at 16:10
@Merlin, there is something wrong? For me it works nice. Ooops, I see request for generalize. — jezrael, May 02 '16 at 19:59
@Merlin - please see edit. Now I am going to bed. If there is something for improvement, please add comment. Tomorrow I try help you. Good night. — jezrael, May 02 '16 at 20:11

Using function in Pandas to split text -- reasons not to use apply

2 Answers2