0

I am reading a xlsb file with pandas read_excel.

df = pd.read_excel("..\df.xlsb",
                   sheet_name="Data",
                   engine='pyxlsb'
                   )

When I check the file, 'Date' column type is changing to excel integer format. I' m giving an example df below.

df = {'Date': [44409, 44409, 44410, 44410, 44411]
     }

I tried to_datetime function of pandas. But it gives 1970-01-01 result. I also tried several methods but nothing is changed.

df['Date'] = pd.to_datetime(df['Date'], unit = 's') #trial1
df['Date'] = pd.to_datetime(df['Date'], unit = 'ns') #trial2
df['Date'] = pd.to_datetime(df['Date'], format = "%Y%m%d") #trial3

I want to see this time format. But I don't want use "for loop", because I have 19000 rows in original data.

    Date
0   2021-08-01
1   2021-08-01
2   2021-08-02
3   2021-08-02
4   2021-08-03

I also tried these solutions;

Convert Excel style date with pandas

How to convert a python datetime.datetime to excel serial date number

piseynir
  • 237
  • 1
  • 4
  • 14
  • For me it working perfecly `df['real_date'] = pd.to_datetime(df['Date'], unit='D', origin='1899-12-30')` – jezrael Sep 02 '21 at 08:45
  • It gives that error "Name: Date, dtype: datetime64[ns]' is not compatible with origin='1899-12-30'; it must be numeric with a unit specified" – piseynir Sep 02 '21 at 08:47
  • also another solutions failed? with `df = pd.DataFrame({'Date': [44409, 44409, 44410, 44410, 44411] })` ? – jezrael Sep 02 '21 at 08:48
  • When I try this: """df['real_date'] = pd.TimedeltaIndex(df['Date'], unit='d') + dt.datetime(1900,1,1)""" it gives that error --> TypeError: dtype datetime64[ns] cannot be converted to timedelta64[ns] – piseynir Sep 02 '21 at 08:49
  • I think need only `df = pd.DataFrame({'Date': [44409, 44409, 44410, 44410, 44411] })` and then `df['real_date'] = pd.to_datetime(df['Date'], unit='D', origin='1899-12-30')` – jezrael Sep 02 '21 at 08:51
  • btw, your error means `Date` is already datetime, not integers – jezrael Sep 02 '21 at 08:52
  • 'Date' is a dataframe column not a dictionary. – piseynir Sep 02 '21 at 08:54
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236675/discussion-between-piseynir-and-jezrael). – piseynir Sep 02 '21 at 08:55

0 Answers0