First read the excel file and create the DataFrame.
import pandas as pd
import numpy as np
my_data = pd.read_excel('D:\\rent-calculation.xlsx')
Display the column names of the DataFrame.
Read more on
displaying DataFrame columns.
print(my_data.columns)
List Departments with number of students
Read more on
vlaue_counts().
print(my_data['dept'].value_counts())
Display the records where student id is not available
Read more on
isnull().
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
Read more on
to_datetime()
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.
Read more on how to import
numpy and use
np.timedelta64
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
Read more on
Nested np.where()
#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']])