All geek questions in one place

Effectively convert a column of rows to multiple columns of single characters in pandas

I have some pretty large data frames (> 1 million rows). In one column are rows of different lengths. I would like to break these lines into separate characters, with each individual character being placed in a column.

I can do this with pd.DataFrame.apply()- see below - however it is too slow to use in practice (and it also tends to crash the kernel).

import pandas as pd

df = pd.DataFrame(['AAVFD','TYU?W_Z', 'SomeOtherString', 'ETC.'], columns = ['One'])

print df
    One
0   AAVFD
1   TYU?W_Z
2   SomeOtherString
3   ETC.

Convert strings to lists of various lengths:

S1 = df.One.apply(list)
print S1
0                                  [A, A, V, F, D]
1                            [T, Y, U, ?, W, _, Z]
2    [S, o, m, e, O, t, h, e, r, S, t, r, i, n, g]
3                                     [E, T, C, .]
Name: One, dtype: object

Put each individual character in a column:

df2 = pd.DataFrame(S1.values.tolist())
print df2
   0  1  2  3     4     5     6     7     8     9    10    11    12    13  \
0  A  A  V  F     D  None  None  None  None  None  None  None  None  None   
1  T  Y  U  ?     W     _     Z  None  None  None  None  None  None  None   
2  S  o  m  e     O     t     h     e     r     S     t     r     i     n   
3  E  T  C  .  None  None  None  None  None  None  None  None  None  None   

     14  
0  None  
1  None  
2     g  
3  None

, . , - , numpy, df.One. , , , , .

+4
3

pandas, numpy ( Python 3, S1 '' U1 ' Python 2):

npchrs = df.values.astype(str).view('U1')
# array([['A', 'A', 'V', 'F', 'D', '', '', '', '', '', '', '', '', '', ''],
#        ['T', 'Y', 'U', '?', 'W', '_', 'Z', '', '', '', '', '', '', '', ''],
#        ['S', 'o', 'm', 'e', 'O', 't', 'h', 'e', 'r', 'S', 't', 'r', 'i', 'n', 'g'],
#        ['E', 'T', 'C', '.', '', '', '', '', '', '', '', '', '', '', '']],
#       dtype='<U1')

None pandas, , df .

@COLDSPEED , , . :

npobjs = npchrs.astype(object)
npobjs[npobjs==''] = None
# array([['A', 'A', 'V', 'F', 'D', None, None, None, None, None, None, None,
#         None, None, None],
#        ['T', 'Y', 'U', '?', 'W', '_', 'Z', None, None, None, None, None,
#         None, None, None],
#        ['S', 'o', 'm', 'e', 'O', 't', 'h', 'e', 'r', 'S', 't', 'r', 'i', 'n', 'g'],
#        ['E', 'T', 'C', '.', None, None, None, None, None, None, None, None,
#         None, None, None]], dtype=object)
+2

, , , , -

df = pd.DataFrame([list(x) for x in df.One])
df

  0  1  2  3     4     5     6     7     8     9     10    11    12    13  \
0  A  A  V  F     D  None  None  None  None  None  None  None  None  None   
1  T  Y  U  ?     W     _     Z  None  None  None  None  None  None  None   
2  S  o  m  e     O     t     h     e     r     S     t     r     i     n   
3  E  T  C  .  None  None  None  None  None  None  None  None  None  None   

     14  
0  None  
1  None  
2     g  
3  None  


df = pd.concat([df] * 10000, ignore_index=True)

# original answer
%timeit pd.DataFrame(df.One.apply(list).values.tolist())
10 loops, best of 3: 36.1 ms per loop

# Paul Panzer answer
%%timeit
npchrs = df.values.astype(str).view('U1')
npobjs = npchrs.astype(object)
npobjs[npobjs==''] = None
pd.DataFrame(npobjs)

10 loops, best of 3: 37.5 ms per loop

# My list comp answer 
%timeit pd.DataFrame([list(x) for x in df.One.values])
10 loops, best of 3: 32.8 ms per loop

# improved version of Paul Panzer answer
%timeit pd.DataFrame(df.values.astype(str).view('U1'))
10 loops, best of 3: 20.1 ms per loop

- , python, .

+2

Here's one approach using string-join, np.fromstringand masking(an idea borrowed from this post) -

def join_mask(df):
    lens = np.array([len(i) for i in df.One])
    n = lens.max()
    out = np.full((len(df),n), None)
    out[lens[:,None] > np.arange(n)] = np.fromstring(''.join(df.One), dtype='S1')
    return pd.DataFrame(out)

Run Example -

In [160]: df
Out[160]: 
               One
0            AAVFD
1          TYU?W_Z
2  SomeOtherString
3             ETC.

In [161]: join_mask(df)
Out[161]: 
  0  1  2  3     4     5     6     7     8     9     10    11    12    13    14
0  A  A  V  F     D  None  None  None  None  None  None  None  None  None  None
1  T  Y  U  ?     W     _     Z  None  None  None  None  None  None  None  None
2  S  o  m  e     O     t     h     e     r     S     t     r     i     n     g
3  E  T  C  .  None  None  None  None  None  None  None  None  None  None  None

Delay

Using the @ cᴏʟᴅs setup sync setup on approaches that create the correct Nonepopulated output df-

In [173]: df = pd.concat([df] * 10000, ignore_index=True)

# original answer
In [175]: %timeit pd.DataFrame(df.One.apply(list).values.tolist())
10 loops, best of 3: 27.2 ms per loop

# @Paul Panzer answer
In [176]: %%timeit
     ...: npchrs = df.values.astype(str).view('S1')
     ...: npobjs = npchrs.astype(object)
     ...: npobjs[npobjs==''] = None
     ...: pd.DataFrame(npobjs)
10 loops, best of 3: 20.3 ms per loop

# @cᴏʟᴅsᴘᴇᴇᴅ answer 
In [177]: %timeit pd.DataFrame([list(x) for x in df.One.values])
10 loops, best of 3: 27.6 ms per loop

# Using solution in this post
In [178]: %timeit join_mask(df)
100 loops, best of 3: 13.8 ms per loop
+2
source

Source: https://habr.com/ru/post/1691383/

More articles:


All Articles