我有以下数据帧,其中一列是一个对象(列表类型单元格):

df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [1, 2]]})

输出:

   A       B
0  1  [1, 2]
1  2  [1, 2]

我的期望输出是:

   A  B
0  1  1
1  1  2
3  2  1
4  2  2

我该怎么做才能做到这一点呢?


相关的问题

Pandas列的列表,为每个列表元素创建一行

很好的问题和答案,但只处理一个列与列表(在我的回答自定义函数将工作于多个列,也接受的答案是使用最耗时的应用,这是不建议的,检查更多信息当我(不)想要使用熊猫应用()在我的代码?)


当前回答

在pandas 0.25中,由于增加了explosion()方法,爆炸一个类似列表的列被大大简化了:

df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [1, 2]]})
df.explode('B')

Out:

   A  B
0  1  1
0  1  2
1  2  1
1  2  2

其他回答

一种替代方法是在列的行上应用meshgrid recipe来取消嵌套:

import numpy as np
import pandas as pd


def unnest(frame, explode):
    def mesh(values):
        return np.array(np.meshgrid(*values)).T.reshape(-1, len(values))

    data = np.vstack(mesh(row) for row in frame[explode].values)
    return pd.DataFrame(data=data, columns=explode)


df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [1, 2]]})
print(unnest(df, ['A', 'B']))  # base
print()

df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [3, 4]], 'C': [[1, 2], [3, 4]]})
print(unnest(df, ['A', 'B', 'C']))  # multiple columns
print()

df = pd.DataFrame({'A': [1, 2, 3], 'B': [[1, 2], [1, 2, 3], [1]],
                   'C': [[1, 2, 3], [1, 2], [1, 2]], 'D': ['A', 'B', 'C']})

print(unnest(df, ['A', 'B']))  # uneven length lists
print()
print(unnest(df, ['D', 'B']))  # different types
print()

输出

   A  B
0  1  1
1  1  2
2  2  1
3  2  2

   A  B  C
0  1  1  1
1  1  2  1
2  1  1  2
3  1  2  2
4  2  3  3
5  2  4  3
6  2  3  4
7  2  4  4

   A  B
0  1  1
1  1  2
2  2  1
3  2  2
4  2  3
5  3  1

   D  B
0  A  1
1  A  2
2  B  1
3  B  2
4  B  3
5  C  1

在我的例子中,有多个列要爆炸,并且需要取消嵌套的数组有变量长度。

我最终应用了新的熊猫0.25爆炸功能两次,然后删除生成的副本,它的工作!

df = df.explode('A')
df = df.explode('B')
df = df.drop_duplicates()
# Here's the answer to the related question in:
# https://stackoverflow.com/q/56708671/11426125

# initial dataframe
df12=pd.DataFrame({'Date':['2007-12-03','2008-09-07'],'names':
[['Peter','Alex'],['Donald','Stan']]})

# convert dataframe to array for indexing list values (names)
a = np.array(df12.values)  

# create a new, dataframe with dimensions for unnested
b = np.ndarray(shape = (4,2))
df2 = pd.DataFrame(b, columns = ["Date", "names"], dtype = str)

# implement loops to assign date/name values as required
i = range(len(a[0]))
j = range(len(a[0]))
for x in i:
    for y in j:
        df2.iat[2*x+y, 0] = a[x][0]
        df2.iat[2*x+y, 1] = a[x][1][y]

# set Date column as Index
df2.Date=pd.to_datetime(df2.Date)
df2.index=df2.Date
df2.drop('Date',axis=1,inplace =True)

下面是一个简单的水平爆炸函数,基于@BEN_YO的答案。

import typing
import pandas as pd

def horizontal_explode(df: pd.DataFrame, col_name: str, new_columns: typing.Union[list, None]=None) -> pd.DataFrame:
    t = pd.DataFrame(df[col_name].tolist(), columns=new_columns, index=df.index)
    return pd.concat([df, t], axis=1)

运行示例:

items = [
    ["1", ["a", "b", "c"]],
    ["2", ["d", "e", "f"]]
]

df = pd.DataFrame(items, columns = ["col1", "col2"])
print(df)

t = horizontal_explode(df=df, col_name="col2")
del t["col2"]
print(t)

t = horizontal_explode(df=df, col_name="col2", new_columns=["new_col1", "new_col2", "new_col3"])
del t["col2"]
print(t)

这是相关的输出:

  col1       col2
0    1  [a, b, c]
1    2  [d, e, f]

  col1  0  1  2
0    1  a  b  c
1    2  d  e  f

  col1 new_col1 new_col2 new_col3
0    1        a        b        c
1    2        d        e        f

选项1

如果另一列的所有子列表都是相同的长度,numpy可以是一个有效的选项:

vals = np.array(df.B.values.tolist())    
a = np.repeat(df.A, vals.shape[1])

pd.DataFrame(np.column_stack((a, vals.ravel())), columns=df.columns)

   A  B
0  1  1
1  1  2
2  2  1
3  2  2

选项2

如果子列表有不同的长度,你需要额外的步骤:

vals = df.B.values.tolist()
rs = [len(r) for r in vals]    
a = np.repeat(df.A, rs)

pd.DataFrame(np.column_stack((a, np.concatenate(vals))), columns=df.columns)

   A  B
0  1  1
1  1  2
2  2  1
3  2  2

选项3

我尝试将其推广到平展N列和平铺M列,稍后我会让它更有效:

df = pd.DataFrame({'A': [1,2,3], 'B': [[1,2], [1,2,3], [1]],
                   'C': [[1,2,3], [1,2], [1,2]], 'D': ['A', 'B', 'C']})

   A          B          C  D
0  1     [1, 2]  [1, 2, 3]  A
1  2  [1, 2, 3]     [1, 2]  B
2  3        [1]     [1, 2]  C

def unnest(df, tile, explode):
    vals = df[explode].sum(1)
    rs = [len(r) for r in vals]
    a = np.repeat(df[tile].values, rs, axis=0)
    b = np.concatenate(vals.values)
    d = np.column_stack((a, b))
    return pd.DataFrame(d, columns = tile +  ['_'.join(explode)])

unnest(df, ['A', 'D'], ['B', 'C'])

    A  D B_C
0   1  A   1
1   1  A   2
2   1  A   1
3   1  A   2
4   1  A   3
5   2  B   1
6   2  B   2
7   2  B   3
8   2  B   1
9   2  B   2
10  3  C   1
11  3  C   1
12  3  C   2

功能

def wen1(df):
    return df.set_index('A').B.apply(pd.Series).stack().reset_index(level=0).rename(columns={0: 'B'})

def wen2(df):
    return pd.DataFrame({'A':df.A.repeat(df.B.str.len()),'B':np.concatenate(df.B.values)})

def wen3(df):
    s = pd.DataFrame({'B': np.concatenate(df.B.values)}, index=df.index.repeat(df.B.str.len()))
    return s.join(df.drop('B', 1), how='left')

def wen4(df):
    return pd.DataFrame([[x] + [z] for x, y in df.values for z in y],columns=df.columns)

def chris1(df):
    vals = np.array(df.B.values.tolist())
    a = np.repeat(df.A, vals.shape[1])
    return pd.DataFrame(np.column_stack((a, vals.ravel())), columns=df.columns)

def chris2(df):
    vals = df.B.values.tolist()
    rs = [len(r) for r in vals]
    a = np.repeat(df.A.values, rs)
    return pd.DataFrame(np.column_stack((a, np.concatenate(vals))), columns=df.columns)

计时

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np
from timeit import timeit

res = pd.DataFrame(
       index=['wen1', 'wen2', 'wen3', 'wen4', 'chris1', 'chris2'],
       columns=[10, 50, 100, 500, 1000, 5000, 10000],
       dtype=float
)

for f in res.index:
    for c in res.columns:
        df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [1, 2]]})
        df = pd.concat([df]*c)
        stmt = '{}(df)'.format(f)
        setp = 'from __main__ import df, {}'.format(f)
        res.at[f, c] = timeit(stmt, setp, number=50)

ax = res.div(res.min()).T.plot(loglog=True)
ax.set_xlabel("N")
ax.set_ylabel("time (relative)")

性能