Pandas question - calculated dataframe column

user_19315674 0
Viewed 22

I'm have half hourly data held within a pandas dataframe as follows:

             DateTime     Open     High      Low    Close  Volume
0 2005-09-06 17:00:00  1103.00  1103.50  1103.00  1103.25     744

I want to add a column to this data called "Daily_Open", which basically equals to the open price, at that given day, at 14:30. Lets say that for each day in question, there are 10 half hour rows referenced, before moving to the data relating to the next day and so on. This desired column would merely show the open price at 14:30 of that particular day, repeated for all relevant rows. In TSQL, I would either do this using a correlated subquery or a join on the date part of the DateTime column. I have tried the following code:

data = pd.read_csv("ESHalf.txt", )
data.rename(columns={"Close/Last": "Close"}, inplace=True)
data.columns = ["DateTime", "Open", "High", "Low", "Close", "Volume"]

data["DateTime"] = pd.to_datetime(data["DateTime"])
data["Date"] = data["DateTime"].dt.date

open_cond = (data["DateTime"].dt.hour == 9) & (data["DateTime"].dt.minute == 30)

data["Daily_Low"] = data["Open"][open_cond]

which successfully extracts the item in question but when applied to the original dataframe, NaN are created for all rows where the underlying time part of the datetime object is not 14:30 etc. I have a feeling that I use apply or transform in some way -any ideas? Many thanks,

pandas apply transform dataframe python
1 Answers

You can mask the non '14:30' values and transform with the first valid value per group:

# ensure datetime
df['DateTime'] = pd.to_datetime(df['DateTime'])

# locate target time
from datetime import time
mask = df['DateTime'].dt.time.eq(time(14, 30))

df['Daily_Open'] = (df['Open'].where(mask).groupby(df['DateTime'].dt.date)
                              .transform('first')
                   )

example (with more dummy rows):

             DateTime    Open    High     Low    Close  Volume  Daily_Open
0 2005-09-06 14:30:00  1000.0  2000.0   500.0  1200.00     700      1000.0
1 2005-09-06 17:00:00  1103.0  1103.5  1103.0  1103.25     744      1000.0
2 2005-09-07 14:30:00  1200.0  2000.0   500.0  1200.00     700      1200.0
3 2005-09-07 17:00:00  1103.0  1103.5  1103.0  1103.25     744      1200.0
user_163434640
Related