## Pandas: groupby

This chapter of our Pandas tutorial deals with an extremely important functionality, i.e. `groupby`

. It is not really complicated, but it is not obvious at first glance and is sometimes found to be difficult. Completely wrong, as we shall see. It is also very important to become familiar with 'groupby' because it can be used to solve important problems that would not be possible without it. The Pandas `groupby`

operation involves some combination of splitting the object, applying a function, and combining the results. We can split a DataFrame object into groups based on various criteria and row and column-wise, i.e. using `axis`

.

'Applying' means

- to filter the data,
- transform the data or
- aggregate the data.

`groupby`

can be applied to Pandas Series objects and DataFrame objects! We will learn to understand how it works with many small practical examples in this tutorial.

### goupby with Series

We create with the following Python program a Series object with an index of size `nvalues`

. The index will not be unique, because the strings for the index are taken from the list `fruits`

, which has less elements than `nvalues`

:

```
import pandas as pd
import numpy as np
import random
nvalues = 30
# we create random values, which will be used as the Series values:
values = np.random.randint(1, 20, (nvalues,))
fruits = ["bananas", "oranges", "apples", "clementines", "cherries", "pears"]
fruits_index = np.random.choice(fruits, (nvalues,))
s = pd.Series(values, index=fruits_index)
print(s[:10])
```

```
grouped = s.groupby(s.index)
grouped
```

We can see that we get a `SeriesGroupBy`

object, if we apply `groupby`

on the index of our series object `s`

. The result of this operation `grouped`

is iterable. In every step we get a tuple object returned, which consists of an index label and a series object. The series object is `s`

reduced to this label.

```
grouped = s.groupby(s.index)
for fruit, s_obj in grouped:
print(f"===== {fruit} =====")
print(s_obj)
```

We could have got the same result - except for the order - without using `` groupby '' with the following Python code.

```
for fruit in set(s.index):
print(f"===== {fruit} =====")
print(s[fruit])
```

We will start with a very simple DataFrame. The DataFRame has two columns one containing names `Name`

and the other one `Coffee`

contains integers which are the number of cups of coffee the person drank.

```
import pandas as pd
beverages = pd.DataFrame({'Name': ['Robert', 'Melinda', 'Brenda',
'Samantha', 'Melinda', 'Robert',
'Melinda', 'Brenda', 'Samantha'],
'Coffee': [3, 0, 2, 2, 0, 2, 0, 1, 3],
'Tea': [0, 4, 2, 0, 3, 0, 3, 2, 0]})
beverages
```

It's simple, and we've already seen in the previous chapters of our tutorial how to calculate the total number of coffee cups. The task is to sum a column of a DatFrame, i.e. the 'Coffee' column:

```
beverages['Coffee'].sum()
```

Let's compute now the total number of coffees and teas:

```
beverages[['Coffee', 'Tea']].sum()
```

'groupby' has not been necessary for the previous tasks. Let's have a look at our DataFrame again. We can see that some of the names appear multiple times. So it will be very interesting to see how many cups of coffee and tea each person drank in total. That means we are applying 'groupby' to the 'Name' column. Thereby we split the DatFrame. Then we apply 'sum' to the results of 'groupby':

```
res = beverages.groupby(['Name']).sum()
print(res)
```

We can see that the names are now the index of the resulting DataFrame:

```
print(res.index)
```

There is only one column left, i.e. the `Coffee`

column:

```
print(res.columns)
```

We can also calculate the average number of coffee and tea cups the persons had:

```
beverages.groupby(['Name']).mean()
```

#### Another Example

The following Python code is used to create the data, we will use in our next `groupby`

example. It is not necessary to understand the following Python code for the content following afterwards.
The module `faker`

has to be installed. In cae of an Anaconda installation this can be done by executing one of the following commands in a shell:

```
conda install -c conda-forge faker
conda install -c conda-forge/label/gcc7 faker
conda install -c conda-forge/label/cf201901 faker
conda install -c conda-forge/label/cf202003 faker
```

```
from faker import Faker
import numpy as np
from itertools import chain
fake = Faker('de_DE')
number_of_names = 10
names = []
for _ in range(number_of_names):
names.append(fake.first_name())
data = {}
workweek = ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
weekend = ("Saturday", "Sunday")
for day in chain(workweek, weekend):
data[day] = np.random.randint(0, 10, (number_of_names,))
data_df = pd.DataFrame(data, index=names)
data_df
```

```
print(names)
```

```
names = ('Ortwin', 'Mara', 'Siegrun', 'Sylvester', 'Metin', 'Adeline', 'Utz', 'Susan', 'Gisbert', 'Senol')
data = {'Monday': np.array([0, 9, 2, 3, 7, 3, 9, 2, 4, 9]),
'Tuesday': np.array([2, 6, 3, 3, 5, 5, 7, 7, 1, 0]),
'Wednesday': np.array([6, 1, 1, 9, 4, 0, 8, 6, 8, 8]),
'Thursday': np.array([1, 8, 6, 9, 9, 4, 1, 7, 3, 2]),
'Friday': np.array([3, 5, 6, 6, 5, 2, 2, 4, 6, 5]),
'Saturday': np.array([8, 4, 8, 2, 3, 9, 3, 4, 9, 7]),
'Sunday': np.array([0, 8, 7, 8, 9, 7, 2, 0, 5, 2])}
data_df = pd.DataFrame(data, index=names)
data_df
```

We will demonstrate with this DataFrame how to combine columns by a function.

```
def is_weekend(day):
if day in {'Saturday', 'Sunday'}:
return "Weekend"
else:
return "Workday"
```

```
for res_func, df in data_df.groupby(by=is_weekend, axis=1):
print(df)
```

```
data_df.groupby(by=is_weekend, axis=1).sum()
```

```
import pandas as pd
d = {"products": ["Oppilume", "Dreaker", "Lotadilo",
"Crosteron", "Wazzasoft", "Oppilume",
"Dreaker", "Lotadilo", "Wazzasoft"],
"colours": ["blue", "blue", "blue",
"green", "blue", "green",
"green", "green", "red"],
"customer_price": [2345.89, 2390.50, 1820.00,
3100.00, 1784.50, 2545.89,
2590.50, 2220.00, 2084.50],
"non_customer_price": [2445.89, 2495.50, 1980.00,
3400.00, 1921.00, 2645.89,
2655.50, 2140.00, 2190.00]}
product_prices = pd.DataFrame(d)
product_prices
```

#### Exercise 2

Calculate the sum of the price according to the colours.

#### Exercise 3

Read in the `project_times.txt`

file from the `data1`

directory. This rows of this file contain comma separated the date, the name of the programmer, the name of the project, the time the programmer spent on the project.

Calculate the time spend on all the projects per day

#### Exercise 4

Create a DateFrame containing the total times spent on a project per day by all the programmers

#### Exercise 5

Calculate the total times spent on the projects over the whole month.

#### Exercise 6

Calculate the monthly times of each programmer regardless of the projects

#### Exercise 7

Rearrange the DataFrame with a MultiIndex consisting of the date and the project names, the columns should be the programmer names and the data of the columns the time of the programmers spent on the projects.

```
time
programmer Antonie Elise Fatima Hella Mariola
date project
2020-01-01 BIRDY NaN NaN NaN 1.50 1.75
NSTAT NaN NaN 0.25 NaN 1.25
XTOR NaN NaN NaN 1.00 3.50
2020-01-02 BIRDY NaN NaN NaN 1.75 2.00
NSTAT 0.5 NaN NaN NaN 1.75
```

Replace the NaN values by 0.

```
x = product_prices.groupby("products").mean()
x
```

```
x = product_prices.groupby("colours").sum()
x
```

```
import pandas as pd
df = pd.read_csv("data1/project_times.txt", index_col=0)
df
```

```
times_per_day = df.groupby(df.index).sum()
print(times_per_day[:10])
```

```
times_per_day_project = df.groupby([df.index, 'project']).sum()
print(times_per_day_project[:10])
```

```
df.groupby(['project']).sum()
```

```
df.groupby(['programmer']).sum()
```

```
x = df.groupby([df.index, 'project', 'programmer']).sum()
x = x.unstack()
x
```

```
x = x.fillna(0)
print(x[:10])
```