Finding last column index that unique value appears

Question

I have 2 Pandas DataFrames, one contains names in a single column:

https://i.stack.imgur.com/DMhxx.png

And I want to find what the last column index the name appear in this other table:

https://i.stack.imgur.com/geCKV.png

for example:

Jacob 2
Dash 3
Tim 1
John 5
Kanye 2

Please don't post data as code, it's not reproducible. See [How to make good pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) — G. Anderson, Sep 24 '19 at 18:37

score 0 · Accepted Answer · answered Sep 24 '19 at 19:01

This is my answer for you: The .last_valid_index() is a handy function that can be used for your task. Unfortunately it finds the last row, and not the last column. Therefore I transpose the dataframe before, so rows and columns are exchanged, then .last_valid_index() can be used.

 import pandas as pd

#creating first df
names = pd.DataFrame()
names["Names"] = ["Jacob","Dash","Tim","John","Kanye"]

#creating second df
table = pd.DataFrame()
table[0] = ["Jacob","NA","NA","NA","NA"]
table[1] = ["NA","Dash","Jacob","NA","NA"]
table[2] = ["NA","NA","Tim","NA","Dash"]
table[3] = ["Tim","NA","NA","John","NA"]

#find last occurrence
table_T = table.transpose() #create transpose copy of df
last_occurrences = [table_T.where(table_T==name).last_valid_index() for name in names["Names"]]
names["LastOcc"] = last_occurrences

Result is added in the first dataframe: see picture

score 0 · Answer 2 · answered Sep 25 '19 at 10:17

import pandas as pd
df1 = pd.DataFrame(["Jacob","Dash","Tim","John","Kanye"],columns=['Names'])

li = [["Jacob","NA","NA","NA","NA","John"], ["NA","NA","NA","Dash","NA","NA"], \
      ["Tim","NA","Jacob","NA","NA","NA"], ["NA","Tim","NA","NA","Dash","NA"], \
     ["Kanye","NA","Kanye","NA","NA","NA"]]
df2 = pd.DataFrame(li)

print([(df2.isin([f"{name}"]).any()).nonzero()[0][-1] for name in df1["Names"]])

Finding last column index that unique value appears

2 Answers2