Pandas series looping with specific index level

Question

I have a Pandas Series with multiple index that I'm trying to iterate over by the level "ID". The idea is the for loop will increment to the next "ID" so I can slice all the values associated with that ID to pass to a function for plotting each ID as a different color.

                rest        confidence
ID  ts      
33  21:30:50    150.01001   95.9864
    21:30:52    148.826187  79.530624
    21:30:53    148.957123  54.75795
55  21:30:52    168.325577  37.43358
    21:30:53    172.813446  33.133442
61  21:30:50    107.335625  32.807873

The Pandas doc (Pandas MultiIndex) has been helpful with slicing and getting a working for loop (below). Using df.index.levels[0] returns the index values I need to run the for loop, however, it seems like there's a better and faster way to tell it to iterate over a given index level. Is there?

for IDn in list(df.index.levels[0]):
    print( df.loc[ (IDn,slice(None)),['confidence','rest'] ].xs(slice(None),level='ID') )

I've gone through this question some (Pandas how to loop through a MultiIndex series) and it seems the groupby and apply function are the way.

rahlf23 · Accepted Answer · 2018-08-30T15:16:01.803

You can use groupby() and loop through the groups. First recreating your dataframe:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

index = pd.MultiIndex.from_product([[33, 55, 61],['21:30:50','21:30:52','21:30:53']], names=['ID','ts'])

df = pd.DataFrame([[150.01001,   95.9864],
                [148.826187,  79.530624],
                [148.957123,  54.75795],
                [np.nan, np.nan],
                [168.325577,  37.43358],
                [172.813446,  33.133442],
                [107.335625,  32.807873],
                [np.nan, np.nan],
                [np.nan, np.nan]],
                columns=['rest', 'confidence'], index=index).dropna()

Yielding:

                   rest  confidence
ID ts                              
33 21:30:50  150.010010   95.986400
   21:30:52  148.826187   79.530624
   21:30:53  148.957123   54.757950
55 21:30:52  168.325577   37.433580
   21:30:53  172.813446   33.133442
61 21:30:50  107.335625   32.807873

Then using groupby('ID'):

grouped = df.groupby('ID')

fig, ax = plt.subplots()
for name, group in grouped:
    ax.plot(group['rest'], group['confidence'], marker='o', linestyle='', label=name)
ax.legend()

plt.xlabel('rest'); plt.ylabel('confidence')
plt.title('Rest vs Confidence'); plt.grid(True)

plt.show()

Produces the following scatter plot:

UPDATE

To create two subplots for the two parameters versus time (ts):

df = df.reset_index()

df['ts'] = pd.to_datetime(df['ts'])

grouped = df.groupby('ID')

fig, (ax1, ax2) = plt.subplots(1, 2)
for name, group in grouped:
    ax1.plot(group['ts'], group['rest'], marker='o', linestyle='', label=name)
    ax2.plot(group['ts'], group['confidence'], marker='o', linestyle='', label=name)

ax1.legend()
ax1.set_xlabel('ts'); ax1.set_ylabel('rest')
ax1.set_title('Rest vs ts'); ax1.grid(True)

ax2.legend()
ax2.set_xlabel('ts'); ax2.set_ylabel('confidence')
ax2.set_title('Confidence vs ts'); ax2.grid(True)

plt.show()

Which gives the following:

Thanks, my goal was to plot 'rest' and 'confidence' as a function of time. Is that possible? I after trying your example I realized the time index was gone. I'm starting to realize it's easier to leave the data as a plain dataframe with no multi-index, with 'ts' and 'ID' in their respective columns. That way I can use your groupby example as needed when plotting. — kilozulu, Aug 29 '18 at 21:54
Thanks, the magic line I was missing was "df = df.reset_index()". Before I was trying to duplicate the timestamp column before performing groupby. Groupby would destroy the duplicated timestamp column even though I gave it a different name. I guess it saw the duplicate data and happily fixed it. — kilozulu, Aug 30 '18 at 18:56

Pandas series looping with specific index level

1 Answers1