Partition a column based on missing values

Question

How would I go about partitioning a column based on missing value in python.

I have have the following table in a dataframe:

Store      Bag 
Alberts    ClothBag
Vons       KateSpade
Ralphs     GroceryBag1
Na         apple
Na         pear
Na         staples
Kmart      ShoppingList
Na         beachball
Na         milk
QuikEmart  List5 
Na         Duff
Na         Donuts

And based on the NA's or any other way, how would I reproduce the following table:

Store      Bag              Item
Alberts    ClothBag         ClothBag
Vons       KateSpade        KateSpade
Ralphs     GroceryBag1      apple
Na         GroceryBag1      pear 
Na         GroceryBag1      staples     
Kmart      ShoppingList     beachball
Na         ShoppingList     milk
QuikEmart  List5            Duff
Na         List5            Dounuts

If there are no Na's beneath the store then the item column will assume the name of the Bag, as shown in the first two columns.

piRSquared · Answer 1 · 2017-09-30T08:21:26.363

def f(d):
    if len(d) > 1:
        r = d.iloc[1:]
        r['Item'] = r['Bag']
        r['Bag'] = d.Bag.iat[0]
        r['Store'].iat[0] = d.Store.iat[0]
        return r
    else:
        return d.assign(Item=d.Bag)

df.groupby(df.Store.ne('Na').cumsum(), group_keys=False).apply(f)

        Store           Bag       Item
0     Alberts      ClothBag   ClothBag
1        Vons     KateSpade  KateSpade
3      Ralphs   GroceryBag1      apple
4          Na   GroceryBag1       pear
5          Na   GroceryBag1    staples
7       Kmart  ShoppingList  beachball
8          Na  ShoppingList       milk
10  QuikEmart         List5       Duff
11         Na         List5     Donuts

hi piRsquared, thanks for taking the time to help out, but I believe there is an issue in the code where, when ran, Bag and Item column are identical. — Harold Chaw, Feb 17 '18 at 02:32

Partition a column based on missing values

1 Answers1