Splitting a each column value into different columns

user_10422855 0
Viewed 243

I have a survey response sheet which has questions which can have multiple answers, selected using a set of checkboxes.

When I get the data from the response sheet and import it into pandas I get this:

             Timestamp           Sports you like          Age
0  23/11/2013 13:22:30   Football, Chess, Cycling          15
1  23/11/2013 13:22:34                   Football          25
2  23/11/2013 13:22:39          Swimming,Football          22
3  23/11/2013 13:22:45              Chess, Soccer          27
4  23/11/2013 13:22:48                     Soccer          30

There can be any number of sport values in sports column (further rows has basketball,volleyball etc.) and there are still some other columns. I'd like to do statistics on the results of the question (how many people liked Football,etc). The problem is, that all of the answers are within one column, so grouping by that column and asking for counts doesn't work.

Is there a simple way within Pandas to convert this sort of data frame into one where there are multiple columns called Sports-Football, Sports-Volleyball, Sports-Basketball, and each of those is boolean (1 for yes, 0 for no)? I can't think of a sensible way to do this

What I need is a new dataframe that looks like this (along with Age column) - 

          Timestamp     Sports-Football   Sports-Chess  Sports-Cycling ....  
0  23/11/2013 13:22:30          1                 1               1
1  23/11/2013 13:22:34          1                 0               0
2  23/11/2013 13:22:39          1                 0               0
3  23/11/2013 13:22:45          0                 1               0

I tried till this point can't proceed further.

df['Sports you like'].str.split(',\s*')

which splits into different columns but the first column may have any sport, I need only 1 in first column if the user likes Football or 0.

google-forms pandas python-3.x
1 Answers

Problem is separator ,\s*, so solution is add str.split with str.join before str.get_dummies:

df1 = (df.pop('Sports you like').str.split(',\s*')
                                .str.join('|')
                                .str.get_dummies()
                                .add_prefix('Sports-'))

df = df.join(df1)
print (df)
             Timestamp  Age  Sports-Chess  Sports-Cycling  Sports-Football  \
0  23/11/2013 13:22:30   15             1               1                1   
1  23/11/2013 13:22:34   25             0               0                1   
2  23/11/2013 13:22:39   22             0               0                1   
3  23/11/2013 13:22:45   27             1               0                0   
4  23/11/2013 13:22:48   30             0               0                0   

   Sports-Soccer  Sports-Swimming  
0              0                0  
1              0                0  
2              0                1  
3              1                0  
4              1                0

Or use MultiLabelBinarizer:

from sklearn.preprocessing import MultiLabelBinarizer

mlb = MultiLabelBinarizer()
s = df.pop('Sports you like').str.split(',\s*')
df1 = pd.DataFrame(mlb.fit_transform(s),columns=mlb.classes_).add_prefix('Sports-')
print (df1)
   Sports-Chess  Sports-Cycling  Sports-Football  Sports-Soccer  \
0             1               1                1              0   
1             0               0                1              0   
2             0               0                1              0   
3             1               0                0              1   
4             0               0                0              1   

   Sports-Swimming  
0                0  
1                0  
2                1  
3                0  
4                0  

df = df.join(df1)
print (df)
             Timestamp  Age  Sports-Chess  Sports-Cycling  Sports-Football  \
0  23/11/2013 13:22:30   15             1               1                1   
1  23/11/2013 13:22:34   25             0               0                1   
2  23/11/2013 13:22:39   22             0               0                1   
3  23/11/2013 13:22:45   27             1               0                0   
4  23/11/2013 13:22:48   30             0               0                0   

   Sports-Soccer  Sports-Swimming  
0              0                0  
1              0                0  
2              0                1  
3              1                0  
4              1                0
user_29010020
Related