CODEWITHSUNDEEP
python-3.xpandasdatetime
user10270654
user10270654

Reputation:

How to add one hour repeately from start time till to next day start time using panda python

Here I have a csv file with data . I want to write a code that start time start from csv file time column first time and it will be equal as 0. Then from that time add one hour one hour till to next day start time. Then after that again that time become as 0 and add one hour one hour till to next day start time . This process continue. 

time_interval = 3600 (in seconds)
date_array = []
date_array.append(pd.to_datetime(data['date'][0]).date())
start_time = []
end_time   = []
temp_date  = pd.to_datetime(data['date'][0]).date()
start_time=0
for i in range(len(data['date'])):
 cur_date = pd.to_datetime(data['date'][i]).date()
 if( cur_date > temp_date):
    end_time.append(pd.to_datetime(data['time'][i-1], format='%H:%M:%S').time())
    start_time=0
    date_array.append(cur_date)
    temp_date = cur_date
end_time.append(pd.to_datetime(data['time'][len(data['date'])-1], format='%H:%M:%S').time())
datetime_array = []
for i in range(len(date_array)):
  s_time = start_time
  e_time = datetime.datetime.combine(date_array[i],  end_time[i])
print(datetime_array)

I didn't get answer that I want. Here I include my csv file. enter image description here

enter image description here

Upvotes: 1

Views: 495

Answers (2)

jezrael
jezrael

Reputation: 862471

you can create new column filled by datetimes, then get first value per days with GroupBy.transform for subtract with datetimes, last convert timedeltas by Series.dt.total_seconds and then to minutes:

df = pd.DataFrame({
        'date':['10/3/2018'] * 5 + ['10/4/2018'],
        'time':['6:00:00','7:00:00','8:00:00','9:00:00','10:00:00','6:00:00'],
        'col':[4,8,9,4,2,3],

})

df['datetime'] =  pd.to_datetime(df['date'] + df['time'], format='%d/%m/%Y%H:%M:%S')
first = df.groupby('date')['datetime'].transform('first')
df['new'] = df['datetime'].sub(first).dt.total_seconds().div(60).astype(int)
print (df)

        date      time  col            datetime  new
0  10/3/2018   6:00:00    4 2018-03-10 06:00:00    0
1  10/3/2018   7:00:00    8 2018-03-10 07:00:00   60
2  10/3/2018   8:00:00    9 2018-03-10 08:00:00  120
3  10/3/2018   9:00:00    4 2018-03-10 09:00:00  180
4  10/3/2018  10:00:00    2 2018-03-10 10:00:00  240
5  10/4/2018   6:00:00    3 2018-04-10 06:00:00    0

Detail:

print (first)
0   2018-03-10 06:00:00
1   2018-03-10 06:00:00
2   2018-03-10 06:00:00
3   2018-03-10 06:00:00
4   2018-03-10 06:00:00
5   2018-04-10 06:00:00
Name: datetime, dtype: datetime64[ns]

Upvotes: 0

Clock Slave
Clock Slave

Reputation: 7957

Is this what you are looking for

import pandas as pd

df = pd.DataFrame([
    ["10/3/2018"],
["10/3/2018"],
["10/3/2018"],
["10/3/2018"],
["10/3/2018"],
["10/3/2018"],
["10/4/2018"],
["10/4/2018"],
["10/4/2018"],
["10/4/2018"],
],columns=['date'])

df['date'] =  pd.to_datetime(df['date'], format='%d/%m/%Y')
start_time = '6:00:00'
df.loc[:,'time'] = start_time
increment = df.groupby(['date', 'time']).cumcount().astype('timedelta64[h]')
df.loc[:,'time'] = pd.to_timedelta(df.loc[:,'time'])
df['time'] =  df['time'] + increment

Output

        date     time
0 2018-03-10 06:00:00
1 2018-03-10 07:00:00
2 2018-03-10 08:00:00
3 2018-03-10 09:00:00
4 2018-03-10 10:00:00
5 2018-03-10 11:00:00
6 2018-04-10 06:00:00
7 2018-04-10 07:00:00
8 2018-04-10 08:00:00
9 2018-04-10 09:00:00

Upvotes: 1

Related Questions