基于“日计数”列复制数据行

Question

我有下面的DF。你知道吗

数据框数量

| 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 |

我想在day_count的基础上增加hold_date。例如item：AB01 box将添加两个新行，如下所示。所以df看起来像这样。你知道吗

数据框数量

| 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 |

Answer 1

需要：

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

Answer 2

这是一个完全矢量化（无for循环）的解决方案。其思想是创建一个包含所有日期列表的临时列，然后将其展开成行。expand_column函数基于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)

Answer 3

您可以通过从DF qty创建一个新的DataFrame并重复元素qty*次来实现这一点：

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)

这将创建一个新列表，其中包含foreach行的qty*副本。你知道吗