In python how split the dataset column to multiple column

user_15057569 0
Viewed 66

How can we split month column into different columns?

Sample data:

    EmployeeId  City     join_month
0   001        Mumbai        1
1   001        Bangalore     3
2   002        Pune          2
3   002        Mumbai        6
4   003        Delhi         9
5   003        Mumbai        12
6   004        Bangalore     11
7   004        Pune          10
8   005        Mumbai         5

Need an output like

    EmployeeId  City     join_month    join_month_jan    jan_count  
0   001        Mumbai        1                 1/True         1 
1   001        Bangalore     3                 0/False      
2   002        Pune          2                 0/False          
3   002        Mumbai        6
4   003        Delhi         9
5   003        Mumbai        12
6   004        Bangalore     11
7   004        Pune          10
8   005        Mumbai         5
data-science pandas python-3.x python
1 Answers

You can use get_dummies with add missing months by DataFrame.reindex, then rename columns and add to original DataFrame:

look_up = {1: 'jan', 2: 'feb', 3: 'mar', 4: 'apr', 5: 'may',
           6: 'jun', 7: 'jul', 8: 'aug', 9: 'sep',
           10: 'oct', 11: 'nov', 12: 'dec'}


df1 = (pd.get_dummies(df['join_month'])
         .reindex(range(1,13), axis=1, fill_value=0)
         .rename(columns=look_up)
         .add_prefix('join_month_'))
# print (df1)


df = df.join(df1)
print (df)

 EmployeeId       City  join_month  join_month_jan  join_month_feb  \
0        001     Mumbai           1               1               0   
1        001  Bangalore           3               0               0   
2        002       Pune           2               0               1   
3        002     Mumbai           6               0               0   
4        003      Delhi           9               0               0   
5        003     Mumbai          12               0               0   
6        004  Bangalore          11               0               0   
7        004       Pune          10               0               0   
8        005     Mumbai           5               0               0   

   join_month_mar  join_month_apr  join_month_may  join_month_jun  \
0               0               0               0               0   
1               1               0               0               0   
2               0               0               0               0   
3               0               0               0               1   
4               0               0               0               0   
5               0               0               0               0   
6               0               0               0               0   
7               0               0               0               0   
8               0               0               1               0   

   join_month_jul  join_month_aug  join_month_sep  join_month_oct  \
0               0               0               0               0   
1               0               0               0               0   
2               0               0               0               0   
3               0               0               0               0   
4               0               0               1               0   
5               0               0               0               0   
6               0               0               0               0   
7               0               0               0               1   
8               0               0               0               0   

   join_month_nov  join_month_dec  
0               0               0  
1               0               0  
2               0               0  
3               0               0  
4               0               0  
5               0               1  
6               1               0  
7               0               0  
8               0               0
user_29010020
Related