CODEWITHSUNDEEP
pythonpandas
Michael Johnson
Michael Johnson

Reputation: 470

Adding a blank row to a hierarchical indexed pandas dataframe

I've placed a blank row after each level 0 row label in the below DataFrame for more convenient viewing. I had a row of values in each I didn't require, so transformed that.

What is the correct way to go about it, as opposed to my hack job?

                              B                     S
                         Depth3 Depth2 Depth1  Depth1  Depth2  Depth3
Market1 actionIndicator       B      B      B       S       S       S
        limit               555    557    558     560     561     562
        quantity         18.474  8.355  8.286   0.626  17.101  17.576
Market2 actionIndicator       B      B      B       S       S       S
        limit               475    477    478     479     480     481
        quantity         30.156  8.329  8.182  37.483  84.416  37.659
Market3 actionIndicator       B      B      B       S       S       S
        limit               583    586    587     589     590     591
        quantity         39.548  8.382   8.18  55.181  17.289  17.689

I wanted this:

                       B                     S
                  Depth3 Depth2 Depth1  Depth1  Depth2  Depth3
Market1 limit        555    557    558     560     561     562
        quantity  18.474  8.355  8.286   0.626  17.101  17.576

Market2 limit        475    477    478     479     480     481
        quantity  30.156  8.329  8.182  37.483  84.416  37.659

Market3 limit        583    586    587     589     590     591
        quantity  39.548  8.382   8.18  55.181  17.289  17.689

The following was used on the original DataFrame to achieve it:

df = df.reindex(display_order, level=0).reindex(["limit", "quantity",
                                                "actionIndicator"], level=1)
df.index = df.index.set_levels(["limit", "quantity", ""], level=1)
for level in set(df.index.get_level_values(0)):
    df.ix[(level, "")] = ""

Full code:

data = {
 'Market1': {'B': {'Depth1': {'actionIndicator': 'B',
    'limit': '558',
    'quantity': '8.286'},
   'Depth2': {'actionIndicator': 'B', 'limit': '557', 'quantity': '8.355'},
   'Depth3': {'actionIndicator': 'B', 'limit': '555', 'quantity': '18.474'}},
  'S': {'Depth1': {'actionIndicator': 'S',
    'limit': '560',
    'quantity': '0.626'},
   'Depth2': {'actionIndicator': 'S', 'limit': '561', 'quantity': '17.101'},
   'Depth3': {'actionIndicator': 'S', 'limit': '562', 'quantity': '17.576'}}},
 'Market2': {'B': {'Depth1': {'actionIndicator': 'B',
    'limit': '478',
    'quantity': '8.182'},
   'Depth2': {'actionIndicator': 'B', 'limit': '477', 'quantity': '8.329'},
   'Depth3': {'actionIndicator': 'B', 'limit': '475', 'quantity': '30.156'}},
  'S': {'Depth1': {'actionIndicator': 'S',
    'limit': '479',
    'quantity': '37.483'},
   'Depth2': {'actionIndicator': 'S', 'limit': '480', 'quantity': '84.416'},
   'Depth3': {'actionIndicator': 'S', 'limit': '481', 'quantity': '37.659'}}},
 'Market3': {'B': {'Depth1': {'actionIndicator': 'B',
    'limit': '587',
    'quantity': '8.18'},
   'Depth2': {'actionIndicator': 'B', 'limit': '586', 'quantity': '8.382'},
   'Depth3': {'actionIndicator': 'B', 'limit': '583', 'quantity': '39.548'}},
  'S': {'Depth1': {'actionIndicator': 'S',
    'limit': '589',
    'quantity': '55.181'},
   'Depth2': {'actionIndicator': 'S', 'limit': '590', 'quantity': '17.289'},
   'Depth3': {'actionIndicator': 'S', 'limit': '591', 'quantity': '17.689'}}},
}

df = DataFrame.from_dict(
        {(k1, k2, k3): data[k1][k2][k3] for k1 in data.keys() for k2 in
         data[k1].keys() for k3 in data[k1][k2].keys()}, orient="columns")

df = df.unstack().reorder_levels([0,3,2,1]).unstack(level=[3,2])
dfB = df[["B"]].sort_index(axis=1, ascending=False)
dfS = df[["S"]]
df = pd.concat([dfB, dfS], axis=1)

df = df.reindex(["limit", "quantity", "actionIndicator"], level=1)
df.index = df.index.set_levels(["limit", "quantity", ""], level=1)
for level in set(df.index.get_level_values(0)):
    df.ix[(level, "")] = ""

print(df)

Upvotes: 0

Views: 132

Answers (1)

jezrael
jezrael

Reputation: 862511

You can use another hack:

df = pd.DataFrame.from_dict(
        {(k1, k2, k3): data[k1][k2][k3] for k1 in data.keys() for k2 in
         data[k1].keys() for k3 in data[k1][k2].keys()}, orient="columns")

df = df.stack(0).swaplevel(1,0).sort_index()
print (df)
                             B                      S                
                        Depth1 Depth2  Depth3  Depth1  Depth2  Depth3
Market1 actionIndicator      B      B       B       S       S       S
        limit              558    557     555     560     561     562
        quantity         8.286  8.355  18.474   0.626  17.101  17.576
Market2 actionIndicator      B      B       B       S       S       S
        limit              478    477     475     479     480     481
        quantity         8.182  8.329  30.156  37.483  84.416  37.659
Market3 actionIndicator      B      B       B       S       S       S
        limit              587    586     583     589     590     591
        quantity          8.18  8.382  39.548  55.181  17.289  17.689

#rename index value 'actionIndicator' for last for sort e.g. zzz
df = df.rename(index={'actionIndicator':'zzz'})
#sorting index, zzz row is last in each group
df = df.sort_index()
#get mask where zzz
mask = df.index.get_level_values(1) == 'zzz'
#replace rows where mask is True by empty spaces
df.loc[mask] = ''
#replace value zzz to empty space
df = df.rename(index={'zzz':''})
print (df)
                      B                      S                
                 Depth1 Depth2  Depth3  Depth1  Depth2  Depth3
Market1 limit       558    557     555     560     561     562
        quantity  8.286  8.355  18.474   0.626  17.101  17.576

Market2 limit       478    477     475     479     480     481
        quantity  8.182  8.329  30.156  37.483  84.416  37.659

Market3 limit       587    586     583     589     590     591
        quantity   8.18  8.382  39.548  55.181  17.289  17.689

Another possible solution with reindex by MultiIndex.from_product:

#dynamically remove 'actionIndicator' and add it to the end of list
a = 'actionIndicator'
lvls = [lvl for lvl in df.index.levels[1] if lvl != a] + [a]
print (lvls)
['limit', 'quantity', 'actionIndicator']

df = df.reindex(pd.MultiIndex.from_product([df.index.levels[0],lvls]))
mask = df.index.get_level_values(1) == a
df.loc[mask] = ''
df = df.rename(index={a:''})
print (df)
                      B                      S                
                 Depth1 Depth2  Depth3  Depth1  Depth2  Depth3
Market1 limit       558    557     555     560     561     562
        quantity  8.286  8.355  18.474   0.626  17.101  17.576

Market2 limit       478    477     475     479     480     481
        quantity  8.182  8.329  30.156  37.483  84.416  37.659

Market3 limit       587    586     583     589     590     591
        quantity   8.18  8.382  39.548  55.181  17.289  17.689

Upvotes: 1

Related Questions