Pandas - How to split date range in dataframe as extra columns
1
Dataset
sample = 'operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019']
df_test = pd.DataFrame(sample)
df_test
I want to be able to split the valid_from and valid_to columns into their individual dates and added into the dataframe.
Output
df3 = pd.DataFrame('operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_1': ['14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'16/02/2019',
'16/02/2019',
'16/02/2019',
'16/02/2019'],
'valid_2': ['15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'17/02/2019',
'17/02/2019',
'17/02/2019',
'17/02/2019'],
'valid_3': ['16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'18/02/2019',
'18/02/2019',
'18/02/2019',
'18/02/2019'],
'valid_4': ['17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'19/02/2019',
'19/02/2019',
'19/02/2019',
'19/02/2019'],
'valid_5': ['18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'20/02/2019',
'20/02/2019',
'20/02/2019',
'20/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019'])
df2
python pandas dataframe
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
add a comment |
1
Dataset
sample = 'operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019']
df_test = pd.DataFrame(sample)
df_test
I want to be able to split the valid_from and valid_to columns into their individual dates and added into the dataframe.
Output
df3 = pd.DataFrame('operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_1': ['14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'16/02/2019',
'16/02/2019',
'16/02/2019',
'16/02/2019'],
'valid_2': ['15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'17/02/2019',
'17/02/2019',
'17/02/2019',
'17/02/2019'],
'valid_3': ['16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'18/02/2019',
'18/02/2019',
'18/02/2019',
'18/02/2019'],
'valid_4': ['17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'19/02/2019',
'19/02/2019',
'19/02/2019',
'19/02/2019'],
'valid_5': ['18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'20/02/2019',
'20/02/2019',
'20/02/2019',
'20/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019'])
df2
python pandas dataframe
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
1
what happened if the difference of days betweenvalid_fromandvalid_tois not the same for all the rows?
– Joe
Nov 15 '18 at 12:55
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04
add a comment |
1
1
1
Dataset
sample = 'operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019']
df_test = pd.DataFrame(sample)
df_test
I want to be able to split the valid_from and valid_to columns into their individual dates and added into the dataframe.
Output
df3 = pd.DataFrame('operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_1': ['14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'16/02/2019',
'16/02/2019',
'16/02/2019',
'16/02/2019'],
'valid_2': ['15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'17/02/2019',
'17/02/2019',
'17/02/2019',
'17/02/2019'],
'valid_3': ['16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'18/02/2019',
'18/02/2019',
'18/02/2019',
'18/02/2019'],
'valid_4': ['17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'19/02/2019',
'19/02/2019',
'19/02/2019',
'19/02/2019'],
'valid_5': ['18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'20/02/2019',
'20/02/2019',
'20/02/2019',
'20/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019'])
df2
python pandas dataframe
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
Dataset
sample = 'operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019']
df_test = pd.DataFrame(sample)
df_test
I want to be able to split the valid_from and valid_to columns into their individual dates and added into the dataframe.
Output
df3 = pd.DataFrame('operator': ['op_a',
'op_a',
'op_a',
'op_a',
'op_b',
'op_b',
'op_b',
'op_b',
'op_c',
'op_c',
'op_c',
'op_c'],
'from': ['a', 'a', 'a', 'a', 'c', 'c', 'c', 'c', 'a', 'a', 'a', 'a'],
'to': ['b', 'b', 'b', 'b', 'd', 'd', 'd', 'd', 'b', 'b', 'b', 'b'],
'valid_from': ['13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'13/11/2018',
'15/02/2019',
'15/02/2019',
'15/02/2019',
'15/02/2019'],
'valid_1': ['14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'14/11/2018',
'16/02/2019',
'16/02/2019',
'16/02/2019',
'16/02/2019'],
'valid_2': ['15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'15/11/2018',
'17/02/2019',
'17/02/2019',
'17/02/2019',
'17/02/2019'],
'valid_3': ['16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'16/11/2018',
'18/02/2019',
'18/02/2019',
'18/02/2019',
'18/02/2019'],
'valid_4': ['17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'17/11/2018',
'19/02/2019',
'19/02/2019',
'19/02/2019',
'19/02/2019'],
'valid_5': ['18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'18/11/2018',
'20/02/2019',
'20/02/2019',
'20/02/2019',
'20/02/2019'],
'valid_to': ['19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'19/11/2018',
'21/02/2019',
'21/02/2019',
'21/02/2019',
'21/02/2019'])
df2
python pandas dataframe
python pandas dataframe
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
edited Nov 15 '18 at 13:37
Mikhail Kholodkov
5,06152951
5,06152951
asked Nov 15 '18 at 12:46
AK91AK91
757
asked Nov 15 '18 at 12:46
AK91AK91
757
asked Nov 15 '18 at 12:46
AK91AK91
757
757
1
what happened if the difference of days betweenvalid_fromandvalid_tois not the same for all the rows?
– Joe
Nov 15 '18 at 12:55
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04
add a comment |
1
what happened if the difference of days betweenvalid_fromandvalid_tois not the same for all the rows?
– Joe
Nov 15 '18 at 12:55
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04
1
1
what happened if the difference of days between
valid_from and valid_to is not the same for all the rows?– Joe
Nov 15 '18 at 12:55
what happened if the difference of days between
valid_from and valid_to is not the same for all the rows?– Joe
Nov 15 '18 at 12:55
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04
add a comment |
1 Answer
1
active
oldest
votes
1
You can try with:
df_test['valid_from'] = pd.to_datetime(df_test['valid_from'])
df_test['valid_to'] = pd.to_datetime(df_test['valid_to'])
diff_days = int((df_test.loc[0,'valid_to'] - df_test.loc[0,'valid_from']).days)
for i in range(diff_days-1):
df_test['valid_'.format(i+1)]= pd.DatetimeIndex(df_test['valid_from']) + pd.DateOffset(i+1)
This solution assumes that all rows have the same difference of days since it is not specified otherwise.
Output:
from operator to valid_from valid_to valid_1 valid_2 valid_3
0 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
1 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
2 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
3 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
4 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
5 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
6 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
7 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
8 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
9 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
10 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
11 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
valid_4 valid_5
0 2018-11-17 2018-11-18
1 2018-11-17 2018-11-18
2 2018-11-17 2018-11-18
3 2018-11-17 2018-11-18
4 2018-11-17 2018-11-18
5 2018-11-17 2018-11-18
6 2018-11-17 2018-11-18
7 2018-11-17 2018-11-18
8 2019-02-19 2019-02-20
9 2019-02-19 2019-02-20
10 2019-02-19 2019-02-20
11 2019-02-19 2019-02-20
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319819%2fpandas-how-to-split-date-range-in-dataframe-as-extra-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can try with:
df_test['valid_from'] = pd.to_datetime(df_test['valid_from'])
df_test['valid_to'] = pd.to_datetime(df_test['valid_to'])
diff_days = int((df_test.loc[0,'valid_to'] - df_test.loc[0,'valid_from']).days)
for i in range(diff_days-1):
df_test['valid_'.format(i+1)]= pd.DatetimeIndex(df_test['valid_from']) + pd.DateOffset(i+1)
This solution assumes that all rows have the same difference of days since it is not specified otherwise.
Output:
from operator to valid_from valid_to valid_1 valid_2 valid_3
0 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
1 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
2 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
3 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
4 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
5 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
6 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
7 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
8 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
9 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
10 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
11 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
valid_4 valid_5
0 2018-11-17 2018-11-18
1 2018-11-17 2018-11-18
2 2018-11-17 2018-11-18
3 2018-11-17 2018-11-18
4 2018-11-17 2018-11-18
5 2018-11-17 2018-11-18
6 2018-11-17 2018-11-18
7 2018-11-17 2018-11-18
8 2019-02-19 2019-02-20
9 2019-02-19 2019-02-20
10 2019-02-19 2019-02-20
11 2019-02-19 2019-02-20
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
add a comment |
1
You can try with:
df_test['valid_from'] = pd.to_datetime(df_test['valid_from'])
df_test['valid_to'] = pd.to_datetime(df_test['valid_to'])
diff_days = int((df_test.loc[0,'valid_to'] - df_test.loc[0,'valid_from']).days)
for i in range(diff_days-1):
df_test['valid_'.format(i+1)]= pd.DatetimeIndex(df_test['valid_from']) + pd.DateOffset(i+1)
This solution assumes that all rows have the same difference of days since it is not specified otherwise.
Output:
from operator to valid_from valid_to valid_1 valid_2 valid_3
0 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
1 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
2 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
3 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
4 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
5 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
6 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
7 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
8 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
9 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
10 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
11 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
valid_4 valid_5
0 2018-11-17 2018-11-18
1 2018-11-17 2018-11-18
2 2018-11-17 2018-11-18
3 2018-11-17 2018-11-18
4 2018-11-17 2018-11-18
5 2018-11-17 2018-11-18
6 2018-11-17 2018-11-18
7 2018-11-17 2018-11-18
8 2019-02-19 2019-02-20
9 2019-02-19 2019-02-20
10 2019-02-19 2019-02-20
11 2019-02-19 2019-02-20
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
add a comment |
1
1
1
You can try with:
df_test['valid_from'] = pd.to_datetime(df_test['valid_from'])
df_test['valid_to'] = pd.to_datetime(df_test['valid_to'])
diff_days = int((df_test.loc[0,'valid_to'] - df_test.loc[0,'valid_from']).days)
for i in range(diff_days-1):
df_test['valid_'.format(i+1)]= pd.DatetimeIndex(df_test['valid_from']) + pd.DateOffset(i+1)
This solution assumes that all rows have the same difference of days since it is not specified otherwise.
Output:
from operator to valid_from valid_to valid_1 valid_2 valid_3
0 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
1 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
2 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
3 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
4 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
5 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
6 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
7 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
8 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
9 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
10 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
11 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
valid_4 valid_5
0 2018-11-17 2018-11-18
1 2018-11-17 2018-11-18
2 2018-11-17 2018-11-18
3 2018-11-17 2018-11-18
4 2018-11-17 2018-11-18
5 2018-11-17 2018-11-18
6 2018-11-17 2018-11-18
7 2018-11-17 2018-11-18
8 2019-02-19 2019-02-20
9 2019-02-19 2019-02-20
10 2019-02-19 2019-02-20
11 2019-02-19 2019-02-20
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
You can try with:
df_test['valid_from'] = pd.to_datetime(df_test['valid_from'])
df_test['valid_to'] = pd.to_datetime(df_test['valid_to'])
diff_days = int((df_test.loc[0,'valid_to'] - df_test.loc[0,'valid_from']).days)
for i in range(diff_days-1):
df_test['valid_'.format(i+1)]= pd.DatetimeIndex(df_test['valid_from']) + pd.DateOffset(i+1)
This solution assumes that all rows have the same difference of days since it is not specified otherwise.
Output:
from operator to valid_from valid_to valid_1 valid_2 valid_3
0 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
1 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
2 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
3 a op_a b 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
4 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
5 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
6 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
7 c op_b d 2018-11-13 19/11/2018 2018-11-14 2018-11-15 2018-11-16
8 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
9 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
10 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
11 a op_c b 2019-02-15 21/02/2019 2019-02-16 2019-02-17 2019-02-18
valid_4 valid_5
0 2018-11-17 2018-11-18
1 2018-11-17 2018-11-18
2 2018-11-17 2018-11-18
3 2018-11-17 2018-11-18
4 2018-11-17 2018-11-18
5 2018-11-17 2018-11-18
6 2018-11-17 2018-11-18
7 2018-11-17 2018-11-18
8 2019-02-19 2019-02-20
9 2019-02-19 2019-02-20
10 2019-02-19 2019-02-20
11 2019-02-19 2019-02-20
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
edited Nov 15 '18 at 13:16
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
answered Nov 15 '18 at 13:01
JoeJoe
6,10421530
6,10421530
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
add a comment |
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
you wouldn't happen to know how to extend your code if the day diff were to be dynamic? What I want to do in the long-run is stack those individual dates...let me know if this warrants a new question altogether
– AK91
Nov 20 '18 at 8:48
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
@AK91 could you please post a new question with the details showing also the expected output for these dynamic differences?
– Joe
Nov 20 '18 at 8:57
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
see stackoverflow.com/questions/53325057/… Also see my last comment to warped's answer which I hope helps in any way.
– AK91
Nov 20 '18 at 9:38
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53319819%2fpandas-how-to-split-date-range-in-dataframe-as-extra-columns%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
what happened if the difference of days between
valid_fromandvalid_tois not the same for all the rows?– Joe
Nov 15 '18 at 12:55
Should've mentioned - sometimes the date range isn't the same, can fluctuate though most of the time will be 6
– AK91
Nov 15 '18 at 13:38
And when the difference Is less, what Is the value of the extra columns?
– Joe
Nov 15 '18 at 14:05
tbh that is another problem and I wanted to tackle one thing at a time...essentially i would use the answer you gave (thanks btw) to then pivot those valid_day's, then do some other stuff....whole other can of worms (was tempted to post the whole problem in SO but wanted to give it a punt myself)
– AK91
Nov 15 '18 at 15:04