Stack Overflow Asked by Clinton Lam on November 4, 2021
Not able to splice data according to date index. I would like to obtain data on '2020-07-20'
This script first read output.csv, then create DataFrame and place column ‘BidClose’, and at last print out specific date range of data AskClose (which is now failed)
In order to splice data with data[start:end] format where start,end is date range, data has to be first sorted.
data.sort_index(inplace=True)
import pandas as pd
import numpy as np
import time
datafile = 'output.csv'
data = pd.read_csv(datafile, index_col = 'Date')
# Selects the columns
close = data['BidClose']
askClose = data['AskClose']
# Converts the date strings in the index into pandas datetime format:
close.index = pd.to_datetime(close.index)
askClose.index = pd.to_datetime(askClose.index)
priceSma_df = pd.DataFrame({
'BidClose' : close,
'AskClose' : askClose
})
print(priceSma_df['2020-07-20':'2020-07-20']['AskClose']) #<-- this line failed, shows Empty DataFrame
This is the data file output.csv
Date,BidOpen,BidHigh,BidLow,BidClose,AskOpen,AskHigh,AskLow,AskClose,Vol
07.20.2020 00:17:00,1808.85,1809.01,1808.85,1808.94,1809.2,1809.36,1809.18,1809.25,55
07.20.2020 00:16:00,1808.88,1808.95,1808.82,1808.85,1809.18,1809.31,1809.16,1809.2,77
07.20.2020 00:15:00,1808.83,1808.89,1808.53,1808.88,1809.18,1809.22,1808.87,1809.18,109
07.20.2020 00:14:00,1808.79,1808.88,1808.77,1808.83,1809.13,1809.23,1809.1,1809.18,78
07.20.2020 00:13:00,1808.8,1808.89,1808.78,1808.79,1809.16,1809.28,1809.1,1809.13,91
07.20.2020 00:12:00,1808.79,1808.8,1808.67,1808.8,1809.14,1809.16,1809.01,1809.16,87
07.20.2020 00:11:00,1808.77,1808.92,1808.77,1808.79,1809.15,1809.27,1809.12,1809.14,68
07.20.2020 00:10:00,1808.89,1808.93,1808.55,1808.77,1809.23,1809.3,1808.88,1809.15,167
07.20.2020 00:09:00,1808.55,1808.93,1808.39,1808.89,1809.11,1809.29,1809.02,1809.23,123
07.20.2020 00:08:00,1808.37,1808.57,1808.37,1808.55,1808.96,1809.19,1808.96,1809.11,66
07.20.2020 00:07:00,1808.1,1808.49,1808.0,1808.37,1808.71,1809.15,1808.62,1808.96,147
07.20.2020 00:06:00,1808.84,1808.95,1808.02,1808.1,1809.51,1809.57,1808.54,1808.71,145
07.20.2020 00:05:00,1808.99,1809.0,1808.79,1808.84,1809.64,1809.64,1809.42,1809.51,69
07.20.2020 00:04:00,1808.86,1809.06,1808.79,1808.99,1809.4,1809.65,1809.4,1809.64,82
07.20.2020 00:03:00,1809.17,1809.17,1808.49,1808.86,1809.69,1809.72,1809.0,1809.4,154
07.20.2020 00:02:00,1809.04,1809.23,1809.04,1809.17,1809.52,1809.7,1809.5,1809.69,36
07.20.2020 00:01:00,1808.97,1809.21,1808.94,1809.04,1809.44,1809.64,1809.44,1809.52,77
07.20.2020 00:00:00,1808.9,1809.22,1808.81,1808.97,1809.37,1809.64,1809.29,1809.44,74
07.19.2020 23:59:00,1808.94,1808.95,1808.9,1808.9,1809.4,1809.43,1809.36,1809.37,18
07.19.2020 23:58:00,1808.98,1808.98,1808.76,1808.94,1809.41,1809.41,1809.28,1809.4,36
07.19.2020 23:57:00,1808.93,1809.02,1808.93,1808.98,1809.37,1809.41,1809.37,1809.41,6
07.19.2020 23:56:00,1809.1,1809.1,1808.77,1808.93,1809.47,1809.47,1809.26,1809.37,70
07.19.2020 23:55:00,1809.2,1809.22,1809.09,1809.1,1809.6,1809.6,1809.47,1809.47,35
07.19.2020 23:54:00,1809.13,1809.22,1809.08,1809.2,1809.52,1809.6,1809.5,1809.6,47
07.19.2020 23:53:00,1809.18,1809.21,1809.12,1809.13,1809.59,1809.61,1809.51,1809.52,21
07.19.2020 23:52:00,1809.1,1809.23,1809.09,1809.18,1809.53,1809.62,1809.52,1809.59,28
07.19.2020 23:51:00,1808.94,1809.1,1808.94,1809.1,1809.52,1809.62,1809.5,1809.53,43
07.19.2020 23:50:00,1809.0,1809.0,1808.92,1808.94,1809.55,1809.58,1809.49,1809.52,36
07.19.2020 23:49:00,1809.04,1809.1,1809.0,1809.0,1809.56,1809.59,1809.54,1809.55,40
07.19.2020 23:48:00,1809.01,1809.06,1808.99,1809.04,1809.57,1809.58,1809.56,1809.56,11
07.19.2020 23:47:00,1809.09,1809.16,1809.01,1809.01,1809.67,1809.68,1809.56,1809.57,29
07.19.2020 23:46:00,1809.29,1809.38,1809.09,1809.09,1809.82,1809.88,1809.66,1809.67,33
07.19.2020 23:45:00,1809.33,1809.33,1809.16,1809.29,1809.87,1809.89,1809.74,1809.82,31
07.19.2020 23:44:00,1809.32,1809.37,1809.27,1809.33,1809.85,1809.87,1809.83,1809.87,14
07.19.2020 23:43:00,1809.19,1809.37,1809.19,1809.32,1809.74,1809.89,1809.74,1809.85,46
07.19.2020 23:42:00,1809.26,1809.27,1809.08,1809.19,1809.74,1809.78,1809.65,1809.74,55
07.19.2020 23:41:00,1809.12,1809.26,1809.11,1809.26,1809.65,1809.74,1809.6,1809.74,27
07.19.2020 23:40:00,1809.18,1809.18,1809.12,1809.12,1809.84,1809.86,1809.65,1809.65,15
07.19.2020 23:39:00,1809.37,1809.39,1809.18,1809.18,1809.98,1809.99,1809.84,1809.84,16
07.19.2020 23:38:00,1809.37,1809.46,1809.31,1809.37,1809.98,1809.99,1809.98,1809.98,11
07.19.2020 23:37:00,1809.26,1809.37,1809.26,1809.37,1809.79,1809.99,1809.78,1809.98,16
07.19.2020 23:36:00,1809.31,1809.45,1809.18,1809.26,1809.72,1809.9,1809.68,1809.79,40
07.19.2020 23:35:00,1809.13,1809.36,1809.13,1809.31,1809.59,1809.75,1809.57,1809.72,67
07.19.2020 23:34:00,1809.23,1809.35,1809.07,1809.13,1809.68,1809.84,1809.57,1809.59,129
07.19.2020 23:33:00,1809.55,1809.55,1809.12,1809.23,1810.03,1810.03,1809.63,1809.68,115
07.19.2020 23:32:00,1809.87,1809.89,1809.51,1809.55,1810.34,1810.39,1810.03,1810.03,106
07.19.2020 23:31:00,1810.25,1810.28,1809.81,1809.87,1810.73,1810.75,1810.34,1810.34,81
07.19.2020 23:30:00,1810.11,1810.27,1809.98,1810.25,1810.58,1810.75,1810.52,1810.73,87
07.19.2020 23:29:00,1809.78,1810.15,1809.78,1810.11,1810.3,1810.65,1810.28,1810.58,103
07.19.2020 23:28:00,1809.79,1809.79,1809.74,1809.78,1810.33,1810.34,1810.28,1810.3,11
07.19.2020 23:27:00,1809.68,1809.81,1809.68,1809.79,1810.3,1810.34,1810.3,1810.33,41
07.19.2020 23:26:00,1809.8,1809.84,1809.68,1809.68,1810.38,1810.38,1810.29,1810.3,47
07.19.2020 23:25:00,1809.88,1809.92,1809.7,1809.8,1810.41,1810.45,1810.34,1810.38,91
07.19.2020 23:24:00,1809.83,1809.95,1809.83,1809.88,1810.36,1810.44,1810.36,1810.41,35
07.19.2020 23:23:00,1809.92,1809.98,1809.72,1809.83,1810.44,1810.5,1810.32,1810.36,69
07.19.2020 23:22:00,1809.78,1809.92,1809.72,1809.92,1810.31,1810.47,1810.31,1810.44,31
07.19.2020 23:21:00,1809.89,1809.89,1809.67,1809.78,1810.46,1810.46,1810.24,1810.31,93
07.19.2020 23:20:00,1809.95,1810.03,1809.84,1809.89,1810.54,1810.56,1810.43,1810.46,66
07.19.2020 23:19:00,1810.1,1810.11,1809.92,1809.95,1810.62,1810.67,1810.54,1810.54,49
07.19.2020 23:18:00,1810.21,1810.24,1809.98,1810.1,1810.78,1810.78,1810.58,1810.62,87
07.19.2020 23:17:00,1810.38,1810.38,1810.1,1810.21,1810.84,1810.84,1810.62,1810.78,71
07.19.2020 23:16:00,1810.5,1810.54,1810.31,1810.38,1811.0,1811.01,1810.8,1810.84,98
07.19.2020 23:15:00,1810.36,1810.67,1810.36,1810.5,1810.73,1811.1,1810.73,1811.0,110
07.19.2020 23:14:00,1810.45,1810.49,1810.34,1810.36,1810.95,1810.96,1810.72,1810.73,63
07.19.2020 23:13:00,1810.52,1810.6,1810.34,1810.45,1810.95,1811.04,1810.92,1810.95,116
Here is an working example how to read data from csv
import pandas as pd
import numpy as np
datafile = 'tmp.csv'
data = pd.read_csv(datafile, index_col = 'Date', parse_dates=True)
data.sort_index(inplace=True) # Sort to ensure that date values are in order
# Slices won't work without sorted index
# Demonstrate slicing and column accessing
print(data['2020-07-20':'2020-07-20'])
print(data['2020-07-20':'2020-07-20']["BidOpen"])
print(data['2020-07-20':'2020-07-20']["AskClose"])
print(data['2020-07-20':'2020-07-20'][["BidOpen", "AskClose"]])
You can remove unwanted columns with DataFrame.drop() or select wanted columns to a new DataFrame with dataframe[["col1", "col2"]].copy()
Answered by SirMizou on November 4, 2021
You are not using the column Date. This should work:
expected_df= (priceSma_df['Date'] >= '2020-07-20') & (priceSma_df['Date'] <= '2020-07-20')
Answered by Rorepio on November 4, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP