如何取消嵌套(爆炸)pandas DataFrame中的列? [英] How to unnest (explode) a column in a pandas DataFrame?
问题描述
我有以下DataFrame,其中一列是对象(列表类型单元格):
I have the following DataFrame where one of the columns is an object (list type cell):
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[1,2]]})
df
Out[458]:
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:当单元格内容为列表时,为列表中的每个元素创建一行
很好的问答,但只能处理列表中的一列(在我的回答中,自定义功能可用于多列,也可以接受的答案是使用最耗时的apply
,不建议这样做,请查看更多信息何时应该使用大熊猫apply()在我的代码中?)
Good question and answer but only handle one column with list(In my answer the self-def function will work for multiple columns, also the accepted answer is use the most time consuming apply
, which is not recommended, check more info When should I ever want to use pandas apply() in my code?)
推荐答案
作为同时使用R
和python
的用户,我已经多次看到这种类型的问题.
As a user with both R
and python
, I have seen this type of question a couple of times.
在R中,它们具有名为unnest
的软件包tidyr
中的内置函数.但是在Python
(pandas
)中,没有针对此类问题的内置函数.
In R, they have the built-in function from package tidyr
called unnest
. But in Python
(pandas
) there is no built-in function for this type of question.
我知道object
列type
总是使pandas
'函数难以转换数据.当我收到这样的数据时,想到的第一件事就是弄平"或取消嵌套列.
I know object
columns type
always make the data hard to convert with a pandas
' function. When I received the data like this , the first thing that came to mind was to 'flatten' or unnest the columns .
我正在使用pandas
和python
函数来解决此类问题.如果您担心上述解决方案的速度,请检查user3483203的答案,因为他正在使用numpy
,并且大多数时候numpy
更快.如果您的速度很重要,我建议Cpython
和 numba
.
I am using pandas
and python
functions for this type of question. If you are worried about the speed of the above solutions, check user3483203's answer , since he is using numpy
and most of the time numpy
is faster . I recommend Cpython
and numba
if speed matters in your case.
方法0 [大熊猫> = 0.25]
从熊猫0.25 ,如果只需要爆炸一个列,则可以使用explode
函数:
Method 0 [pandas >= 0.25]
Starting from pandas 0.25, if you only need to explode one column, you can use the explode
function:
df.explode('B')
A B
0 1 1
1 1 2
0 2 1
1 2 2
方法1
apply + pd.Series
(易于理解,但不建议在性能方面进行推荐.)
Method 1
apply + pd.Series
(easy to understand but in terms of performance not recommended . )
df.set_index('A').B.apply(pd.Series).stack().reset_index(level=0).rename(columns={0:'B'})
Out[463]:
A B
0 1 1
1 1 2
0 2 1
1 2 2
方法2
将repeat
与DataFrame
构造函数一起使用,重新创建您的数据框(擅长性能,不擅长多列)
Method 2
Using repeat
with DataFrame
constructor , re-create your dataframe (good at performance, not good at multiple columns )
df=pd.DataFrame({'A':df.A.repeat(df.B.str.len()),'B':np.concatenate(df.B.values)})
df
Out[465]:
A B
0 1 1
0 1 2
1 2 1
1 2 2
方法2.1
例如,除了A,我们还有A.1 ..... A.n.如果我们仍然使用上面的方法(方法2 ),则很难一一重新创建列.
Method 2.1
for example besides A we have A.1 .....A.n. If we still use the method(Method 2) above it is hard for us to re-create the columns one by one .
解决方案:join
或merge
,其中index
在嵌套"单列之后
Solution : join
or merge
with the index
after 'unnest' the single columns
s=pd.DataFrame({'B':np.concatenate(df.B.values)},index=df.index.repeat(df.B.str.len()))
s.join(df.drop('B',1),how='left')
Out[477]:
B A
0 1 1
0 2 1
1 1 2
1 2 2
如果您需要与以前完全相同的列顺序,请在末尾添加reindex
.
If you need the column order exactly the same as before, add reindex
at the end.
s.join(df.drop('B',1),how='left').reindex(columns=df.columns)
方法3
重新创建list
Method 3
recreate the list
pd.DataFrame([[x] + [z] for x, y in df.values for z in y],columns=df.columns)
Out[488]:
A B
0 1 1
1 1 2
2 2 1
3 2 2
如果多于两列,请使用
s=pd.DataFrame([[x] + [z] for x, y in zip(df.index,df.B) for z in y])
s.merge(df,left_on=0,right_index=True)
Out[491]:
0 1 A B
0 0 1 1 [1, 2]
1 0 2 1 [1, 2]
2 1 1 2 [1, 2]
3 1 2 2 [1, 2]
方法4
使用reindex
或loc
Method 4
using reindex
or loc
df.reindex(df.index.repeat(df.B.str.len())).assign(B=np.concatenate(df.B.values))
Out[554]:
A B
0 1 1
0 1 2
1 2 1
1 2 2
#df.loc[df.index.repeat(df.B.str.len())].assign(B=np.concatenate(df.B.values))
方法5
当列表仅包含唯一值时:
Method 5
when the list only contains unique values:
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[3,4]]})
from collections import ChainMap
d = dict(ChainMap(*map(dict.fromkeys, df['B'], df['A'])))
pd.DataFrame(list(d.items()),columns=df.columns[::-1])
Out[574]:
B A
0 1 1
1 2 1
2 3 2
3 4 2
方法6
使用numpy
获得高性能:
Method 6
using numpy
for high performance:
newvalues=np.dstack((np.repeat(df.A.values,list(map(len,df.B.values))),np.concatenate(df.B.values)))
pd.DataFrame(data=newvalues[0],columns=df.columns)
A B
0 1 1
1 1 2
2 2 1
3 2 2
方法7
使用基本函数itertools
cycle
和chain
:纯粹的python解决方案,只是很有趣
Method 7
using base function itertools
cycle
and chain
: Pure python solution just for fun
from itertools import cycle,chain
l=df.values.tolist()
l1=[list(zip([x[0]], cycle(x[1])) if len([x[0]]) > len(x[1]) else list(zip(cycle([x[0]]), x[1]))) for x in l]
pd.DataFrame(list(chain.from_iterable(l1)),columns=df.columns)
A B
0 1 1
1 1 2
2 2 1
3 2 2
归纳为多列
df=pd.DataFrame({'A':[1,2],'B':[[1,2],[3,4]],'C':[[1,2],[3,4]]})
df
Out[592]:
A B C
0 1 [1, 2] [1, 2]
1 2 [3, 4] [3, 4]
自定义功能:
def unnesting(df, explode):
idx = df.index.repeat(df[explode[0]].str.len())
df1 = pd.concat([
pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
df1.index = idx
return df1.join(df.drop(explode, 1), how='left')
unnesting(df,['B','C'])
Out[609]:
B C A
0 1 1 1
0 2 2 1
1 3 3 2
1 4 4 2
列式嵌套
以上所有方法都在谈论 垂直 的嵌套和爆炸,如果您确实需要扩展列表 水平 ,使用pd.DataFrame
构造函数
Column-wise Unnesting
All above method is talking about the vertical unnesting and explode , If you do need expend the list horizontal, Check with pd.DataFrame
constructor
df.join(pd.DataFrame(df.B.tolist(),index=df.index).add_prefix('B_'))
Out[33]:
A B C B_0 B_1
0 1 [1, 2] [1, 2] 1 2
1 2 [3, 4] [3, 4] 3 4
更新的功能
def unnesting(df, explode, axis):
if axis==1:
idx = df.index.repeat(df[explode[0]].str.len())
df1 = pd.concat([
pd.DataFrame({x: np.concatenate(df[x].values)}) for x in explode], axis=1)
df1.index = idx
return df1.join(df.drop(explode, 1), how='left')
else :
df1 = pd.concat([
pd.DataFrame(df[x].tolist(), index=df.index).add_prefix(x) for x in explode], axis=1)
return df1.join(df.drop(explode, 1), how='left')
测试输出
unnesting(df, ['B','C'], axis=0)
Out[36]:
B0 B1 C0 C1 A
0 1 2 1 2 1
1 3 4 3 4 2
这篇关于如何取消嵌套(爆炸)pandas DataFrame中的列?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!