Tukukkk

I have a dataset where I would like to select data where only the submission date is greater than '2018/11/14 01:26PM'.

The code below is what I have so far, but all other columns in the dataset gets populated with a value of nan. What am I doing wrong?

d = datetime.strptime('2018-11-14 01:26PM', '%Y-%m-%d %H:%M%p')

data[data['submission_date'] > d]

Data sample below:

 ID    Name   submission_date  

 12    Mike   2018-11-14 01:26PM

 13    Mark   2018-11-14 02:00PM

 14    Taylor 2018-11-14 03:26PM

 14    Taylor 2018-11-15 03:26PM

I know almost nothing about pandas but, using your question as a learning exercise, I found the following pattern. When data.columns is initialized with a flat list, which creates an Index object, all is well:

data = pandas.DataFrame( numpy.random.randn( 5, 2 ) )

data.columns=[ 'one', 'two' ]

print( data )



# Output:

#         one       two

# 0 -1.242567  0.430084

# 1 -1.125710 -0.342616

# 2 -0.514284  0.479382

# 3  0.108649 -0.789272

# 4  1.489155  0.842427



criterion = data[ 'one' ] > 0    # NB: criterion.shape is (5,): it is one-dimensional

print( data[ criterion ] )



# Output:

#         one       two

# 3  0.108649 -0.789272

# 4  1.489155  0.842427

However, if I change the dimensionality of the column structure (creating a MultiIndex) then I can recreate the NaN syndrome you describe:

data.columns = [ [ 'one', 'two' ] ]   # note the double-nesting

print(data)  # it "looks" identical to how it did before...



# Output:

#         one       two

# 0 -1.242567  0.430084

# 1 -1.125710 -0.342616

# 2 -0.514284  0.479382

# 3  0.108649 -0.789272

# 4  1.489155  0.842427



criterion = data[ 'one' ] > 0   # but this criterion.shape is now (5,1): it's two-dimensional...

print( data[ criterion ] )



# Output:

#         one two

# 0       NaN NaN

# 1       NaN NaN

# 2       NaN NaN

# 3  0.108649 NaN

# 4  1.489155 NaN

It depends on the (superficially invisible) details of your DataFrame's column structure. It's very surprising to me that there was no warning or exception when you performed the slicing, and I can't imagine any context in which the NaN-ridden result would be the sensible, expected outcome.

Anyway, the problem can clearly be circumvented by reshaping the array you're using to index your data, so that its shape is (5,) again:

print( data[ criterion.values.flatten() ] )    # back to sanity



# Output:

#         one       two

# 3  0.108649 -0.789272

# 4  1.489155  0.842427

However if you don't want to take advantage of any particular MultiIndex behavior provided by your existing column structure, then the more elegant solution (indicated by your comment) may be simply to reassign data.columns to ensure that it's a flat list to start with.