data frame selecting data using a DatetimeIndex

Question

I have a data frame looks like this:

import numpy as np
import pandas as pd    

data = {'datetime' : ['2009-07-24 02:00:00', '2009-07-24 03:00:00','2009-07-24 04:00:00'],
     'value1' : ['a', np.nan ,'c'],
     'value2' : ['d','e','f']}
df = pd.DataFrame(data)
df = df.set_index(pd.DatetimeIndex(df['datetime']))
missing = df.loc[:, df.columns != ('datetime')]

the data above is just a sample. but let say I have a lot of missing values in bigger data. I want to select all the data with missing values in 'value1' column.

missing_index = df[df['value1'].isnull()].index

this code will let get me all the indices of missing values, but I want the actual rows of them, in this case, second row.

So, I tried,

df[missing_index]

but I am having an error saying

KeyError: "DatetimeIndex(['2009-07-24 03:00:00'], dtype='datetime64[ns]', name='datetime', freq=None) not in index"

df[df['value1'].isnull()] ?

panktijk
– panktijk

2019-02-17 03:00:11 +00:00
Commented Feb 17, 2019 at 3:00 — panktijk
– panktijk, Commented Feb 17, 2019 at 3:00

aiguofer · Accepted Answer · 2019-05-22 20:29:04Z

1

The error comes from the fact that df[<something>] is used to get columns. When you call df[missing_index] it's trying to find the missing_index in the columns (which is also an Index).

The easiest way to do what you want is as @panktijk pointed out in his comment:

df[df['value1'].isnull()]

However, if for some reason (maybe you want to manipulate them) you want to go your way where you first get the indexes and then use those to pull your sub-dataframe, you could do the following:

df.loc[missing_index]

answered May 22, 2019 at 20:29

aiguofer

2,16522 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Frenchy · Accepted Answer · 2019-02-17 09:27:13Z

0

i am using index to capure the row number:(begin to 0)

import pandas as pd
import numpy as np

data = {'datetime' : ['2009-07-24 02:00:00', '2009-07-24 03:00:00','2009-07-24 04:00:00', '2009-07-24 05:00:00'],
     'value1' : ['a', np.nan ,'c', np.nan],
     'value2' : ['d','e','f', 'g']}
df = pd.DataFrame(data)
df = df.set_index(pd.DatetimeIndex(df['datetime']))

listofnan = df.index[df['value1'].isnull()].tolist()

for i in listofnan:
    print(df.index.get_loc(i))

result:

1
3

answered Feb 17, 2019 at 9:27

Frenchy

17.1k3 gold badges21 silver badges42 bronze badges

Collectives™ on Stack Overflow

data frame selecting data using a DatetimeIndex

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related