Pandas explode index

Question

I have a df like below

a = pd.DataFrame([{'col1': ['a,b,c'], 'col2': 'x'},{'col1': ['d,b'], 'col2': 'y'}])

When I do an explode using df.explode(‘col1’), I get below results

col1 col2
a      x
b      x
c      x
d      y
b      y

However, I wanted something like below,

col1 col2 col1_index
a      x      1
b      x      2
c      x      3
d      y      1
b      y      2

Can someone help me?

Answer 1

You could do the following:

result = a.explode('col1').reset_index().rename(columns={'index' : 'col1_index'})
result['col1_index'] = result.groupby('col1_index').cumcount()
print(result)

Output

   col1_index col1 col2
0           0    a    x
1           1    b    x
2           2    c    x
3           0    d    y
4           1    b    y

Answer 2

After you explode you can simply do:

 a['col1_index'] = a.groupby('col2').cumcount()+1

  col1 col2  col1_index
0    a    x           1
1    b    x           2
2    c    x           3
3    d    y           1
4    b    y           2