How to add date column to already existing time column in python?

Question

I have a df as follows:

        Time    Werktag
0   00:15:00    73.0
1   00:30:00    70.1
2   00:45:00    67.1
3   01:00:00    64.5
4   01:15:00    62.3
5   01:30:00    60.6
6   01:45:00    59.2
7   02:00:00    57.9
8   02:15:00    56.7
9   02:30:00    55.6
10  02:45:00    54.7
11  03:00:00    54.2
12  03:15:00    54.1
13  03:30:00    54.6
14  03:45:00    55.8
15  04:00:00    57.9
16  04:15:00    60.9
17  04:30:00    64.4
18  04:45:00    68.0
19  05:00:00    71.0
20  05:15:00    73.2
21  05:30:00    74.7
22  05:45:00    75.7
23  06:00:00    76.6
24  06:15:00    77.7
25  06:30:00    79.3
26  06:45:00    81.9
27  07:00:00    86.0
28  07:15:00    91.9
29  07:30:00    100.4
30  07:45:00    111.9
31  08:00:00    127.1
32  08:15:00    145.9
33  08:30:00    166.2
34  08:45:00    185.2
35  09:00:00    200.0
36  09:15:00    208.5
37  09:30:00    212.1
38  09:45:00    212.6
39  10:00:00    212.1
40  10:15:00    212.2
41  10:30:00    213.1
42  10:45:00    214.6
43  11:00:00    216.8
44  11:15:00    219.2
45  11:30:00    221.2
46  11:45:00    222.0
47  12:00:00    220.5
48  12:15:00    216.3
49  12:30:00    209.8
50  12:45:00    201.9
51  13:00:00    193.4
52  13:15:00    185.0
53  13:30:00    177.4
54  13:45:00    170.9
55  14:00:00    166.3
56  14:15:00    163.9
57  14:30:00    163.6
58  14:45:00    165.1
59  15:00:00    168.2
60  15:15:00    172.5
61  15:30:00    177.3
62  15:45:00    181.4
63  16:00:00    184.1
64  16:15:00    184.5
65  16:30:00    183.6
66  16:45:00    182.1
67  17:00:00    181.3
68  17:15:00    181.5
69  17:30:00    181.7
70  17:45:00    180.0
71  18:00:00    174.7
72  18:15:00    164.8
73  18:30:00    151.5
74  18:45:00    137.0
75  19:00:00    123.3
76  19:15:00    112.1
77  19:30:00    103.4
78  19:45:00    97.3
79  20:00:00    93.4
80  20:15:00    91.6
81  20:30:00    91.1
82  20:45:00    91.1
83  21:00:00    90.6
84  21:15:00    89.2
85  21:30:00    87.2
86  21:45:00    85.0
87  22:00:00    83.2
88  22:15:00    82.1
89  22:30:00    81.7
90  22:45:00    81.6
91  23:00:00    81.3
92  23:15:00    80.6
93  23:30:00    79.5
94  23:45:00    77.9
95  00:00:00    75.7

the Time column is from 00:15:00 (current day) till 00:00:00 (next day). I would like to change the Time column in such a way that the format is as follows: DD.MM.YYYY HH:MM and the date starting from 01.01.2020 till 31.12.2020.

Sample Output

     Time         Werktag
01.01.2020 00:15    73.0
01.01.2020 00:30    70.1
01.01.2020 00:45    67.1
01.01.2020 01:00    64.5
01.01.2020 01:15    62.3
02.01.2020 00:00    75.7
02.01.2020 00:15    73.0
02.01.2020 00:30    70.1
02.01.2020 00:45    67.1
02.01.2020 01:00    64.5
02.01.2020 01:15    62.3
03.01.2020 00:00    75.7

and so on till 31.12.2020

31.12.2020 00:15    73.0
31.12.2020 00:30    70.1
31.12.2020 00:45    67.1
31.12.2020 01:00    64.5
31.12.2020 01:15    62.3
01.01.2021 00:00    75.7

How can this be done?

Thanks in advance

Last value is 01.01.2021 00:00 or 31.12.2020 01:15 ?

jezrael
– jezrael

2020-02-04 11:17:12 +00:00
Commented Feb 4, 2020 at 11:17 — jezrael
– jezrael, Commented Feb 4, 2020 at 11:17
Last value is 01.01.2021 00:00

some_programmer
– some_programmer

2020-02-04 11:18:22 +00:00
Commented Feb 4, 2020 at 11:18 — some_programmer
– some_programmer, Commented Feb 4, 2020 at 11:18
Is 75.7 in last row correct?

jezrael
– jezrael

2020-02-04 11:21:30 +00:00
Commented Feb 4, 2020 at 11:21 — jezrael
– jezrael, Commented Feb 4, 2020 at 11:21
yes. the last row must be 01.01.2021 00:00 75.7

some_programmer
– some_programmer

2020-02-04 11:23:40 +00:00
Commented Feb 4, 2020 at 11:23 — some_programmer
– some_programmer, Commented Feb 4, 2020 at 11:23
@jezrael I added the entire df

some_programmer
– some_programmer

2020-02-04 11:26:42 +00:00
Commented Feb 4, 2020 at 11:26 — some_programmer
– some_programmer, Commented Feb 4, 2020 at 11:26

jezrael · Accepted Answer · 2020-02-04 11:29:50Z

1

Use cross join by DataFrame.merge with helper DataFrame created by date_range, last join by Series.add and add custom format by Series.dt.strftime, also is added 1 day if 00:00 values in input column:

td = pd.to_timedelta(df['Time'].astype(str))
df1 = df.assign(Time = td.mask(td==pd.Timedelta(0), td + pd.Timedelta(1, 'd')), a=1)
df2 = pd.DataFrame({'dates': pd.date_range('01.01.2020', '31.12.2020'), 'a': 1})

df = df2.merge(df1, how='outer').drop('a', axis=1)
df['dates'] = df['dates'].add(df.pop('Time')).dt.strftime('%d.%m.%Y %H:%M')
print (df)
                  dates  Werktag
0      01.01.2020 00:15     73.0
1      01.01.2020 00:30     70.1
2      01.01.2020 00:45     67.1
3      01.01.2020 01:00     64.5
4      01.01.2020 01:15     62.3
                ...      ...
35131  31.12.2020 23:00     81.3
35132  31.12.2020 23:15     80.6
35133  31.12.2020 23:30     79.5
35134  31.12.2020 23:45     77.9
35135  01.01.2021 00:00     75.7

[35136 rows x 2 columns]

edited Feb 4, 2020 at 11:29

answered Feb 4, 2020 at 11:14

jezrael

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

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

2 Comments

some_programmer Over a year ago

df1 = df.assign(Time = pd.to_timedelta(df['Time']), a=1) gives me the following error: ValueError: Invalid type for timedelta scalar: <class 'datetime.time'>

jezrael Over a year ago

@Junkrat - Use pd.to_timedelta(df['Time'].astype(str))

Collectives™ on Stack Overflow

How to add date column to already existing time column in python?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related