Exercise : Pandas and Date on Rent

import pandas as pd 
import numpy as np
my_data = pd.read_excel('D:\\rent-calculation.xlsx')

Display the column names of the DataFrame.

print(my_data.columns)

List Departments with number of students

print(my_data['dept'].value_counts())

Display the records where student id is not available

print(my_data[my_data['id'].isnull()])

Create a column showing diff_days to show difference in check in days ( ck_days ) as on 1st of Jun 2020

my_data['ck_date'] = pd.to_datetime(my_data['ck_date'])
bd=pd.to_datetime('2020-06-01') # base date 
my_data['diff_days']=bd-my_data['ck_date']
print(my_data)

List of students who have stayed more than 200 days.

my_data['diff_days'] = my_data['diff_days']/ np.timedelta64(1, 'D') 
print(my_data[my_data['diff_days'] > 200])

Create a column ( name it allowed ) showing number of free days. For mktg free days are 50 , for production it is 65 and for planning it is 45

#my_data['allowed']=my_data['dept'].apply(lambda x:50 if x=="mktg" else np.nan)
#my_data['allowed']=my_data['dept'].apply(lambda x:65 if x=="production" else np.nan)
#my_data['allowed']=my_data['dept'].apply(lambda x:45 if x=="planning" else np.nan)
my_data['allowed']=np.where(my_data['dept']=='mktg',50,
                np.where(my_data['dept']=='production',65,
                 np.where(my_data['dept']=='planning',45,np.nan)))
print(my_data)

List the students who have stayed for more than the free days allowed.

print(my_data[my_data['diff_days'] > my_data['allowed']])