team
Reputation: 526
How to add timedelta for time in csv file using
Here I have a dataset with date, time and one input. Then I need to first read some special time which is having same value. Then I want to add to that one hour one hour as a timedelta till to range of 6hours.
So here only 5 change. only change values with 5, I just need to separate the time which is related to the 5 values and then add timedelta (hours=1*6) into that each time This is not write as a another column of data csv file.
data['date_time']= pd.to_datetime(data['date'] + " " + data['time'],
format='%d/%m/%Y %H:%M:%S', dayfirst=True)
t = data.loc[data['date_time'] == 5]
for it in range(6):
time=[]
time= t+timedelta(hours=1*it)
But it gave me an error.
Here what I expected output:
first separate the specific time of values 5
date time x3 first expected time
10/3/2018 6:15:00 7
10/3/2018 6:45:00 5 first seperate the time related to 6:45:00 =1st time of 5
10/3/2018 7:45:00 7 the 5
10/3/2018 9:00:00 7
10/3/2018 9:25:00 7
10/3/2018 9:30:00 5 9:30:00 = 2nd time of 5
10/3/2018 11:00:00 7
10/3/2018 11:30:00 7
10/3/2018 13:30:00 7
10/3/2018 13:50:00 5 13:50:00 = 3rd time 0f 5
10/3/2018 15:00:00 7
10/3/2018 15:25:00 7After separating add timedelta each of that time separately
final expected output:
time final output
6:45:00 6:45:00 +timedelta(hours=1*6)
09:30:00 9:30:00 +timedelta(hours=1*6)
13:50:00 13:50:00 +timedelta(hours=1*6)Then for 7 value:
date time x3 first seperate time of 7
10/3/2018 6:15:00 7 6:15:00
10/3/2018 6:45:00 5
10/3/2018 7:45:00 7 7:45:00
10/3/2018 9:00:00 7 9:00:00
10/3/2018 9:25:00 7 9:25:00
10/3/2018 9:30:00 5
10/3/2018 11:00:00 7 11:00:00
10/3/2018 11:30:00 7 11:30:00
10/3/2018 13:30:00 7 13:30:00Then add timedelta into that time separately:
6:15:00 6:15:00 +timedelta(hours=1*2)
7:45:00 7:45:00 +timedelta(hours=1*2)
9:00:00 9:00:00 +timedelta(hours=1*2)This timedelta I want to write in for loop
Subset of my csv file:
date time x3
10/3/2018 6:15:00 7
10/3/2018 6:45:00 5
10/3/2018 7:45:00 7
10/3/2018 9:00:00 7
10/3/2018 9:25:00 7
10/3/2018 9:30:00 5
10/3/2018 11:00:00 7
10/3/2018 11:30:00 7
10/3/2018 13:30:00 7
10/3/2018 13:50:00 5
10/3/2018 15:00:00 7
10/3/2018 15:25:00 7
10/3/2018 16:25:00 7
10/3/2018 18:00:00 5
10/3/2018 19:00:00 7
10/3/2018 19:30:00 7
10/3/2018 20:00:00 7
10/3/2018 22:05:00 7My csv file : Csv file
For the reference to jezrael:
Here I want get the new value of 5 with the help of one summation equation in the range of timedelta (6 hours) from starting time for each value of 5.
Assume My new input take as X
Then
I will take start time first as t of value of 5
Then
x=[]
x3 = data['X3']
for _ in range (x3):
if x3.all()==5:
for i in range(t+timedelta(hours=1*it)for it in range(1,6)):
X1 = 5 - 0.006 *np.sum(i*5)
x.append(X1)
So each one houe one hour x values are there till to range 6.
For this I required the time and adding timedelta into it inside the for loopFor the refernce:
here just want to use my csv file only read the start time and the value only. Then starting from time add one hour one hour into it to get the new value for x.
take the first 5 value and the time.
my start time =t
X value
start time x
0 5
1 hr 4.97
2 hr 4.94
3 hr 4.91
4 hr 4.88
5 hr 4.85
6 hr 4.82
If in between two value of 5 time range < range of 6 hr
Then add
5 value into 5 hr value then new value
start time x
0 hr 5+4.85
1hr 9.82
If in between two value of 5 time range > range of 6 hr
start time x
0 5
1 hr 4.97
2 hr 4.94
3 hr 4.91
4 hr 4.88
5 hr 4.85
6 hr 4.82
Normally run this code.
So this should be continue.Upvotes: 0
Views: 202
Answers (1)
jezrael
Reputation: 862681
Use:
data['date_time']= pd.to_datetime(data['date'] + " " + data['time'],
format='%d/%m/%Y %H:%M:%S', dayfirst=True)
mask = data['x3'].eq(5)
data['duration'] = data['date_time'].mask(mask, data['date_time'].dt.floor('d'))
m = mask.cumsum()
mask1 = (m != 0) & ~mask
td = pd.to_timedelta(np.arange(mask1.sum()) % 5 + 1, unit='h')
data['hours'] = (pd.Series(td, index=data.index[mask1])
.reindex(data.index, fill_value=pd.Timedelta(0)))
data['new'] = data['hours'].dt.total_seconds() / 3600
data['new1'] = 5 - 0.006 *data['new']
print (data)
print (data)
date time x3 date_time duration hours \
0 10/3/2018 6:15:00 7 2018-03-10 06:15:00 2018-03-10 06:15:00 00:00:00
1 10/3/2018 6:45:00 5 2018-03-10 06:45:00 2018-03-10 00:00:00 00:00:00
2 10/3/2018 7:45:00 7 2018-03-10 07:45:00 2018-03-10 07:45:00 01:00:00
3 10/3/2018 9:00:00 7 2018-03-10 09:00:00 2018-03-10 09:00:00 02:00:00
4 10/3/2018 9:25:00 7 2018-03-10 09:25:00 2018-03-10 09:25:00 03:00:00
5 10/3/2018 9:30:00 5 2018-03-10 09:30:00 2018-03-10 00:00:00 00:00:00
6 10/3/2018 11:00:00 7 2018-03-10 11:00:00 2018-03-10 11:00:00 04:00:00
7 10/3/2018 11:30:00 7 2018-03-10 11:30:00 2018-03-10 11:30:00 05:00:00
8 10/3/2018 13:30:00 7 2018-03-10 13:30:00 2018-03-10 13:30:00 01:00:00
9 10/3/2018 13:50:00 5 2018-03-10 13:50:00 2018-03-10 00:00:00 00:00:00
10 10/3/2018 15:00:00 7 2018-03-10 15:00:00 2018-03-10 15:00:00 02:00:00
11 10/3/2018 15:25:00 7 2018-03-10 15:25:00 2018-03-10 15:25:00 03:00:00
12 10/3/2018 16:25:00 7 2018-03-10 16:25:00 2018-03-10 16:25:00 04:00:00
13 10/3/2018 18:00:00 5 2018-03-10 18:00:00 2018-03-10 00:00:00 00:00:00
14 10/3/2018 19:00:00 7 2018-03-10 19:00:00 2018-03-10 19:00:00 05:00:00
15 10/3/2018 19:30:00 7 2018-03-10 19:30:00 2018-03-10 19:30:00 01:00:00
16 10/3/2018 20:00:00 7 2018-03-10 20:00:00 2018-03-10 20:00:00 02:00:00
17 10/3/2018 22:05:00 7 2018-03-10 22:05:00 2018-03-10 22:05:00 03:00:00
new new1
0 0.0 5.000
1 0.0 5.000
2 1.0 4.994
3 2.0 4.988
4 3.0 4.982
5 0.0 5.000
6 4.0 4.976
7 5.0 4.970
8 1.0 4.994
9 0.0 5.000
10 2.0 4.988
11 3.0 4.982
12 4.0 4.976
13 0.0 5.000
14 5.0 4.970
15 1.0 4.994
16 2.0 4.988
17 3.0 4.982
Upvotes: 1
Related Questions
- Python Pandas Working with Timedelta then saving it to csv
- How to add timedelta into datetime while time is in "00:00:00' using python
- How to add extra time into the time column with data using python
- How to add timedelta for each time seperately contain in one column using python
- Calculated timedelta column in pandas
- How append date info to a time column on read_csv
- python how to change the time format from csv file
- Add Time column by adding timedelata in pandas
- Python: Adding hours to pandas timestamp
- timedelta csv pandas