CODEWITHSUNDEEP
pythonexport-to-csv
Huzefa Sadikot
Huzefa Sadikot

Reputation: 581

Converting Multiple Excel Files Into One

I have multiple excel files saved in a folder which I converted into one Data Frame. I wrote a python code to combine all the csv files into one Data Frame. The issue is that the column format is not what I expect to be. This means that the original files contained separate Open, High, Low, Close and Volume functions but the combined Data Frame contains all these data merged into one. I want to merge the files such that the original column format is preserved that is Open, High, Low, Close, Volume are separate columns.

Here is my code:

import os
import pandas as pd
os.chdir("C:/Users/Administrator/Desktop/Zerodha/Day2/")
path = "C:/Users/Administrator/Desktop/Zerodha/Day2/"
files =os.listdir(path)
values =pd.DataFrame()
for f in files:
    data = pd.read_csv(f, delim_whitespace=True)
    values = values.append(data)

This is the output of my master data Frame:

Values Data Frame

However, I would like the master Data Frame to have Seperate Columns as shown:

Original Files

Any feedbacks will be appreciated.

Thanks.

Upvotes: 0

Views: 153

Answers (1)

Attila Viniczai
Attila Viniczai

Reputation: 693

Try using a column separator that matches the contents of the CSV files. For example use pd.read_csv(f, delimiter=',') if the plain-text content of the CSV files is similar to below:

date,open,high,low,close,volume
2020-11-09 09:15:00+05:30,10.25,10.45,10.25,10.45,300

Reason is that pandas.read_csv(fileobj, delim_whitespace=True) expects whitespaces as column separators. If the CSV files have the format assumed in my example (comma-separated), then having delim_whitespace=True will result in the problem encountered.

A snippet from pandas.read_csv() docs on delim_whitespace:

delim_whitespace: bool, default False

Specifies whether or not whitespace (e.g. ' ' or ' ') will be used as the sep. Equivalent to setting sep='\s+'. If this option is set to True, nothing should be passed in for the delimiter parameter.

Upvotes: 1

Related Questions