代码之家 › 专栏 › 技术社区 › as - if

pandas基于day count列复制df行

pandas python

as - if · 技术社区 · 8 年前

我有以下内容 DF .

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 |

我想增加 hold_date 基于 day_count .例如 item 以下内容: AB01 box 将添加两个新行,如下所示。所以 df 可能看起来像这样。

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 |

4 回复 | 直到 8 年前

BENY 8 年前

需要:

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

Dennis Golomazov 8 年前

这是一个完全矢量化的(不 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)

Haytam 8 年前

您可以通过创建新的 DataFrame 从您的df qty,同时重复元素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 *副本。

Anton vBR 8 年前

这很难看,但还是留在这里:)

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

完整示例:

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)

   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