Pandas: Split a dataframe rows and re-arrange column values

debugcn Published at Dev

Abhishek

I have a DataFrame :

import pandas as pd
df = pd.DataFrame({'Board': ['A', 'B'], 'Off': ['C', 'D'], 'Stops': ['Q/W/E', 'Z'], 'Pax': [10, 100]})

which looks like:

    Board   Off  Pax    Stops
0   A       C    10     Q/W/E
1   B       D    100    Z

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.

jezrael

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

Timings:

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

Collected from the Internet

Please contact [email protected] to delete if infringement.