CODEWITHSUNDEEP
pythonpython-3.xpandasdataframemultiple-columns
Abhilash Singh Chauhan
Abhilash Singh Chauhan

Reputation: 289

Selecting value of column based on the values in another column, then applying the value at each row in pandas dataframe

I have to calculate the value of S, which has the formula as: S = (25400/CN) − 254

the CN value which I have to choose will depend on the amc_active condition viz 1, 2 and 3. if amc_active condition at 'index 0 or 1st row' is 1 then I have to choose the CN value from column cn1 i.e 47

and if the amc_active is 3, then I have to choose CN value as 95 from cn3 column in the 4th row and so on..

     cn1         cn2     cn3     amc_active
0     47         56       78         1
1     55         61       87         2
2     36         67       73         1
3     42         84       95         3

... ... ... ... ... ... ... ... ...
17410   42       84       96         3
17411   48       81       85         1
17412   55       59       82         1
17413   57       86       93         2
17414   36       87       91         2

for doing so, I am using else if condition

    if (df_col_all_merged['amc_active'] == 1):
        cn_for_s = df_col_all_merged['cn1']
    elif (df_col_all_merged['amc_active'] == 2):
        cn_for_s = df_col_all_merged['cn2']
    elif (df_col_all_merged['amc_active'] == 3):
        cn_for_s = df_col_all_merged['cn3']

but getting the error as

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-43-40c3b3817474> in <module>
----> 1 if (df_col_all_merged['amc_active'] == 1):
      2     cn_for_s = df_col_all_merged['cn1']
      3 elif (df_col_all_merged['amc_active'] == 2):
      4     cn_for_s = df_col_all_merged['cn2']
      5 elif (df_col_all_merged['amc_active'] == 3):

~\Anaconda3\envs\geocube\lib\site-packages\pandas\core\generic.py in __nonzero__(self)
   1327 
   1328     def __nonzero__(self):
-> 1329         raise ValueError(
   1330             f"The truth value of a {type(self).__name__} is ambiguous. "
   1331             "Use a.empty, a.bool(), a.item(), a.any() or a.all()."

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

and from this

def select_cn(df_col_all_merged):
    result = cn_for_s
    if (df_col_all_merged['amc_active'] == 1):
        cn_for_s = df_col_all_merged['cn1']
    elif (df_col_all_merged['amc_active'] == 2):
        cn_for_s = df_col_all_merged['cn2']
    elif (df_col_all_merged['amc_active'] == 3):
        cn_for_s = df_col_all_merged['cn3']
    return result

df_col_all_merged['s_mm'] = (25400/select_cn(df_col_all_merged)) - 254

the error is

---------------------------------------------------------------------------
UnboundLocalError                         Traceback (most recent call last)
<ipython-input-54-df43eddeac39> in <module>
----> 1 df_col_all_merged['s_mm'] = (num_const/select_cn(df_col_all_merged)) - dev_const

<ipython-input-51-7405a6dd24db> in select_cn(df_col_all_merged)
      1 def select_cn(df_col_all_merged):
----> 2     result = cn_for_s
      3     if (df_col_all_merged['amc_active'] == 1):
      4         cn_for_s = df_col_all_merged['cn1']
      5     elif (df_col_all_merged['amc_active'] == 2):

UnboundLocalError: local variable 'cn_for_s' referenced before assignment

How to rectify this?

Upvotes: 1

Views: 563

Answers (2)

Mustafa Aydın
Mustafa Aydın

Reputation: 18296

You can use numpy's fancy indexing:

# get the values of `cn*` columns
cn_123 = df_col_all_merged[["cn1", "cn2", "cn3"]].to_numpy()

# index into it as "(row_index, amc_active_value-1)"
cn = cn_123[np.arange(len(df_col_all_merged)),
            df.amc_active-1]

# perform the formula
df_col_all_merged["s_mm"] = (25400/cn) - 254

where we index each row with the index from amc_active (but minus 1 since 0-indexed),

to get

       cn1  cn2  cn3  amc_active        s_mm
0       47   56   78           1  286.425532
1       55   61   87           2  162.393443
2       36   67   73           1  451.555556
3       42   84   95           3   13.368421
17410   42   84   96           3   10.583333
17411   48   81   85           1  275.166667
17412   55   59   82           1  207.818182
17413   57   86   93           2   41.348837
17414   36   87   91           2   37.954023

Alternatively, there is np.select in lieu of multiple if-elif's:

# form the conditions & corresponding choices
conditions = [df.amc_active.eq(1), df.amc_active.eq(2), df.amc_active.eq(3)]
choices = [df.cn1, df.cn2, df.cn3]

# select so
cn = np.select(conditions, choices)

# formula
df_col_all_merged["s_mm"] = (25400/cn) - 254

But due to convenient structure of df.amc_active this may not be the best. (also you may omit the last condition & choice and give it as the default to np.select, i.e., as an "else").

Upvotes: 1

jezrael
jezrael

Reputation: 862511

Use DataFrame.melt for alternative for lookup:

df1 = df.melt('amc_active', ignore_index=False)

CN = df1.loc['cn' + df1['amc_active'].astype(str) == df1['variable'], 'value']
df['new'] = (25400/CN) - 254
print (df)
       cn1  cn2  cn3  amc_active         new
0       47   56   78           1  286.425532
1       55   61   87           2  162.393443
2       36   67   73           1  451.555556
3       42   84   95           3   13.368421
17410   42   84   96           3   10.583333
17411   48   81   85           1  275.166667
17412   55   59   82           1  207.818182
17413   57   86   93           2   41.348837
17414   36   87   91           2   37.954023

Upvotes: 0

Related Questions