String split a row in a table in every nth space

Question

I have this dataset where I am trying to split the OrtoB column to allow for my data to be organized A to B in a many to many interaction.

Example dataset

   new_name  Score            OrtoA                                      OrtoB
0         1   3064   g2797.t1 1.000                              YHR165C 1.000
1         2   2820   g2375.t1 1.000                              YJL130C 1.000
2         3   2711   g1023.t1 1.000                              YLR106C 1.000
3         4   2710  g15922.t1 1.000                              YNR016C 1.000
4         5   2568   g3549.t1 1.000                              YDL171C 1.000
5         6   2494  g10464.t1 1.000  YOR153W 1.000 YDR406W 0.585 YOR328W 0.454
6         7   2402  g15604.t1 1.000                YGR032W 1.000 YLR342W 0.679

I have been able to split a string so far by using the code below in python and follow the example from an earlier answered post pandas: How do I split text in a column into multiple rows? .

z = pd.read_table("table.augustus",header=0)
col_name =z.columns[0]
z = z.rename(columns = {col_name:'new_name'}
z['OrtoB'].str.split(" ").apply(Series,1).stack()

However it only works if there is only one space where I am trying to split. What I am looking for is help with splitting on every 2nd space to get a result like below.

Desired Result

        OrtoA       OrtoB   
g2797.t1    1   YHR165C 1
g2375.t1    1   YJL130C 1
g1023.t1    1   YLR106C 1
g15922.t1   1   YNR016C 1
g3549.t1    1   YDL171C 1
g10464.t1   1   YOR153W 1
g10464.t1   1   YDR406W 0.585
g10464.t1   1   YOR328W 0.454
g15604.t1   1   YGR032W 1
g15604.t1   1   YLR342W 0.679

I think something went wrong with the formatting when you printed / copy pasted your Example dataset. Columns do not seem to match the column names. — Elmex80s, Apr 28 '17 at 21:13

Serge · Answer 1 · 2017-04-28T21:46:39.603

If some of desired columns have a space inside, then merge what is needed after split. Also you can use split() without any arguments, instead of split(" "). Work even if tabs or other white-space is used.

 def concat_pairs(l)
     return [ "%s %s" % (l[i], l[i+1] for i, x in enumerate(l) if not i % 2]
 concat_pairs( z['OrtoB'].str ).apply( ...

An easier fix is re.split

 re.split('[a-f]+ [a-f]+', z['OrtoB'].str).apply(...

score 1 · Accepted Answer · answered Apr 29 '17 at 06:13

You can use:

#column into lists
orto = z['OrtoB'].str.split()
#remove all empty lists
orto = orto[orto.astype(bool)]
#get lengths of lists, but floor divide by 2 because pairs
lens = orto.str.len() // 2
#explode nested lists to array
orto2 = np.concatenate(orto.values)
#repeat index to explode
idx = z.index.repeat(lens)
#create DataFrame and join both column together
s = pd.DataFrame(orto2.reshape(-1,2), index=idx).apply(' '.join, axis=1).rename('OrtoB')
#remove original column and join s
z = z.drop('OrtoB', axis=1).join(s).reset_index(drop=True)
print (z)
   new_name  Score            OrtoA          OrtoB
0         1   3064   g2797.t1 1.000  YHR165C 1.000
1         2   2820   g2375.t1 1.000  YJL130C 1.000
2         3   2711   g1023.t1 1.000  YLR106C 1.000
3         4   2710  g15922.t1 1.000  YNR016C 1.000
4         5   2568   g3549.t1 1.000  YDL171C 1.000
5         6   2494  g10464.t1 1.000  YOR153W 1.000
6         6   2494  g10464.t1 1.000  YDR406W 0.585
7         6   2494  g10464.t1 1.000  YOR328W 0.454
8         7   2402  g15604.t1 1.000  YGR032W 1.000
9         7   2402  g15604.t1 1.000  YLR342W 0.679

MaxU - stand with Ukraine · Answer 3 · 2017-05-05T00:08:52.723

Here is my solution:

# split `OrtoB` into lists
df['OrtoB'] = df['OrtoB'].str.findall(r'([A-Z\d]{6,}\s[\d\.]+)')

# now we can use the same technique as in: http://stackoverflow.com/a/40449726/5741205    
def split_list_in_cols_to_rows(df, lst_cols, fill_value=''):
    # make sure `lst_cols` is a list
    if lst_cols and not isinstance(lst_cols, list):
        lst_cols = [lst_cols]
    # all columns except `lst_cols`
    idx_cols = df.columns.difference(lst_cols)

    # calculate lengths of lists
    lens = df[lst_cols[0]].str.len()

    return pd.DataFrame({
        col:np.repeat(df[col].values, df[lst_cols[0]].str.len())
        for col in idx_cols
    }).assign(**{col:np.concatenate(df[col].values) for col in lst_cols}) \
      .append(df.loc[lens==0, idx_cols]).fillna(fill_value) \
      .loc[:, df.columns]

Result:

In [30]: %paste
df['OrtoB'] = df['OrtoB'].str.findall(r'([A-Z\d]{6,}\s[\d\.]+)')
new = split_list_in_cols_to_rows(df, 'OrtoB')

new
## -- End pasted text --
Out[30]:
   new_name  Score            OrtoA          OrtoB
0         1   3064   g2797.t1 1.000  YHR165C 1.000
1         2   2820   g2375.t1 1.000  YJL130C 1.000
2         3   2711   g1023.t1 1.000  YLR106C 1.000
3         4   2710  g15922.t1 1.000  YNR016C 1.000
4         5   2568   g3549.t1 1.000  YDL171C 1.000
5         6   2494  g10464.t1 1.000  YOR153W 1.000
6         6   2494  g10464.t1 1.000  YDR406W 0.585
7         6   2494  g10464.t1 1.000  YOR328W 0.454
8         7   2402  g15604.t1 1.000  YGR032W 1.000
9         7   2402  g15604.t1 1.000  YLR342W 0.679

this worked wonderful and thanks for putting it in a function. — user1238097, May 01 '17 at 19:17

score 0 · Answer 4 · answered Apr 28 '17 at 22:46

Based on the answer you quoted, I've got something working:

import pandas as pd
s = df.OrtoB.str.split(' ').apply(pd.Series, 1).stack()
s.index = s.index.droplevel(-1)
#merge 2 consecutive OrtoB values in to 1 and separated by ' '.
s = pd.DataFrame(data = s.values.reshape(-1,2),index=s.index[::2]).apply(lambda x: ' '.join(x), axis=1)
del(df['OrtoB'])
s.name = 'OrtoB'
df.join(s)
Out[148]: 
   new_name  Score            OrtoA          OrtoB
0         1   3064   g2797.t1 1.000  YHR165C 1.000
1         2   2820   g2375.t1 1.000  YJL130C 1.000
2         3   2711   g1023.t1 1.000  YLR106C 1.000
3         4   2710  g15922.t1 1.000  YNR016C 1.000
4         5   2568   g3549.t1 1.000  YDL171C 1.000
5         6   2494  g10464.t1 1.000  YOR153W 1.000
5         6   2494  g10464.t1 1.000  YDR406W 0.585
5         6   2494  g10464.t1 1.000  YOR328W 0.454
6         7   2402  g15604.t1 1.000  YGR032W 1.000
6         7   2402  g15604.t1 1.000  YLR342W 0.679

String split a row in a table in every nth space

4 Answers4