如何在python中计算特定的单元格值？

Question

有如下数据帧：

import pandas as pd
raw_data = {'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
'age': [20, 19, 22, 21],
'favorite_color': ['blue', 'blue', 'yellow', "green"],
'grade': [88, 92, 95, 70]}

df = pd.DataFrame(raw_data)

我想将age和grade数值单元格值除以favorite_color列中的blue为125.0，将yellow值除以130.0，将green除以135.0。结果必须插入新的列age_new，grade_new。 通过下面的代码我收到错误。你知道吗

df['age_new'] =(df.loc[df['favorite_color']=='blue']/125.0)
df['age_new'] =(df.loc[df['favorite_color']=='yellow']/130.0)
df['age_new'] =(df.loc[df['favorite_color']=='green']/135.0)
df['grade_new'] =(df.loc[df['favorite_color']=='blue']/125.0)
df['grade_new'] =(df.loc[df['favorite_color']=='yellow']/130.0)
df['grade_new'] =(df.loc[df['favorite_color']=='green']/135.0)

错误：

TypeError: unsupported operand type(s) for /: 'str' and 'int'

Answer 1

您可以使用.replace而不是.loc，这样您只执行一次操作。你知道吗

import pandas as pd

raw_data = {
    'name': ['Willard Morris', 'Al Jennings', 'Omar Mullins', 'Spencer McDaniel'],
    'age': [20, 19, 22, 21],
    'favorite_color': ['blue', 'blue', 'yellow', "green"],
    'grade': [88, 92, 95, 70]}

df = pd.DataFrame(raw_data)

color_d = {
    "blue": 125,
    "yellow": 130,
    "green": 135
}

df[["age_new", "grade_new"]] = df[["age", "grade"]].div(
    df['favorite_color'].replace(color_d), 
    axis=0)

df.head()

这给了

    name                age favorite_color  grade   age_new     grade_new
0   Willard Morris      20  blue            88      0.160000    0.704000
1   Al Jennings         19  blue            92      0.152000    0.736000
2   Omar Mullins        22  yellow          95      0.169231    0.730769
3   Spencer McDaniel    21  green           70      0.155556    0.518519

Answer 2

map

mods = {'blue': 125, 'yellow': 130, 'green': 135}

df.assign(
    mods=df.favorite_color.map(mods),
    age_new=lambda d: d.age / d.mods,
    grade_new=lambda d: d.grade / d.mods
)

               name  age favorite_color  grade  mods   age_new  grade_new
0    Willard Morris   20           blue     88   125  0.160000   0.704000
1       Al Jennings   19           blue     92   125  0.152000   0.736000
2      Omar Mullins   22         yellow     95   130  0.169231   0.730769
3  Spencer McDaniel   21          green     70   135  0.155556   0.518519

---

类似

mods = {'blue': 125, 'yellow': 130, 'green': 135}

df.join(df[['age', 'grade']].div(df.favorite_color.map(mods), axis=0).add_suffix('_new'))

               name  age favorite_color  grade   age_new  grade_new
0    Willard Morris   20           blue     88  0.160000   0.704000
1       Al Jennings   19           blue     92  0.152000   0.736000
2      Omar Mullins   22         yellow     95  0.169231   0.730769
3  Spencer McDaniel   21          green     70  0.155556   0.518519