python - 根据另一列的值替换 pandas DataFrame 中的值

Question

我正在处理一个由几个不同变量组成的数据集。对于每个变量，数据集还包含一个“编码”变量。也就是说，一种分类变量，包含有关它所引用的变量的附加信息，前提是存在有关该变量的任何附加信息。

例如:

data = { year: [2000, 2001, 2000, 2001],
         observation: ['A', 'A', 'B', 'B'],
         height: [1, 2, 3, 4],
         height_code: ['S', 'BF', 'BF', 'S'] }

df = pd.DataFrame(data)

在此示例中，如果编码变量的值为“BF”，则表示赤脚。也就是说，测量高度时，该人的脚上没有穿任何东西。相反，“S”代表鞋。

现在，我需要确定哪些人在穿鞋时测量了高度，并且:(1) - 将他们的高度转换为 np.nan ，这样他们就不会被包含在以后一年的平均高度计算中。或者(2) - 生成一个替代数据帧，其中穿鞋时测量的人从这个新的 DF 中删除。然后，我需要按年份计算平均高度并将其添加到另一个 DF 中。

为了清楚起见:这是一个通用的示例。我的数据集包含许多不同的变量，每个变量可能有一个需要考虑的代码，或者可能不会被编码(在这种情况下，我不需要关心该观察的值)。因此，真正的问题是，我可能有包含 4 个变量的观察结果(行)，其中 2 个已编码(以便在以后的计算中必须忽略它们的值)，而其他 2 个未编码(必须考虑) 。因此，我不能完全放弃观察结果，但必须更改两个编码变量中的值，以便在计算中忽略它们。 (假设我必须独立计算每个变量的年份平均值)

我已经尝试过:

我编写了同一概念的这两个函数版本。第二个函数必须使用 .apply() 传递给 DataFrame。尽管如此，它仍然必须应用至少 4 次(每个 target_variable/code_variable 对一次，我在这里称之为编码变量 test_col)...

# sub_val / sub_value -
# This function goes through each row in a pandas DataFrame and each time/iteration the 
# function will [1] check one of the columns (the "test_col") against a specific value 
# (maybe passed in as an argument, maybe default null value). [2] If the check returns 
# True, then the function will replace the value of another column (the "target_col") 
# in the same row for np.nan . [3] If the check returns False, the fuction will skip to
# the next row.

# - This version is inefficient because it creates one Series object for every
#   row in the DataFrame when iterating through it.
def sub_val(df, target_col, test_col, test_val) :

    # iterate through DataFrame's rows - returns lab (row index) and row (row values as Series obj)
    for lab, row in df.iterrows() : 

        # if observation contains combined data code, ignore variable value
        if row[test_col] == test_val :
            df.loc[lab, target_col] = np.nan # Sub current variable value by NaN (NaN won't count in yearly agg value)

    return df

# - This version is more efficient.
#   Parameters:
#   [1] obs - DataFrame's row (observation) as Series object
#   [2] col - Two strings representing the target and test columns' names
#   [3] test_val - The value to be compared to the value in test_col
def sub_value(obs, target_col, test_col, test_val) :

    # Check value in the column being tested.
    if obs[test_col] == test_val :
        # If condition holds, it means target_col contains a so-called "combined" value
        # and should be ignored in the calculation of the variable by year.
        obs[target_col] = np.nan # Substitute value in target column for NaN
    else :
        # If condition does not hold, we can assign NaN value to the column being tested
        # (i.e. the combined data code column) in order to make sure its value isn't 
        # some undiserable value.
        obs[test_col] = np.nan

    return obs # Returns the modified row

Answer 1

OR (2) - generate an alternative DataFrame in which people measured while wearing shoes are dropped from this new DF. Then, I need to calculate the mean height by year and add that to yet another DF.

切片和pandas.DataFrame.groupby将成为您的 friend :

import pandas as pd

data = dict(
    year = [2000, 2001, 2000, 2001, 2001],
    observation = ['A', 'A', 'B', 'B', 'C'],
    height = [1, 2, 3, 4, 1],
    height_code = ['S', 'BF', 'BF', 'S', 'BF'],
)

df = pd.DataFrame(data)

df_barefoot = df[df['height_code'] == 'BF']
print(df_barefoot)

mean_barefoot_height_by_year = df_barefoot.groupby('year').mean()
print(mean_barefoot_height_by_year)

example in python tutor

编辑:您也可以跳过创建第二个 df_barefoot 的整个过程，而只是 groupby 'year' 和 'height_code':

import pandas as pd

df = pd.DataFrame(dict(
    year = [2000, 2001, 2000, 2001, 2001],
    observation = ['A', 'A', 'B', 'B', 'C'],
    height = [1, 2, 3, 4, 1],
    height_code = ['S', 'BF', 'BF', 'S', 'BF'],
))

mean_height_by_year_and_code = df.groupby(['year','height_code']).mean()
print(mean_height_by_year_and_code)

Example 2 in Python Tutor