CODEWITHSUNDEEP
pythonpandasdictionary
YHPVY8
YHPVY8

Reputation: 23

Time as key in Python dictionary

I have a CSV export with the date/time listed in 15 minute intervals.

The time appears as this example in the export: 2019-09-04T02:15:00Z

After importing the file, I separated the time (HH:MM:SS) into a new column. I would like to add a new column that would assign a text string (either "ON" or "RTH") to each 15 minute interval from a dictionary.

Doing this in Excel would be straightforward, but am trying to learn how through Python. Each time I run this code, the new column is blank (no error message). My thought is there is a problem with the dictionary key using time. Could anyone please let me know what I am missing?

import pandas as pd

# file export from TradingView

df = pd.read_csv(file, sep=',')
df = df.rename(columns={'time': 'Date', 'open': 'Open', 'high': 'High', 'low': 'Low', 'close': 'Close'})


df["Date"] = pd.to_datetime(df["Date"])

df["Time"] = df["Date"].dt.time

# a shortened version of the dictionary for illustration

time_dictionary = {'02:15:00': 'ON', '02:30:00': 'ON', '11:00:00': 'RTH'}

# new column to assign the text strings

df['Session'] = df['Time'].map(time_dictionary)

Upvotes: 1

Views: 656

Answers (3)

user5401398
user5401398

Reputation: 114

You are using

df["Time"] = df["Date"].dt.time

which

returns numpy array of datetime.time (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.time.html)

Use 

df["Time"] = df["Date"].dt.strftime('%H:%M:%S')

instead which returns formatted strings (https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.dt.strftime.html).

Full code:

import pandas as pd

# file export from TradingView

df = pd.read_csv(file, sep=',')
df = df.rename(columns={'time': 'Date', 'open': 'Open', 'high': 'High', 'low': 'Low', 'close': 'Close'})


df["Date"] = pd.to_datetime(df["Date"])

df["Time"] = df["Date"].dt.strftime('%H:%M:%S')

# a shortened version of the dictionary for illustration

time_dictionary = {'02:15:00': 'ON', '02:30:00': 'ON', '11:00:00': 'RTH'}

# new column to assign the text strings

df['Session'] = df['Time'].map(time_dictionary)

Upvotes: 1

maurera
maurera

Reputation: 1659

You're trying to create a new column in your dataframe based on whether a particular trade occurred Overnight "ON" or during Regular Trading Hours "RTH". You've suggested using a dictionary to do this, but here's a solution that just checks whether the trade occurred between the start and end of trading day.

import pandas as pd
import datetime

# Create dataframe with example dates
df = pd.DataFrame(data=['2019-09-04T02:15:00Z','2019-09-04T10:30:00Z','2019-09-04T19:00:00Z'],columns=['Date'])

# Convert string date to datetime object
df["Date"] = pd.to_datetime(df["Date"])

# Extract just time time portion of the datetime object
df["Time"] = df["Date"].dt.time

# Create new column based on new time column
STARTOFDAY = datetime.time(9,0)
ENDOFDAY = datetime.time(16,0)
df['Session']=['RTH' if STARTOFDAY <= x <= ENDOFDAY else 'ON' for x in df.Time]

# View result
print(df)

This returns:

                       Date      Time Session
0 2019-09-04 02:15:00+00:00  02:15:00      ON
1 2019-09-04 10:30:00+00:00  10:30:00     RTH
2 2019-09-04 19:00:00+00:00  19:00:00      ON

I have assumed that the trading day starts at 9:00 and ends at 16:00. You can change the values of STARTOFDAY and ENDOFDAY if your trading session has different hours.

Upvotes: 0

Mayowa Ayodele
Mayowa Ayodele

Reputation: 559

I think this is because df['Time'] is not the same data type as the keys in time_dictionary. You may be comparing string with time.

You may need to change it to string first using something like this


df["Time"] = df["Date"].dt.time

df["Time"] = df["Time"].strftime('%r')

Upvotes: 0

Related Questions