Substitution of values in a pandas DataFrame conditional on the value of another column

user_13128398 0
Viewed 165

I am working with a data set that consists of a few different variables. For each of these variables, the data set also contains a "coding" variable. That is, a sort of categorical variable that contains additional information about the variable it refers to, provided there is any additional information about that variable.

For example:

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)

In this example, if the coding variable takes the value 'BF' means barefoot. That is, the person wasn't wearing anything on the feet when her height was measured. In contrast, 'S' stands for shoe.

Now, I need to identify which people had their height measured while wearing shoes and either: (1) - convert their heights to np.nan , so that they are not included in the mean height calculation by year later in the process. 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.

To make things clear: this is a generalized example. My data set contains a number of different variables and each variable may have a code that needs to be considered or it may not be coded (in which case I need not concern myself with the value for that observation). Hence, the real problem is that I may have observations (rows) containing say 4 variables and 2 of them are coded (so that their values must be ignored in the later calculations) whereas the other 2 are not coded (and must be considered). Thus, I cannot completely discard the observation, but must alter the values in the 2 coded variables in order to ignore them in the calculations. (Suppose I must calculate the mean by year for each of the variables independently)

WHAT I HAVE TRIED:

I wrote these two function versions of the same concept. The second function must be passed to the DataFrame with .apply() . Still, it would have to be applied at least a 4 times (one time for each target_variable/code_variable pair, I call the coding variables test_col here) ...

# 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
pandas data-analysis dataframe validation python
3 Answers

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.

Slicing and pandas.DataFrame.groupby will be your friends here:

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

EDIT: You could also skip the whole creating a second df_barefoot and just groupby 'year' and '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

user_112285410

Do you want the mean per observation category? Then possibly something like:

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

Simply use: after = df[df.height_code != 'S'].groupby('year').mean() if observation is irrelevant and you want mean per year as a total for all observations.

user_97581940

I did not check you actual problem but only wrote a solution for the example.

# 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"})
)
user_51509420
Related