Payne Payne - 1 month ago 7
Python Question

Iterate Date range using python pandas

Good day, please all I want to expand the date range implied by

StartDate
and
EndDate
.

import pandas as pd
import datetime
from pandas_datareader import data, wb
import csv

out= open("testfile.csv", "rb")
data = csv.reader(out)
data = [[row[0],row[1] + row[2],row[3] + row[4], row[5],row[6]] for row in data]
out.close()
print data

out=open("data.csv", "wb")
output = csv.writer(out)

for row in data:
output.writerow(row)

out.close()

df = pd.read_csv('data.csv')
for DateDpt, DateAr in df.iteritems():
df.DateDpt = pd.to_datetime(df.DateDpt, format='%Y-%m-%d')
df.DateAr = pd.to_datetime(df.DateAr, format='%Y-%m-%d')

df['DateAr'] = [pd.to_datetime(x, format='%Y-%m-%d') for x in df['DateAr']]
df['DateDpt'] = [pd.to_datetime(x, format='%Y-%m-%d') for x in df['DateDpt']]

df['range'] = df['DateDpt']-df['DateAr']

print df

ID ArCityArCountry DptCityDptCountry EndDate StartDate
1922 ParisFrance NewYorkUnitedState 2008-03-10 2008-12-01
1901 ParisFrance LagosNigeria 2001-03-05 2001-02-02
1922 ParisFrance NewYorkUnitedState 2011-02-03 2008-12-01
1002 ParisFrance CaliforniaUnitedState 2003-03-04 2002-03-04
1099 ParisFrance BeijingChina 2011-02-03 2009-02-04
1901 LosAngelesUnitedState ParisFrance 2001-03-05 2001-02-02


Output:

ID ArCityArCountry DptCityDptCountry EndDate
1922 ParisFrance NewYorkUnitedState 2008-03-10
1002 LosAngelesUnitedState ForidaUnitedState 2008-03-10
1901 ParisFrance LagosNigeria 2001-03-05
1922 ParisFrance NewYorkUnitedState 2011-02-03
1002 ParisFrance CaliforniaUnitedState 2003-03-04
1099 ParisFrance BeijingChina 2011-02-03
1901 LosAngelesUnitedState ParisFrance 2001-03-05

StartDate range
2001-02-02 2593 days
2008-12-01 266 days
2001-02-02 31 days
2008-12-01 794 days
2002-03-04 365 days
2009-02-04 729 days
2001-02-02 31 days


Expected out:

Let's consider
row1
, we have 2593 days, I want a situation that from
StartDate
i.e.
2001-02-02
to the
EndDate
i.e
2008-03-10
, be listed
this should run through all the rows by expanding based on range until the value on
StartDate
matches
EndDate
.

ID ArCityArCountry DptCityDptCountry StartDate EndDate
1922 ParisFrance NewYorkUnitedState 2004-03-10 2008-12-01
1922 ParisFrance NewYorkUnitedState 2004-03-11 2008-12-01
1922 ParisFrance NewYorkUnitedState 2004-03-12 2008-12-01


until it gets to that date of EndDate which means on both date I should have something like StartDate = EndDate, ie 2008-12-01 on both sides. Considering the csv

1922 ParisFrance NewYorkUnitedState 2008-12-01 2008-12-01


Thank you so much

Another Question on it:
Thanks.. I have another question. I want to create a JSON, considering StartDate (However, wherever there are two dates matching each other, one of the dates would be used while all attributes will be appended. Let me make an example,
{ "2001-02-02" = { ParisFrance (ArCityArCountry): 1922 NewYorkUnitedStates: 1922} }

if we traverse the down csv we might likely to have another 2001-02-02. Instead of creating it, we can append it to initial StartDate. However, the DptCityDptCountry might be different but if another ID matches with the StartDate and DptCityDptCountry, it will be added up i.e.

{"2001-02-02" = {
ParisFrance (ArCityArCountry): 1922, 2212 //these are IDs with same StartDate and ArCityArCountry
NewYorkUnitedStates: 1922, 0029 //these are IDs with same StartDate and DptCityDptCountry}
}

Answer

Starting with:

     ID        ArCityArCountry      DptCityDptCountry  EndDate StartDate
0  1922            ParisFrance     NewYorkUnitedState  3/10/08    2/2/01
1  1002  LosAngelesUnitedState      ForidaUnitedState  3/10/08   12/1/08
2  1901            ParisFrance           LagosNigeria   3/5/01    2/2/01
3  1922            ParisFrance     NewYorkUnitedState   2/3/11   12/1/08
4  1002            ParisFrance  CaliforniaUnitedState   3/4/03    3/4/02
5  1099            ParisFrance           BeijingChina   2/3/11    2/4/09
6  1901  LosAngelesUnitedState            ParisFrance   3/5/01    2/2/01

You can get your desired output as follows:

df.EndDate = pd.to_datetime(df.EndDate)
df.StartDate = pd.to_datetime(df.StartDate)
df = df.set_index('StartDate')
new_df = pd.DataFrame()
for i, data in df.iterrows():
    data = data.to_frame().transpose()
    data = data.reindex(pd.date_range(start=data.index[0], end=data.EndDate[0])).fillna(method='ffill').reset_index().rename(columns={'index': 'StartDate'})
    new_df = pd.concat([new_df, data])

new_df = new_df[['ID', 'ArCityArCountry', 'DptCityDptCountry', 'StartDate', 'EndDate']]

      ID        ArCityArCountry   DptCityDptCountry  StartDate    EndDate
0   1922            ParisFrance  NewYorkUnitedState 2001-02-02 2008-03-10
1   1922            ParisFrance  NewYorkUnitedState 2001-02-03 2008-03-10
2   1922            ParisFrance  NewYorkUnitedState 2001-02-04 2008-03-10
3   1922            ParisFrance  NewYorkUnitedState 2001-02-05 2008-03-10
4   1922            ParisFrance  NewYorkUnitedState 2001-02-06 2008-03-10
5   1922            ParisFrance  NewYorkUnitedState 2001-02-07 2008-03-10
6   1922            ParisFrance  NewYorkUnitedState 2001-02-08 2008-03-10
7   1922            ParisFrance  NewYorkUnitedState 2001-02-09 2008-03-10
8   1922            ParisFrance  NewYorkUnitedState 2001-02-10 2008-03-10
9   1922            ParisFrance  NewYorkUnitedState 2001-02-11 2008-03-10
10  1922            ParisFrance  NewYorkUnitedState 2001-02-12 2008-03-10
11  1922            ParisFrance  NewYorkUnitedState 2001-02-13 2008-03-10
12  1922            ParisFrance  NewYorkUnitedState 2001-02-14 2008-03-10
13  1922            ParisFrance  NewYorkUnitedState 2001-02-15 2008-03-10
14  1922            ParisFrance  NewYorkUnitedState 2001-02-16 2008-03-10
15  1922            ParisFrance  NewYorkUnitedState 2001-02-17 2008-03-10
16  1922            ParisFrance  NewYorkUnitedState 2001-02-18 2008-03-10
17  1922            ParisFrance  NewYorkUnitedState 2001-02-19 2008-03-10
18  1922            ParisFrance  NewYorkUnitedState 2001-02-20 2008-03-10
19  1922            ParisFrance  NewYorkUnitedState 2001-02-21 2008-03-10
20  1922            ParisFrance  NewYorkUnitedState 2001-02-22 2008-03-10
21  1922            ParisFrance  NewYorkUnitedState 2001-02-23 2008-03-10
22  1922            ParisFrance  NewYorkUnitedState 2001-02-24 2008-03-10
23  1922            ParisFrance  NewYorkUnitedState 2001-02-25 2008-03-10
24  1922            ParisFrance  NewYorkUnitedState 2001-02-26 2008-03-10
25  1922            ParisFrance  NewYorkUnitedState 2001-02-27 2008-03-10
26  1922            ParisFrance  NewYorkUnitedState 2001-02-28 2008-03-10
27  1922            ParisFrance  NewYorkUnitedState 2001-03-01 2008-03-10
28  1922            ParisFrance  NewYorkUnitedState 2001-03-02 2008-03-10
29  1922            ParisFrance  NewYorkUnitedState 2001-03-03 2008-03-10
..   ...                    ...                 ...        ...        ...
2   1901  LosAngelesUnitedState         ParisFrance 2001-02-04 2001-03-05
3   1901  LosAngelesUnitedState         ParisFrance 2001-02-05 2001-03-05
4   1901  LosAngelesUnitedState         ParisFrance 2001-02-06 2001-03-05
5   1901  LosAngelesUnitedState         ParisFrance 2001-02-07 2001-03-05
6   1901  LosAngelesUnitedState         ParisFrance 2001-02-08 2001-03-05
7   1901  LosAngelesUnitedState         ParisFrance 2001-02-09 2001-03-05
8   1901  LosAngelesUnitedState         ParisFrance 2001-02-10 2001-03-05
9   1901  LosAngelesUnitedState         ParisFrance 2001-02-11 2001-03-05
10  1901  LosAngelesUnitedState         ParisFrance 2001-02-12 2001-03-05
11  1901  LosAngelesUnitedState         ParisFrance 2001-02-13 2001-03-05
12  1901  LosAngelesUnitedState         ParisFrance 2001-02-14 2001-03-05
13  1901  LosAngelesUnitedState         ParisFrance 2001-02-15 2001-03-05
14  1901  LosAngelesUnitedState         ParisFrance 2001-02-16 2001-03-05
15  1901  LosAngelesUnitedState         ParisFrance 2001-02-17 2001-03-05
16  1901  LosAngelesUnitedState         ParisFrance 2001-02-18 2001-03-05
17  1901  LosAngelesUnitedState         ParisFrance 2001-02-19 2001-03-05
18  1901  LosAngelesUnitedState         ParisFrance 2001-02-20 2001-03-05
19  1901  LosAngelesUnitedState         ParisFrance 2001-02-21 2001-03-05
20  1901  LosAngelesUnitedState         ParisFrance 2001-02-22 2001-03-05
21  1901  LosAngelesUnitedState         ParisFrance 2001-02-23 2001-03-05
22  1901  LosAngelesUnitedState         ParisFrance 2001-02-24 2001-03-05
23  1901  LosAngelesUnitedState         ParisFrance 2001-02-25 2001-03-05
24  1901  LosAngelesUnitedState         ParisFrance 2001-02-26 2001-03-05
25  1901  LosAngelesUnitedState         ParisFrance 2001-02-27 2001-03-05
26  1901  LosAngelesUnitedState         ParisFrance 2001-02-28 2001-03-05
27  1901  LosAngelesUnitedState         ParisFrance 2001-03-01 2001-03-05
28  1901  LosAngelesUnitedState         ParisFrance 2001-03-02 2001-03-05
29  1901  LosAngelesUnitedState         ParisFrance 2001-03-03 2001-03-05
30  1901  LosAngelesUnitedState         ParisFrance 2001-03-04 2001-03-05
31  1901  LosAngelesUnitedState         ParisFrance 2001-03-05 2001-03-05
Comments