0

I need to save datetimes to an Excel file, such that, after reading the Excel file again, I can compare them with other datetimes. Here is a minimal example:

import pandas as pd, datetime
df = pd.DataFrame({'A': [1, 2],
                   'B': [datetime.datetime(2000,1,1,1,2,3), 
                         datetime.datetime(2010,1,1,1,2,3)]})
df.to_excel('test.xlsx')                # save to Excel file
df2 = pd.read_excel('test.xlsx')        # load from Excel file
print(df2)
t = df2.loc[df['A'] == 2, 'B']         # select the value in col B by filtering rows
L = [t, datetime.datetime(2020,1,1,1,2,3)]
print(sorted(L))

This gives the following error:

ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all().

What's the standard pandas way to retrieve the value of t as a datetime?

Basj
  • 41,386
  • 99
  • 383
  • 673

1 Answers1

2

You need scalar from one element Series:

#select first value if always matched at least one row
t = df2.loc[df['A'] == 2, 'B'].iat[0]

#general solution for return default value if no match
t = next(iter(df2.loc[df['A'] == 2, 'B']), 'no match')

EDIT: If create index by A column by DataFrame.set_index and always matching value like here 2 and column B is possibe use DataFrame.loc for select by index nand columns names:

df = df2.set_index('A')
t = df.loc[2, 'B']
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • Thanks @jezrael. Isn't there a shorter way in the API to access to a value at a location obtained by row-filtering? Something like `df.value[df['A'] == 2, 'B']`? Here it seems unusually complex to have to `.loc[...].iat[...]`, don't you think so? – Basj Mar 08 '21 at 15:19
  • PS @jezrael: I mean without changing the index (I could filter on other columns than the index) – Basj Mar 08 '21 at 15:22
  • Thank you very much @jezrael. Since you are one of the top pandas gurus (pandas developer?), you might have an idea for this one: https://stackoverflow.com/questions/66421693/modify-an-excel-file-with-pandas-with-minimal-change-of-the-layout I started a bounty, and a few ideas would be really great! Just in case, thanks :) – Basj Mar 08 '21 at 15:27
  • @Basj - Now not idea about another solution. – jezrael Mar 08 '21 at 15:27