Convert dataframe Pandas using pre-defined columns

user_10988616 0
Viewed 60

I have a Pandas DataFrame with multiple rows and columns as follows:

      a    b   c   d
0     2    1   3   1
1     2    2   1   1      
2     2    3   6   1    
3     2    4   4   1

I want to convert the above DataFrame to matrix based on column b as rows and column c as column. Using crosstab, I will get:

c   1   3   4   6
b
1   0   1   0   0
2   1   0   0   0
3   0   0   0   1
4   0   0   1   0

What I want is the column should show from 1-6 including 2 and 5 as below.

c   1   2   3   4   5   6
b
1   0   0   1   0   0   0
2   1   0   0   0   0   0
3   0   0   0   0   0   1
4   0   0   0   1   0   0

How to obtain the above form using Pandas?

pandas numpy python
1 Answers

Use DataFrame.reindex with minimal and maximal columns:

df1 = df.reindex(np.arange(df.columns.min(), df.columns.max() + 1), axis=1, fill_value=0)
print (df1)
   1  2  3  4  5  6
c                  
1  0  0  1  0  0  0
2  1  0  0  0  0  0
3  0  0  0  0  0  1
4  0  0  0  1  0  0
user_29010020
Related