基于其他列的值创建新列/在Pandas中按行应用多列函数

qij5mzcb  于 2022-12-21  发布在  其他
关注(0)|答案(8)|浏览(129)

我想将我的自定义函数(它使用if-else梯形图)应用于 Dataframe 每行的这六列(ERI_HispanicERI_AmerInd_AKNatvERI_AsianERI_Black_Afr.AmerERI_HI_PacIslERI_White)。
我尝试了其他问题的不同方法,但似乎仍然找不到我的问题的正确答案。关键是,如果一个人被算作西班牙裔,他们就不能被算作其他任何人。即使他们在另一个种族列中有一个“1”,他们仍然被算作西班牙裔,而不是两个或两个以上的种族。同样,如果所有ERI列的总和大于1,则将其计为两个或多个人种,不能计为唯一种族(西班牙裔除外)。
这几乎就像在每行中执行for循环,如果每条记录都满足某个条件,则将它们添加到一个列表中,并从原始列表中删除。
从下面的数据框中,我需要根据SQL中的以下规范计算一个新列:

标准

IF [ERI_Hispanic] = 1 THEN RETURN “Hispanic”
ELSE IF SUM([ERI_AmerInd_AKNatv] + [ERI_Asian] + [ERI_Black_Afr.Amer] + [ERI_HI_PacIsl] + [ERI_White]) > 1 THEN RETURN “Two or More”
ELSE IF [ERI_AmerInd_AKNatv] = 1 THEN RETURN “A/I AK Native”
ELSE IF [ERI_Asian] = 1 THEN RETURN “Asian”
ELSE IF [ERI_Black_Afr.Amer] = 1 THEN RETURN “Black/AA”
ELSE IF [ERI_HI_PacIsl] = 1 THEN RETURN “Haw/Pac Isl.”
ELSE IF [ERI_White] = 1 THEN RETURN “White”

备注:如果西班牙裔的ERI标志为真(1),则将员工分类为“西班牙裔”
备注:如果超过1个非西班牙裔ERI标志为真,则返回“两个或更多”

** Dataframe **

lname          fname       rno_cd  eri_afr_amer    eri_asian   eri_hawaiian    eri_hispanic    eri_nat_amer    eri_white   rno_defined
0    MOST           JEFF        E       0               0           0               0               0               1           White
1    CRUISE         TOM         E       0               0           0               1               0               0           White
2    DEPP           JOHNNY              0               0           0               0               0               1           Unknown
3    DICAP          LEO                 0               0           0               0               0               1           Unknown
4    BRANDO         MARLON      E       0               0           0               0               0               0           White
5    HANKS          TOM         0                       0           0               0               0               1           Unknown
6    DENIRO         ROBERT      E       0               1           0               0               0               1           White
7    PACINO         AL          E       0               0           0               0               0               1           White
8    WILLIAMS       ROBIN       E       0               0           1               0               0               0           White
9    EASTWOOD       CLINT       E       0               0           0               0               0               1           White
svdrlsy4

svdrlsy41#

好的,有两个步骤--第一步是编写一个函数来完成你想要的转换--我已经根据你的伪代码整理了一个例子:

def label_race (row):
   if row['eri_hispanic'] == 1 :
      return 'Hispanic'
   if row['eri_afr_amer'] + row['eri_asian'] + row['eri_hawaiian'] + row['eri_nat_amer'] + row['eri_white'] > 1 :
      return 'Two Or More'
   if row['eri_nat_amer'] == 1 :
      return 'A/I AK Native'
   if row['eri_asian'] == 1:
      return 'Asian'
   if row['eri_afr_amer']  == 1:
      return 'Black/AA'
   if row['eri_hawaiian'] == 1:
      return 'Haw/Pac Isl.'
   if row['eri_white'] == 1:
      return 'White'
   return 'Other'

您可能想过一遍,但它似乎起到了作用-注意,进入函数的参数被认为是一个标记为“行”的Series对象。
接下来,在Pandas中使用apply函数来应用函数-例如

df.apply (lambda row: label_race(row), axis=1)

注意axis=1说明符,这意味着应用程序是在行级别而不是列级别执行的。结果如下所示:

0           White
1        Hispanic
2           White
3           White
4           Other
5           White
6     Two Or More
7           White
8    Haw/Pac Isl.
9           White

如果您对这些结果感到满意,那么再次运行它,将结果保存到原始 Dataframe 的新列中。

df['race_label'] = df.apply (lambda row: label_race(row), axis=1)

生成的 Dataframe 如下所示(向右滚动以查看新列):

lname   fname rno_cd  eri_afr_amer  eri_asian  eri_hawaiian   eri_hispanic  eri_nat_amer  eri_white rno_defined    race_label
0      MOST    JEFF      E             0          0             0              0             0          1       White         White
1    CRUISE     TOM      E             0          0             0              1             0          0       White      Hispanic
2      DEPP  JOHNNY    NaN             0          0             0              0             0          1     Unknown         White
3     DICAP     LEO    NaN             0          0             0              0             0          1     Unknown         White
4    BRANDO  MARLON      E             0          0             0              0             0          0       White         Other
5     HANKS     TOM    NaN             0          0             0              0             0          1     Unknown         White
6    DENIRO  ROBERT      E             0          1             0              0             0          1       White   Two Or More
7    PACINO      AL      E             0          0             0              0             0          1       White         White
8  WILLIAMS   ROBIN      E             0          0             1              0             0          0       White  Haw/Pac Isl.
9  EASTWOOD   CLINT      E             0          0             0              0             0          1       White         White
tcomlyy6

tcomlyy62#

由于这是第一个谷歌搜索结果'Pandas新专栏从其他',这里有一个简单的例子:

import pandas as pd

# make a simple dataframe
df = pd.DataFrame({'a':[1,2], 'b':[3,4]})
df
#    a  b
# 0  1  3
# 1  2  4

# create an unattached column with an index
df.apply(lambda row: row.a + row.b, axis=1)
# 0    4
# 1    6

# do same but attach it to the dataframe
df['c'] = df.apply(lambda row: row.a + row.b, axis=1)
df
#    a  b  c
# 0  1  3  4
# 1  2  4  6

如果你得到了SettingWithCopyWarning,你也可以这样做:

fn = lambda row: row.a + row.b # define a function for the new column
col = df.apply(fn, axis=1) # get column data with an index
df = df.assign(c=col.values) # assign values to column 'c'

来源:https://stackoverflow.com/a/12555510/243392
如果列名中包含空格,则可以使用如下语法:

df = df.assign(**{'some column name': col.values})

这是applyassign的文档。

68bkxrlz

68bkxrlz3#

上面的答案是完全正确的,但是存在一个矢量化的解决方案,其形式为numpy.select。这允许您定义条件,然后定义这些条件的输出,这比使用apply要高效得多:
首先,定义条件:

conditions = [
    df['eri_hispanic'] == 1,
    df[['eri_afr_amer', 'eri_asian', 'eri_hawaiian', 'eri_nat_amer', 'eri_white']].sum(1).gt(1),
    df['eri_nat_amer'] == 1,
    df['eri_asian'] == 1,
    df['eri_afr_amer'] == 1,
    df['eri_hawaiian'] == 1,
    df['eri_white'] == 1,
]

现在,定义相应的输出:

outputs = [
    'Hispanic', 'Two Or More', 'A/I AK Native', 'Asian', 'Black/AA', 'Haw/Pac Isl.', 'White'
]

最后,使用numpy.select

res = np.select(conditions, outputs, 'Other')
pd.Series(res)
0           White
1        Hispanic
2           White
3           White
4           Other
5           White
6     Two Or More
7           White
8    Haw/Pac Isl.
9           White
dtype: object

为什么要使用numpy.select而不是apply?下面是一些性能检查:

df = pd.concat([df]*1000)

In [42]: %timeit df.apply(lambda row: label_race(row), axis=1)
1.07 s ± 4.16 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)

In [44]: %%timeit
    ...: conditions = [
    ...:     df['eri_hispanic'] == 1,
    ...:     df[['eri_afr_amer', 'eri_asian', 'eri_hawaiian', 'eri_nat_amer', 'eri_white']].sum(1).gt(1),
    ...:     df['eri_nat_amer'] == 1,
    ...:     df['eri_asian'] == 1,
    ...:     df['eri_afr_amer'] == 1,
    ...:     df['eri_hawaiian'] == 1,
    ...:     df['eri_white'] == 1,
    ...: ]
    ...:
    ...: outputs = [
    ...:     'Hispanic', 'Two Or More', 'A/I AK Native', 'Asian', 'Black/AA', 'Haw/Pac Isl.', 'White'
    ...: ]
    ...:
    ...: np.select(conditions, outputs, 'Other')
    ...:
    ...:
3.09 ms ± 17 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

使用numpy.select给我们带来了“巨大”的性能改进,而且这种差异只会随着数据的增长而增加。

yc0p9oo0

yc0p9oo04#

.apply()取函数作为第一个参数;传入label_race函数,如下所示:

df['race_label'] = df.apply(label_race, axis=1)

你不需要创建一个lambda函数来传入一个函数。

7xllpg7q

7xllpg7q5#

试试这个,

df.loc[df['eri_white']==1,'race_label'] = 'White'
df.loc[df['eri_hawaiian']==1,'race_label'] = 'Haw/Pac Isl.'
df.loc[df['eri_afr_amer']==1,'race_label'] = 'Black/AA'
df.loc[df['eri_asian']==1,'race_label'] = 'Asian'
df.loc[df['eri_nat_amer']==1,'race_label'] = 'A/I AK Native'
df.loc[(df['eri_afr_amer'] + df['eri_asian'] + df['eri_hawaiian'] + df['eri_nat_amer'] + df['eri_white']) > 1,'race_label'] = 'Two Or More'
df.loc[df['eri_hispanic']==1,'race_label'] = 'Hispanic'
df['race_label'].fillna('Other', inplace=True)

O/P:

lname   fname rno_cd  eri_afr_amer  eri_asian  eri_hawaiian  \
0      MOST    JEFF      E             0          0             0   
1    CRUISE     TOM      E             0          0             0   
2      DEPP  JOHNNY    NaN             0          0             0   
3     DICAP     LEO    NaN             0          0             0   
4    BRANDO  MARLON      E             0          0             0   
5     HANKS     TOM    NaN             0          0             0   
6    DENIRO  ROBERT      E             0          1             0   
7    PACINO      AL      E             0          0             0   
8  WILLIAMS   ROBIN      E             0          0             1   
9  EASTWOOD   CLINT      E             0          0             0   

   eri_hispanic  eri_nat_amer  eri_white rno_defined    race_label  
0             0             0          1       White         White  
1             1             0          0       White      Hispanic  
2             0             0          1     Unknown         White  
3             0             0          1     Unknown         White  
4             0             0          0       White         Other  
5             0             0          1     Unknown         White  
6             0             0          1       White   Two Or More  
7             0             0          1       White         White  
8             0             0          0       White  Haw/Pac Isl.  
9             0             0          1       White         White

使用.loc代替apply
它改进了矢量化。
.loc的工作方式很简单,根据条件屏蔽行,将值应用于冻结行。
有关详细信息,请访问.loc docs
性能指标:
接受的答案:

def label_race (row):
   if row['eri_hispanic'] == 1 :
      return 'Hispanic'
   if row['eri_afr_amer'] + row['eri_asian'] + row['eri_hawaiian'] + row['eri_nat_amer'] + row['eri_white'] > 1 :
      return 'Two Or More'
   if row['eri_nat_amer'] == 1 :
      return 'A/I AK Native'
   if row['eri_asian'] == 1:
      return 'Asian'
   if row['eri_afr_amer']  == 1:
      return 'Black/AA'
   if row['eri_hawaiian'] == 1:
      return 'Haw/Pac Isl.'
   if row['eri_white'] == 1:
      return 'White'
   return 'Other'

df=pd.read_csv('dataser.csv')
df = pd.concat([df]*1000)

%timeit df.apply(lambda row: label_race(row), axis=1)

1.15 s ± 46.5 ms/循环(7次运行的平均值±标准差,每次运行1个循环)
我的建议答案:

def label_race(df):
    df.loc[df['eri_white']==1,'race_label'] = 'White'
    df.loc[df['eri_hawaiian']==1,'race_label'] = 'Haw/Pac Isl.'
    df.loc[df['eri_afr_amer']==1,'race_label'] = 'Black/AA'
    df.loc[df['eri_asian']==1,'race_label'] = 'Asian'
    df.loc[df['eri_nat_amer']==1,'race_label'] = 'A/I AK Native'
    df.loc[(df['eri_afr_amer'] + df['eri_asian'] + df['eri_hawaiian'] + df['eri_nat_amer'] + df['eri_white']) > 1,'race_label'] = 'Two Or More'
    df.loc[df['eri_hispanic']==1,'race_label'] = 'Hispanic'
    df['race_label'].fillna('Other', inplace=True)
df=pd.read_csv('s22.csv')
df = pd.concat([df]*1000)

%timeit label_race(df)

24.7 ms ± 1.7 ms/循环(7次运行的平均值±标准差,每次运行10次循环)

wi3ka0sx

wi3ka0sx6#

如果我们检查它的source codeapply()是Python for循环的语法糖(通过FrameApply类的apply_series_generator()方法),因为它有panda开销,它通常比Python循环"慢"。
尽可能使用优化的(矢量化的)方法。如果你必须使用循环,使用@numba.jit装饰器。

1.不要将apply()用于if-else阶梯

df.apply()几乎是Pandas中最慢的方法。如user3483203Mohamed Thasin ah的答案所示,根据 Dataframe 大小,np.select()df.loc可能比df.apply()快50 - 300倍才能产生相同的输出。
实际上,使用numba模块中的@jit装饰器的循环实现(与apply()类似)比df.locnp.select快(大约50 - 60%)。1
Numba可以处理numpy数组,所以在使用jit装饰器之前,需要将 Dataframe 转换为numpy数组。然后通过检查循环中的条件,在预先初始化的空数组中填充值。由于numpy数组没有列名,你必须通过循环中的索引来访问这些列。if- jitted函数中的else ladders在apply()中的else ladders上是通过索引访问列的,除此之外几乎是相同的实现。

import numpy as np
import numba as nb
@nb.jit(nopython=True)
def conditional_assignment(arr, res):    
    length = len(arr)
    for i in range(length):
        if arr[i][3] == 1:
            res[i] = 'Hispanic'
        elif arr[i][0] + arr[i][1] + arr[i][2] + arr[i][4] + arr[i][5] > 1:
            res[i] = 'Two Or More'
        elif arr[i][0]  == 1:
            res[i] = 'Black/AA'
        elif arr[i][1] == 1:
            res[i] = 'Asian'
        elif arr[i][2] == 1:
            res[i] = 'Haw/Pac Isl.'
        elif arr[i][4] == 1:
            res[i] = 'A/I AK Native'
        elif arr[i][5] == 1:
            res[i] = 'White'
        else:
            res[i] = 'Other'
    return res

# the columns with the boolean data
cols = [c for c in df.columns if c.startswith('eri_')]
# initialize an empty array to be filled in a loop
# for string dtype arrays, we need to know the length of the longest string
# and use it to set the dtype
res = np.empty(len(df), dtype=f"<U{len('A/I AK Native')}")
# pass the underlying numpy array of `df[cols]` into the jitted function
df['rno_defined'] = conditional_assignment(df[cols].values, res)

2.不要使用apply()进行数值运算

如果需要通过添加两列来添加新行,您的第一React可能是编写

df['c'] = df.apply(lambda row: row['a'] + row['b'], axis=1)

但是,使用sum(axis=1)方法(如果只有几列,则使用+运算符)执行按行加法:

df['c'] = df[['a','b']].sum(axis=1)
# equivalently
df['c'] = df['a'] + df['b']

根据 Dataframe 大小,sum(1)可能比apply()快100倍。
事实上,在panda Dataframe 上,几乎不需要使用apply()进行数值运算,因为它对大多数运算都进行了优化:加法(sum(1)),减法(sub()diff()),乘法(prod(1)),部门(1个一米26英寸1 x或1个一米27英寸1 x),电源(pow())、>>===%//&|等都可以在没有apply()的整个 Dataframe 上执行。
例如,假设您要使用以下规则创建一个新列:

IF [colC] > 0 THEN RETURN [colA] * [colB]
ELSE RETURN [colA] / [colB]

使用优化的panda方法,这可以写成

df['new'] = df[['colA','colB']].prod(1).where(df['colC']>0, df['colA'] / df['colB'])

等效的apply()溶液为:

df['new'] = df.apply(lambda row: row.colA * row.colB if row.colC > 0 else row.colA / row.colB, axis=1)

对于20k行的 Dataframe ,使用优化方法的方法比等效的apply()方法快250倍。这个差距只会随着数据大小的增加而增加(对于1mil行的 Dataframe ,它快365倍),时间差异将变得越来越明显。2
1:在下面的结果中,我使用24mil行的 Dataframe (这是我可以在我的机器上构建的最大帧)展示了三种方法的性能。对于较小的帧,numba-jitted函数始终比其他两种方法至少快50%(您可以自己检查)。

def pd_loc(df):
    df['rno_defined'] = 'Other'
    df.loc[df['eri_nat_amer'] == 1, 'rno_defined'] = 'A/I AK Native'
    df.loc[df['eri_asian'] == 1, 'rno_defined'] = 'Asian'
    df.loc[df['eri_afr_amer'] == 1, 'rno_defined'] = 'Black/AA'
    df.loc[df['eri_hawaiian'] == 1, 'rno_defined'] = 'Haw/Pac Isl.'
    df.loc[df['eri_white'] == 1, 'rno_defined'] = 'White'
    df.loc[df[['eri_afr_amer', 'eri_asian', 'eri_hawaiian', 'eri_nat_amer', 'eri_white']].sum(1) > 1, 'rno_defined'] = 'Two Or More'
    df.loc[df['eri_hispanic'] == 1, 'rno_defined'] = 'Hispanic'
    return df

def np_select(df):
    conditions = [df['eri_hispanic'] == 1,
                  df[['eri_afr_amer', 'eri_asian', 'eri_hawaiian', 'eri_nat_amer', 'eri_white']].sum(1).gt(1),
                  df['eri_nat_amer'] == 1,
                  df['eri_asian'] == 1,
                  df['eri_afr_amer'] == 1,
                  df['eri_hawaiian'] == 1,
                  df['eri_white'] == 1]
    outputs = ['Hispanic', 'Two Or More', 'A/I AK Native', 'Asian', 'Black/AA', 'Haw/Pac Isl.', 'White']
    df['rno_defined'] = np.select(conditions, outputs, 'Other')
    return df

@nb.jit(nopython=True)
def conditional_assignment(arr, res):
    
    length = len(arr)
    for i in range(length):
        if arr[i][3] == 1 :
            res[i] = 'Hispanic'
        elif arr[i][0] + arr[i][1] + arr[i][2] + arr[i][4] + arr[i][5] > 1 :
            res[i] = 'Two Or More'
        elif arr[i][0]  == 1:
            res[i] = 'Black/AA'
        elif arr[i][1] == 1:
            res[i] = 'Asian'
        elif arr[i][2] == 1:
            res[i] = 'Haw/Pac Isl.'
        elif arr[i][4] == 1 :
            res[i] = 'A/I AK Native'
        elif arr[i][5] == 1:
            res[i] = 'White'
        else:
            res[i] = 'Other'
            
    return res

def nb_loop(df):
    cols = [c for c in df.columns if c.startswith('eri_')]
    res = np.empty(len(df), dtype=f"<U{len('A/I AK Native')}")
    df['rno_defined'] = conditional_assignment(df[cols].values, res)
    return df

# df with 24mil rows
n = 4_000_000
df = pd.DataFrame({
    'eri_afr_amer': [0, 0, 0, 0, 0, 0]*n, 
    'eri_asian': [1, 0, 0, 0, 0, 0]*n, 
    'eri_hawaiian': [0, 0, 0, 1, 0, 0]*n, 
    'eri_hispanic': [0, 1, 0, 0, 1, 0]*n, 
    'eri_nat_amer': [0, 0, 0, 0, 1, 0]*n, 
    'eri_white': [0, 0, 1, 1, 0, 0]*n
}, dtype='int8')
df.insert(0, 'name', ['MOST', 'CRUISE', 'DEPP', 'DICAP', 'BRANDO', 'HANKS']*n)

%timeit nb_loop(df)
# 5.23 s ± 45.2 ms per loop (mean ± std. dev. of 10 runs, 10 loops each)

%timeit pd_loc(df)
# 7.97 s ± 28.8 ms per loop (mean ± std. dev. of 10 runs, 10 loops each)

%timeit np_select(df)
# 8.5 s ± 39.6 ms per loop (mean ± std. dev. of 10 runs, 10 loops each)

二:在下面的结果中,我使用20k行和1mil行的 Dataframe 显示了两种方法的性能。对于较小的帧,差距较小,因为优化的方法有开销,而apply()是一个循环。随着帧大小的增加,矢量化开销成本减少了对代码的整个运行时间的影响,而apply()仍然是帧上的循环。

n = 20_000 # 1_000_000
df = pd.DataFrame(np.random.rand(n,3)-0.5, columns=['colA','colB','colC'])

%timeit df[['colA','colB']].prod(1).where(df['colC']>0, df['colA'] / df['colB'])
# n = 20000: 2.69 ms ± 23.5 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)
# n = 1000000: 86.2 ms ± 441 µs per loop (mean ± std. dev. of 7 runs, 10 loops each)

%timeit df.apply(lambda row: row.colA * row.colB if row.colC > 0 else row.colA / row.colB, axis=1)
# n = 20000: 679 ms ± 33.8 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
# n = 1000000: 31.5 s ± 587 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)
vh0rcniy

vh0rcniy7#

As @user3483203 pointed out, numpy.select is the best approach
将条件语句和相应的操作存储在两个列表中

conds = [(df['eri_hispanic'] == 1),(df[['eri_afr_amer', 'eri_asian', 'eri_hawaiian', 'eri_nat_amer', 'eri_white']].sum(1).gt(1)),(df['eri_nat_amer'] == 1),(df['eri_asian'] == 1),(df['eri_afr_amer'] == 1),(df['eri_hawaiian'] == 1),(df['eri_white'] == 1,])

actions = ['Hispanic', 'Two Or More', 'A/I AK Native', 'Asian', 'Black/AA', 'Haw/Pac Isl.', 'White']

You can now use np.select using these lists as its arguments

df['label_race'] = np.select(conds,actions,default='Other')

参考:https://numpy.org/doc/stable/reference/generated/numpy.select.html

kt06eoxx

kt06eoxx8#

还有另一种(易于推广的)方法,其基石是pandas.DataFrame.idxmax

# Indeed, all your conditions boils down to the following
_gt_1_key = 'two_or_more'
_lt_1_key = 'other'

# The "dictionary-based" if-else statements
labels = {
    _gt_1_key     : 'Two Or More',
    'eri_hispanic': 'Hispanic',
    'eri_nat_amer': 'A/I AK Native',
    'eri_asian'   : 'Asian',
    'eri_afr_amer': 'Black/AA',
    'eri_hawaiian': 'Haw/Pac Isl.',
    'eri_white'   : 'White',  
    _lt_1_key     : 'Other',
}

# The output-driving 1-0 matrix
mat = df.filter(regex='^eri_').copy()  # `~.copy` to avoid `SettingWithCopyWarning`

...最后,在 vectorized fashion 中:

mat[_gt_1_key] = gt1 = mat.sum(axis=1)
mat[_lt_1_key] = gt1.eq(0).astype(int)
race_label     = mat.idxmax(axis=1).map(labels)

其中

>>> race_label
0           White
1        Hispanic
2           White
3           White
4           Other
5           White
6     Two Or More
7           White
8    Haw/Pac Isl.
9           White
dtype: object

这是一个pandas.Series示例,您可以轻松地将其托管在df中,即执行df['race_label'] = race_label

相关问题