Exercise on using Pandas DataFrame on managing date time query

Pandas sample dataframe with Date time column and random values with incremental numbers

To generate the DataFrame df, run the code given at the end of this page. As the DataFrame is generated based on todays date and randomly selected values from different lists , so the result will change and every time a different DataFrame is generated.
Here is the sample output.

df.head()

	Log_ID	dt	dept	activity	eqpt
0	1000	2021-04-24	dept1	cleaning	UPS
1	1001	2021-04-25	dept3	repair	Generator
2	1002	2021-04-26	dept1	cheking	AC
3	1003	2021-04-27	dept3	painting	Generator
4	1004	2021-04-28	dept1	cleaning	UPS

Download log_report.xlsx file

How many records are there for the year 2022 or current year
How many records are there for the year 2022 March month
Show the records of Log_ID: 1012
When was the last inspection done for the equipment 'Pump'
How many records are there between April 2021 and Jun 2021
How many days back inspection was completed for equp='Pump' from today.
Display all the records available for last 30 days ( starting from today )
List all records having done ( st_date) on Wednesday ( Weekday =2 )

Grouping data and using aggregate functions

Display year and number of records in that year.
Display month and number of records in that month.
Display Year and Month with number of records in that year month

Pandas sample DataFrame with date column to get matching records over Year, month, days & weekdays

How many records are there for the year 2022 or current year

c_year=pd.to_datetime('now').year #current year 
print(df[df['dt'].dt.year==c_year].count()[0])
#print(df[df['dt'].dt.year==2022].count())

Output

You can un-comment the other line and check how all columns are listed.
We can use set_index() to index date column and then apply the queries.

How many records are there for the year 2022 March month

List of records .

print(df.set_index('dt')['2022-03'])

The number of records.

print(df.set_index('dt')['2020-03'].count())

Show the records of Log_ID: 1012

print(df.loc[df['Log_ID']==1012])

Output

    Log_ID         dt   dept  activity eqpt
12    1012 2021-05-08  Dept3  Checking  Fan

When was the last inspection done for the equipment 'Pump'

max_date=df.loc[df['eqpt']=='Pump']['dt'].max()
#print(max_date)
print(df.loc[df['dt']==max_date])

Output

     Log_ID         dt   dept  activity  eqpt
361    1361 2022-04-22  Dept3  Cleaning  Pump

How many records are there between April 2021 and Jun 2021

#print(df.set_index('dt')['2021-04':'2021-06']) # total list
print(df.set_index('dt')['2021-04':'2021-06'].count())# Total Number

Output

How many days back inspection was completed for equp='Pump' from today.

max_date=df.loc[df['eqpt']=='Pump']['dt'].max()
#print(max_date)
print(df.loc[df['dt']==max_date])
today=pd.to_datetime('now')
print( "Days difference = ", ( today - max_date))

Output

     Log_ID         dt   dept  activity  eqpt
364    1364 2022-04-25  Dept3  Checking  Pump
Days difference =  0 days 10:13:00.490353

Display all the records available for last 30 days ( starting from today )

You can use today’s date by using to_datetime() or any other date for calculating last 7 days records.

today=pd.to_datetime('now')
#today=pd.to_datetime('2022-04-12') # change this date YYYY-MM-DD
new_day=today-datetime.timedelta(7)
print(df[(df['dt']>new_day)])

Output

     Log_ID         dt   dept    activity     eqpt
358    1358 2022-04-19  Dept2  Inspection      Fan
359    1359 2022-04-20  Dept4    Checking  Battery
360    1360 2022-04-21  Dept2    Painting       AC
361    1361 2022-04-22  Dept1    Cleaning       AC
362    1362 2022-04-23  Dept4    Cleaning   Server
363    1363 2022-04-24  Dept4    Cleaning      UPS
364    1364 2022-04-25  Dept4    Painting       AC

List all records having done ( dt) on Wednesday ( Weekday =2 )

Monday is weekday = 0 , Tuesday =1, Sunday is Weekday = 6

print(df[df['dt'].dt.weekday==2])

To check the weekday we can add one more column saying weekday. We can add our format by using strftime

df['weekday']=df['dt'].dt.strftime('%A')

Display year and number of records in that year.

print(df.groupby(df['dt'].dt.year).count())
print(df.groupby(df['dt'].dt.year).count()['Log_ID'])

Display month and number of records in that month.

print(df.groupby(df['dt'].dt.month).count())

Display Year and Month with number of records in that year month

print(df.groupby([df['dt'].dt.year,df['dt'].dt.month]).count())

Pandas Pandas date & time Basic Exercise using datetime 3-2

Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

Exercise : Pandas and Date query

Grouping data and using aggregate functions

How many records are there for the year 2022 or current year

How many records are there for the year 2022 March month

Show the records of Log_ID: 1012

When was the last inspection done for the equipment 'Pump'

How many records are there between April 2021 and Jun 2021

How many days back inspection was completed for equp='Pump' from today.

Display all the records available for last 30 days ( starting from today )

List all records having done ( dt) on Wednesday ( Weekday =2 )

Display year and number of records in that year.

Display month and number of records in that month.

Display Year and Month with number of records in that year month

Subscribe