Skip to content

Instantly share code, notes, and snippets.

@paduel
Last active May 3, 2019 16:24
Show Gist options
  • Save paduel/2e29d45341bb62c3f28c8e845b7a8c5d to your computer and use it in GitHub Desktop.
Save paduel/2e29d45341bb62c3f28c8e845b7a8c5d to your computer and use it in GitHub Desktop.
Read a xlxs file from Visualchart data patch into a pandas DataFrame
from pandas import read_excel, to_datetime
def read_vc_excel(io):
"""Read a xlxs file from Visualchart historical data patch
into a pandas DataFrame.
Parameters:
io : str, file descriptor, pathlib.Path, ExcelFile or xlrd.Book
The string could be a URL. Valid URL schemes include http, ftp, s3,
gcs, and file. For file URLs, a host is expected. For instance, a local
file could be /path/to/workbook.xlsx.
Returns:
DataFrame : DataFrame from the passed in Excel file.
Example:
file.xlsx is a Excel file from ExcelData export tool of VisualChart,
df = read_vc_excel('/yourpath/file.xlsx')
df result a pandas DataFrame with the same informatión correctly parsed.
"""
data = read_excel(io,
dtype={'Date':'str', 'Time': 'str'})[1:]
data.set_index(to_datetime(data.Date + ' ' + data.Time.str.zfill(4)), inplace=True)
data.drop(['Date','Time'], axis=1, inplace=True)
data.index.name='Datetime'
return data
@paduel
Copy link
Author

paduel commented May 3, 2019

Only copy and paste the function into your code, or import the function.

Supose file.xlsx is a Excel file from ExcelData export tool of VisualChart, then

df = read_vc_excel('/yourpath/file.xlsx')

df result a pandas DataFrame with the same information correctly parsed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment