Creating flags and checking values of column in pandas dataframe by creating a function-CodePudding

I have pandas data frame read from an excel file and I need to check whether values present in these columns follow certain conditions or not. Firstly I wrote the following code and it is working fine.

tmd.iloc[:, 10] = tmd.iloc[:, 10].fillna(tmd.iloc[:, 10].mean())
tmd.iloc[tmd.iloc[:, 10] == 0, 10] = tmd.iloc[:, 10].mean()
tmd.iloc[:, 10] = tmd.iloc[:, 10].where(~((tmd.iloc[:, 10] > 0) & (pd.Series.abs(tmd.iloc[:, 10].diff()) > 30)), tmd.iloc[:, 10].mean())

However, the number of lines needed for this operation was increasing. So I wrote a function and tried to apply it to each column of the data frame.

def checkFlags_current(var):
    """
        This function calculates,

            Input(s):
            - var1: Current in Ampere

            Returns:
            - flags
    """
    rows = len(var)
    flags = np.zeros((rows, 1))

    for i in range(0, rows):
        if (var[i] > 0 & (abs(var[i 1] - var[i]) > 30)):
            flags[i] = 1
        elif (pd.isnull(var[i])):
            flags[i] = 3
        elif (var[i] == 0):
            flags[i] = 2
        else:
            flags[i] = 0
    flags = list(itertools.chain(*flags))

    return flags

tmd_flags['Load Current(A)'] = checkFlags_current(tmd.iloc[:, 10])

However, I'm getting the KeyError.

File "D:\AssetManager\Scripts\req_functions.py", line 1215, in checkFlags_current
    if (var[i] > 0 & (abs(var[i 1] - var[i]) > 30)):
  File "C:\Users\jadha\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\series.py", line 958, in __getitem__
    return self._get_value(key)
  File "C:\Users\jadha\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\series.py", line 1069, in _get_value
    loc = self.index.get_loc(label)
  File "C:\Users\jadha\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\pandas\core\indexes\range.py", line 387, in get_loc
    raise KeyError(key) from err
KeyError: 32073

Please help.

CodePudding user response：

As you use range in your loop, you have to use .iloc to get rows. However, you can vectorize your function to avoid a loop with np.select:

def checkFlags_current(var):
    conds = [(var > 0) & (var.diff().abs() > 30), var.isna(), var == 0]
    choices = [1, 3, 2]
    return np.select(condlist=conds, choicelist=choices, default=0)

tmd_flags['Load Current(A)'] = checkFlags_current(tmd.iloc[:, 10])

CodePudding user response：

Use numpy.select instead loops, also assign to same DataFrame tmd:

def checkFlags_current(x):
    """
        This function calculates,

            Input(s):
            - x: Current in Ampere

            Returns:
            - flags
    """
    flags = np.select([x.isna(), x.eq(0), x.gt(0) & x.diff().abs().gt(30)],
                      [3,2,1], 0)
    return flags


tmd['Load Current(A)'] = checkFlags_current(tmd.iloc[:, 10])