我有以下数据帧，其中一列是一个对象(列表类型单元格):
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列的列表，为每个列表元素创建一行
很好的问题和答案，但只处理一个列与列表(在我的回答自定义函数将工作于多个列，也接受的答案是使用最耗时的应用，这是不建议的，检查更多信息当我(不)想要使用熊猫应用()在我的代码?)
当前回答
demo = {'set1':{'t1':[1,2,3],'t2':[4,5,6],'t3':[7,8,9]}, 'set2':{'t1':[1,2,3],'t2':[4,5,6],'t3':[7,8,9]}, 'set3': {'t1':[1,2,3],'t2':[4,5,6],'t3':[7,8,9]}}
df = pd.DataFrame.from_dict(demo, orient='index')
print(df.head())
my_list=[]
df2=pd.DataFrame(columns=['set','t1','t2','t3'])
for key,item in df.iterrows():
t1=item.t1
t2=item.t2
t3=item.t3
mat1=np.matrix([t1,t2,t3])
row1=[key,mat1[0,0],mat1[0,1],mat1[0,2]]
df2.loc[len(df2)]=row1
row2=[key,mat1[1,0],mat1[1,1],mat1[1,2]]
df2.loc[len(df2)]=row2
row3=[key,mat1[2,0],mat1[2,1],mat1[2,2]]
df2.loc[len(df2)]=row3
print(df2)
set t1 t2 t3
0 set1 1 2 3
1 set1 4 5 6
2 set1 7 8 9
3 set2 1 2 3
4 set2 4 5 6
5 set2 7 8 9
6 set3 1 2 3
7 set3 4 5 6
8 set3 7 8 9
其他回答
一种替代方法是在列的行上应用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
问题的设置
假设有多个列，其中包含不同长度的对象
df = pd.DataFrame({
'A': [1, 2],
'B': [[1, 2], [3, 4]],
'C': [[1, 2], [3, 4, 5]]
})
df
A B C
0 1 [1, 2] [1, 2]
1 2 [3, 4] [3, 4, 5]
当长度相同时，我们很容易假设不同的元素重合，并且应该“压缩”在一起。
A B C
0 1 [1, 2] [1, 2] # Typical to assume these should be zipped [(1, 1), (2, 2)]
1 2 [3, 4] [3, 4, 5]
然而，当我们看到不同长度的对象时，这个假设就会受到挑战，我们应该“压缩”吗?如果是的话，我们如何处理其中一个对象中的多余部分呢?或者，也许我们想要所有物体的乘积。这将迅速扩大规模，但可能正是人们想要的。
A B C
0 1 [1, 2] [1, 2]
1 2 [3, 4] [3, 4, 5] # is this [(3, 3), (4, 4), (None, 5)]?
OR
A B C
0 1 [1, 2] [1, 2]
1 2 [3, 4] [3, 4, 5] # is this [(3, 3), (3, 4), (3, 5), (4, 3), (4, 4), (4, 5)]
这个函数
该函数基于一个参数优雅地处理zip或product，并假设根据最长的zip_longest对象的长度进行压缩
from itertools import zip_longest, product
def xplode(df, explode, zipped=True):
method = zip_longest if zipped else product
rest = {*df} - {*explode}
zipped = zip(zip(*map(df.get, rest)), zip(*map(df.get, explode)))
tups = [tup + exploded
for tup, pre in zipped
for exploded in method(*pre)]
return pd.DataFrame(tups, columns=[*rest, *explode])[[*df]]
压缩
xplode(df, ['B', 'C'])
A B C
0 1 1.0 1
1 1 2.0 2
2 2 3.0 3
3 2 4.0 4
4 2 NaN 5
产品
xplode(df, ['B', 'C'], zipped=False)
A B C
0 1 1 1
1 1 1 2
2 1 2 1
3 1 2 2
4 2 3 3
5 2 3 4
6 2 3 5
7 2 4 3
8 2 4 4
9 2 4 5
新设置
稍微改变一下这个例子
df = pd.DataFrame({
'A': [1, 2],
'B': [[1, 2], [3, 4]],
'C': 'C',
'D': [[1, 2], [3, 4, 5]],
'E': [('X', 'Y', 'Z'), ('W',)]
})
df
A B C D E
0 1 [1, 2] C [1, 2] (X, Y, Z)
1 2 [3, 4] C [3, 4, 5] (W,)
压缩
xplode(df, ['B', 'D', 'E'])
A B C D E
0 1 1.0 C 1.0 X
1 1 2.0 C 2.0 Y
2 1 NaN C NaN Z
3 2 3.0 C 3.0 W
4 2 4.0 C 4.0 None
5 2 NaN C 5.0 None
产品
xplode(df, ['B', 'D', 'E'], zipped=False)
A B C D E
0 1 1 C 1 X
1 1 1 C 1 Y
2 1 1 C 1 Z
3 1 1 C 2 X
4 1 1 C 2 Y
5 1 1 C 2 Z
6 1 2 C 1 X
7 1 2 C 1 Y
8 1 2 C 1 Z
9 1 2 C 2 X
10 1 2 C 2 Y
11 1 2 C 2 Z
12 2 3 C 3 W
13 2 3 C 4 W
14 2 3 C 5 W
15 2 4 C 3 W
16 2 4 C 4 W
17 2 4 C 5 W
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[1,2]]})
pd.concat([df['A'], pd.DataFrame(df['B'].values.tolist())], axis = 1)\
.melt(id_vars = 'A', value_name = 'B')\
.dropna()\
.drop('variable', axis = 1)
A B
0 1 1
1 2 1
2 1 2
3 2 2
对我想到的这个方法有什么意见吗?或者同时做concat和melt被认为太“昂贵”?
选项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)")
性能
# 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)
