pandas :拆分数据框行并重新排列列值 [英] Pandas: Split a dataframe rows and re-arrange column values
本文介绍了 pandas :拆分数据框行并重新排列列值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个DataFrame
:
import pandas as pd
df = pd.DataFrame({'Board': ['A', 'B'], 'Off': ['C', 'D'], 'Stops': ['Q/W/E', 'Z'], 'Pax': [10, 100]})
如下所示:
Board Off Pax Stops
0 A C 10 Q/W/E
1 B D 100 Z
我想用Stops
列拆分DataFrame
,然后在Pax
值重复的行中重新排列为Board
和Off
,如下所示;
I want to have a DataFrame
split by Stops
column and re-arranged as Board
and Off
in rows with Pax
value being duplicated as follows;
Board Off Pax
0 A Q 10
1 Q W 10
2 W E 10
3 E C 10
4 B Z 100
5 Z D 100
任何对此的帮助将不胜感激.
Any help regarding this would be much appreciated.
推荐答案
from itertools import islice
#https://stackoverflow.com/a/6822773/2901002
#added a for return Pax
def window(a, seq, n=2):
"Returns a sliding window (of width n) over data from the iterable"
" s -> (s0,s1,...s[n-1]), (s1,s2,...,sn), ... "
it = iter(seq)
result = tuple(islice(it, n))
if len(result) == n:
yield (*result, a)
for elem in it:
result = result[1:] + (elem,)
yield (*result, a)
#join all columns
a = df['Board'] + '/' + df['Stops'] + '/' + df['Off']
#split to lists
a = a.str.split('/')
#apply window function to each value and flatten
L = [(j,k,l) for x, y in zip(a, df['Pax']) for j,k,l in list(window(y, x, 2)) ]
print (L)
[('A', 'Q', 10), ('Q', 'W', 10), ('W', 'E', 10),
('E', 'C', 10), ('B', 'Z', 100), ('Z', 'D', 100)]
#DataFrame constructor
df = pd.DataFrame(L, columns=['Board','Off','Pax'])
print (df)
Board Off Pax
0 A Q 10
1 Q W 10
2 W E 10
3 E C 10
4 B Z 100
5 Z D 100
时间:
import pandas as pd
N = 1000
L1 = list('ABCDEFGHIJKLMNOP')
L = ['Q/W/E','Q1/W1/E1','Z','A/B/C/D']
df = pd.DataFrame({'Board': np.random.choice(L1, N),
'Off': np.random.choice(L1, N),
'Stops': np.random.choice(L, N),
'Pax': np.random.randint(100, size=N)})
print (df)
def bharath(df):
df['New'] = (df['Board']+'/'+df['Stops']+'/'+df['Off']).str.split('/')
temp = df['New'].apply(lambda x : list(zip(x,x[1:])))
di = {0 : 'Board',1:'Off'}
return pd.concat([pd.DataFrame(i,index=np.repeat(j,len(i))) for (i,j) in zip(temp,df['Pax'].values)]).reset_index().rename(columns=di)
def jez(df):
from itertools import islice
#https://stackoverflow.com/a/6822773/2901002
#added a for return Pax
def window(a, seq, n=2):
"Returns a sliding window (of width n) over data from the iterable"
" s -> (s0,s1,...s[n-1]), (s1,s2,...,sn), ... "
it = iter(seq)
result = tuple(islice(it, n))
if len(result) == n:
yield (*result, a)
for elem in it:
result = result[1:] + (elem,)
yield (*result, a)
a = df['Board'] + '/' + df['Stops'] + '/' + df['Off']
a = a.str.split('/')
L = [(j,k,l) for x, y in zip(a, df['Pax']) for j,k,l in list(window(y, x, 2)) ]
return pd.DataFrame(L, columns=['Board','Off','Pax'])
def wen(df):
df['New']=df[['Board','Stops','Off']].apply(lambda x : '/'.join(x),1)
df['New2']= df['New'].str.split('/').apply(lambda x : list(zip(x[:-1],x[1:])))
namedict = {0 : 'Board',1:'Off'}
return df[['Pax','New2']].set_index('Pax').New2.apply(pd.Series).stack().apply(pd.Series).reset_index().drop('level_1',1).rename(columns=namedict)
print (jez(df))
#print (bharath(df))
print (wen(df))
In [433]: %timeit (jez(df))
100 loops, best of 3: 6.6 ms per loop
In [434]: %timeit (wen(df))
1 loop, best of 3: 747 ms per loop
In [450]: %timeit (bharath(df))
1 loop, best of 3: 406 ms per loop
这篇关于 pandas :拆分数据框行并重新排列列值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文