Create Matrix from a csv file - Python
Question: I am trying to read some numbers from a .csv file and store them into a matrix
using Python. The input file looks like this

> Input File
>  
>  
>     B,1
>     A,1
>     A,1
>     B,1
>     A,3
>     A,2
>     B,1
>     B,2
>     B,2
>  

The input is to be manipulated to a matrix like -

> Output File
>  
>  
>       1 2 3
>     A 2 1 1
>     B 3 2 0
>  

Here, the first column of the input file becomes the row, second column
becomes the column and the value is the count of the occurrence. How should I
implement this? The size of my input file is huge (1000000 rows) and hence
there can be large number of rows (anywhere between 50 to 10,000) and columns
(from 1 to 50)


Answer: With pandas, it becomes easy, almost in just 3 lines

    
    
    import pandas as pd
    
    df = pd.read_csv('example.csv', names=['label', 'value'])
    # >>> df
    #   label  value
    # 0     B      1
    # 1     A      1
    # 2     A      1
    # 3     B      1
    # 4     A      3
    # 5     A      2
    # 6     B      1
    # 7     B      2
    # 8     B      2
    
    s = df.groupby(['label', 'value']).size()
    # >>> s
    # label  value
    # A      1        2
    #        2        1
    #        3        1
    # B      1        3
    #        2        2
    # dtype: int64
    
    # ref1: http://stackoverflow.com/questions/15751283/converting-a-pandas-multiindex-dataframe-from-rows-wise-to-column-wise
    # ref2: http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.unstack.html
    m = s.unstack()
    # >>> m
    # value  1  2   3
    # label          
    # A      2  1   1
    # B      3  2 NaN
    
    # Below are optional: just to make it look more like what you want
    m.columns.name = None
    m.index.name = None
    m = m.fillna(0)
    print m
    #    1  2  3
    # A  2  1  1
    # B  3  2  0
    

