Pandas: Combine consecutive months having same values in other column

Question

I have monthly performance of students for several years for all subjects. DataFrame has following columns: [Name, Subject, Month, Year, Marks] as given in following image 1:

  Name  Month  Year Subject  Marks
0    A      1  2022    Math     80
1    A      2  2022    Math     80
2    A      3  2022    Math     80
3    A      4  2022    Math     70
4    A      5  2022    Math     80
5    A      6  2022    Math     80
6    A      7  2022    Math     80

Now I want combine consecutive rows having same performance for given student and subject. As given in following image:

  Name Subject  Marks    Time_Period
0    A    Math     80  1.2022-3.2022
1    A    Math     70  4.2022-4.2022
2    A    Math     80  5.2022-7.2022

I have tried to group dataframe and extract Min/Max(Month) and Min/Max(Year). But it will give wrong result if student has different performance in month in between.

You should provide your data as text, not images – mozway Jan 25 '23 at 08:31 — mozway, Jan 25 '23 at 08:31

mozway · Accepted Answer · 2023-01-25T11:59:59.520

You can use a custom groupby.agg:

# identify consecutive marks
group = df['Marks'].ne(df['Marks'].shift()).cumsum()

out = (df.assign(Time_Period=lambda d: d['Month'].astype(str)
                                  +'.'+d['Year'].astype(str))
         .groupby(['Name', 'Subject', 'Marks', group],
                  sort=False, as_index=False)
         ['Time_Period']
         .agg(lambda x: '-'.join([x.iloc[0], x.iloc[-1]]))
      )

If you want to start a new group when a month is missing:

# identify consecutive marks
group1 = df['Marks'].ne(df['Marks'].shift()).cumsum()

# group by successive months
group2 = df.groupby('Year')['Month'].diff().ne(1)

out = (df.assign(Time_Period=lambda d: d['Month'].astype(str)
                                  +'.'+d['Year'].astype(str))
         .groupby(['Name', 'Subject', 'Marks', group1, group2],
                  sort=False, as_index=False)
         ['Time_Period']
         .agg(lambda x: '-'.join([x.iloc[0], x.iloc[-1]]))
      )

Output:

  Name Subject  Marks    Time_Period
0    A    Math     80  1.2022-3.2022
1    A    Math     70  4.2022-4.2022
2    A    Math     80  5.2022-7.2022

It shows wrong calculation if one of the month is missing. Let's say I have months 1,2,4,5 with same Marks. In this case it will return 1.2022-5.2022 instead of two seperate rows. Could you please have a look? @mozway — RVD, Jan 25 '23 at 11:28

Pandas: Combine consecutive months having same values in other column

1 Answers1