pythoncsvdelimiterohlc

Python: Issues with writing comma delimited data to a .csv file

Goal:

  1. Read stock price data in from a .csv file
  2. resample it
  3. finally print the output to a new .csv file whereby all columns are comma delimited.

Issues:

Currently, the program can 1) read in the data and 2) resample it, but when printing to an output .csv there are no comma delimiters. Furthermore, the column headers are not printed on the same first line.

Input data sample:

*Columns of interest: First column (which is date/time), second column (bid price), and fourth column (ask price).

20210602 22:02:00,3.07,50,3.086,50
20210602 22:03:00,3.07,50,3.087,50
20210602 22:04:00,3.071,50,3.087,50
20210602 22:04:00,3.071,50,3.088,50
20210602 22:05:00,3.07,50,3.088,50
20210602 22:05:00,3.07,50,3.087,50
20210602 22:06:00,3.071,50,3.087,50

Current output:

                     open    high    low      close
Date_time
2021-06-02 22:02:00  3.0790  3.0790  3.0780  3.0780
2021-06-02 22:03:00  3.0785  3.0785  3.0785  3.0785
2021-06-02 22:04:00  3.0790  3.0795  3.0790  3.0795
2021-06-02 22:05:00  3.0790  3.0790  3.0785  3.0785
2021-06-02 22:06:00  3.0790  3.0790  3.0790  3.0790

Expected output:

Date_time,open,high,low,close
2021-06-02 22:02:00,3.0790,3.0790,3.0780,3.0780
2021-06-02 22:03:00,3.0785,3.0785,3.0785,3.0785
2021-06-02 22:04:00,3.0790,3.0795,3.0790,3.0795
2021-06-02 22:05:00,3.0790,3.0790,3.0785,3.0785
2021-06-02 22:06:00,3.0790,3.0790,3.0790,3.0790

Program attempt:

import pandas as pd
import sys

data_frame = pd.read_csv('03-06-21-xngusd.csv',
                         names=['Date_time', 'Bid', 'Bid qty',
                                  'Ask', 'Ask qty'],
                         index_col=0, parse_dates=True)
data_frame.head()

# Resample the data into 1 minute increments
data_ask = data_frame['Ask'].resample('1MIN').ohlc()
data_bid = data_frame['Bid'].resample('1MIN').ohlc()
data_mid = (data_ask + data_bid) / 2

data_mid.head()

# Print the new resample data to a .csv file
original_stdout = sys.stdout # Save reference to original standard output
with open('filename.csv', 'w') as f:
    sys.stdout = f
    print(data_mid)
    sys.stdout = original_stdout

Also tried the following code, which only printed the first row and also missed the first column header showing an output of open, high, low, close:

with open('filename.csv', 'w') as writeFile:
    writer = csv.writer(writeFile, delimiter=',')
    writer.writerow(data_mid)

Q1. How can the program be modified to ensure that the output columns are comma delimited?

Q2. How can the column headers be printed to be on the same line at the top of the file?

Solution

  • You can use data_frame.to_csv() for this. Read about it here.