Exercise : Pandas and Date 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_IDdtdeptactivityeqpt
010002021-04-24 dept1 cleaning UPS
110012021-04-25 dept3 repair Generator
210022021-04-26 dept1 cheking AC
310032021-04-27 dept3 painting Generator
410042021-04-28 dept1 cleaningUPS

Download log_report.xlsx file

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

Grouping data and using aggregate functions

  1. Display year and number of records in that year.
  2. Display month and number of records in that month.
  3. 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
115
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
66

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')
Read more on groupby() here

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
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com







    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer