TransWikia.com

Unable to splice DataFrame with date index if data was not sorted

Stack Overflow Asked by Clinton Lam on November 4, 2021

Issue

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)

Solution

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

Datafile

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
 

2 Answers

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

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP