Python: How to repeat each combination of rows in Dataframe ranging 1 to n?

December 7, 2021

Have got a dataframe df like below:

Store   Aisle   Table
11      59      2
11      61      3

Need to expand each combination of row 3 times generating new column ‘bit’ with range value as below:

Store   Aisle   Table   Bit
11      59      2       1
11      59      2       2
11      59      2       3
11      61      3       1
11      61      3       2
11      61      3       3

Have tried the below code but didn’t worked out.

df.loc[df.index.repeat(range(3))]

Help me out! Thanks in Advance.

>Solution :

You should provide a number, not a range to repeat. Also, you need a bit of processing:

(df.loc[df.index.repeat(3)]
   .assign(Bit=lambda d: d.groupby(level=0).cumcount().add(1))
   .reset_index(drop=True)
)

output:

   Store  Aisle  Table  Bit
0     11     59      2    1
1     11     59      2    2
2     11     59      2    3
3     11     61      3    1
4     11     61      3    2
5     11     61      3    3

Alternatively, using MultiIndex.from_product:

idx = pd.MultiIndex.from_product([df.index, range(1,3+1)], names=(None, 'Bit'))
(df.reindex(idx.get_level_values(0))
   .assign(Bit=idx.get_level_values(1))
)