CODEWITHSUNDEEP
pythonpandasdataframemulti-index
gerscorpion
gerscorpion

Reputation: 188

Pandas dataframe reset index count of multiindex

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?

Upvotes: 1

Views: 241

Answers (1)

Shubham Sharma
Shubham Sharma

Reputation: 71707

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

Upvotes: 2

Related Questions