pandas element-wise comparison and create selection

Question

In a dataframe I would like to compare the elements of a column with a value and sort the elements which pass the comparison into a new column.

df = pandas.DataFrame([{'A':3,'B':10},
                       {'A':2, 'B':30},
                       {'A':1,'B':20},
                       {'A':2,'B':15},
                       {'A':2,'B':100}])

df['C'] = [x for x in df['B'] if x > 18]

I can't find out what's wrongs and why I get:

ValueError: Length of values does not match length of index

jezrael · Answer 1 · 2016-05-26T13:57:56.680

I think you can use loc with boolean indexing:

print (df)
   A    B
0  3   10
1  2   30
2  1   20
3  2   15
4  2  100

print (df['B'] > 18)
0    False
1     True
2     True
3    False
4     True
Name: B, dtype: bool

df.loc[df['B'] > 18, 'C'] = df['B']
print (df)
   A    B      C
0  3   10    NaN
1  2   30   30.0
2  1   20   20.0
3  2   15    NaN
4  2  100  100.0

If you need select by condition use boolean indexing:

print (df[df['B'] > 18])
   A    B
1  2   30
2  1   20
4  2  100

If need something more faster, use where:

df['C'] = df.B.where(df['B'] > 18)

Timings (len(df)=50k):

In [1367]: %timeit (a(df))
The slowest run took 8.34 times longer than the fastest. This could mean that an intermediate result is being cached.
1000 loops, best of 3: 1.14 ms per loop

In [1368]: %timeit (b(df1))
100 loops, best of 3: 15.5 ms per loop

In [1369]: %timeit (c(df2))
100 loops, best of 3: 2.93 ms per loop

Code for timings:

import pandas as pd

df = pd.DataFrame([{'A':3,'B':10},
                       {'A':2, 'B':30},
                       {'A':1,'B':20},
                       {'A':2,'B':15},
                       {'A':2,'B':100}])
print (df)
df = pd.concat([df]*10000).reset_index(drop=True)
df1 = df.copy()
df2 = df.copy()

def a(df):
    df['C'] = df.B.where(df['B'] > 18)
    return df

def b(df1):    
    df['C'] = ([x if x > 18 else None for x in df['B']])
    return df

def c(df2):    
    df.loc[df['B'] > 18, 'C'] = df['B']
    return df

print (a(df))
print (b(df1))
print (c(df2))

I add new faster method, please check it. Thanks. – jezrael May 26 '16 at 13:58 — jezrael, May 26 '16 at 13:58

score 2 · Accepted Answer · answered May 24 '16 at 07:27

As Darren mentioned, all columns in a DataFrame should have same length.

When you try print [x for x in df['B'] if x > 18], you get only [30, 20, 100] values. But you have got five index/rows. That's the reason you get Length of values does not match length of index error.

You can change your code as follows:

df['C'] = [x if x > 18 else None for x in df['B']]
print df

You will get:

   A    B      C
0  3   10    NaN
1  2   30   30.0
2  1   20   20.0
3  2   15    NaN
4  2  100  100.0

score 0 · Answer 3 · edited May 23 '17 at 12:34

All columns in a DataFrame have to be the same length. Because you are filtering away some values, you are trying to insert fewer values into column C than are in columns A and B.

So, your two options are to start a new DataFrame for C:

dfC = [x for x in df['B'] if x > 18]

or but some dummy value in the column for when x is not 18+. E.g.:

df['C'] = np.where(df['B'] > 18, True, False)

Or even:

df['C'] = np.where(df['B'] > 18, 'Yay', 'Nay')

P.S. Also take a look at: Pandas conditional creation of a series/dataframe column for other ways to do this.

pandas element-wise comparison and create selection

3 Answers3