如何将 Dataframe 单元格内的列表分解为单独的行

问题描述：

我希望将包含列表的 pandas 单元格转换为每个值的行。

因此，请看以下这段话：

如果我想解压并堆叠nearest_neighbors列中的值，以便每个值都是每个opponent索引中的一行，我该如何最好地做到这一点？ 是否有适用于此类操作的 pandas 方法？

解决方案 1：

在 pandas 0.25 中，通过添加
方法，列表类列的展开已经得到显著简化explode()：

df = (pd.DataFrame({'name': ['A.J. Price'] * 3, 
                    'opponent': ['76ers', 'blazers', 'bobcats'], 
                    'nearest_neighbors': [['Zach LaVine', 'Jeremy Lin', 'Nate Robinson', 'Isaia']] * 3})
      .set_index(['name', 'opponent']))

df.explode('nearest_neighbors')

出去：

                    nearest_neighbors
name       opponent                  
A.J. Price 76ers          Zach LaVine
           76ers           Jeremy Lin
           76ers        Nate Robinson
           76ers                Isaia
           blazers        Zach LaVine
           blazers         Jeremy Lin
           blazers      Nate Robinson
           blazers              Isaia
           bobcats        Zach LaVine
           bobcats         Jeremy Lin
           bobcats      Nate Robinson
           bobcats              Isaia

解决方案 2：

在下面的代码中，我首先重置索引以使行迭代更容易。

我创建了一个列表列表，其中外部列表​​的每个元素都是目标 DataFrame 的一行，而内部列表的每个元素都是其中的一列。此嵌套列表最终将连接起来以创建所需的 DataFrame。

我使用一个函数和列表迭代来为与相关和配对lambda的每个元素创建一行。 nearest_neighbors`name`opponent

最后，我从这个列表创建一个新的 DataFrame（使用原始列名并将索引设置回name和opponent）。

df = (pd.DataFrame({'name': ['A.J. Price'] * 3, 
                    'opponent': ['76ers', 'blazers', 'bobcats'], 
                    'nearest_neighbors': [['Zach LaVine', 'Jeremy Lin', 'Nate Robinson', 'Isaia']] * 3})
      .set_index(['name', 'opponent']))

>>> df
                                                    nearest_neighbors
name       opponent                                                  
A.J. Price 76ers     [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           blazers   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           bobcats   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]

df.reset_index(inplace=True)
rows = []
_ = df.apply(lambda row: [rows.append([row['name'], row['opponent'], nn]) 
                         for nn in row.nearest_neighbors], axis=1)
df_new = pd.DataFrame(rows, columns=df.columns).set_index(['name', 'opponent'])

>>> df_new
                    nearest_neighbors
name       opponent                  
A.J. Price 76ers          Zach LaVine
           76ers           Jeremy Lin
           76ers        Nate Robinson
           76ers                Isaia
           blazers        Zach LaVine
           blazers         Jeremy Lin
           blazers      Nate Robinson
           blazers              Isaia
           bobcats        Zach LaVine
           bobcats         Jeremy Lin
           bobcats      Nate Robinson
           bobcats              Isaia

2017 年 6 月编辑

另一种方法如下：

>>> (pd.melt(df.nearest_neighbors.apply(pd.Series).reset_index(), 
             id_vars=['name', 'opponent'],
             value_name='nearest_neighbors')
     .set_index(['name', 'opponent'])
     .drop('variable', axis=1)
     .dropna()
     .sort_index()
     )

解决方案 3：

使用apply(pd.Series)和stack，然后reset_index和to_frame

In [1803]: (df.nearest_neighbors.apply(pd.Series)
              .stack()
              .reset_index(level=2, drop=True)
              .to_frame('nearest_neighbors'))
Out[1803]:
                    nearest_neighbors
name       opponent
A.J. Price 76ers          Zach LaVine
           76ers           Jeremy Lin
           76ers        Nate Robinson
           76ers                Isaia
           blazers        Zach LaVine
           blazers         Jeremy Lin
           blazers      Nate Robinson
           blazers              Isaia
           bobcats        Zach LaVine
           bobcats         Jeremy Lin
           bobcats      Nate Robinson
           bobcats              Isaia

细节

In [1804]: df
Out[1804]:
                                                   nearest_neighbors
name       opponent
A.J. Price 76ers     [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           blazers   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           bobcats   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]

解决方案 4：

我认为这是一个非常好的问题，在 Hive 中你会使用EXPLODE，我认为 Pandas 应该默认包含此功能。我可能会使用嵌套的生成器理解来扩展列表列，如下所示：

pd.DataFrame({
    "name": i[0],
    "opponent": i[1],
    "nearest_neighbor": neighbour
    }
    for i, row in df.iterrows() for neighbour in row.nearest_neighbors
    ).set_index(["name", "opponent"])

解决方案 5：

到目前为止我发现的最快的方法是扩展 DataFrame.iloc并重新分配扁平的目标列。

给出通常的输入（稍微复制一下）：

df = (pd.DataFrame({'name': ['A.J. Price'] * 3, 
                    'opponent': ['76ers', 'blazers', 'bobcats'], 
                    'nearest_neighbors': [['Zach LaVine', 'Jeremy Lin', 'Nate Robinson', 'Isaia']] * 3})
      .set_index(['name', 'opponent']))
df = pd.concat([df]*10)

df
Out[3]: 
                                                   nearest_neighbors
name       opponent                                                 
A.J. Price 76ers     [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           blazers   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           bobcats   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           76ers     [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
           blazers   [Zach LaVine, Jeremy Lin, Nate Robinson, Isaia]
...

给出以下建议的替代方案：

col_target = 'nearest_neighbors'

def extend_iloc():
    # Flatten columns of lists
    col_flat = [item for sublist in df[col_target] for item in sublist] 
    # Row numbers to repeat 
    lens = df[col_target].apply(len)
    vals = range(df.shape[0])
    ilocations = np.repeat(vals, lens)
    # Replicate rows and add flattened column of lists
    cols = [i for i,c in enumerate(df.columns) if c != col_target]
    new_df = df.iloc[ilocations, cols].copy()
    new_df[col_target] = col_flat
    return new_df

def melt():
    return (pd.melt(df[col_target].apply(pd.Series).reset_index(), 
             id_vars=['name', 'opponent'],
             value_name=col_target)
            .set_index(['name', 'opponent'])
            .drop('variable', axis=1)
            .dropna()
            .sort_index())

def stack_unstack():
    return (df[col_target].apply(pd.Series)
            .stack()
            .reset_index(level=2, drop=True)
            .to_frame(col_target))

我发现这extend_iloc()是最快的：

%timeit extend_iloc()
3.11 ms ± 544 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit melt()
22.5 ms ± 1.25 ms per loop (mean ± std. dev. of 7 runs, 100 loops each)

%timeit stack_unstack()
11.5 ms ± 410 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

解决方案 6：

使用 apply(pd.Series) 的更好的替代解决方案：

df = pd.DataFrame({'listcol':[[1,2,3],[4,5,6]]})

# expand df.listcol into its own dataframe
tags = df['listcol'].apply(pd.Series)

# rename each variable is listcol
tags = tags.rename(columns = lambda x : 'listcol_' + str(x))

# join the tags dataframe back to the original dataframe
df = pd.concat([df[:], tags[:]], axis=1)

解决方案 7：

与Hive的EXPLODE功能类似：

import copy

def pandas_explode(df, column_to_explode):
    """
    Similar to Hive's EXPLODE function, take a column with iterable elements, and flatten the iterable to one element 
    per observation in the output table

    :param df: A dataframe to explod
    :type df: pandas.DataFrame
    :param column_to_explode: 
    :type column_to_explode: str
    :return: An exploded data frame
    :rtype: pandas.DataFrame
    """

    # Create a list of new observations
    new_observations = list()

    # Iterate through existing observations
    for row in df.to_dict(orient='records'):

        # Take out the exploding iterable
        explode_values = row[column_to_explode]
        del row[column_to_explode]

        # Create a new observation for every entry in the exploding iterable & add all of the other columns
        for explode_value in explode_values:

            # Deep copy existing observation
            new_observation = copy.deepcopy(row)

            # Add one (newly flattened) value from exploding iterable
            new_observation[column_to_explode] = explode_value

            # Add to the list of new observations
            new_observations.append(new_observation)

    # Create a DataFrame
    return_df = pandas.DataFrame(new_observations)

    # Return
    return return_df

解决方案 8：

这是针对较大数据框的潜在优化。当“爆炸”字段中有多个相等的值时，此优化运行速度会更快。（与字段中的唯一值计数相比，数据框越大，此代码的性能就越好。）

def lateral_explode(dataframe, fieldname): 
    temp_fieldname = fieldname + '_made_tuple_' 
    dataframe[temp_fieldname] = dataframe[fieldname].apply(tuple)       
    list_of_dataframes = []
    for values in dataframe[temp_fieldname].unique().tolist(): 
        list_of_dataframes.append(pd.DataFrame({
            temp_fieldname: [values] * len(values), 
            fieldname: list(values), 
        }))
    dataframe = dataframe[list(set(dataframe.columns) - set([fieldname]))] 
        .merge(pd.concat(list_of_dataframes), how='left', on=temp_fieldname) 
    del dataframe[temp_fieldname]

    return dataframe

解决方案 9：

扩展 Oleg 的.iloc答案以自动展平所有列表列：

def extend_iloc(df):
    cols_to_flatten = [colname for colname in df.columns if 
    isinstance(df.iloc[0][colname], list)]
    # Row numbers to repeat 
    lens = df[cols_to_flatten[0]].apply(len)
    vals = range(df.shape[0])
    ilocations = np.repeat(vals, lens)
    # Replicate rows and add flattened column of lists
    with_idxs = [(i, c) for (i, c) in enumerate(df.columns) if c not in cols_to_flatten]
    col_idxs = list(zip(*with_idxs)[0])
    new_df = df.iloc[ilocations, col_idxs].copy()

    # Flatten columns of lists
    for col_target in cols_to_flatten:
        col_flat = [item for sublist in df[col_target] for item in sublist]
        new_df[col_target] = col_flat

    return new_df

这假设每个列表列具有相同的列表长度。

解决方案 10：

您可以展平列，而不是使用 apply(pd.Series)。这可以提高性能。

df = (pd.DataFrame({'name': ['A.J. Price'] * 3, 
                'opponent': ['76ers', 'blazers', 'bobcats'], 
                'nearest_neighbors': [['Zach LaVine', 'Jeremy Lin', 'Nate Robinson', 'Isaia']] * 3})
  .set_index(['name', 'opponent']))



%timeit (pd.DataFrame(df['nearest_neighbors'].values.tolist(), index = df.index)
           .stack()
           .reset_index(level = 2, drop=True).to_frame('nearest_neighbors'))

1.87 ms ± 9.74 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)


%timeit (df.nearest_neighbors.apply(pd.Series)
          .stack()
          .reset_index(level=2, drop=True)
          .to_frame('nearest_neighbors'))

2.73 ms ± 16.7 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)