Apply list of filters to pandas dataframe

Gabriel Salazar 0
Viewed 42

I need to apply multiple filters to a pandas DataFrame in order to get various time series out of the filtered results.

The DataFrame looks like this:

         Date  Country    Type    Channel  Metric1  Metric2  Metric3
0  2021-01-01  Ecuador  A_type  channel_1      1.0      2.3      1.3
1  2021-01-02  Ecuador  A_type  channel_1      2.0      4.5      1.2
2  2021-01-03  Ecuador  B_type  channel_1      3.0      4.3      3.5
...

I need to get some time series from the values of the Metric columns + Date, so in order to do so, i have the list of filters that look like this:

   Country    Type    Channel   Metric
0  Ecuador  A_type  channel_1  Metric1
1  Ecuador  A_type  channel_1  Metric2
2  Ecuador  A_type  channel_1  Metric3
3   Brazil  A_type  channel_1  Metric1
4   Brazil  A_type  channel_1  Metric2
...

And so on. My thought process is to loop through the dataframe, apply the filters from the list and append the obtained series to a list of series. The main part im stuck on is the dataframe filtering based on the list of filters. Can someone point me towards a direction here?

Thanks!

dataframe filter pandas python
1 Answers

I don't know what result you exactly expect.

But you can always do

mask1 = (df["Coutry"]  == filter_row["Coutry"])
mask2 = (df["Type"]    == filter_row["Type"])
mask3 = (df["Channel"] == filter_row["Channel"])

column = filter_row["Metric"]

data = df[ mask1 & mask2 & mask3 ][column]

And you have to use for-loop to run it for ever row in filters.

Minimal working example

data = '''Date  Country    Type    Channel  Metric1  Metric2  Metric3
2021-01-01  Ecuador  A_type  channel_1      1.0      2.3      1.3
2021-01-02  Ecuador  A_type  channel_1      2.0      4.5      1.2
2021-01-03  Ecuador  B_type  channel_1      3.0      4.3      3.5'''

data_filters = '''Country    Type    Channel   Metric
Ecuador  A_type  channel_1  Metric1
Ecuador  A_type  channel_1  Metric2
Ecuador  A_type  channel_1  Metric3
Brazil  A_type  channel_1  Metric1
Brazil  A_type  channel_1  Metric2'''

import pandas as pd
import io

df = pd.read_csv(io.StringIO(data), sep='\s+')
#print(df)

filters = pd.read_csv(io.StringIO(data_filters), sep='\s+')
#print(filters)

for index, row in filters.iterrows():
    print('>>> filter:', row['Country'], row['Type'], row['Channel'], row['Metric'])
    mask1 = (df['Country'] == row['Country'])
    mask2 = (df['Type'] == row['Type'])    
    mask3 = (df['Channel'] == row['Channel'])
    column = row['Metric']
    print(df[ mask1 & mask2 & mask3 ][column])
    print('---')

Result:

>>> filter: Ecuador A_type channel_1 Metric1
0    1.0
1    2.0
Name: Metric1, dtype: float64
---
>>> filter: Ecuador A_type channel_1 Metric2
0    2.3
1    4.5
Name: Metric2, dtype: float64
---
>>> filter: Ecuador A_type channel_1 Metric3
0    1.3
1    1.2
Name: Metric3, dtype: float64
---
>>> filter: Brazil A_type channel_1 Metric1
Series([], Name: Metric1, dtype: float64)
---
>>> filter: Brazil A_type channel_1 Metric2
Series([], Name: Metric2, dtype: float64)
---
furas0
Related