pottolom pottolom - 10 days ago 7
Python Question

Python/pandas conditional statement based on date_time index values

I have a Pandas dataframe with a time series index based on a date range applied using date_time, as follows:

Column A
2016-11-24 00:00:00 4.0
2016-11-24 01:00:00 7.8
2016-11-24 02:00:00 95.1
2016-11-24 03:00:00 78.4
etc


What I'd like to do is create a new column that applies a factor to Column A based on the month in the index.

Correction factors: September = 1, October = 2, November = 3, December = 4, etc.

So, in the example above case, because the month is November, the result would be:

Column A Column B
2016-11-24 00:00:00 4.0 12
2016-11-24 01:00:00 7.8 23.4
2016-11-24 02:00:00 95.1 285.3
2016-11-24 03:00:00 78.4 235.2
etc


I have tried a number of solutions some trouble with this. Does anyone have any tips?

Answer

I think you can substract 8 from month and then multiple, but in months before September get numbers less as 1:

print (df.index.month - 8)
[3 3 3 3]

df['Column B'] = df['Column A'] * (df.index.month - 8)
print (df)
                     Column A  Column B
2016-11-24 00:00:00       4.0      12.0
2016-11-24 01:00:00       7.8      23.4
2016-11-24 02:00:00      95.1     285.3
2016-11-24 03:00:00      78.4     235.2

You can also use map by dict and then multiple by mul. In this solution is necessary create Series from df.index.month which return numpy array with index of df (if not index, get all values NaN because indexes not match).

d = {9:1,10:2,11:3,12:4}

df['Column B'] = pd.Series(df.index.month, index=df.index).map(d).mul(df['Column A'])
print (df)
                     Column A  Column B
2016-11-24 00:00:00       4.0      12.0
2016-11-24 01:00:00       7.8      23.4
2016-11-24 02:00:00      95.1     285.3
2016-11-24 03:00:00      78.4     235.2