Exercise : Pandas and Date on Rent

Pandas date & time Pandas

Background of Data & sample excel file

Different department students are staying in a company provided accommodation. In an excel file we have details like id, name, check in date, department.
While entering, some blank data also gone into the excel file. Based on the department each student is allowed some free days.

Download rent-calculation.xlsx file

  1. Display the column names of the DataFrame.
  2. List Departments with number of students
  3. Display the records where student id is not available
  4. Create a column showing diff_days to show difference in check in days ( ck_days ) as on 1st of Jun 2020
  5. List of students who have stayed more than 200 days.
  6. 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
  7. List the students who have stayed for more than the free days allowed.
Read more on how to read excel file and create DataFrame.

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']])


Pandas date & time
Exercise datetime 3-1 Exercise datetime 3-3 Exercise datetime 3-3


plus2net.com



Post your comments , suggestion , error , requirements etc here




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