Writing data in csv file using pandas-CodePudding

I am trying to write data into a csv file Rest all the fields are getting written fine , but not the date field can someone please explain me what is going wrong

Here is my code

def writeData(self):

    query_string=f'https://query1.finance.yahoo.com/v7/finance/download/{self.ticker}?period1={self.period1}&period2={self.period2}&interval={self.interval}&events=history&includeAdjustedClose=true'.format(ticker=self.ticker,period1=self.period1,period2=self.period2,interval=self.interval)

    filename="C:\\Users\\BRBCO\\Downloads\\out.csv"

    url = query_string.replace(" ","")   

    df=pd.read_csv(url)

    df.columns= df.columns.str.lower() 

    df.to_csv(filename,encoding='utf-8',date_format='%s')

This is the output of the csv file

enter image description here

CodePudding user response：

In 98% of cases, the reason is that the CSV was opened directly with Excel and therefore Microsoft standards apply.

Excel always displays hash marks when the contents of a cell are too wide to be displayed in the cell. You can solve this problem by making the corresponding column wider.

In general

If you want to work with Excel, convert the DataFrame .to_excel() or open Excel, import the CSV file via the menu item Data and make your adjustments for interpreting column values.
Otherwise, you should rather open the CSV in a text editor, which will display the values accordingly.

CodePudding user response：

First of all, column B in the screenshot is not wide enough so all values show ########, it's impossible for us to see the actual output values.

Secondly, make sure the datetime values are actually interpreted as datetime datatypes when reading the file. So add dayfirst=True, parse_dates=True to the read_csv, and try changing the dayfirst parameter to True/False depending on your data.

Finally, when writing datetime values to CSV it's always easier to use ISO format YYYY-MM-DD, so for example 2022-03-31, because this is unambiguous and doesn't rely on any arbitrary system settings. You can write ISO date by setting the date_format in write_csv.

filename="C:\\Users\\BRBCO\\Downloads\\out.csv"

url = query_string.replace(" ", "")   

df=pd.read_csv(url, dayfirst=True, parse_dates=True)

df.columns = df.columns.str.lower() 

df.to_csv(filename, encoding='utf-8', date_format='%Y-%m-%d')