0

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:

sequences_colored

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.

monart
  • 129
  • 1
  • 10

2 Answers2

2

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
RunOrVeith
  • 4,487
  • 4
  • 32
  • 50
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

Vivek Kalyanarangan
  • 8,951
  • 1
  • 23
  • 42