what's the fastest way to check that all rows in a dataframe have one or zero non-NaN values?

user_875343 0
Viewed 60

It's fairly common that I want to check that there is no more than one non-NaN value in rows of some subset of columns in a pandas dataframe (for instance if I want to collapse multiple columns into one as long as they don't overlap).

This is my current solution, but it also creates a noticeable delay after calling a function that makes this check on a dataframe with around 1.4e5 rows

import pandas as pd

data = [{'A':'a', 'B':42, 'messy':'z'},
    {'A':'b', 'B':52, 'messy':'y'},
    {'A':'c', 'C':31},
    {'A':'d', 'C':2, 'messy':'w'},
    {'A':'e', 'D':62, 'messy':'v'},
    {'A':'f', 'D':70, 'messy':['z']}]
df = pd.DataFrame(data)

df2 = df.append({}, ignore_index=True)

df3 = df.append({'B':555, 'C':555}, ignore_index=True)

def check_rows(df, cols):
    if df[cols].apply(lambda x: x.dropna().size < 2, axis=1).all():
        print("columns don't overlap")
    else:
        raise ValueError('found multiple values in a single row for columns ' + str(cols))

cols = ['B', 'C', 'D']

check_rows(df, cols) # columns don't overlap
check_rows(df2, cols) # columns don't overlap
check_rows(df3, cols) # error

What's the fastest way to do this?

pandas python
1 Answers

For improve performance count non missing values by DataFrame.count, then less values like 2 by Series.lt with Series.all:

def check_rows(df, cols):
    if df[cols].count(axis=1).lt(2).all():
        print("columns don't overlap")
    else:
        raise ValueError('found multiple values in a single row for columns ' + str(cols))
user_29010020
Related