Convert column to date format-CodePudding

I am trying to convert the date to a correct date format. I have tested some of the possibilities that I have read in the forum but, I still don't know how to tackle this issue:

After importing:

df = pd.read_excel(r'/path/df_datetime.xlsb', sheet_name="12FEB22", engine='pyxlsb')

I get the following df:

{'Unnamed: 0': {0: 'Administrative ID',
  1: '000002191',
  2: '000002382',
  3: '000002434',
  4: '000002728',
  5: '000002826',
  6: '000003265',
  7: '000004106',
  8: '000004333'},
 'Unnamed: 1': {0: 'Service',
  1: 'generic',
  2: 'generic',
  3: 'generic',
  4: 'generic',
  5: 'generic',
  6: 'generic',
  7: 'generic',
  8: 'generic'},
 'Unnamed: 2': {0: 'Movement type',
  1: 'New',
  2: 'New',
  3: 'New',
  4: 'Modify',
  5: 'New',
  6: 'New',
  7: 'New',
  8: 'New'},
 'Unnamed: 3': {0: 'Date',
  1: 37503,
  2: 37475,
  3: 37453,
  4: 44186,
  5: 37711,
  6: 37658,
  7: 37770,
  8: 37820},
 'Unnamed: 4': {0: 'Contract Term',
  1: '12',
  2: '12',
  3: '12',
  4: '12',
  5: '12',
  6: '12',
  7: '12',
  8: '12'}}

However, even although I have tried to convert the 'Date' Column (or 'Unnamed 3', because the original dataset hasn't first row so I have to change the header after that) during the importation, it has been unsuccessful.

Is there any option that I can do?

Thanks!

CodePudding user response：

try this:

from xlrd import xldate_as_datetime


def trans_date(x):
    if isinstance(x, int):
        return xldate_as_datetime(x, 0).date()
    else:
        return x


print(df['Unnamed: 3'].apply(trans_date))
>>>
0          Date
1    2002-09-04
2    2002-08-07
3    2002-07-16
4    2020-12-21
5    2003-03-31
6    2003-02-06
7    2003-05-29
8    2003-07-18
Name: Unnamed: 3, dtype: object