Pandas: select multiple rows or default with new API

user_7058363 0
Viewed 128

I need to retrieve multiples rows (which could be duplicated) and if the index does not exist get a default value. An example with Series:

s = pd.Series(np.arange(4), index=['a', 'a', 'b', 'c'])
labels = ['a', 'd', 'f']
result = s.loc[labels]
result = result.fillna(my_default_value)

Now, I'm using DataFrame, an equivalent with names is:

df = pd.DataFrame({
    "Person": {
        "name_1": "Genarito",
        "name_2": "Donald Trump",
        "name_3": "Joe Biden",
        "name_4": "Pablo Escobar",
        "name_5": "Dalai Lama"
    }
})

default_value = 'No name'
names_to_retrieve = ['name_1', 'name_2', 'name_8', 'name_3']
result = df.loc[names_to_retrieve]
result = result.fillna(default_value)

With both examples it's throwing a warning saying:

FutureWarning: Passing list-likes to .loc or [] with any missing label will raise KeyError in the future, you can use .reindex() as an alternative.

In the documentation of the issue it says that you should use reindex but they say that It won't work with duplicates...

Is there any way to work without warnings and duplicated indexes?

Thanks in advance

pandas
4 Answers

Let's try merge:

result = (pd.DataFrame({'label':labels})
            .merge(s.to_frame(name='x'), left_on='label', 
                   right_index=True, how='left')
            .set_index('label')['x']
         )

Output:

label
a    0.0
a    1.0
d    NaN
f    NaN
Name: x, dtype: float64
user_42384080

How about :

on_values = s.loc[s.index.intersection(labels).unique()]
off_values = pd.Series(default_value,index=s.index.difference(labels))
result = pd.concat([on_values,off_values])
user_1522440

You can write a simple function to handle the rows in labels and missing from labels separately, then join. When True the in_order argument will ensure that if you specify labels = ['d', 'a', 'f'], the output is ordered ['d', 'a', 'f'].

def reindex_with_dup(s: pd.Series or pd.DataFrame, labels, fill_value=np.NaN, in_order=True):
    labels = pd.Series(labels)

    s1 = s.loc[labels[labels.isin(s.index)]]
    
    if isinstance(s, pd.Series):
        s2 = pd.Series(fill_value, index=labels[~labels.isin(s.index)])
    if isinstance(s, pd.DataFrame):
        s2 = pd.DataFrame(fill_value, index=labels[~labels.isin(s.index)],
                          columns=s.columns)
        
    s = pd.concat([s1, s2])

    if in_order:
        s = s.loc[labels.drop_duplicates()]

    return s


reindex_with_dup(s, ['d', 'a', 'f'], fill_value='foo')
#d    foo
#a      0
#a      1
#f    foo
#dtype: object

This retains the .loc behavior that if your index is duplicated and your labels are duplicated it duplicates the selection:

reindex_with_dup(s, ['d', 'a', 'a', 'f', 'f'], fill_value='foo')
#d    foo
#a      0
#a      1
#a      0
#a      1
#f    foo
#f    foo
#dtype: object
user_43333590

Check isin with append

out = s[s.index.isin(labels)]
out = out.append(pd.Series(index=set(labels)-set(s.index),dtype='float').fillna(0))
out
Out[341]: 
a    0.0
a    1.0
d    0.0
f    0.0
dtype: float64
user_79645270
Related