Issue
I have multiple .csv files with different names like ATUL.csv, ISEC.csv, XYZ.csv and so on... Every file has similar data format mentioned below:
datetime symbol open high low close volume
2005-03-10 09:15:00 NSE:ATUL 85.59 89.00 85.19 86.84 73582
2005-03-11 09:15:00 NSE:ATUL 89.44 89.80 85.50 85.94 153945
2005-03-14 09:15:00 NSE:ATUL 86.90 88.75 84.00 84.65 73539
2005-03-15 09:15:00 NSE:ATUL 85.00 85.94 82.00 82.40 79053
I want to create a new .txt for every .csv file with formatting given below:
- remove 'symbol' column
- remove header row i.e. datetime|symbol|open.....
- change 'datetime' column date and time format to YYYYMMDD
- separate each column with ";"
Desired format sample given below for understanding.
20050310;85.59;89.00;85.19;86.84;73582
20050311;89.44;89.80;85.50;85.94;153945
20050314;86.90;88.75;84.00;84.65;73539
20050315;85.00;85.94;82.00;82.40;79053
I am trying below code to get my output
import pandas as pd
data = pd.read_csv('ATUL.csv')
data.drop('symbol', inplace=True, axis=1)
data['datetime'] = pd.to_datetime(data['datetime']).dt.strftime('%Y%m%d')
data.to_csv('output.txt', sep=';', header=None, index=False)
Above code is working great but its creating/converting one file at a time, my problem is that above with above code I am able to convert only one file at once, I have more than 500 files that I have to update on daily basis.
Is there any way or code that I could convert all my .csv files to text files with desired formatting (as motioned above code)
Solution
Here's code that does exactly what I suggested in my earlier comment.
It makes use of the built-in pathlib
module to simplify processing.
import pandas as pd
from pathlib import Path
def export_csv(input_filepath, output_filepath):
"""Reformat input file and save result to the given output file path."""
data = pd.read_csv(input_filepath)
data.drop('symbol', inplace=True, axis=1)
data['datetime'] = pd.to_datetime(data['datetime']).dt.strftime('%Y%m%d')
data.to_csv(output_filepath, sep=';', header=None, index=False)
folderpath = Path('path/to/csv/files/folder').resolve()
new_suffix = '.txt'
# Convert all .csv files in given folder.
for input_filepath in folderpath.glob('*.csv'):
# Output file path is the same as the input file except it has a different
# extension.
output_filepath = input_filepath.with_suffix(new_suffix)
export_csv(input_filepath, output_filepath) # Convert the file.
Answered By - martineau
0 comments:
Post a Comment
Note: Only a member of this blog may post a comment.