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

Answer 1

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

Answer 2

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

Answer 3

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