Lookup based on row and column header Pandas

Question

How do I use the QuantityFormula column to iterate over the column headers. For example to find

1. where count (from QuantityFormula) == count (from headers.
2. Take the value of that row
3. To produce a new column called Quantity, with that value.
4. Do the same for all Count, Area, Volume

It needs to work if new rows are added too.

I found this code online, to start with looking to modify it or create a new piece of code to do what I need. How do I loop and compare Column to header (lookup_array == lookup_value) and store row value of that.

Note: the NaN columns (count, area, volume) could have values in them in future tables

def xlookup(lookup_value, lookup_array, return_array, if_not_found:str = ''):
    match_value = return_array.loc[lookup_array == lookup_value]
    if match_value.empty:
        return f'"{lookup_value}" not found!' if if_not_found == '' else if_not_found

    else:
        return match_value.tolist()[0]

Merged['Quantity'] = Merged['QuantityFormula'].apply(xlookup, args = (Merged['NRM'], left['UoM']))

I have a XLOOKUP functionality but I need something slightly different.

Answer 1

With your current data, you have nan in the columns that aren't the one you want, and only have a real value in the one you do.

So, I say you just add up those three columns, which will effectively be the_number_you_want + 0 + 0. You can use np.nansum() to properly add the nan as zero.

...
import numpy as np
...

df['Quantity'] = np.nansum(df[['Count','Area','Volume']],axis=1)

Answer 2

here is one way to do it

I used a made-up Dataframe, if you had shared the dataframe as a code (preferably) or text, I would have used that. Refer to https://stackoverflow.com/help/minimal-reproducible-example

# use apply, to capture a row value for a column in forumla, along x-axis

df['quantity']=df.apply(lambda x: x[x['formula']] , axis=1)
df

    count   area    formula     quantity
0   1.0     NaN     count   1.0
1   1.0     NaN     count   1.0
2   NaN     1.4     area    1.4
3   NaN     0.6     area    0.6