---
jupytext:
  formats: content_md///md:myst,content///ipynb
  text_representation:
    extension: .md
    format_name: myst
    format_version: 0.13
    jupytext_version: 1.13.8
kernelspec:
  display_name: Python 3 (ipykernel)
  language: python
  name: python3
---

(exercises:managing-data:advanced-pandas)=
# Advanced Pandas

Before solving these exercises you should have read [](managing-data:pandas:advanced-indexing), [](managing-data:pandas:dates-times), [](managing-data:pandas:categorical-data), and [](managing-data:pandas:restructuring-data).

```{code-cell} ipython3
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:

```{code-cell} ipython3
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:**

```{code-cell} ipython3
# 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`](https://pandas.pydata.org/docs/reference/api/pandas.cut.html) 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:**

```{code-cell} ipython3
# your solution
```

### Mean Grade

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

**Solution:**

```{code-cell} ipython3
# your solution
```

## Cafeteria

For these exercises we use the dataset obtained in the [](projects:cafeteria) project.

```{code-cell} ipython3
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`](https://pandas.pydata.org/docs/reference/api/pandas.to_datetime.html) function is very flexible.

**Solution:**

```{code-cell} ipython3
# your solution
```

### Categories

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

**Solution:**

```{code-cell} ipython3
# your solution
```

### Mean Price per Category

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

**Solution:**

```{code-cell} ipython3
# 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`](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.plot.html) method (works without arguments) to visualize the results.

**Solution:**

```{code-cell} ipython3
# 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:**

```{code-cell} ipython3
# your solution
```
