CODEWITHSUNDEEP
pythonpandas
Chet Meinzer
Chet Meinzer

Reputation: 1741

python pandas: how to loop over dateframe and add columns

I need a loop to do what this code is doing and automatically generate columns ep1 ep2 and so on..

df['ep1'] = df.ep1.apply(lambda x: datetime.datetime(x.year,x.month,1))
df['ep2'] = df.ep1.apply(lambda x: datetime.datetime((x+datetime.timedelta(days=40)).year,(x+datetime.timedelta(days=40)).month,1))
df['ep3'] = df.ep2.apply(lambda x: datetime.datetime((x+datetime.timedelta(days=40)).year,(x+datetime.timedelta(days=40)).month,1))

where the ep vector is the first day of months between df.opdate and df.closdate.

as a start

import pandas as pd
import datetime
d = {'closdate' : pd.Series([datetime.datetime(2014, 3, 2), datetime.datetime(2014, 2, 2)]),'opdate' : pd.Series([datetime.datetime(2014, 1, 1), datetime.datetime(2014, 1, 1)])}

df=pd.DataFrame(d)

df['ep1'] = df.opdate.apply(lambda x: x if x > datetime.datetime(2014,1,1) else datetime.datetime(2014,1,1))
df['ep1'] = df.ep1.apply(lambda x: datetime.datetime(x.year,x.month,1))
df['ep2'] = df.ep1.apply(lambda x: datetime.datetime((x+datetime.timedelta(days=40)).year,(x+datetime.timedelta(days=40)).month,1))
df['ep3'] = df.ep2.apply(lambda x: datetime.datetime((x+datetime.timedelta(days=40)).year,(x+datetime.timedelta(days=40)).month,1))

How do i loop until ep is larger than the df.closdate?

Upvotes: 0

Views: 1687

Answers (1)

Phillip Cloud
Phillip Cloud

Reputation: 25662

Use where instead of apply and add days with np.timedelta64

import numpy as np
from pandas import Timestamp

months = range(1, 13)
df['ep0'] = df.opdate.where(df.opdate > Timestamp('20140101'), Timestamp('20140101'))
for month in months:
    colname = 'ep%d' % month
    prev_colname = 'ep%d' % (month - 1)
    df[colname] = df[prev_colname] + np.timedelta64(40, 'D')

Upvotes: 4

Related Questions