Matching value with column to retrieve index value-CodePudding

Please see example dataframe below: I'm trying match values of columns X with column names and retrieve value from that matched column

so that:

A B C X result
1 2 3 B 2 
5 6 7 A 5
8 9 1 C 1

Any ideas?

CodePudding user response：

Here I just build a dataframe from your example and call it df

dict = {
    'A': (1,5,8),
    'B': (2,6,9),
    'C': (3,7,1),
    'X': ('B','A','C')}

df = pd.DataFrame(dict)

You can extract the value from another column based on 'X' using the following code. There may be a better way to do this without having to convert first to list and retrieving the first element.

list(df.loc[df['X'] == 'B', 'B'])[0]

I'm going to create a column called 'result' and fill it with 'NA' and then replace the value based on your conditions. The loop below, extracts the value and uses .loc to replace it in your dataframe.

df['result'] = 'NA'
for idx, val in enumerate(list(vals)):
    extracted = list(df.loc[df['X'] == val, val])[0]
    df.loc[idx, 'result'] = extracted

Here it is as a function:

def search_replace(dataframe, search_col='X', new_col_name='result'):
    dataframe[new_col_name] = 'NA'
    for idx, val in enumerate(list(vals)):
        extracted = list(dataframe.loc[dataframe[search_col] == val, val])[0]
        dataframe.loc[idx, new_col_name] = extracted
    return df

and the output

>>> search_replace(df)
    A   B   C   X   result
0   1   2   3   B   2
1   5   6   7   A   5
2   8   9   1   C   1

CodePudding user response：

Here are a couple of methods:

# Apply Method:
df['result'] = df.apply(lambda x: df.loc[x.name, x['X']], axis=1)

# List comprehension Method:
df['result'] = [df.loc[i, x] for i, x in enumerate(df.X)]

# Pure Pandas Method:
df['result'] = (df.melt('X', ignore_index=False)
                  .loc[lambda x: x['X'].eq(x['variable']), 'value'])