CODEWITHSUNDEEP
pythonpandasdataframe
iuuujkl
iuuujkl

Reputation: 2384

Get closest datetime index value from pd DataFrame

I've got following DataFrame:

                                  holdings
2021-08-28 04:10:14.130412+00:00  {'$USD': 158, 'Apple': 3}
2021-08-25 18:10:14.130412+00:00  {'$USD': 158, 'Apple': 3}

With holdings as column and datetimes as index.

I got this by converting following dict to a DataFrame: (data is not consistent with previous example but it is the same format, so please ignore that)

{
    datetime.datetime(2021, 8, 28, 4, 10, 15, 180064, tzinfo=datetime.timezone.utc): {
        "$USD": "158.1727087865",
        "Apple": "3",
        "MSFT": "3",
    },
    datetime.datetime(2021, 8, 24, 4, 10, 15, 180064, tzinfo=datetime.timezone.utc): {
        "$USD": "158.1727087865",
        "Apple": "3",
        "MSFT": "3",
    }
}

I transform the dict to a dataframe by:

    holdings_dict = {
        key: {"holdings": holdings_dict[key]}
        for key in holdings_dict.keys()
    }
    holdings_df =  pd.DataFrame.from_dict(
        holdings_dict, orient="index", columns=["holdings"]
    ).sort_index(axis=0)

Now I try to get the nearest index and value to a certain date, let's say 2021-08-25, which is stored as cur_datetime

holdings_df.index.get_loc(
                    pd.to_datetime(cur_datetime), method="previous"
                )["holdings"]

But this gives an error

ValueError: Invalid fill method. Expecting pad (ffill), backfill (bfill) or nearest. Got previous

How can I get the value of the nearest datetime (in a query you would do this by LTE)

Upvotes: 0

Views: 90

Answers (1)

mozway
mozway

Reputation: 260640

I have to say you have a bit unconventional way to work with pandas ;)

Nevertheless, get_loc return the range, so you need to use iloc to slice your row:

holdings_df.iloc[holdings_df.index.get_loc(pd.to_datetime(cur_datetime), method='backfill')]['holdings']

output:

{'$USD': '158.1727087865', 'Apple': '3', 'MSFT': '3'}

Upvotes: 1

Related Questions