Find the last value in a list in a dataframe

Question

please, i want to find the last value of in client in a dataframe, how can i do it?

Example :

df = pd.DataFrame({'date': 
    ['2018-06-13', '2018-06-14', '2018-06-15', '2018-06-16'],
    'gain': [[10, 12, 15],[14, 11, 15],[9, 10, 12], [6, 4, 2]],
    'how':  [['customer1', 'customer2', 'customer3'], 
            ['customer4','customer5','customer6' ],
            ['customer7', 'customer8', 'customer9'],
            ['customer5', 'customer6', 'customer10'] ]}


   df : 
           date       gain                    how
    0 2018-06-13  [10, 12, 15]    [customer1, customer2, customer3]
    1 2018-06-14  [14, 11, 15]    [customer4, customer5, customer6]
    2 2018-06-15  [9, 10, 12]     [customer7, customer8, customer9]
    3 2018-06-16  [6, 4, 2]       [customer5, customer6, customer10]

I want to do a function that returns the last gain in the dataframe.
example :
 for the customer5 = 6
 for the customer4 = 14
 for the customer20 = 'not found'

thank you so much

You have a list stored in a dataframe. Pandas will only be an impediment for this. Nothing can be vectorized. — roganjosh, Dec 09 '18 at 17:30
thank you @roganjosh how can I detect that the lines that contain the costumers ? — Data Scientist, Dec 09 '18 at 17:34

BENY · Accepted Answer · 2018-12-09T17:44:43.943

Using unnesting function then , drop_duplicates

newdf=unnesting(df,['gain','how']).drop_duplicates('how',keep='last')
newdf
Out[25]: 
   gain         how        date
0    10   customer1  2018-06-13
0    12   customer2  2018-06-13
0    15   customer3  2018-06-13
1    14   customer4  2018-06-14
2     9   customer7  2018-06-15
2    10   customer8  2018-06-15
2    12   customer9  2018-06-15
3     6   customer5  2018-06-16
3     4   customer6  2018-06-16
3     2  customer10  2018-06-16

Then input your search list with reindex

l=['customer5','customer6','customer20']

newdf.loc[newdf.how.isin(l)].set_index('how').reindex(l,fill_value='not_find')
Out[34]: 
                gain        date
how                             
customer5          6  2018-06-16
customer6          4  2018-06-16
customer20  not_find    not_find

Interesting reading about the solution of this type question

How do I unnest a column in a pandas DataFrame?

def unnesting(df, explode):
    idx=df.index.repeat(df[explode[0]].str.len())
    df1=pd.concat([pd.DataFrame({x:np.concatenate(df[x].values)} )for x in explode],axis=1)
    df1.index=idx
    return df1.join(df.drop(explode,1),how='left')

@roganjosh I think the new pandas method is under developing for this type of question , let us waiting for them to add that in next version :-) — BENY, Dec 09 '18 at 17:42
I meant "just" rather than "does", but nice answer. I wasn't aware of the function. — roganjosh, Dec 09 '18 at 17:46

Find the last value in a list in a dataframe

1 Answers1