T-M

Data Analysis
Python
Class Booklet
 T-M

Python Unit – Class practice booklet

Lesson 1 - Introduction and basics

General instructions:

Each section should be completed in a separate cell

Part 1 - Familiarity with the work environment and use of operators

1. Open a new folder on your desktop named Python_Class

2. In the folder you opened, create a new workbook called Ex_sol_1

(Make sure that the workbook has been opened in the desired location)

3. Open a new cell and turn it into a Markdown cell

4. Give it the following title: My First Python Lesson

5. Run the cell - make sure the cell appears with the title

6. Open more rows

7. Write 5 short and simple arithmetic exercises, followed by 4 more complex

exercises using the operators we learned.

After each cell, run the cell and check that the result is correct

Part 2 - Working with variables

8. Define a new Markdown cell and inside it, write a large heading:
Python - Lesson 1 - Exercises solution

9. Underneath that one, add a Markdown cell with the title: Numbers

10. Define a variable named my_number and insert the value 500

11. Display the value for the variable my_number

12. Add 30 to the variable, and multiply the result by 2

Display the value for the variable my_number

Page 1 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

13. Show the remainder when my_number is divided by 7

14. Define a unit_price variable, with the value of 503

15. Define a qty_sold variable, with the value of 8

16. Using the variables you defined in the previous sections, calculate what amount
the customer would pay if he received a 10% discount.

Perform the assignment statement (save the value) to the variable named
total_price

Display the value of the variable

17. Show the result of dividing total_price by 3

18. Show the result of dividing total_price by 3 but show only a whole number

19. What is the variable type of total_price?

Part 3 - Working with Strings, Functions and Useful Methods

20. Define a Markdown cell with the title: String slicing and methods

21. Define a new variable named my_name, and put in your name

22. Define a new variable named city, and put in the name of the city where you live

23. Show the variable type of city

24. Set to print (<> refers to the value in the variable in parentheses):
Welcome <my_name> from <City>, please pay <total_price>

25. Print the following text accurately:

Hello <my_name>,
It's our first Python lesson How fun! =)

26. What will be the result of the expression? Think to yourself first and only then
check the software:

"He" + "l" * 2 + "o" + " Class - " + "Python " + str(100//5) + str(45%23)

Page 2 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

27. Define a new variable named my_address, and put in your full address

28. Print only the first 5 characters

29. Print the last 6 characters

30. Print the address from the beginning to the end in increments of 3

31. Print the address from the end to the beginning

32. Print the following text exactly as it appears (including line breaks and
indentations):

Twinkle, twinkle, little star,

How I wonder what you are!
Up above the world so high,
Like a diamond in the sky.
Twinkle, twinkle, little star,
How I wonder what you are

Page 3 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 2 - Pandas - Importing and Filtering
data

General instructions:

Each section should be completed in a separate cell

Part 1 - Importing data, methods and basic attributes

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_2
(Make sure that the notebook opens in the desired location)

3. Open a new cell and make it a Markdown cell

4. Write the following headline: Pandas - Import file

5. Import the Pandas Library to the notebook

6. Import the data file 'hotel_bookings.csv', and save it into a variable named df.

Examine the CSV file, and if necessary use appropriate parameters so that the data
will be entered accurately

7. Add a Markdown cell containing the title:

Viewing and Inspecting data

8. Show only the first 5 lines - what is the month and year in which the data begins?

9. Show only the last 5 lines - what is the month and year in which the data ends?

10. Use one or more methods to answer the following questions regarding df:

a. How many lines are there in df?

b. How many columns does df contain?

c. Are there columns that lack significant data?

d. Check if there is a date column, and if so, is the data type set as a date?

Page 4 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

11. How many cells are in df?

Does it match the amount of columns and rows?

Write an arithmetic exercise that will calculate the number of rows and columns,
and check that the result is the same

a. You can see how many columns and how many rows there are, and write the
exercise manually

b. Challenge:
How can you get a TRUE or FALSE response by only using code for this
question?
(You can use everything we learned, even in a previous lesson)

12. Use method(s) to answer the following questions:

a. What is the earliest year in which we have data on customer orders?

(based on the column arrival_date_year)?

b. What is the amount of adults reserved for most of the reservations?

c. What is the amount of children reserved for most of the reservations?

d. Are most of the vacationers at the hotel returning customers or first timers?

e. What is the average number of days one is on the waiting list in order to
reserve a room at the hotel?

13. Run the following command: 'April' in df

Why did you get False as an answer?

Page 5 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Part 2 - Selecting and Filtering Data

14. Add a new title: Selecting and sorting data

15. Select the column arrival_date_month

16. Continuing from the previous section, use the ‘describe’ method for the column.

What can be learned from the data shown?

In which month did the most guests come to the hotel?

17. Show only the following columns:

hotel, arrival_date_month, stays_in_weekend_nights, stays_in_week_nights,
reservation_status

18. Continuing from the previous section, run an assignment and save the results in a
new variable called sdf

Continue to explore the sdf variable

19. Show the data in the sdf variable

20. How many dimensions does the variable sdf have?

21. What is the minimum value and what is the maximum value of the nights reserved
in the middle of the week?

22. What is the minimum value and what is the maximum value of the nights reserved
on the weekend?

23. Filter the data and show only the row with the most nights reserved in the middle
of the week. Does this row also have the maximum number of nights on a
weekend?

24. Show only the reservations where customers have checked out.

How many such reservations are there?

Page 6 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 3 - Selecting, sorting, and working
with dates

General instructions:

Each section should be performed in a separate cell

Part 1 - Data Selection

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_3

(Make sure that the notebook opens in the desired location)

3. Open a new cell and make it a Markdown cell

4. Write the following headline: Pandas - select data

5. Import the Pandas Library to the notebook

6. Import the data file 'hotel_bookings.csv', and save it in a variable named df.

Make sure you import the data properly (dates, omitting unnecessary rows). Also,
import the data in the reservation_no column so that it appears in the label index

7. Show only the first 5 lines

8. Show only the last 3 lines

9. Which label index contains the highest number of nights in the middle of the
week?

10. Show every label index row which contains the highest number of nights in the
middle of the week

11. Display the name of the hotel with the lowest number of reserved nights on a
weekend

Page 7 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

12. Show the hotel name, arrival month, number of adults, and number of nights in
the middle of the week but only for guests who arrived for a number of days
above the middle of the week average.

How many such orders are there?

13. Look at the reservation_status column, how many unique values are there in this
column?

14. Show the value which is repeated the most number of times, and the number of
times it is repeated

15. Challenge:
Run an assignment on a variable called ‘status’, on the value which is repeated
most often, by only writing code

16. Are the values in the Order Number column unique?

17. What is the highest number of children in the reservations for which the customer
arrived at the hotel?

If you did the challenge in section 15, you can use the value in the status variable

Part 2 – Sorting, Handling columns, updating data and working with

dates

18. Add a MARKDOWN cell and write the title Part 2 in it

19. Rename the following columns permanently to shorter names, as indicated:

a. Arrival_date_year → year

b. arrival_date_month → month

c. arrival_date_week_number → week

d. arrival_date_day_of_month → day

20. Display only the columns ‘Number of nights in the middle of the week’ and
‘Number of nights on the weekend’

Page 8 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

21. Because we want to analyze the data for the total number of nights, define a new
column called total_nights that will contain the number of nights in the middle of
the week plus the weekend nights for each reservation row

22. Check your answer, and see that a new column has indeed been added at the end
of the df

23. Show descriptive statistics (describe) for the Total Nights column.

What is the average number of nights?

What is the highest number of nights reserved?

24. Sort the data by hotel and then by the total amount of nights from high to low.

Set the sort so that it stays.

View the data after sorting

25. Because the hotel looks at two groups of reservations - reservations where the
guests have arrived at the hotel, and reservations which were canceled - update all
reservations with the status No-Show to Canceled

Check your answer

We will now work with new data - data on cars stopped by traffic police

26. Add a new title called PART-3

27. Import to the notebook the [Link] file. Pay attention to the date column

28. Check that the date column was accepted correctly

29. Add a column that contains the year, taken from the date

30. Add a column that contains the month, taken from the date

31. Check that the columns have been added correctly and that they contain the
correct information

Page 9 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 4 - Working with NULL Values and
Aggregative Methods

General instructions:

Each section should be performed in a separate cell

Part 1 - Working with NULL Values

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_4

(Make sure that a notebook has been opened in the desired location)

3. Import the Pandas Library to the notebook

4. Open a new cell and turn it into a Markdown cell containing the following title:
NULL values and analytics

5. Import the file called [Link] to the notebook. Note the date column

6. Delete all empty columns from the df, and determine which operation to use
How many column (s) were deleted?

7. Check how many blank values there are in the column search_type

8. Delete rows that contain NULL values in only one of the following columns:
stop_date
violation_raw
violation

9. How many blank rows are there in the column driver_age ?

10. What is the percentage of blank rows in the driver_age column?

Hint: The number of rows in the df can be calculated by the len () function

11. What is the average value in the driver_age column?

Page 10 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

12. Fill in the missing values in the driver_age column with the average value of the
column, rounded to the nearest whole number

13. What is the new average value in the driver_age column?

Did filling in the missing values make a significant change to the average?

Part 2 - Aggregative Methods

14. Open a new cell and turn it into a Markdown cell

15. Write the following headline: Aggregative methods

16. What is the total monetary amount of all tickets given to drivers (ticket_amount)

17. What is the total monetary amount of tickets given to drivers aged 20-50?

18. What is the average monetary amount of tickets given to women?

19. What is the maximum age of male drivers arrested for speeding?

20. How many women have been arrested for the traffic offense Call for Service?

21. Who had more vehicle searches conducted (search_conducted), men or women?
By what percentage more?

Page 11 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 5 - Grouping and Aggregation

General instructions:

Each section should be performed in a separate cell

Part 1 - Grouping and Aggregation

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_5
(Make sure that a notebook has been opened in the desired location)

3. Import the Pandas Library to the notebook

4. Define a Markdown cell called Grouping and aggregate

5. Import the [Link] file into df

If there is a column with a date, change the import so that the column is properly
imported.

6. Create a new column called stop_year, which will contain only the year a vehicle
was stopped by traffic police

7. Add a column called stop_month that will contain the month the vehicle was
stopped by traffic police

8. Check that the columns have been added correctly and that they contain the
correct information

9. Group the data by year, and save into a new object called g_year

Page 12 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

10. Use the g_year object and show the three years in which the most tickets were
given, and the amount of tickets given each year

Instruction:
Use g_year, and run a method on it that counts how many rows there are in each
group (year).

Sort the results in descending order, showing only the first three rows

11. Use the g_year object and show for each year:

e. How many traffic offenses were committed each year?

f. What is the average number of the tickets for that year (ticket_amount)?

g. What is the total monetary amount of tickets for that year?

12. Save the result from the operation of the previous section in an object named
temp_df.

(The object will contain a different temporary df each time we want to investigate)

Preview:

13. Look at the temp_df and answer the following questions:

Page 13 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

a. What is the total monetary amount of tickets issued in 2010?

b. What is the average monetary amount of the tickets given in 2005?

c. What is the total number of tickets issued in 2013?

14. Sort temp_df by the values in the sum column in descending order.

Which year has the highest monetary total of tickets?

15. Sort temp_df by the values in the mean column in descending order.

Which year has the lowest average amount of tickets?

16. Use temp_df, and calculate what is the average of the sum of the tickets of all the
months?

Save the answer into a variable called mean_years_ticket

Hint: run an average calculation on the total amount of tickets for each month

17. Filter the data in temp_df, and display the years in which the monetary amount of
tickets was higher than the average amount of tickets (mean_years_ticket)

18. Create a new df named yearly_ticket_data, which contains the total monetary
amount of tickets given each year.

Instruction:
Use the g_year object (the data is grouped by year) to create a new df named
yearly_ticket_data, which contains the monetary sum of the tickets.

Remember that in order to create a new df (rather than a series), the

[Link] () function must be used

19. Add to a new df (yearly_ticket_data) a column named prev_month that will contain
the total monetary amount of tickets given in the previous month

Hint: shift method

Page 14 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

20. Add to the new df (yearly_ticket_data) a column called diff_last_year that will
contain the increase or decrease between the previous month and the current
month. Round out the result.

Hint: (curr_month - prev month) / prev_month * 100

Note that since we have a NaN value, there will still be displayed results with one
digit after the decimal.

21. Using the yearly_ticket_data, show descriptive statistics for the ticket_amount
column

22. Group the traffic ticket data for speeding violations by gender.

Is there a gender that has received significantly more speeding tickets than the
other gender?

23. Group traffic offense data by year and month and save in a variable named
g_month

24. Show the ten months (and the year in which each month is) in which there are the
most traffic offenses

Part 2 - Pivot Table

1. Create a pivot table showing the distribution of traffic offenses by gender.

(That is, create a pivot table that will have traffic offenses listed in the rows, and
gender in the columns, and the table itself will have the amount of traffic offenses)

2. Show the distribution of traffic offenses by gender for 2013 and later

(That is, a table with traffic offenses listed in the rows, while the columns will have
the last three years, where for each year there are two columns: men / women. The
data in the table will contain the number of offenses)

3. For 2013 and later, how many men / women were arrested (is_arrested) after
committing a traffic offense?

Save the result within a variable called temp_df

Page 15 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

4. Use temp_df and calculate what percentage were men arrested (is_arrested) more
than women in 2015

Reminder: male_arrested - female_arrested / female_arrested * 100

Note: You can calculate the entire df, and look only at the appropriate row in the
result

5. Create a pivot table that shows for 2013 and later the distribution of traffic
offenses for each year and gender but only for arrested drivers (is_arrested).
Save the result within a variable called temp_df

6. Use temp_df and show only the traffic offenses (rows) in which in one of the years
(2013-2015) men committed 15 or more traffic offenses

Preview:

7. Add a Markdown cell called Analytic methods

8. Create a pivot table only for the 2015 data that shows the monthly distribution of
the number of tickets by gender.
Save the result within a variable called temp_df

9. Use temp_db and apply the cumulative sum method to display the amount of
tickets up to (and including) the month in the row

What was the amount of tickets for men and the amount for women from the
beginning of 2015 up to and including June 2015?

Page 16 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

10. Use temp_db, and rank the months by the amount of tickets given

Use a dense ranking so that months with the same rank get the same number, and
the month in the subsequent ranking will get the number which follows

a. To make it easier for you, you can run a ranking once for the men’s data and
once for the women

b. For both genders, what is the month with the lowest number of rankings?

c. For both genders, what is the month with the highest number of rankings?

Page 17 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 6 - Concatenation and Merging
DataFrames

General instructions:

Each section should be performed in a separate cell

Part 1 - Grouping and Aggregation

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_6
(Make sure that a notebook has been opened in the desired location)

3. Import the Pandas Library to the notebook

4. Add a Markdown cell with the title: Concatenate

In this section we will look at the daily grocery shopping list of three families

5. From the "Data" folder, import the following files into DataFrame objects, and
display their contents:
(These files contain each family's daily shopping list)

• Family_1_groceries >> into a variable named df1

• Family_2_groceries >> into a variable named df2

• Family_3_groceries >> into a variable named df3

Reminder: If you want to import from a subfolder, the file name should be:
'subfolder_name / file_name.csv'

6. Merge the shopping data of the three families (df1, df2, df3) into a new DataFrame
called shop_df, which will contain the complete shopping list of all the families.

7. Because the indices of the rows are in disarray, please re-index the rows anew.

Page 18 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

8. How many purchase rows did the three families make on the day documented in
the data?

9. Which families purchased item number 1011 or 1000

If you need to look for a method that deletes duplicate values, search the web for
how to do this

10. What is the best-selling item, and how many times was it sold?

11. Which family purchased the most items, how many times did the family shop?

12. Which family bought the most items if you don’t include the sales for items 1000 &
1011.

13. How many times was item number 1003 purchased?

Part 2 - Merging DataFrames

1. Add a Markdown cell containing the header: Merge

2. From the "Data" folder, import the Groceries file into a variable named gr_df

3. Merge the information from gr_df and shop_df into a new variable called sales, so
that for each row in shop_df, the product description and price will also be added.

4. Delete repetitive columns, if there are any.

The Sales variable will contain the following columns:

• Customer number

• Product Code

• Description

• Price

5. What is the total purchase amount for all of the families?

6. What is the purchase amount only for Family 1?

7. What is the amount of the most expensive item purchased by family number 2?

Page 19 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

8. What is the description of the most expensive item purchased by family number
2?

9. Challenge question:
What item/s has/have been purchased the most times?

10. Use Group and Aggregate of the data to display a "Product Sales Report" that will
contain the following columns:

• Product code

• Quantity sold

• Total sale amount

Sort the display by total price from highest to lowest

Preview:

We will now look at the products in the product table, in order to understand
which are the best-selling products and for which products there is no demand

11. Add a Markdown cell that contains a subtitle: Product analysis

12. Merge the information from gr_df and shop_df into a new variable called
products, so that for each row in gr_df, the product's sales data will be added - if
there were any (that is, if there were no sales for the product, the product data will
appear without sales data, and if the product had more than one sale, a row will
appear for each sales row)

Page 20 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

13. Are there any products that have never been purchased?
If so, show the product code, description, and price

14. Use Group and Aggregate of the data to display a "Product Sales Report" that will
contain the following columns:

• Product code

• Quantity sold

• Total sale amount

Sort the display by total price from highest to lowest

Preview:

15. Continuing from the previous section, what is the difference between this report
and the report in section 10?

When do you think it would be worthwhile to use each of these reports?

Page 21 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 7 - Visualization

General instructions:

Each section should be completed in a separate cell

Part 1 - Familiarity with the work environment and use of operators

1. Open a new desktop folder named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_7
(Make sure that a notebook has been opened in the desired location)

3. Add a Markdown cell with the title: General

4. Import the Pandas library and [Link] to the notebook

5. Import the [Link] file into df

If there is a column with a date, adjust the import so that the column is imported
correctly.

6. Create a new column called stop_year, which will contain only the year the vehicle
was stopped by the police.

7. Add a column called stop_month which will contain the month the vehicle was
stopped by the police.

8. Add a column called stop_day_of_week which will contain the day of the week the
vehicle was stopped by the police.

Reminder: series [Link] (day 0 = Monday, day 6 = Sunday)

9. View the first three rows of data from the df, and check that the columns you
added have been added correctly

10. Add a Markdown cell containing the title: matplotlib

Page 22 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

11. Use Group and Aggregate to view the distribution of traffic offenses committed in
2010 for each day of the week

Hint: Data filtering + group by

12. Continuing from the previous question, change the answer you got from the data
into a column chart type.
Add a title to the chart, and change the colors of the bars

13. Continuing from the previous question, copy the code you wrote down into a new
cell, and add to the display a new series of the 2015 data.

The series should be of the line chart type, with markers in the shape you choose
and a line in black

Sample Preview:

14. Based on the chart from the previous question, answer the following questions:

a. On which day are there the most vehicle stops by the traffic police?

b. Examine and compare the data for 2010 and 2015 - has the situation on the
roads improved?

Express your opinion. Would you need to continue researching the data in
order to express a more accurate opinion?

If so, list the questions you would like to explore (just list questions, do not
solve them)

Page 23 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

15. We will now look at the Speeding Offense data:

Create a pivot table that shows for each year the distribution of traffic offenses
(rows) classified as Speeding by Gender (columns)

Pivot table example:

16. Continuing from the previous question, create a Stacked bar chart which shows
the distribution of the speeding offenses over the years by gender.

a. Add a title and an appropriate X-axis title

b. Change the colors of the chart

c. Add a border to the columns

d. Expand the chart to size (10,5)

e. Change the series titles to Female and Male respectively

Page 24 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

f. Preview example:

17. Create a pie chart that shows the distribution percentage of the number of times a
vehicle was searched (search_conducted column) compared to the number of
times that a search was not conducted

Design the chart as you wish

Hint: value_counts

Preview example:

Page 25 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Part 2 - Seaborn

18. Add a Markdown cell containing the title: Seaborn

19. Import the seaborn library

20. Copy the following code to the notebook, and run it:

[Link](figsize=(15,5))
[Link](data=df, x='driver_age', y='ticket_amount', hue='driver_gender')

What does the chart show us?

What can be learned from it?

21. Use countplot to create a chart showing the distribution of traffic offenses
(violation column) by type of traffic offense.

Design the chart and add the following elements:

a. Enlarge the size of the chart to (12,6)

(Note that the order of the rows is important ... If the display does not
change, try changing the position of the line of code that defines the size of
the chart)

b. Add a title to the chart, and set the font size of the title to 20

c. Save the chart into a variable called splot

22. Continuing from the previous question, add the following line to the end of the
code of the chart ... What has changed?

plt.bar_label ([Link] [0], size = 12)

Page 26 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

23. Copy the code of the previous question, and split it into two series according to
the driver's gender.

Hint 1:
Parameter hue

Hint 2:
In order for tags with the data values to also appear in the additional series, add
the following line, which defines the code to add the tags to the second series as
well:

plt.bar_label([Link][1],size=12)

Preview example:

Page 27 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 8 - Integration with MSSQL and
Excel

General instructions:

Each section should be completed in a separate cell

Part 1 - Integration with MSSQL

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_8
(Make sure that a notebook has been opened in the desired location)

3. Import the Pandas Library to the notebook

4. Define a Markdown cell with a subtitle: Integration with MSSQL

5. Import the pyodbc directory

6. From the sqlalchemy library import the create_engine module

7. Set a variable named conn, for the SQL server connection type, and set the
connection properties for your computer.

Page 28 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

8. Open the MSSQL Management Studio software, and write a query that displays
the following data from the [Link] table:

a. Entity Code (BusinessEntityID)

b. First name (FirstName)

c. Last name (LastName)

9. Define a variable named ppl_names,

Set the query results so that they show in this variable.

Note - it is necessary to define the query and the connection properties. Use this
picture for help:

10. View the contents of the ppl_names variable (that is, the results of the query
above)

11. Just looking at what was printed in the previous section - how many records did
you get?

12. Show how many times each of the first names are repeated

13. Continuing from the previous section, show only the 10 most common first names,
and the number of occurrences of each

14. Go back to the MSSQL Management Studio, and write a query that shows the
2013 data only, as detailed below.

Extract the data by connecting (JOIN) between the following tables:

Order Title Table - [Link]
Order Details Table - [Link]
Product table - [Link]

Show data:

a. Order Number (SalesOrderID)

b. Order Date (OrderDate)

Page 29 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

c. Each day of the week of the orders (Sunday, Monday, Tuesday ...
Hint: DATEPART)

d. Product ID (ProductID)

e. Order Quantity (OrderQty)

f. Total per order line (LineTotal)

g. Product Subcategory (ProductSubcategoryID)

h. Product color

15. Enter the results of the query into a variable called sales

16. View the query results you received, and check that the number of rows is the
same as the number of rows you got when running MSSQL Management Studio

17. Group sales data by color (Color), and for each color display the number of
orders, the number of items ordered, and the total number of orders.

Hint: groupby with agg

18. Continuing from the previous section, sort the data by total orders from highest to
lowest.

Which color has the highest total income?

Which color has the lowest total income?

Hint: After agg method, add sort_values

Page 30 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

19. Analyze the sales data, and show for each sub-category how many items were
purchased for each color.
Add the total sold items to each color and subcategory (Use the Margins = True
variable)

Sample partial result:

Hint: Pivot_table

20. Close the SQL connection

Part 2 - Integration with Excel

21. Define a Markdown cell with a title: Integration with Excel

22. The following libraries will be used to work with charts and integration with Excel:

a. import [Link] as plt

b. import seaborn as sns

c. import openpyxl

d. from [Link] import dataframe_to_rows

e. %matplotlib inline

23. Import from the seaborn library the dataset called taxis into the df variable, by
running the following line of code:
df = sns.load_dataset ('taxis')

24. View the contents of the df variable

Page 31 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

25. Go through the data and understand the meaning of each column:

a. Pickup - Date and time when passenger boards

b. Dropoff - Date and time of end of the trip

c. Passengers - Number of passengers

d. Distance - Driving distance

e. Fare - Cost of ride

f. Tip - Amount of tip given to the driver

g. Tolls - Tolls paid by the passenger

h. Total - Total to be paid

i. Color - Taxi color

j. Payment - Type of payment

k. pickup_zone - Pickup zone

l. dropoff_zone - Passenger drop off zone

m. pickup_borough - Pickup borough

n. dropoff_borough - The borough where the passenger got off

26. Set up a new Workbook variable called wb

27. Activate the worksheet that is created automatically when you create a new
workbook

28. Rename the active worksheet to: 'Backup full data'

29. Add in the active sheet all the content contained in the df variable

Note that there are two ways to put information into a worksheet. One is suitable
for a processed df (for example, a pivot table), and another way is for raw df data

Also, make sure that you include the column headings in the worksheet

30. Change the tab color (tabColor) of the worksheet to black (000000)

31. Add password protection to this worksheet so that the data can not be modified.
Password: 1234

Page 32 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

32. Add a new worksheet called 'Pickup dist chart'

33. Check which columns do not have NULL values

34. Create a pivot table that shows for each borough (pickup_borough) how many
pick-ups there were for each color of cab (color). Save the pivot table inside a
variable called piv

Remember to use a column that does not have NULL values

35. View the contents of the variable piv

36. Create from the variable piv a horizontal bar chart, add a suitable title, and color
the series in the chart according to the color of the taxi

Continue to design the chart as you wish.

Save the chart inside a variable called plot

Page 33 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

37. View the chart inside the plot variable
Example:

38. Add the image into the active worksheet

39. Create a new worksheet called 'Pickup dist data'

40. Add the contents of the pivot table to the active worksheet

41. Save the workbook as an excel file named 'Ex_sol_8.xlsx'

Page 34 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

Lesson 9 - Conditions, Loops and
Functions

General instructions:

Each section should be completed in a separate cell

Part 1 - Conditions (IF Block)

1. Open a new folder on your desktop named Python_Class

2. Go to the folder you opened, and create a new notebook called Ex_sol_9
(Make sure that a notebook has been opened in the desired location)

3. Add a Markdown cell containing the title: Conditions

4. Manually calculate the following (without copying and running this in the jupyter
notebook), what is the answer for this code snippet:

x=2
if x == 3:
print ("x is 3")
elif x < 3:
print ("x is less than 3")
else:
print ("x is more than 3")

5. Define a variable named my_num, and update it with any number you choose

Write a conditional block so that:

If my_num is less than 1000, "Not Expensive" will be printed
If between 1000 and 1500, "Expensive" is printed
And if above 1500, "Very Expensive" will be printed

6. Define a variable named chosen_food, and insert your favorite food (insert = place
a value into the variable )

7. Write a conditional block that will check the value in the chosen_food variable:

Page 35 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

a. If the name of the dish is fewer than 5 characters long: Print the name of the
dish 5 times in the same line

b. If the name of the dish is 5-10 characters in total, print: "Let's eat!"

c. If the name of the dish is over 10 characters, type: "Next time"

8. Define two variables:

a. full_name - Update the value with your full name

b. birth_year - Update the value with your year of birth

9. Use conditions to print the following output:

a. If your full name is up to 10 characters (but not including) - Print your full
name from the end to the beginning (backwards)

For example: if your full name is Gal Gadot

>> todaG laG

b. If your full name is between 10 characters (including) and 14 characters (but

not including) - Print: <full name> was born in <birth_year>

For example (if the full name is Frida Kahlo)

>> Frida Kahlo was born in 1907

c. If your full name is longer than 14 characters, print your name by skipping
every other character (that is, the first character will be printed, the next
character will not be printed)

For example (if the full name is Katherine Johnsons)

>> KteieJhsn

Part 2 - Loops: For, while

10. Open a new cell and turn it into a Markdown cell, and place the following main
header: Loops

11. Define a variable called: name. Put your full name in it

Page 36 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

12. Using a loop, print out your name as follows (each row with a different letter, and
using only uppercase letters)

for example:
T
A
L
I
A

M
O
R
C
H
I

13. The Fibonacci series is the series whose first two numbers are 0, 1 and each
number thereafter is equal to the sum of its two predecessors. Therefore, the first
several numbers of the series are:
0, 1, 1, 2, 3, 5, 8, 13, 21…

Write a code that goes uses the numbers from 0 to 50, and produces a Fibonacci
series

Part 3 - User Functions

14. Define a Markdown cell named User function

15. Create a function called multiple, which receives a list, and doubles all the items in
the list

16. Create a function that receives a list and returns a list with only the even numbers

17. Import the Pandas Library to the notebook

18. Import the [Link] file into the df

Only import the columns driver_gender, driver_age, and violation

Hint: usecols parameter

Page 37 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

19. Define a user function called get_age_category, which accepts an integer
representing the age (for example 18, 26, 68) and returns the age group
according to the following rules:

a. Less than 18 → ‘0 to 18’

b. Between 18 (inclusive) and 30 (not inclusive) → ‘18 to 30’

c. Between 30 (inclusive) and 42 (not inclusive) → ‘30 to 42’

d. Between 42 (inclusive) and 54 (not inclusive) → ‘42 to 54’

e. Between 54 (inclusive) and 66 (not inclusive) → ‘54 to 66’

f. 66 (inclusive) and above → ‘66 +’

20. Check if the function is working properly by calling to the function and entering
appropriate arguments (e.g. 5, 18, 70)

If the function returns correct results, proceed to the next section.

If the function does not work properly - fix it and check again

21. Add a new column to the table called age_category, which will contain the age
category of the person who was stopped, by running the get_age_category
function on the driver_age column

Hint: apply

22. Calculate how many traffic offenses are associated with each age category
(age_category)

23. Continuing from the previous section, create a bar chart that shows the
distribution of traffic offenses by age category (age_category).

Note that the chart must be sorted by age category (age_category)

24. Continuing from the previous section, what is the age category (age_category) in
which most traffic offenses were committed?

25. Define a user function called get_violation_severity, which accepts the traffic
offense committed (violation), and returns its severity according to the following
categories:

a. 'Speeding' or 'Seat belt' → 1-Serious offense

b. 'Registration/plates' or 'Equipment' → 2-Medium offense

Page 38 – Data Analysis Python Class booklet

 T-M

Python Unit – Class practice booklet

c. Every other violation → 3-Minor offense

26. Check if the function is working properly by calling to the function and sending
appropriate arguments (for example 'Speeding', 'Equipment', 'Moving violation')

If the function returns correct results, proceed to the next section.

If the function does not work properly - fix it and check again

27. Add a new column to the table called violation_severity, which will contain the
severity category of the traffic offense by running the get_violation_severity
function on the violation column

Hint: apply

28. Create a pie chart that shows the distribution of traffic offenses according to the
severity category of the traffic offense (violation_severity).

Note that the chart must be sorted by traffic offense severity category
(violation_severity)

29. Continuing from the previous section, what is the category of traffic offense
severity (violation_severity) in which the most traffic offenses were committed?

30. Challenge: Create a function that implements the split method

a. The signature of the function is:

def split(string, by):

b. Do not use the method:

[Link]()

c. You can use all the topics learned so far

d. To check this, call to the function you created and check your result:
split ('[Link].!.!.!', '.')

31. Create a function that receives a list of numbers and returns True if all the numbers
are different from each other, otherwise it returns False

Page 39 – Data Analysis Python Class booklet