pandas duplicate df rows based on day count column

Question

I have the following DF.

DF qty

| hold_date  | day_count | qty  |   item   | ccy |
+------------+-----------+------+----------+-----+
| 2015-01-01 |         1 | 1200 | CB04 box | USD |
| 2015-01-01 |         3 | 1500 | AB01 box | USD |
| 2015-01-02 |         2 |  550 | CB03 box | USD |

I want to increment the hold_date based on the day_count. for example item : AB01 box will add two new rows as below. so the df could look like this.

DF qty

| hold_date  | qty  |   item   | ccy |
+------------+------+----------+-----+
| 2015-01-01 | 1200 | CB04 box | USD |
| 2015-01-01 | 1500 | AB01 box | USD |
| 2015-01-02 | 1500 | AB01 box | USD |
| 2015-01-03 | 1500 | AB01 box | USD |
| 2015-01-02 |  550 | CB03 box | USD |
| 2015-01-03 |  550 | CB03 box | USD |

score 2 · Accepted Answer · answered Jun 29 '18 at 19:50

2

Need :

s=df.day_count
s1=[pd.Timedelta(x,'D') for x in sum(df.day_count.apply(lambda x : list(range(x))),[])]
df_new=df.reindex(df.index.repeat(s))
df_new['hold_date']=df_new.hold_date+s1
df_new
Out[642]: 
   hold_date  day_count   qty     item  ccy
0 2015-01-01          1  1200  CB04box  USD
1 2015-01-01          3  1500  AB01box  USD
1 2015-01-02          3  1500  AB01box  USD
1 2015-01-03          3  1500  AB01box  USD
2 2015-01-02          2   550  CB03box  USD
2 2015-01-03          2   550  CB03box  USD

answered Jun 29 '18 at 19:50

BENY

317,841
20
164
234

getting this error with your solution `ValueError: cannot reindex from a duplicate axis` – as - if Jun 29 '18 at 20:05
@as-if try `df=df.reset_index(drop=True)` before my solution – BENY Jun 29 '18 at 20:06
1

Yes this is nice! But your `s1` variable could be as simple as: `s1 = [pd.Timedelta(days=i) for y in s for i in range(y)]` – Anton vBR Jun 29 '18 at 20:40
1

@AntonvBR much neater :-) – BENY Jun 29 '18 at 20:42
now getting this `TypeError: unsupported operand type(s) for +: 'DatetimeIndex' and 'list'` – as - if Jun 29 '18 at 21:18

score 1 · Answer 2 · answered Jun 29 '18 at 19:53

Here is a fully vectorized (no for loops) solution. The idea is to create a temporary column with lists of all dates, and then expand it into rows. The expand_column function is based on this answer.

df = pd.DataFrame([['2015-01-01', 1, 1200, 'CB04 box', 'USD'],
                   ['2015-01-01', 3, 1500, 'AB01 box', 'USD'], 
                   ['2015-01-02', 2, 550, 'CB03 box', 'USD'], 
                  ], columns=['hold_date', 'day_count', 'qty', 'item', 'ccy'])        

range_col = lambda row: list(pd.date_range(start=pd.to_datetime(row.hold_date), periods=row.day_count))
df = df.assign(hold_date=df.apply(range_col, axis=1))
expand_column(df, 'hold_date')[['hold_date', 'qty', 'item', 'ccy']]

     hold_date   qty        item    ccy
0   2015-01-01  1200    CB04 box    USD
1   2015-01-01  1500    AB01 box    USD
1   2015-01-02  1500    AB01 box    USD
1   2015-01-03  1500    AB01 box    USD
2   2015-01-02  550     CB03 box    USD
2   2015-01-03  550     CB03 box    USD

def expand_column(dataframe, column):
    """Transform iterable column values into multiple rows.

    Source: https://stackoverflow.com/a/27266225/304209.

    Args:
        dataframe: DataFrame to process.
        column: name of the column to expand.

    Returns:
        copy of the DataFrame with the following updates:
            * for rows where column contains only 1 value, keep them as is.
            * for rows where column contains a list of values, transform them
                into multiple rows, each of which contains one value from the list in column.
    """
    tmp_df = dataframe.apply(
        lambda row: pd.Series(row[column]), axis=1).stack().reset_index(level=1, drop=True)
    tmp_df.name = column
    return dataframe.drop(column, axis=1).join(tmp_df)

That's not entirely correct though. The lambda function you use is not vectorized. — Anton vBR, Jun 29 '18 at 20:31
@AntonvBR `apply` is vectorized, so it's applied to rows in parallel. The lambda function operates on 2 column values, so there is no loop inside. — Dennis Golomazov, Jun 29 '18 at 22:27

score 0 · Answer 3 · answered Jun 29 '18 at 19:30

You can do this by creating a new DataFrame from your DF qty and also repeating the elements qty* times:

df_qty = pd.DataFrame([df_qty.ix[idx] 
                      for idx in df_qty.index 
                      for _ in range(df_qty.ix[idx]['qty'])]).reset_index(drop=True)

This will create a new list that contains foreach row, qty* duplicate of it.

score 0 · Answer 4 · answered Jun 29 '18 at 19:34

This is ugly but leaving it here anyway :)

df = pd.concat(pd.DataFrame([df.loc[i]]*df.loc[i]['day_count'])
            .assign(hold_date= pd.date_range(
                    df.loc[i]['hold_date'], 
                    periods=df.loc[i]['day_count'],
                    freq='D')) 
    for i in range(len(df)))

full example:

import pandas as pd

df = pd.DataFrame({
    'hold_date': pd.date_range('2015-01-01', '2015-01-02'),
    'day_count': [2,3],
    'qty': [1200,1500]
})

df = pd.concat(pd.DataFrame([df.loc[i]]*df.loc[i]['day_count'])
            .assign(hold_date= pd.date_range(
                    df.loc[i]['hold_date'], 
                    periods=df.loc[i]['day_count'],
                    freq='D')) 
    for i in range(len(df)))

print(df)

Returns:

   day_count  hold_date   qty
0          2 2015-01-01  1200
0          2 2015-01-02  1200
1          3 2015-01-02  1500
1          3 2015-01-03  1500
1          3 2015-01-04  1500

@as-if this reassigns the df to df. df is now the new df. Or what do you mean? — Anton vBR, Jun 29 '18 at 19:48

pandas duplicate df rows based on day count column

4 Answers4