问题:熊猫将列表的一列分为多列
我有一列的pandas DataFrame:
import pandas as pd
df = pd.DataFrame(
data={
"teams": [
["SF", "NYG"],
["SF", "NYG"],
["SF", "NYG"],
["SF", "NYG"],
["SF", "NYG"],
["SF", "NYG"],
["SF", "NYG"],
]
}
)
print(df)
输出:
teams
0 [SF, NYG]
1 [SF, NYG]
2 [SF, NYG]
3 [SF, NYG]
4 [SF, NYG]
5 [SF, NYG]
6 [SF, NYG]
如何将列表的这一列分为两列?
回答 0
您可以将DataFrame
构造函数与lists
创建者to_list
:
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)
回答 1
更简单的解决方案:
pd.DataFrame(df2["teams"].to_list(), columns=['team1', 'team2'])
Yield
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'])
回答 2
df2
与使用tolist()
以下解决方案的解决方案不同,此解决方案保留了DataFrame 的索引:
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
回答 3
与提议的解决方案相比,似乎在语法上更简单,因此更容易记住。我假设该列在数据帧df中称为“元”:
df2 = pd.DataFrame(df['meta'].str.split().values.tolist())
回答 4
根据先前的答案,这是另一个解决方案,它以更快的运行时间返回与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)
回答 5
由于我的nan
观察中有上述发现,上述解决方案对我不起作用dataframe
。就我而言,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
Yield:
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
回答 6
清单理解
列表理解的简单实现(我的最爱)
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
回答 7
这是另一个使用df.transform
和的解决方案df.set_index
:
>>> (df['teams']
.transform([lambda x:x[0], lambda x:x[1]])
.set_axis(['team1','team2'],
axis=1,
inplace=False)
)
team1 team2
0 SF NYG
1 SF NYG
2 SF NYG
3 SF NYG
4 SF NYG
5 SF NYG
6 SF NYG