1

The indices to my DataFrame are datetimes, one row every 10 minutes. I want to set the value of a column based on the hour value of the index. Here is my code so far:

import pandas as pd

df = pd.read_csv('Data.csv', sep='\t');
df['DateTime'] = pd.to_datetime(df['DateTime'])
df.set_index('DateTime', inplace=True)

rng = df.index
day_index = rng[(rng.hour >= 8) & (rng.hour < 22)] 
night_index = rng[(rng.hour > 8 ) & (rng.hour <= 22)]

So far so good: the following give the expected parts of the DF:

df.loc[day_index].head()

                     LAeq
DateTime                 
2018-01-16 08:00:00  50.0
2018-01-16 08:10:00  49.4
2018-01-16 08:20:00  49.2
2018-01-16 08:30:00  48.9
2018-01-16 08:40:00  48.1

and 

df.loc[night_index].head()

             LAeq
DateTime                 
2018-01-15 23:10:00  42.8
2018-01-15 23:20:00  41.9
2018-01-15 23:30:00  43.5
2018-01-15 23:40:00  45.6
2018-01-15 23:50:00  45.7

Shouldn't I be able to set a value for a column for these day and night periods, something like:

df.loc[day_index, 'Limit'] = 55
df.loc[night_index, 'Limit'] = 45

Both give an AttributeError: 'bool' object has no attribute 'any' What is the simplest way of setting these? I'm making do with the terrible un-Pythonic (warning this code may hurt your eyes):

df['Hour'] = df.index.hour

for i in range(len(df.index)):
h = int(df.ix[i]['Hour'])
if (8 <= h < 22):
    df.ix[i, 'Limit'] = 55
else:
    df.ix[i,'Limit'] = 45

Which is ugly, but it works.

djnz0feh
  • 383
  • 4
  • 13

1 Answers1

0

You need remove filtering and return boolean mask:

day_mask = (rng.hour >= 8) & (rng.hour < 22)
night_mask = (rng.hour > 8 ) & (rng.hour <= 22)


df[day_mask].head()
df[night_mask ].head()

#same as
#df.loc[night_mask ].head()

df.loc[day_mask, 'Limit'] = 55
df.loc[night_mask , 'Limit'] = 45

Or better:

df['limit'] = np.where(day_mask, 55, 45)
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252
  • 1
    Thank you. That works. You guys & gals are starting to scare me with your response times. – djnz0feh Apr 23 '18 at 14:22
  • In SO is there a metric / ratio: time taken to formulate a question over the time taken to answer it?? – djnz0feh Apr 23 '18 at 14:23
  • @djnz0feh - in my opinion it is hard create good question, like say [andy](http://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – jezrael Apr 23 '18 at 14:25