Python/pandas conditional statement based on date_time index values

Question

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?

jezrael · Accepted Answer · 2016-11-28 12:52:11Z

4

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

edited Nov 28, 2016 at 12:52

answered Nov 28, 2016 at 12:38

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

pottolom Over a year ago

Thanks. I went with your second method. Worked perfectly!

jezrael Over a year ago

Super, I am glad can help you!

Collectives™ on Stack Overflow

Python/pandas conditional statement based on date_time index values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related