Pandas dataframe reset index count of multiindex

Question

constructed a dataframe by concatenating several dataframes with the keys [a,b,c] as Index

+-------+----------+----------+
| Index | IndexPos | SomeData |
+-------+----------+----------+
| a     |        1 | some1     |
|       |        2 | some2     |
|       |        3 | some3     |
| b     |        1 | some1     |
|       |        2 | some2     |
|       |        3 | some3     |
| c     |        1 | some1     |
|       |        2 | some2     |
|       |        3 | some3     |
+-------+----------+----------+

and now want slice it down to the last 2 elements like:

df.groupby(df.index.levels[0].name).tail(2)

After that I want to recount the remaining elements IndexPos to get this:

+-------+----------+----------+
| Index | IndexPos | SomeData |
+-------+----------+----------+
| a     |        1 | some2     |
|       |        2 | some3     |
| b     |        1 | some2     |
|       |        2 | some3     |
| c     |        1 | some2     |
|       |        2 | some3     |
+-------+----------+----------+

Is there a way to do this, or do I have to slice it before concatenating?

Shubham Sharma · Accepted Answer · 2020-09-08 17:17:11Z

2

First groupby on level=0 and get the last two rows from each group using tail, then using groupby + cumcount on sliced dataframe create a sequential counter for each group and set it as new index at level=1:

d = df.groupby(level=0).tail(2)
d = d.droplevel(1).set_index(d.groupby(level=0).cumcount().add(1), append=True)

Or using factorize in place of groupby + cumcount inspired by @anky's solution:

d = df.groupby(level=0).tail(2)
d = d.droplevel(1).set_index(d.index.get_level_values(1).factorize()[0] + 1, append=True)

Result:

print(d)

        SomeData
Index           
a     1    some2
      2    some3
b     1    some2
      2    some3
c     1    some2
      2    some3

edited Sep 8, 2020 at 17:17

answered Sep 8, 2020 at 16:21

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

2 Comments

gerscorpion Over a year ago

Wow, very nice. Would've taken me days to come up with

Shubham Sharma Over a year ago

@LucaR Happy coding :)

Collectives™ on Stack Overflow

Pandas dataframe reset index count of multiindex

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related