Vfrdtyky

Let's say I have a list of events that happen on different keys.

data = [

    {"key": "A", "event": "created"},

    {"key": "A", "event": "updated"},

    {"key": "A", "event": "updated"},

    {"key": "A", "event": "updated"},

    {"key": "B", "event": "created"},

    {"key": "B", "event": "updated"},

    {"key": "B", "event": "updated"},

    {"key": "C", "event": "created"},

    {"key": "C", "event": "updated"},

    {"key": "C", "event": "updated"},

    {"key": "C", "event": "updated"},

    {"key": "C", "event": "updated"},

    {"key": "C", "event": "updated"},

]



df = pandas.DataFrame(data)

I would like to index my DataFrame on the key first and then an enumeration. It looks like a simple unstack operation, but I'm unable to find how to do it properly.

The best I could do was

df.set_index("key", append=True).swaplevel(0, 1)



          event

key            

A   0   created

    1   updated

    2   updated

    3   updated

B   4   created

    5   updated

    6   updated

C   7   created

    8   updated

    9   updated

    10  updated

    11  updated

    12  updated

but what I'm expecting is

          event

key            

A   0   created

    1   updated

    2   updated

    3   updated

B   0   created

    1   updated

    2   updated

C   0   created

    1   updated

    2   updated

    3   updated

    4   updated

    5   updated

I also tried something like

df.groupby("key")["key"].count().apply(range).apply(pandas.Series).stack()

but the order is not preserved, so I can't apply the result as an index. Besides, I feel it overkill for an operation that looks quite standard...

Any idea?

groupby + cumcount

Here are a couple of ways:

# new version thanks @ScottBoston

df = df.set_index(['key', df.groupby('key').cumcount()])

       .rename_axis(['key','count'])



# original version

df = df.assign(count=df.groupby('key').cumcount())

       .set_index(['key', 'count'])



print(df)



             event

key count         

A   0      created

    1      updated

    2      updated

    3      updated

B   0      created

    1      updated

    2      updated

C   0      created

    1      updated

    2      updated

    3      updated

    4      updated

    5      updated

You can do this in numpy like this:

# df like in OP

keys = df['key'].values

# detect indices where key changes value

change = np.zeros(keys.size, dtype=int)

change[1:] = keys[1:] != keys[:-1]

# naive sequential number

seq = np.arange(keys.size)

# offset by seq at most recent change

offset = np.maximum.accumulate(change * seq)

df['seq'] = seq - offset

print(df.set_index(['key', 'seq']))



           event

key seq         

A   0    created

    1    updated

    2    updated

    3    updated

B   0    created

    1    updated

    2    updated

C   0    created

    1    updated

    2    updated

    3    updated

    4    updated

    5    updated