How do I create a dataframe from another dataframe with only the last non negative values?

user_16053979 0
Viewed 49

I have a multi-indexed dataframe like so:

          year  value  some_other_column_i_dont_care_about
 one two     
 a   t     2000     0   aaa
     w     2001     3   bbb
     t     2002    -2   ccc
 b   t     2000     4   ddd
     w     2001     7   eee    
     t     2002    -8   fff
 c   t     2000    11   ggg
     w     2001   -12   hhh
     t     2002   -15   iii

How do I create a new, single level df, that just has the latest (in terms of years) non-negative values, like so:

          year   value
 one    
 a        2001     3
 b        2001     7
 c        2000    11
multi-index pandas vectorization dataframe python
1 Answers

Create a Boolean index for values greater than or equal 0 then use groupby last:

m = df['value'].ge(0)
new_df = df[m].groupby(level=0).last()

If the years aren't guarenteed to be sorted in ascending order, chain in a sort_values

new_df = df[m].sort_values('year').groupby(level=0).last()

new_df:

     year  value
one             
a    2001      3
b    2001      7
c    2000     11

Complete Working Example:

import pandas as pd

df = pd.DataFrame({
    'one': ['a', 'a', 'a', 'b', 'b', 'b', 'c', 'c', 'c'],
    'two': ['t', 'w', 't', 't', 'w', 't', 't', 'w', 't'],
    'year': [2000, 2001, 2002, 2000, 2001, 2002, 2000, 2001, 2002],
    'value': [0, 3, -2, 4, 7, -8, 11, -12, -15]
}).set_index(['one', 'two'])

m = df['value'].ge(0)
new_df = df[m].groupby(level=0).last()

print(new_df)
user_154978880
Related