我正在处理一个由几个不同变量组成的数据集。对于这些变量中的每一个，数据集还包含一个"编码"变量。也就是说，一种分类变量，它包含关于它所指变量的附加信息，前提是有关于该变量的任何附加信息。
例如：
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个没有被编码(必须考虑(。因此，我不能完全放弃观察结果，但必须更改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
OR(2(-生成一个替代数据帧，在该数据帧中，穿着鞋子测量的人将从这个新的DF中删除。然后，我需要计算每年的平均高度，并将其添加到另一个DF中。
切片和熊猫。DataFrame.groupby将在这里成为您的朋友：
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)
python教程中的示例
编辑：您也可以跳过创建第二个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)
Python教程中的示例2
您想要每个观察类别的平均值吗？然后可能是这样的：
import pandas as pd
data = {'year': [2000, 2001, 2000, 2001, 2001, 2001],
'observation': ['A', 'A', 'B', 'B', 'C', 'C'],
'height': [1, 2, 3, 4, 5, 7],
'height_code': ['S', 'BF', 'BF', 'S', 'BF', 'BF'] }
df = pd.DataFrame(data)
after = df[df.height_code != 'S'].groupby(['year', 'observation']).mean()
height
year observation
2000 B 3
2001 A 2
C 6
如果观测不相关，并且您希望将每年平均值作为所有观测的总和，则只需使用：after = df[df.height_code != 'S'].groupby('year').mean()。
我没有检查您的实际问题，只是为示例编写了一个解决方案。
# Separating the data
df = pd.DataFrame(data)
df_bare_foot = df[df["height_code"] == "BF"]
df_shoe = df[df["height_code"] == "S"]
# Calculating Mean separately for 2 different group
mean_df_bf = (
df_bare_foot
.groupby(["year"])
.agg({"height": "mean"})
.reset_index()
# that a new way to add a new column when doing other operation
# equivalant to df["height_code"] = "BF"
.assign(height_code="BF")
.rename(columns={"height": "mean_height"})
)
# The mean for shoes category
# we can keep the height_code in group by as
# it is not going to affect the group by
mean_df_sh = (
df_shoe
.groupby(["year", "height_code"])
.agg({"height": "mean"})
.reset_index()
.rename(columns={"height": "mean_height"})
)