Pandas, group matching rows together in new df

user_14699321 0
Viewed 58

I have the following dataframe:

date        kpi1    kpi2    kpi3    group
10/01/20    None    19      50      1
11/01/20    6       10      None    1
12/01/20    None    None    None    1
13/01/2020  None    None    None    1
14/01/2020  None    None    None    1
15/01/2020  50      None    None    1
16/01/2020  None    40      12      1
17/01/2020  None    90      77      1
18/01/2020  77      90      18      1

I need to group dates that have none in any of the kpis. so far I've been able to identify those rows:

mask = group.apply(
            lambda row:
            True if row.isnull().any()
            else False,
            axis=1
        )
group[HAS_MISSING_VALUES] = mask
group[MISSING_KPIS] = group.apply(lambda x: list(x[x.isnull()].index), axis=1)

so the group df looks like this now:

date        kpi1    kpi2    kpi3    group   has_missing_kpis    missing_kpis_list
10/01/20    None    19      50      1       TRUE                [kpi1]
11/01/20    6       10      None    1       TRUE                [kpi3]
12/01/20    None    None    None    1       TRUE                [kpi1,kpi2,kpi3]
13/01/2020  None    None    None    1       TRUE                [kpi1,kpi2,kpi3]
14/01/2020  None    None    None    1       TRUE                [kpi1,kpi2,kpi3]
15/01/2020  50      None    None    1       TRUE                [kpi2,kpi3]
16/01/2020  None    40      12      1       TRUE                [kpi1]
17/01/2020  None    90      77      1       TRUE                [kpi1]
18/01/2020  77      90      18      1       FALSE               []

now, I want to group consecutive dates that the exact same missing kpis. so the output should be something like:

start_date    end_date        missing_kpis_list   group 
10/01/2020    10/01/2020      [kpi1]              1
11/01/2020    11/01/2020      [kpi3]              1
12/01/2020    14/01/2020      [kpi1,kpi2,kpi3]    1
15/01/2020    15/01/2020      [kpi2,kpi3]         1
16/01/2020    17/01/2020      [kpi1]              1

don't know if it matters but date are consecutive and there no missing dates in the dataframe. also the kpis names in the dataframe may vary

pandas time-series dataframe python
2 Answers

You can use standard itertools.groupby for the task:

from itertools import groupby

data = []
for v, g in groupby(df.iterrows(), key=lambda row: row[1]["MISSING_KPIS"]):
    if v:
        g = list(g)
        data.append((g[0][1]["date"], g[-1][1]["date"], v, g[0][1]["group"]))

df_out = pd.DataFrame(
    data, columns=["start_date", "end_date", "missing_kpis_list", "group"]
)
print(df_out)

Prints:

   start_date    end_date   missing_kpis_list  group
0    10/01/20    10/01/20              [kpi1]      1
1    11/01/20    11/01/20              [kpi3]      1
2    12/01/20  14/01/2020  [kpi1, kpi2, kpi3]      1
3  15/01/2020  15/01/2020        [kpi2, kpi3]      1
4  16/01/2020  17/01/2020              [kpi1]      1
user_100359850

You can groupby consecutive rows in pandas using the (df.col != df.col.shift()).cumsum() approach.

df['missing_kpi_list'] = df.apply(lambda row: df.columns[row.isnull()].tolist(), axis=1)
df['has_missing_kpis'] = df['missing_kpi_list'].astype(bool)

print(
    df.loc[df.has_missing_kpis].groupby(
         (df.missing_kpi_list != df.missing_kpi_list.shift()).cumsum()
     ).agg(
              start_date=('date', 'min'),
                end_date=('date', 'max'),
        missing_kpi_list=('missing_kpi_list', 'first'),
                   group=('group', 'first')
    ).reset_index(drop=True)
)

Named Aggregation can be used to generate the final output.

+----+--------------+------------+--------------------------+---------+
|    | start_date   | end_date   | missing_kpi_list         |   group |
|----+--------------+------------+--------------------------+---------|
|  0 | 10/01/20     | 10/01/20   | ['kpi1']                 |       1 |
|  1 | 11/01/20     | 11/01/20   | ['kpi3']                 |       1 |
|  2 | 12/01/20     | 14/01/2020 | ['kpi1', 'kpi2', 'kpi3'] |       1 |
|  3 | 15/01/2020   | 15/01/2020 | ['kpi2', 'kpi3']         |       1 |
|  4 | 16/01/2020   | 17/01/2020 | ['kpi1']                 |       1 |
+----+--------------+------------+--------------------------+---------+
Admin1
Related