将Pandas列表列拆分为多个列

小开

最佳答案

你可以使用由to_list创建的lists构造函数DataFrame:

import pandas as pd


d1 = {'teams': [['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],
['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG']]}
df2 = pd.DataFrame(d1)
print (df2)
teams
0  [SF, NYG]
1  [SF, NYG]
2  [SF, NYG]
3  [SF, NYG]
4  [SF, NYG]
5  [SF, NYG]
6  [SF, NYG]

df2[['team1','team2']] = pd.DataFrame(df2.teams.tolist(), index= df2.index)
print (df2)
teams team1 team2
0  [SF, NYG]    SF   NYG
1  [SF, NYG]    SF   NYG
2  [SF, NYG]    SF   NYG
3  [SF, NYG]    SF   NYG
4  [SF, NYG]    SF   NYG
5  [SF, NYG]    SF   NYG
6  [SF, NYG]    SF   NYG

对于一个新的DataFrame:

df3 = pd.DataFrame(df2['teams'].to_list(), columns=['team1','team2'])
print (df3)
team1 team2
0    SF   NYG
1    SF   NYG
2    SF   NYG
3    SF   NYG
4    SF   NYG
5    SF   NYG
6    SF   NYG

使用apply(pd.Series)的解决方案非常缓慢:

#7k rows
df2 = pd.concat([df2]*1000).reset_index(drop=True)


In [121]: %timeit df2['teams'].apply(pd.Series)
1.79 s ± 52.5 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)


In [122]: %timeit pd.DataFrame(df2['teams'].to_list(), columns=['team1','team2'])
1.63 ms ± 54.3 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)

小开

与所提出的解决方案相比，似乎有一种语法更简单的方法，因此更容易记住。我假设这个列在数据框架df中叫做“meta”:

df2 = pd.DataFrame(df['meta'].str.split().values.tolist())

小开

更简单的解决方案:

pd.DataFrame(df2["teams"].to_list(), columns=['team1', 'team2'])

产量,

  team1 team2
-------------
0    SF   NYG
1    SF   NYG
2    SF   NYG
3    SF   NYG
4    SF   NYG
5    SF   NYG
6    SF   NYG
7    SF   NYG

如果你想拆分一列带分隔符的字符串而不是列表，你可以类似地做:

pd.DataFrame(df["teams"].str.split('<delim>', expand=True).values,
columns=['team1', 'team2'])

小开

这个解决方案保留了df2数据帧的索引，不像任何使用tolist()的解决方案:

df3 = df2.teams.apply(pd.Series)
df3.columns = ['team1', 'team2']

结果如下:

  team1 team2
0    SF   NYG
1    SF   NYG
2    SF   NYG
3    SF   NYG
4    SF   NYG
5    SF   NYG
6    SF   NYG

小开

根据前面的回答，下面是另一个解决方案，它返回与df2.teams.apply(pd.Series)相同的结果，但运行时间要快得多:

pd.DataFrame([{x: y for x, y in enumerate(item)} for item in df2['teams'].values.tolist()], index=df2.index)

计时:

In [1]:
import pandas as pd
d1 = {'teams': [['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],
['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG']]}
df2 = pd.DataFrame(d1)
df2 = pd.concat([df2]*1000).reset_index(drop=True)


In [2]: %timeit df2['teams'].apply(pd.Series)


8.27 s ± 2.73 s per loop (mean ± std. dev. of 7 runs, 1 loop each)


In [3]: %timeit pd.DataFrame([{x: y for x, y in enumerate(item)} for item in df2['teams'].values.tolist()], index=df2.index)


35.4 ms ± 5.22 ms per loop (mean ± std. dev. of 7 runs, 10 loops each)

小开

以前的解决方案并不适用于我，因为我在我的dataframe中有nan观察。在我的例子中，df2[['team1','team2']] = pd.DataFrame(df2.teams.values.tolist(), index= df2.index)产生:

object of type 'float' has no len()

我用一个列表理解来解决这个问题。下面是一个可复制的例子:

import pandas as pd
import numpy as np
d1 = {'teams': [['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],
['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG'],['SF', 'NYG']]}
df2 = pd.DataFrame(d1)
df2.loc[2,'teams'] = np.nan
df2.loc[4,'teams'] = np.nan
df2

输出:

        teams
0   [SF, NYG]
1   [SF, NYG]
2   NaN
3   [SF, NYG]
4   NaN
5   [SF, NYG]
6   [SF, NYG]


df2['team1']=np.nan
df2['team2']=np.nan

用列表推导法求解，

for i in [0,1]:
df2['team{}'.format(str(i+1))]=[k[i] if isinstance(k,list) else k for k in df2['teams']]


df2

收益率:

    teams   team1   team2
0   [SF, NYG]   SF  NYG
1   [SF, NYG]   SF  NYG
2   NaN        NaN  NaN
3   [SF, NYG]   SF  NYG
4   NaN        NaN  NaN
5   [SF, NYG]   SF  NYG
6   [SF, NYG]   SF  NYG

小开

< >强列表理解< / >强

带有列表理解的简单实现(我的最爱)

df = pd.DataFrame([pd.Series(x) for x in df.teams])
df.columns = ['team_{}'.format(x+1) for x in df.columns]

输出计时:

CPU times: user 0 ns, sys: 0 ns, total: 0 ns
Wall time: 2.71 ms

输出:

team_1    team_2
0    SF    NYG
1    SF    NYG
2    SF    NYG
3    SF    NYG
4    SF    NYG
5    SF    NYG
6    SF    NYG

小开

下面是另一个使用df.transform和df.set_index的解决方案:

>>> from operator import itemgetter
>>> df['teams'].transform({'item1': itemgetter(0), 'item2': itemgetter(1)})


team1 team2
0    SF   NYG
1    SF   NYG
2    SF   NYG
3    SF   NYG
4    SF   NYG
5    SF   NYG
6    SF   NYG

当然可以概括为:

>>> indices = range(len(df['teams'][0]))


>>> df['teams'].transform({f'team{i+1}': itemgetter(i) for i in indices})


team1 team2
0    SF   NYG
1    SF   NYG
2    SF   NYG
3    SF   NYG
4    SF   NYG
5    SF   NYG
6    SF   NYG

这种方法具有提取所需指标的额外好处:

>>> df
teams
0  [SF, NYG, XYZ, ABC]
1  [SF, NYG, XYZ, ABC]
2  [SF, NYG, XYZ, ABC]
3  [SF, NYG, XYZ, ABC]
4  [SF, NYG, XYZ, ABC]
5  [SF, NYG, XYZ, ABC]
6  [SF, NYG, XYZ, ABC]


>>> indices = [0, 2]
>>> df['teams'].transform({f'team{i+1}': itemgetter(i) for i in indices})


team1 team3
0    SF   XYZ
1    SF   XYZ
2    SF   XYZ
3    SF   XYZ
4    SF   XYZ
5    SF   XYZ
6    SF   XYZ

小开

我想推荐一种更有效的python方法。

首先定义DataFrame作为原始post:

df = pd.DataFrame({"teams": [["SF", "NYG"] for _ in range(7)]})

我的解决方案:

%%timeit
df['team1'], df['team2'] = zip(*list(df['teams'].values))
>> 761 µs ± 8.35 µs per loop

相比之下，获得最多好评的解决方案是:

%%timeit
df[['team1','team2']] = pd.DataFrame(df.teams.tolist(), index=df.index)
df = pd.DataFrame(df['teams'].to_list(), columns=['team1','team2'])
>> 1.31 ms ± 11.2 µs per loop

我的解决方案节省了40%的时间，而且时间短得多。你唯一需要记住的是如何通过使用zip(*list)来解包和重塑二维列表。

小开

您可以尝试使用两次apply在df中创建新列'team1'和'team2'

df = pd.DataFrame({"teams": [["SF", "NYG"] for _ in range(7)]})
df["team1"]=df['teams'].apply(lambda x: x[0]  )
df["team2"]=df['teams'].apply(lambda x: x[1]  )
df

enter image description here

小开

如果有人来这里找现成的函数，我写了一个。

如果columns未指定，它会找到所有带有列表的列并展开它们;
增加了名为column_name_0， column_name_1等的列;
列的顺序在final df中保持不变;
如果strict=True，它检查给定列中的列表是否大小相等。

感谢改进和评论。

def unfold_columns(df, columns=[], strict=False):
assert isinstance(columns, list), "Columns should be a list of column names"
if len(columns) == 0:
columns = [
column for column in df.columns
if df.applymap(lambda x: isinstance(x, list)).all()[column]
]
else:
assert(all([(column in df.columns) for column in columns])), \
"Not all given columns are found in df"
columns_order = df.columns
for column_name in columns:
if df[column_name].apply(lambda x: isinstance(x, list)).all():
if strict:
assert len(set(df[column_name].apply(lambda x: len(x)))) == 1, \
f"Lists in df['{column_name}'] are not of equal length"
unfolded = pd.DataFrame(df[column_name].tolist())
unfolded.columns = [f'{column_name}_{x}' for x in unfolded.columns]
columns_order = [
*columns_order[:list(columns_order).index(column_name)],
*unfolded.columns,
*columns_order[list(columns_order).index(column_name)+1:]
]
df = df.join(unfolded).drop([column_name], axis=1)
return df[columns_order]