johnson
Reputation: 429
dataframe to shift python pandas
i Have a df with time. what i want to do is create another column and set it as Shift. Everyday 7AM to 7PM is Day Shift and 7PM to 7AM is night Shift.
ex: 2/11: If time btw 2/11 7 AM to 2/11 7 PM is 2-11 Day and 2/11 7 PM to 2/12 7 AM is 2/11 Night.
To create 'Day-Shift' column first I created 'Date' and then created 'Date-Shift' column.
But the problem is my code correctly classifies Each day 'Day' shift (7AM to 7PM) but it fails to classify 'night' shift correctly. Please check highlighted rows.
Ex: 21st row: its Day-Shift value should be '01-07 Night' instead of '01-08 Night'
mycode:
df2["Date"]=df2['Time'].astype(str).str[:10]
df2["Shift"] = pd.to_datetime(df2['Time'],unit='s').apply(lambda x: "Day" if x.hour >= 7 and x.hour <= 18 else "Night")
df2['Date']=df2['Date'].astype(str)
df2['Date'] = df2['Date'].str[5:]
df2["Day-Shift"]=df2["Date"]+" "+df2["Shift"]
df2.head(2)
sample df:
{'Time': {17: Timestamp('2021-01-07 23:11:53'),
18: Timestamp('2021-01-07 23:11:53'),
19: Timestamp('2021-01-07 23:29:13'),
20: Timestamp('2021-01-07 23:29:13'),
21: Timestamp('2021-01-08 00:12:23'),
22: Timestamp('2021-01-08 00:12:23'),
23: Timestamp('2021-01-08 00:19:43'),
24: Timestamp('2021-01-08 00:19:43'),
25: Timestamp('2021-01-08 00:58:13'),
26: Timestamp('2021-01-08 00:58:13'),
27: Timestamp('2021-01-08 01:24:13'),
28: Timestamp('2021-01-08 01:24:13'),
29: Timestamp('2021-01-08 06:31:09'),
30: Timestamp('2021-01-08 06:31:09'),
31: Timestamp('2021-01-08 06:54:39'),
32: Timestamp('2021-01-08 06:54:39'),
33: Timestamp('2021-01-08 06:54:49'),
34: Timestamp('2021-01-08 07:00:00'),
35: Timestamp('2021-01-08 07:16:29'),
36: Timestamp('2021-01-08 07:17:59'),
37: Timestamp('2021-01-08 07:17:59'),
38: Timestamp('2021-01-08 07:28:39'),
39: Timestamp('2021-01-08 07:28:39'),
40: Timestamp('2021-01-08 07:48:59'),
41: Timestamp('2021-01-08 07:48:59'),
42: Timestamp('2021-01-08 10:04:59'),
43: Timestamp('2021-01-08 10:07:59'),
44: Timestamp('2021-01-08 12:19:49'),
45: Timestamp('2021-01-08 12:19:49'),
46: Timestamp('2021-01-08 12:24:09'),
47: Timestamp('2021-01-08 12:24:09'),
48: Timestamp('2021-01-08 18:19:05'),
49: Timestamp('2021-01-08 18:19:05')},
'Date': {17: '01-07',
18: '01-07',
19: '01-07',
20: '01-07',
21: '01-08',
22: '01-08',
23: '01-08',
24: '01-08',
25: '01-08',
26: '01-08',
27: '01-08',
28: '01-08',
29: '01-08',
30: '01-08',
31: '01-08',
32: '01-08',
33: '01-08',
34: '01-08',
35: '01-08',
36: '01-08',
37: '01-08',
38: '01-08',
39: '01-08',
40: '01-08',
41: '01-08',
42: '01-08',
43: '01-08',
44: '01-08',
45: '01-08',
46: '01-08',
47: '01-08',
48: '01-08',
49: '01-08'},
'Shift': {17: 'Night',
18: 'Night',
19: 'Night',
20: 'Night',
21: 'Night',
22: 'Night',
23: 'Night',
24: 'Night',
25: 'Night',
26: 'Night',
27: 'Night',
28: 'Night',
29: 'Night',
30: 'Night',
31: 'Night',
32: 'Night',
33: 'Night',
34: 'Day',
35: 'Day',
36: 'Day',
37: 'Day',
38: 'Day',
39: 'Day',
40: 'Day',
41: 'Day',
42: 'Day',
43: 'Day',
44: 'Day',
45: 'Day',
46: 'Day',
47: 'Day',
48: 'Day',
49: 'Day'},
'Day-Shift': {17: '01-07 Night',
18: '01-07 Night',
19: '01-07 Night',
20: '01-07 Night',
21: '01-08 Night',
22: '01-08 Night',
23: '01-08 Night',
24: '01-08 Night',
25: '01-08 Night',
26: '01-08 Night',
27: '01-08 Night',
28: '01-08 Night',
29: '01-08 Night',
30: '01-08 Night',
31: '01-08 Night',
32: '01-08 Night',
33: '01-08 Night',
34: '01-08 Day',
35: '01-08 Day',
36: '01-08 Day',
37: '01-08 Day',
38: '01-08 Day',
39: '01-08 Day',
40: '01-08 Day',
41: '01-08 Day',
42: '01-08 Day',
43: '01-08 Day',
44: '01-08 Day',
45: '01-08 Day',
46: '01-08 Day',
47: '01-08 Day',
48: '01-08 Day',
49: '01-08 Day'}}
Upvotes: 1
Views: 380
Answers (1)
jezrael
Reputation: 862431
You can subtract one day from Time if hour is less like 7 in Series.mask, then create Shift by compare hours in Series.between and set values by numpy.where and last join columns with Series.dt.strftime for extract days and months:
df['Date'] = (df['Time'].mask(df['Time'].dt.hour.lt(7),
df['Time'] - pd.offsets.DateOffset(days=1)))
df["Shift"] = np.where(df['Time'].dt.hour.between(7, 18), 'Day','Night')
df["Day-Shift"] = df["Date"].dt.strftime('%m-%d') + " " + df["Shift"]
print (df)
Time Date Shift Day-Shift
17 2021-01-07 23:11:53 2021-01-07 23:11:53 Night 01-07 Night
18 2021-01-07 23:11:53 2021-01-07 23:11:53 Night 01-07 Night
19 2021-01-07 23:29:13 2021-01-07 23:29:13 Night 01-07 Night
20 2021-01-07 23:29:13 2021-01-07 23:29:13 Night 01-07 Night
21 2021-01-08 00:12:23 2021-01-07 00:12:23 Night 01-07 Night
22 2021-01-08 00:12:23 2021-01-07 00:12:23 Night 01-07 Night
23 2021-01-08 00:19:43 2021-01-07 00:19:43 Night 01-07 Night
24 2021-01-08 00:19:43 2021-01-07 00:19:43 Night 01-07 Night
25 2021-01-08 00:58:13 2021-01-07 00:58:13 Night 01-07 Night
26 2021-01-08 00:58:13 2021-01-07 00:58:13 Night 01-07 Night
27 2021-01-08 01:24:13 2021-01-07 01:24:13 Night 01-07 Night
28 2021-01-08 01:24:13 2021-01-07 01:24:13 Night 01-07 Night
29 2021-01-08 06:31:09 2021-01-07 06:31:09 Night 01-07 Night
30 2021-01-08 06:31:09 2021-01-07 06:31:09 Night 01-07 Night
31 2021-01-08 06:54:39 2021-01-07 06:54:39 Night 01-07 Night
32 2021-01-08 06:54:39 2021-01-07 06:54:39 Night 01-07 Night
33 2021-01-08 06:54:49 2021-01-07 06:54:49 Night 01-07 Night
34 2021-01-08 07:00:00 2021-01-08 07:00:00 Day 01-08 Day
35 2021-01-08 07:16:29 2021-01-08 07:16:29 Day 01-08 Day
36 2021-01-08 07:17:59 2021-01-08 07:17:59 Day 01-08 Day
37 2021-01-08 07:17:59 2021-01-08 07:17:59 Day 01-08 Day
38 2021-01-08 07:28:39 2021-01-08 07:28:39 Day 01-08 Day
39 2021-01-08 07:28:39 2021-01-08 07:28:39 Day 01-08 Day
40 2021-01-08 07:48:59 2021-01-08 07:48:59 Day 01-08 Day
41 2021-01-08 07:48:59 2021-01-08 07:48:59 Day 01-08 Day
42 2021-01-08 10:04:59 2021-01-08 10:04:59 Day 01-08 Day
43 2021-01-08 10:07:59 2021-01-08 10:07:59 Day 01-08 Day
44 2021-01-08 12:19:49 2021-01-08 12:19:49 Day 01-08 Day
45 2021-01-08 12:19:49 2021-01-08 12:19:49 Day 01-08 Day
46 2021-01-08 12:24:09 2021-01-08 12:24:09 Day 01-08 Day
47 2021-01-08 12:24:09 2021-01-08 12:24:09 Day 01-08 Day
48 2021-01-08 18:19:05 2021-01-08 18:19:05 Day 01-08 Day
49 2021-01-08 18:19:05 2021-01-08 18:19:05 Day 01-08 Day
Upvotes: 2
Related Questions
- DateTime adjustment in pandas
- Shifting datetime index in selected rows of Pandas DataFrame
- Pandas how to shift column by datetime into datetime not in index
- pandas shift and extrapolate datetime column
- Pandas Dataframe time shift columns
- Shifting a date time index
- Python pandas shift dataframe with time index value
- Pandas dataframe shift column by date
- Python datetime transform
- pandas dataframe shift dates