[FIXED] Shift entities of a row to the right based on filtering?

Issue

if found a great solution from @mozway to shift entities to the right of a column here:

Python / Pandas: Shift entities of a row to the right (end)

If there is a NaN in the last column (number of columns can vary), I want to right shift all the columns to the end of the data frame such that it then looks like below:

The solution works fine:

import numpy as np
import pandas as pd

data = {
    'Customer': ['A', 'B', 'C'],
    'Date1': [10, 20, 30],
    'Date2': [40, 50, np.nan],
    'Date3': [np.nan, np.nan, np.nan],
    'Date4': [60, np.nan, np.nan]
}

df = pd.DataFrame(data)

out = (df
   .set_index('Customer', append=True)
   .pipe(lambda d: d.mask(d.iloc[:, -1].isna(),
                          d.transform(lambda x : sorted(x, key=pd.notnull), axis=1)
                         )
        )
   .reset_index('Customer')
)

But is there a way with the existing solution to implement a filter, that only the rows are shifted, which contain e.g. the word main in Customer? It shall look like this: Cucumber.main was shifted, because of main in the word. Strawberry.fruit not, because main is missing.

Desired Output:

Solution

You can add new condition to DataFrame.mask with Series.str.contains:

out = (df
   .set_index('Customer', append=True)
   .pipe(lambda d: d.mask(d.iloc[:, -1].isna() &
                          df['Customer'].str.contains('main').to_numpy(),
                          d.transform(lambda x : sorted(x, key=pd.notnull), axis=1)
                         )
        )
   .reset_index('Customer')
)

Or use level of MultiIndex by Index.get_level_values:

out = (df
   .set_index('Customer', append=True)
   .pipe(lambda d: d.mask(d.iloc[:, -1].isna() & 
                          d.index.get_level_values('Customer').str.contains('main'),
                          d.transform(lambda x : sorted(x, key=pd.notnull), axis=1)
                         )
        )
   .reset_index('Customer')
)

Answered By - jezrael