How to add extra time into the time column with data using python

Question

Here I have a dataset with date, time and one input column. So here my time column is not good. So I want to give time range into that time column. So here first I did I just convert start time into 0 and convert whole time column into minutes.

Then next what I want to give time range like 0,60,120....

Mean what I expected output is:

first time convert

date	     time         time          
10/3/2018	6:15:00       0              
10/3/2018	6:45:00       30             
10/3/2018	7:45:00       90              
10/3/2018	9:00:00       165.0           
10/3/2018	9:25:00       190.0           
10/3/2018	9:30:00       195.0            
10/3/2018	11:00:00      285.0
10/3/2018	11:30:00      315.0

Expected output

time            x3
0               7
30              5
60              0
120             0
165             7
180             0
190             7
195             5
240             0
285             7
300             0
315             7

So here you can see extra time added 60,120,180... for that x3 values are not available Then add 0 into x3 column

So here I just want to do is extra 60 min 60 min add to the time column

Here I wrote the code for convert time. But I don't know how to fill the extra 60 min into the time column.

My code:

data['date']= pd.to_datetime(data['date'] + " " + data['time'],
                    format='%d/%m/%Y %H:%M:%S', dayfirst=True)

lastday = data.loc[0, 'date']
def convert_time(x):
global lastday
if x.date() == lastday.date():
    tm = x - lastday
    return tm.total_seconds()/60
else:
    lastday = x
    return 0

data['time'] = data['date'].apply(convert_time)

Can anyone help me to solve this problem?

Subset of my csv:

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	7
10/3/2018	19:00:00	5

My csv : enter link description here

for the reference:

                   expected time                            
                         0.0
                         30.0             
                         60
                         90.0 
                         120
                         165.0
                         180
                         190.0
                         195.0
                         240
                         285.0
                         300
                         315.0
                         360
                         420
                         435
                         455
                         480
                         525
                         540
                         550
                          :
                          :
                          :
                          :
                          0   new date ,start time=0
                          2
                          47
                          60
                          120
                          152
                          180

I check it now and time column is changed in output data? Because input data has 8 rows and output 6 rows. — jezrael
– jezrael, Commented Sep 18, 2019 at 7:34
@jezrael I edited my question. Now you can see my output is more than inputs. Because I added extra time into it. Then X3 column value will be 0 related to that extra time. — team
– team, Commented Sep 18, 2019 at 7:40
@jezrael I just want to add extra 60 min into my time column — team
– team, Commented Sep 18, 2019 at 7:42

jezrael · Accepted Answer · 2019-09-18 07:56:09Z

1

Use:

data['date']= pd.to_datetime(data['date'] + " " + data['time'],
                    format='%d/%m/%Y %H:%M:%S', dayfirst=True)

Subtract firat value of date column and convert to minutes:

data['time1'] = data['date'].sub(data.loc[0, 'date']).dt.total_seconds()/60
print (data)
                  date      time  X3  time1
0  2018-03-10 06:15:00   6:15:00   7    0.0
1  2018-03-10 06:45:00   6:45:00   5   30.0
2  2018-03-10 07:45:00   7:45:00   7   90.0
3  2018-03-10 09:00:00   9:00:00   7  165.0
4  2018-03-10 09:25:00   9:25:00   7  190.0
5  2018-03-10 09:30:00   9:30:00   5  195.0
6  2018-03-10 11:00:00  11:00:00   7  285.0
7  2018-03-10 11:30:00  11:30:00   7  315.0
8  2018-03-10 13:30:00  13:30:00   7  435.0
9  2018-03-10 13:50:00  13:50:00   5  455.0
10 2018-03-10 15:00:00  15:00:00   7  525.0
11 2018-03-10 15:25:00  15:25:00   7  550.0
12 2018-03-10 16:25:00  16:25:00   7  610.0
13 2018-03-10 18:00:00  18:00:00   7  705.0
14 2018-03-10 19:00:00  19:00:00   5  765.0

Create new 60 range values:

arr = np.arange(0, int(data['time1'].max()), 60)
print (arr)
[  0  60 120 180 240 300 360 420 480 540 600 660 720]

Join together with minute column:

union = np.union1d(data['time1'], arr)
print (union)
[  0.  30.  60.  90. 120. 165. 180. 190. 195. 240. 285. 300. 315. 360.
 420. 435. 455. 480. 525. 540. 550. 600. 610. 660. 705. 720. 765.]

Create index by time1 column and DataFrame.reindex - added new values from arr:

data = data.set_index('time1')['X3'].reindex(union, fill_value=0).reset_index()
print (data)
    time1  X3
0     0.0   7
1    30.0   5
2    60.0   0
3    90.0   7
4   120.0   0
5   165.0   7
6   180.0   0
7   190.0   7
8   195.0   5
9   240.0   0
10  285.0   7
11  300.0   0
12  315.0   7
13  360.0   0
14  420.0   0
15  435.0   7
16  455.0   5
17  480.0   0
18  525.0   7
19  540.0   0
20  550.0   7
21  600.0   0
22  610.0   7
23  660.0   0
24  705.0   7
25  720.0   0
26  765.0   5

answered Sep 18, 2019 at 7:56

jezrael

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

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

7 Comments

team Over a year ago

Ohh thankyou friend. You saved my day . I have one question If I had inputs columns more than 1 , then how to add it into this code.\

jezrael Over a year ago

@team - it is possible by data = data.set_index('time1').reindex(union, fill_value=0).reset_index() - but then get 0 for all columns - e.g. for datetimes default 'zero' datetime 1970-01-01 00:00:00 and also for times 0

team Over a year ago

worked it charm. Sorry to ask like this did you check my last question"stackoverflow.com/questions/57971667/…" Can you have any idea to solve this process? Can you give me suggestion for this? Then I can also try it.

team Over a year ago

@ jezrael related to this question. Here one issue came , Here when the date is changed then the all the start time should be 0 and then again start to run 60 min. In your code it didn't came

team Over a year ago

@ jezrael sure I will. Here I edited the question and you can see the reference

|

Collectives™ on Stack Overflow

How to add extra time into the time column with data using python

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related