How To Find Column Number By Looking Up A Value

From a dataframe, how do I find the column number by lookup for a value in each row? For instance, a dataframe look like below: A B C D 1 x1 x9 x7 x5 2 x8 x1

Solution 1:

You could melt the dataframe, which will transform to two columns variable and value, then just filter for the value x1 and column variable and send to a list. But, if order is important, then you can sort by the row number using groupby with cumcount to create a temporary sorter column. Also, % df.shape[0] means that you are dividing by the total number of rows of the initial dataframe and getting the remainder, which essentially tells you the order from the initial dataframe.

df1 = df.melt()
([*df1.assign(sorter=df1.groupby('variable').cumcount() % df.shape[0])
  .sort_values('sorter')
  .loc[df1['value'] == 'x1', 'variable']])

['A', 'B', 'D', 'B']

Solution 2:

You could use np.nonzero to get the locations, then index the columns of the dataframe:

df.columns[np.nonzero(df.to_numpy() == "x1")[-1]]

Index(['A', 'B', 'D', 'B'], dtype='object')

Solution 3:

This will help you

df.melt()[df.melt()['value'] == 'x1']['variable']

or

tmp = df.melt()
tmp[tmp['value'] == 'x1']['variable']