Pandas (or Excel) find Search Key and get Value

user_11378968 0
Viewed 20

I have a Excelfile which I read with pandas into a dataframe. In this Excelfile are different Key-Value Pairs.

I want to Search for a Key and get the Value (with an row and/or column offset)

This is my code so far (with an example dataframe):

import json
import pandas as pd

def SearchValues(df,str_search,r_offset,c_offset):
    print(df[df.eq(str_search).any(1)])
    #return Value

data = {'Unnamed: 1':  ['', ''],
        'Unnamed: 2': ['', 'Key1'],
        'Unnamed: 3': ['', ''],
        'Unnamed: 4': ['', 'Value1'],
        'Unnamed: n': ['Key2', 'Value2'],
   }

df = pd.DataFrame(data)


SearchValues(df,'Key1',0,2) #=> result= Value1
SearchValues(df,'Key2',1,0) #=> result= Value2

I struggle in the search function. Is this a possible way? If yes how can I proceed? Or is there any other option. Maybe without dataframe to search directly in the Excelfile.

pandas excel python
1 Answers

You can reset your indices to have numerical ranges, then use a stacked DataFrame to identify the first match and get the indices, then slice after adding your offsets:

def SearchValues(df, str_search, r_offset, c_offset):
    # reset the index/columns to be a numerical range
    df = df.set_axis(range(df.shape[1]), axis=1).reset_index(drop=True)

    # find the row/col coordinates of the first match
    r,c = df.eq(str_search).stack().idxmax()

    # add the offsets and slice based on position
    return df.loc[r+r_offset, c+c_offset]

SearchValues(df, 'Key1', 0, 2)
# 'Value1'

SearchValues(df, 'Key2', 1, 0)
# 'Value2'

NB. this doesn't handle the case where the offset makes it overflow the dimensions of your DataFrame, but it's quite easy to add a check for that using df.shape

user_163434640
Related