Odtnhj

Let's assume I have a pandas DataFrame in Python which shows the name of the business unit leader for different units over time. It could look something like this

 Leader_Jan Leader_Feb Leader_Mar Leader_Apr
Unit1 Nina Nina Nina Nina
Unit2 Lena Lena NaN Lena
Unit3 Alex Maria Alex Alex
Unit4 Emilia NaN NaN NaN
Unit5 NaN Corinna Petra NaN

and can be recreated like:

import pandas as pd
import numpy as np
a = ['Nina','Nina','Nina','Nina']
b = ['Lena','Lena',np.NaN,'Lena']
c = ['Alex','Maria','Alex','Alex']
d = ['Emilia',np.NaN,np.NaN,np.NaN]
e = [np.NaN,'Corinna','Petra',np.NaN]
data = pd.DataFrame(data=[a,b,c,d,e], columns =['Leader_Jan','Leader_Feb','Leader_Mar','Leader_Apr'], index=['Unit1','Unit2','Unit3','Unit4','Unit5'])

Context: I would like to find out in which units leaders stay for very short or very long periods (in months) to later find out whether there are team conflicts in specific units of my company.

I would like to add to the dataframe the minimum and maximum values (in months) per unit of how long leaders have been there in one uninterrupted period. Because of possible interruptions (see Unit 2 and Unit 3) I cannot just use value_counts for the different names in each row. I rather need to find the length of sequences of different leader names separated by NaN values and other names. To see what I consider a sequence check the different colors in this photo:

As you might see, an interruption as in Unit 2 and 3 should result in multiple lengths of stay. The amount of NaN-months in a sequence should not be counted.

The result should look like:

 Leader_Jan Leader_Feb Leader_Mar Leader_Apr Min_length_of_stay_leaders 
Unit1 Nina Nina Nina Nina 4 
Unit2 Lena Lena NaN Lena 1 
Unit3 Alex Maria Alex Alex 1 
Unit4 Emilia NaN NaN NaN 1 
Unit5 NaN Corinna Petra NaN 1 

 Max_length_of_stay_leaders 
Unit1 4 
Unit2 2 
Unit3 2 
Unit4 1 
Unit5 1 

I know this might be rather complicated to follow, but I would love any kind of help/tips, etc. because I am a little lost here.

This is actually quite easy using itertools.groupby:

from itertools import groupby

def min_max_durations(row):
 # the group object consumes the iterator, but we don't care about the values 
 # so we just sum "1" to get the length.
 # Taken from https://stackoverflow.com/questions/44490079/how-to-turn-an-itertools-grouper-object-into-a-list
 durations = [sum(1 for _ in group) for key, group in groupby(row) if not isinstance(key, float)]
 return min(durations), max(durations)

data["min_lengths_of_stay"], data["max_lengths_of_stay"] = zip(*data.apply(min_max_durations, axis=1))

The instance check for float is just a quick way to remove the NaN value from counting here, you can make this arbitrarily complex.

This outputs the correct results (note that copy pasting your reproduction code has 3 "Alex" entries in Unit3, unlike your example)

 Leader_Jan Leader_Feb Leader_Mar Leader_Apr min_lengths_of_stay 
Unit1 Nina Nina Nina Nina 4 
Unit2 Lena Lena NaN Lena 1 
Unit3 Maria Alex Alex Alex 1 
Unit4 Emilia NaN NaN NaN 1 
Unit5 NaN Corinna Petra NaN 1 
 max_lengths_of_stay 
Unit1 4 
Unit2 2 
Unit3 3 
Unit4 1 
Unit5 1 

This should get you started -

temp = df.apply(lambda x: x.groupby((x != x.shift()).cumsum()).cumcount()+1, axis=1)

mins = temp.min(1)
maxs = temp.max(1)
mask = temp.apply(lambda x: x.is_monotonic_increasing and x.is_unique, axis=1)
mins.loc[mask] = maxs.loc[mask]
mins.name='Min_length_of_stay_leaders'
maxs.name='Max_length_of_stay_leaders'

df.join(mins).join(maxs)

Output

 Leader_Jan Leader_Feb Leader_Mar Leader_Apr Min_length_of_stay_leaders 
Unit1 Nina Nina Nina Nina 4 
Unit2 Lena Lena NaN Lena 1 
Unit3 Alex Maria Alex Alex 1 
Unit4 Emilia NaN NaN NaN 1 
Unit5 NaN Corinna Petra NaN 1 

 Max_length_of_stay_leaders 
Unit1 4 
Unit2 2 
Unit3 2 
Unit4 1 
Unit5 1 

Explanation

temp = df.apply(lambda x: x.groupby((x != x.shift()).cumsum()).cumcount()+1, axis=1)

This gives you a consecutive count of leaders grouped by their name -

 Leader_Jan Leader_Feb Leader_Mar Leader_Apr
Unit1 1 2 3 4
Unit2 1 2 1 1
Unit3 1 1 1 2
Unit4 1 1 1 1
Unit5 1 1 1 1

Simply extract the max and min -

mins = temp.min(1)
maxs = temp.max(1)

The problem then comes for Nina - she did her tenure throughout, so in that case the min needs to be 4 too.

So just for that edge case, the mask object detects strictly monotonically increasing series and replaces with the max if that is the case.

I am still not sure whether it will work for all cases or not, so please check