How to filter and classify with time and dates input

user_14688150 0
Viewed 303

I am new in python and trying to solve two problems, hopefully someone can help me:

  1. I have a data, which one of the columns are date format:
    Date                Sales

0   2019-07-01 00:00:00 16.66
1   2019-07-01 02:14:00 17.35
2   2019-07-01 03:17:00 28.78
3   2019-07-01 03:25:00 15.65

24765   2020-03-20 23:13:00     33.21
24766   2020-03-20 23:15:00     36.60
24767   2020-03-20 23:17:00     12.33

I wanted to create a third column where I identify the shift the sales happened:

From 8:00 to 16:00 - 'First Shift'
From 16:00 to 00:00 - 'Second Shift'
From 00:00 to 08:00 - 'Third Shift'
  1. I wanted to create a sum of sales per day and per shift, the result would be similar to:
 Day           Shift     Total Sales

2019-07-01     First       $543.23
2019-07-01     Second      $413.87
2019-07-01     Third       $301.12
2020-03-14     Third       $214.13

I used to work in Excel and I am trying to convert to python.

In Excel I would use if conditions and pivot table, I did an extended research and could not solve so far.

pandas pivot-table if-statement datetime python
2 Answers

Lets try find time between and allocate shift. Then group by Date, Shift and sum in an agg function

#Set datetime component as index
df.set_index('Date', inplace=True)


#Find time between and allocate shift
df.loc[df.between_time('16:00','00:00').any(1).index,'Shift']='Second Shift'
df.loc[df.between_time('00:00','08:00').any(1).index,'Shift']='Third Shift'
df.loc[df.between_time('08:00','16:00').any(1).index,'Shift']='First Shift'

#Groupby date, shift and sum the sales
df.groupby([df.index.date,'Shift']).agg(TotalSales=('Sales', 'sum'))

Alternatively

#Create new column Time
df['Date']=pd.to_datetime(df['Date'])
df['Time']=pd.to_datetime(df['Date']).dt.strftime('%H:%M')

#Allocate Shifts using np.select
c=[df.Time.between('00:00','08:00'),df.Time.between('08:00','16:00'),df.Time.between('16:00','23:59')]
choices=['Third Shift','First Shift','Second Shift']

df['Shift']=np.select(c,choices)
#Groupby and sum
df.groupby([df.Date.dt.date,'Shift']).agg(TotalSales=('Sales', 'sum'))
user_89869750

Try this:

#This function converts time to shifts:

def to_shift(hour):
    if hour >= 8 and hour <= 16:        
        return 'First'
    elif hour >= 16 and hour <= 24:
        return 'Second'
    elif hour >= 0 and hour <= 8:
        return 'Third'


df['Time'] = pd.to_datetime(df['Time']) #convert time to a pandas date time if it is already not
df['hour'] = df['Time'].dt.hour #populate a new column with hour
df['date'] = df['Time'].dt.date #populate a new column with date

df['shift'] = df['hour'].apply(to_shift) #apply the to_shift function to each hour in the data set to derive the shift

df_sum = df.groupby(['date', 'shift']).agg({'Sales': 'sum'}) #groupby date and shift to calculate the total sale for the shift and date and populate a new data frame

df_sum output is:

date        shift  Sales    
2019-07-01  Third   78.44
2020-03-20  Second  82.14
user_5570220
Related