pythonpandasdatetimeindex

Pandas: Extracting values from a DatetimeIndex

I have a Pandas DataFrame whose rows and columns are a DatetimeIndex.

import pandas as pd

data = pd.DataFrame(
    {
        "PERIOD_END_DATE": pd.date_range(start="2018-01", end="2018-04", freq="M"),
        "first": list("abc"),
        "second": list("efg")
    }
).set_index("PERIOD_END_DATE")

data.columns = pd.date_range(start="2018-01", end="2018-03", freq="M")
data

DataFrame

Unfortunately, I am getting a variety of errors when I try to pull out a value:

data['2018-01', '2018-02']       # InvalidIndexError: ('2018-01', '2018-02')
data['2018-01', ['2018-02']]     # InvalidIndexError: ('2018-01', ['2018-02'])
data.loc['2018-01', '2018-02']   # TypeError: only integer scalar arrays can be converted to a scalar index
data.loc['2018-01', ['2018-02']] # KeyError: "None of [Index(['2018-02'], dtype='object')] are in the [columns]"

How do I extract a value from a DataFrame that uses a DatetimeIndex?

Solution

  • There are 2 issues:

    1. Since, you are using a DateTimeIndex dataframe, the correct notation to traverse between rows and columns are:
    a) data.loc[rows_index_name, [column__index_name]]

    or

    b) data.loc[rows_index_name, column__index_name]

    depending on the type of output you desire.

    Notation A will return a series value, while notation (b) returns a string value.

    1. The index names can not be amputated- you must specify the whole string.

    As such, your issue will be resolved with:

    data.loc['2018-01-31',['2018-01-31']] or data.loc['2018-01-31','2018-01-31']