CODEWITHSUNDEEP
pythonpandasdataframe
Scott Skiles
Scott Skiles

Reputation: 3857

pandas - Copy each row 'n' times depending on column value

I'd like to copy or duplicate the rows of a DataFrame based on the value of a column, in this case orig_qty. So if I have a DataFrame and using pandas==0.24.2:

import pandas as pd

d = {'a': ['2019-04-08', 4, 115.00], 'b': ['2019-04-09', 2, 103.00]}

df = pd.DataFrame.from_dict(
        d, 
        orient='index', 
        columns=['date', 'orig_qty', 'price']
    )

Input

>>> print(df)
         date  orig_qty   price
a  2019-04-08         4   115.0
b  2019-04-09         2   103.0

So in the example above the row with orig_qty=4 should be duplicated 4 times and the row with orig_qty=2 should be duplicated 2 times. After this transformation I'd like a DataFrame that looks like:

Desired Output

>>> print(new_df)
         date  orig_qty  price  fifo_qty
1  2019-04-08         4  115.0         1
2  2019-04-08         4  115.0         1
3  2019-04-08         4  115.0         1
4  2019-04-08         4  115.0         1
5  2019-04-09         2  103.0         1
6  2019-04-09         2  103.0         1

Note I do not really care about the index after the transformation. I can elaborate more on the use case for this, but essentially I'm doing some FIFO accounting where important changes can occur between values of orig_qty.

Upvotes: 3

Views: 5763

Answers (2)

Chris Adams
Chris Adams

Reputation: 18647

Use Index.repeat, DataFrame.loc, DataFrame.assign and DataFrame.reset_index

 new_df = df.loc[df.index.repeat(df['orig_qty'])].assign(fifo_qty=1).reset_index(drop=True)

[output]

         date  orig_qty  price  fifo_qty
0  2019-04-08         4  115.0         1
1  2019-04-08         4  115.0         1
2  2019-04-08         4  115.0         1
3  2019-04-08         4  115.0         1
4  2019-04-09         2  103.0         1
5  2019-04-09         2  103.0         1

Upvotes: 8

rafaelc
rafaelc

Reputation: 59274

Use np.repeat

new_df = pd.DataFrame({col: np.repeat(df[col], df.orig_qty) for col in df.columns})

Upvotes: 2

Related Questions