Get list with element's columns from Pandas DataFrame

Question

I need to have a list containing all specific element's columns for every index. For example, this DataFrame:

>>> df
                     1           2           3           4           5
2016-01-27           A           B           B           I           I  
2016-03-07           A           C           D           U           U   
2016-04-12           H           A           V           V           V   
2016-05-02           B           L           Y           S           N   
2016-05-23           L           N           N           A           S

Inputting "A" I'd like to have this list as output:

[1,1,2,NaN,4]

Is there a built-in method for this?

Edit: In the original table all items in a row are unique, when editing original table to make it less "dense" to post here and I made this mistake, sorry.

Do you want the first index of the input? What would 'B' return for row 1? — brianpck
– brianpck, Commented Nov 1, 2016 at 19:18
In the original table all items in a row are unique, sorry, I edited the original table to make it less "dense" to post here and I made this mistake. — Vinícius Figueiredo
– Vinícius Figueiredo, Commented Nov 1, 2016 at 19:23

akuiper · Accepted Answer · 2016-11-01 19:22:32Z

2

You may want to melt your data frame to long format and then calculate the corresponding list of columns for each input(value), After obtaining the Series as follows, it would be easy for you to query the result for any intended input:

import pandas as pd
pd.melt(df).groupby('value').variable.apply(list)

#value
#A    [1, 1, 2, 4]
#B       [1, 2, 3]
#C             [2]
#D             [3]
#H             [1]
#I          [4, 5]
#L          [1, 2]
#N       [2, 3, 5]
#S          [4, 5]
#U          [4, 5]
#V       [3, 4, 5]
#Y             [3]
#Name: variable, dtype: object

To get the list of columns for input A:

result = pd.melt(df).groupby('value').variable.apply(list)

result['A']
# ['1', '1', '2', '4']

edited Nov 1, 2016 at 19:22

answered Nov 1, 2016 at 19:17

akuiper

216k33 gold badges362 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Vinícius Figueiredo Over a year ago

This works well, but is there a way to get a "NaN" value when there is no 'A' in the row?

akuiper Over a year ago

Is it guaranteed each row has at most one A? What if it has multiple As? Which one you want to keep?

Vinícius Figueiredo Over a year ago

Yes, all items in a row are unique, I made this mistake when editing the table to look less "dense" in here, I just edited original post.

akuiper Over a year ago

Then you may try something as follows. df.apply(lambda r: (r == 'A').idxmax() if any(r == 'A') else np.nan, axis = 1).tolist().

Collectives™ on Stack Overflow

Get list with element's columns from Pandas DataFrame

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related