Python pandas series multiindex iteration

Question

I've aggregated some data and i'd like to get the aggregation values back into another dataframe. I've aggregated the data like this

bycluster_type = df.groupby(['cluster', 'Type'])
tCount = bycluster_type['Type'].agg([len])

Edit: From this point i made some mistakes which i've corrected now and added a few new thoughts.

tCount is now a DataFrame with a MultiIndex. What i'd lke to do now is to get the cluster, type and the corresponding value to put it together with some other data in another dataframe. For example:

>>> tCount
                 len
cluster Type
1.0     M        1
2.0     M        7
4.0     M        2

So the next step is to get the index and the row:

index, row = next(tCount.index.values)

So what i'd do next is to unzip the index tuple into cluster and type and get the len value from the row.

cluster, type = index
val = row['len']

Is there a more efficent or elegant way of achieving my goal?

Edit: Some example data

cluster, Type, foo
      1,    M,   f
      1,    T,   o
      1,    S,   o
      2,    M,   f
      2,    M,   o
      3,    T,   o

The degree of elegance depends on what you want to do and you haven’t given enough information. There are many things we could do. We don’t know which thing to show you. — piRSquared
– piRSquared, Commented Apr 10, 2018 at 14:25
Perhaps its combine_first or concat that you are after. In case the index of the other dataframe is similar then df2.loc[tCount.index] = tCount.values might help. We need more clear info for a good answer. — Bharath M Shetty
– Bharath M Shetty, Commented Apr 10, 2018 at 14:26

Victor Valente · Accepted Answer · 2018-04-10 15:46:33Z

1

Ran your code and found that tCount results in a MultiIndex Dataframe.

You don't need to iterate throug the index, df = tCount.reset_index() should do the trick.

answered Apr 10, 2018 at 15:46

Victor Valente

8111 gold badge10 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

swas Over a year ago

You are right i've just edited it because i noticed i did something wrong. And thank you very much for the reset_index() that's exactly what i was looking for.

Collectives™ on Stack Overflow

Python pandas series multiindex iteration

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related