CODEWITHSUNDEEP
pythonpandas
bluetooth
bluetooth

Reputation: 559

Splitting Pandas column into multiple columns

Is there a way in Pandas to split a column into multiple columns? I have a columns in a dataframe where the contents are as follows:

a
[c,a]
b

I would like to split this into:

colA colB colC
a    nan  nan
a    nan   c
a     b   nan

Please note the order of variables in the 2nd row in the original column. Thanks

Upvotes: 2

Views: 659

Answers (3)

frogcoder
frogcoder

Reputation: 1003

Assuming you get the column out as a series called s.

s = pd.Series(['a', ['c', 'a'], 'b'])
pd.DataFrame({"col" + x.upper(): s.apply(lambda n: x if x in n else np.NaN)
              for x in ['a', 'b', 'c']})

Upvotes: 0

piRSquared
piRSquared

Reputation: 294218

Consider the series s

s = pd.Series(['a', ['c', 'a'], 'b'])

s

0         a
1    [c, a]
2         b
dtype: object

Use pd.Series and '|'.join to magically turn into concatenated pipe separated strings. Use str.get_dummies to get array of zeros and ones. Multiply that by the columns to replace ones with column values. where masks the zeros and replaces with np.NaN.

d1 = s.apply(lambda x: '|'.join(pd.Series(x))).str.get_dummies()
d1.mul(d1.columns.values).where(d1.astype(bool))

     a    b    c
0    a  NaN  NaN
1    a  NaN    c
2  NaN    b  NaN

PROJECT/KILL 

import itertools

n = len(s)
i = np.arange(n).repeat([len(x) if hasattr(x, '__len__') else 1 for x in s])
j, u = pd.factorize(list(itertools.chain(*s)))
m = u.size
b = np.bincount(i * m + j, minlength=n * m).reshape(n, m)
pd.DataFrame(np.where(b, u, np.NaN), columns=u)

     a    b    c
0    a  NaN  NaN
1    a  NaN    c
2  NaN    b  NaN

Timing 

%%timeit
d1 = s.apply(lambda x: '|'.join(pd.Series(x))).str.get_dummies()
d1.mul(d1.columns.values).where(d1.astype(bool))

100 loops, best of 3: 2.58 ms per loop

%%timeit
n = len(s)
i = np.arange(n).repeat([len(x) if hasattr(x, '__len__') else 1 for x in s])
j, u = pd.factorize(list(itertools.chain(*s)))
m = u.size
b = np.bincount(i * m + j, minlength=n * m).reshape(n, m)
pd.DataFrame(np.where(b, u, np.NaN), columns=u)

1000 loops, best of 3: 287 µs per loop

%%timeit
s.apply(pd.Series)\
  .stack().str.get_dummies().sum(level=0)\
  .pipe(lambda x: x.mul(x.columns.values))\
  .replace('',np.nan)\
  .add_prefix('col')

100 loops, best of 3: 4.24 ms per loop

Upvotes: 2

Allen Qin
Allen Qin

Reputation: 19947

First stack the lists in the col column, get dummies for each element, and then transform them to a,b,c. Finally rename the columns.

df.col.apply(pd.Series)\
  .stack().str.get_dummies().sum(level=0)\
  .pipe(lambda x: x.mul(x.columns.values))\
  .replace('',np.nan)\
  .add_prefix('col')
Out[204]: 
  cola colb colc
0    a  NaN  NaN
1    a  NaN    c
2  NaN    b  NaN

Upvotes: 0

Related Questions