How to convert the data as following in python?
<p>I have some data in the following format in a csv file.</p>

<pre><code>   Id   Category
    1   A
    2   B
    3   C
    4   B
    5   C
    6   d
</code></pre>

<p>I'd like to convert it into the below format and save it another csv file</p>

<pre><code>Id  A   B   C   D   E
1   1   0   0   0   0
2   0   1   0   0   0
3   0   0   1   0   0
4   0   1   0   0   0
5   0   0   1   0   0
6   0   0   0   1   0
</code></pre>

<p>Try with <code>pd.get_dummies()</code></p>

<pre><code>&gt;&gt; df = pd.read_csv(&lt;path_to_file&gt;, sep=',', encoding='utf-8', header=0)

&gt;&gt; df
   Id   Category
0   1          A
1   2          B
2   3          C
3   4          B
4   5          C
5   6          d

&gt;&gt; pd.get_dummies(df.Category)
</code></pre>

<p>This will encode <code>Category</code> and give you new columns: </p>

<pre><code>A B C d
</code></pre>

<p>But will not 'fix' d -> D and will not give you any columns that can not be deduced from the values you have in <code>Category</code>.</p>

<p>I suggest you check the solution posted in the comment earlier for that.</p>

<p><strong>EDIT</strong></p>

<pre><code># Load data from .CSV with pd.read_csv() as demonstrated above

In [13]: df
Out[13]: 
  Category  Id
0        A   1
1        B   2
2        C   3
3        B   4
4        C   5
5        D   6

## One-liner for hot-encoding, then concatenating to original dataframe 
## and finally dropping the old column 'Category', you can skip the 
## last part if you want to keep original column as well.
In [14]: df = pd.concat([df, pd.get_dummies(df.Category)], axis=1).drop('Category', axis=1)

In [15]: df
Out[15]: 
   Id    A    B    C    D
0   1  1.0  0.0  0.0  0.0
1   2  0.0  1.0  0.0  0.0
2   3  0.0  0.0  1.0  0.0
3   4  0.0  1.0  0.0  0.0
4   5  0.0  0.0  1.0  0.0
5   6  0.0  0.0  0.0  1.0

## Write to file
In [16]: df.to_csv(&lt;output_path&gt;, sep='\t', encoding='utf-8', index=None)
</code></pre>

<p>As you can see this is not the Transpose, only the result of the hot-encoding of the <code>Category</code> column is added to each row.</p>

<p>Whether Excel accepts the final data or not, there's not much you can do with Pandas about this, unfortunately.</p>

<p>I hope this helps.</p>

python python-3.x pandas text-processing spyder