not keeping the dates when creating a dataframe pandas

52 Views Asked by At

I am merging multiple xlsx files using pd.concat(). I have 7 date columns with custom formatting in excel file. When I concatenate the files, instead of keeping the original dates, all dates change to today's date. I would appreciate your help. Columns, contains dates are given by indices.

Here is the code I am using:

import csv
import glob
import sys
import pandas as pd
import os

os.chdir(r"path")

all_files = glob.glob(os.path.join(path, "*.xlsx"))   

header=['header names']

df = (pd.read_excel(f, sheet_name=0, skiprows=2, usecols='A:DN', sort=False, 
                    header=None, names=header, infer_datetime_format=False,  
                    format='%m/%d/%Y', parse_dates=[0,1,2,3,4,5,6,28], 
                    start='01/01/1987', end='01/01/2019',
                    converters={'Initial Warrant Call Date':'datetime64[ns]', 
                                'Trade Date':'datetime64[ns]', 
                                'Overallotment Exercise Date':'datetime64[ns]', 
                                'Filing Date':'datetime64[ns]', 
                                'Issue Date':'datetime64[ns]', 
                                'Date Filing Amended':'datetime64[ns]',  
                                'Date Founded':'datetime64[ns]'}) 
                    for f in all_files)

concatenated_df = pd.concat(df, ignore_index=True)
concatenated_df.to_csv('merged_ipo.csv', encoding='utf-8', index=False)

Output: a snapshot of the concatenated df. a snapshot of the concatenated df

0

There are 0 best solutions below