Skip to main content
`Ctrl`+`K`

# Advanced Pandas

Before solving these exercises you should have read Advanced Indexing, Dates and Times, Categorical Data, and Restructuring Data.

    import pandas as pd
    import numpy as np

## Grades

Use the following code to create a series containing student IDs as index and points in exam as data:

    rng = np.random.default_rng(123)

    n_students = 20
    max_points = 40

    student_ids = rng.integers(20000, 25000, n_students)
    points = np.floor(rng.normal(0.6 * max_points, 0.2 * max_points, n_students))
    points = points.clip(0, max_points).astype(np.int8)
    exam_points = pd.Series(points, index=student_ids)

    exam_points

### Understand the Code

What do the two `points = ...` lines in the above code do in detail?

Solution:

    # your answer

### Points to Grades

Add a column to the series (resulting in a data frame) containing corresponding grades. Conversion from points to grade is as follows:

percent of points | grade
--- | ---
less than 40 | 5.0
at least 40 | 4.0
at least 54 | 3.7
at least 60 | 3.3
at least 68 | 3.0
at least 74 | 2.7
at least 80 | 2.3
at least 84 | 2.0
at least 88 | 1.7
at least 92 | 1.3
at least 96 | 1.0

Use `pd.cut` to get the grades. Result should look as follows:

           points grade
    id
    20077      24   3.3
    23411      11   5.0
    22964      33   2.3
    ...

The `'grade'` column should be of categorical type.

Solution:

    # your solution

### Mean Grade

Get the mean grade for all students who passed the exam (grade better than 5).

Solution:

    # your solution

## Cafeteria

For these exercises we use the dataset obtained in the Cafeteria project.

    data = pd.read_csv('meals.csv', names=['date', 'category', 'name', 'students', 'staff', 'guests'])

    data

### Dates

Convert `'date'` column to `Timestamp`. Hint: the `pd.to_datetime` function is very flexible.

Solution:

    # your solution

### Categories

Set type of `'category'` column to categorical. Print all categories.

Solution:

    # your solution

### Mean Price per Category

Get mean students/staff/guests prices per category. Sort results by students price.

Solution:

    # your solution

### Prices over Time

Drop rows with `nan` or `0.0` prices. Then get minimum, average, maxmium students prices per day. Create a data frame with three columns `'min'`, `'mean'` `'max'` and `DatetimeIndex`. Call the data frame’s `plot` method (works without arguments) to visualize the results.

Solution:

    # your solution

### Restructuring

Create a data frame showing prices only, no meal names. Use dates for row index. Column index shall be multi-level with first level showing the category and second level showing the price level (students/staff/guests).

Solution:

    # your solution