Pandas Dataframe: Split multiple columns each into two columns

Question

I have a pandas dataframe named df like this：

0   2J-AAB1 AA  AA  CC  CC  AA  AA  CC  AA  CC
1   2J-AAB4 AA  TA  TC  TC  GA  AA  CC  AA  CC
2   2J-AAB6 AA  TA  CC  CC  AA  AA  CC  AA  CC
3   2J-AAB8 AA  TT  TT  TT  GG  AA  TC  CC  CC
4   2J-AAB9 AA  TT  TT  TT  GG  AA  TC  CC  CC
5   2J-AABA AA  AA  CC  CC  GA  AG  CC  AA  CG
6   2J-AABE AA  TT  TT  TT  GG  AA  TC  CA  CC
7   2J-AABF AA  AA  CC  CC  AA  AA  CC  AA  CC
8   2J-AABH AA  TT  TT  TT  GG  AA  CC  AA  CC
9   2J-AABI AA  AA  CC  CC  AA  AA  CC  AA  CG

I want to split columns like "AA,AT,CC" etc all into two columns and get new data-frame like:

0   2J-AAB1 A   A   A   A   C   C   C   C   A   A   A   A   C   C   A   A   C   C
1   2J-AAB4 A   A   T   A   T   C   T   C   G   A   A   A   C   C   A   A   C   C
2   2J-AAB6 A   A   T   A   C   C   C   C   A   A   A   A   C   C   A   A   C   C
3   2J-AAB8 A   A   T   T   T   T   T   T   G   G   A   A   T   C   C   C   C   C
4   2J-AAB9 A   A   T   T   T   T   T   T   G   G   A   A   T   C   C   C   C   C
5   2J-AABA A   A   A   A   C   C   C   C   G   A   A   G   C   C   A   A   C   G
6   2J-AABE A   A   T   T   T   T   T   T   G   G   A   A   T   C   C   A   C   C
7   2J-AABF A   A   A   A   C   C   C   C   A   A   A   A   C   C   A   A   C   C
8   2J-AABH A   A   T   T   T   T   T   T   G   G   A   A   C   C   A   A   C   C
9   2J-AABI A   A   A   A   C   C   C   C   A   A   A   A   C   C   A   A   C   G

Is there a pythonic way to make it? Any suggestion are appreciated .. Thanks in advance

MaxU - stand with Ukraine · Accepted Answer · 2017-05-01 08:23:16Z

5

Try this:

In [60]: x = df.set_index(1).stack().str.extractall('(.)').unstack([-2, -1]).reset_index()

In [61]: x.columns = np.arange(len(x.columns))

In [62]: x
Out[62]:
        0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18
0  2J-AAB1  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
1  2J-AAB4  A  A  T  A  T  C  T  C  G  A  A  A  C  C  A  A  C  C
2  2J-AAB6  A  A  T  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
3  2J-AAB8  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
4  2J-AAB9  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
5  2J-AABA  A  A  A  A  C  C  C  C  G  A  A  G  C  C  A  A  C  G
6  2J-AABE  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  A  C  C
7  2J-AABF  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
8  2J-AABH  A  A  T  T  T  T  T  T  G  G  A  A  C  C  A  A  C  C
9  2J-AABI  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  G

answered May 1, 2017 at 8:23

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Iris Over a year ago

Awesome! Helps a lot, Thanks so much!

amit kumar Over a year ago

While this code snippet may be the solution, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Chuck · Accepted Answer · 2017-05-01 09:01:41Z

You have a good answer, but I started typing this so figure I'd leave it up.

You can use apply with split and list to output to multiple columns. For your dataframe with labels:

            A    B
0   "2J-AAB1" "AA" 
1   "2J-AAB4" "AA"  
2   "2J-AAB6" "AA" 
3   "2J-AAB8" "AA"


df['B1'], df['B2'] = zip(*df['B'].apply(lambda x: list(x)))

This gives you:

         A   B B2 B1
0  2J-AAB1  AA  A  A
1  2J-AAB4  AA  A  A
2  2J-AAB6  AA  A  A
3  2J-AAB8  AA  A  A

For more columns, or with specific columns names, can do:

for i in df.columns[1:]:
    df['{}1'.format(i)], df['{}2'.format(i)] = zip(*df[i].apply(lambda x: list(x)))

This gives:

         0   1   2   3   4   5   6   7   8   9 11 12 21 22 31 32 41 42 51 52 61 62 71 72 81 82 91 92
0  2J-AAB1  AA  AA  CC  CC  AA  AA  CC  AA  CC  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
1  2J-AAB4  AA  TA  TC  TC  GA  AA  CC  AA  CC  A  A  T  A  T  C  T  C  G  A  A  A  C  C  A  A  C  C
2  2J-AAB6  AA  TA  CC  CC  AA  AA  CC  AA  CC  A  A  T  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
3  2J-AAB8  AA  TT  TT  TT  GG  AA  TC  CC  CC  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
4  2J-AAB9  AA  TT  TT  TT  GG  AA  TC  CC  CC  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
5  2J-AABA  AA  AA  CC  CC  GA  AG  CC  AA  CG  A  A  A  A  C  C  C  C  G  A  A  G  C  C  A  A  C  G
6  2J-AABE  AA  TT  TT  TT  GG  AA  TC  CA  CC  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  A  C  C
7  2J-AABF  AA  AA  CC  CC  AA  AA  CC  AA  CC  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
8  2J-AABH  AA  TT  TT  TT  GG  AA  CC  AA  CC  A  A  T  T  T  T  T  T  G  G  A  A  C  C  A  A  C  C
9  2J-AABI  AA  AA  CC  CC  AA  AA  CC  AA  CG  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  G

Thanks for your help, you are so kind. And it works well. Thank you.

rnso · Accepted Answer · 2018-01-02 08:18:31Z

Very interesting question. One can solve it stepwise as follows:

dfpart = df.iloc[:,1:]                    # get columns to be split
ll = dfpart.values                        # get values as list of lists
sl = list(map(lambda x: "".join(x), ll))  # join all rows into strings
sl = list(map(list, sl))                  # split strings to lists of characters
newdf = pd.DataFrame(data=sl)             # create dataframe from new lists
newdf = pd.concat([df.iloc[:,0], newdf], axis=1) # restore first column
newdf.columns= range(len(newdf.columns))  # correct column numbers; 
print(newdf)

Output:

        0  1  2  3  4  5  6  7  8  9  10 11 12 13 14 15 16 17 18
0  2J-AAB1  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
1  2J-AAB4  A  A  T  A  T  C  T  C  G  A  A  A  C  C  A  A  C  C
2  2J-AAB6  A  A  T  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
3  2J-AAB8  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
4  2J-AAB9  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  C  C  C
5  2J-AABA  A  A  A  A  C  C  C  C  G  A  A  G  C  C  A  A  C  G
6  2J-AABE  A  A  T  T  T  T  T  T  G  G  A  A  T  C  C  A  C  C
7  2J-AABF  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  C
8  2J-AABH  A  A  T  T  T  T  T  T  G  G  A  A  C  C  A  A  C  C
9  2J-AABI  A  A  A  A  C  C  C  C  A  A  A  A  C  C  A  A  C  G

Collectives™ on Stack Overflow

Pandas Dataframe: Split multiple columns each into two columns

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related