Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert Two column data frame to occurrence matrix in pandas

Hi all I have a csv file which contains data as the format below

A   a
A   b
B   f
B   g
B   e
B   h
C   d
C   e
C   f

The first column contains items second column contains available feature from feature vector=[a,b,c,d,e,f,g,h] I want to convert this to occurence matrix look like below

    a,b,c,d,e,f,g,h
A   1,1,0,0,0,0,0,0
B   0,0,0,0,1,1,1,1
C   0,0,0,1,1,1,0,0

Can anyone tell me how to do this using pandas?

like image 450
Isura Nirmal Avatar asked Jul 20 '15 14:07

Isura Nirmal


People also ask

How do you convert a data frame to a matrix in python?

A two-dimensional rectangular array to store data in rows and columns is called python matrix. Matrix is a Numpy array to store data in rows and columns. Using dataframe. to_numpy() method we can convert dataframe to Numpy Matrix.

How do I convert multiple columns in Pandas?

You can use the DataFrame. apply() and pd. to_datetime() function to convert multiple columns to DataTime. apply() function applies a function to each and every row and column of the DataFrame.


2 Answers

Here is another way to do it using pd.get_dummies().

import pandas as pd

# your data
# =======================
df

  col1 col2
0    A    a
1    A    b
2    B    f
3    B    g
4    B    e
5    B    h
6    C    d
7    C    e
8    C    f

# processing
# ===================================
pd.get_dummies(df.col2).groupby(df.col1).apply(max)

      a  b  d  e  f  g  h
col1                     
A     1  1  0  0  0  0  0
B     0  0  0  1  1  1  1
C     0  0  1  1  1  0  0
like image 68
Jianxun Li Avatar answered Sep 23 '22 16:09

Jianxun Li


Unclear if your data has a typo or not but you can crosstab for this:

In [95]:
pd.crosstab(index=df['A'], columns = df['a'])

Out[95]:
a  b  d  e  f  g  h
A                  
A  1  0  0  0  0  0
B  0  0  1  1  1  1
C  0  1  1  1  0  0

In your sample data your second column has value a as the name of that column but in your expected output it's in the column as a value

EDIT

OK I fixed your input data so it generates the correct result:

In [98]:
import pandas as pd
import io
t="""A   a
A   b
B   f
B   g
B   e
B   h
C   d
C   e
C   f"""
df = pd.read_csv(io.StringIO(t), sep='\s+', header=None, names=['A','a'])
df

Out[98]:
   A  a
0  A  a
1  A  b
2  B  f
3  B  g
4  B  e
5  B  h
6  C  d
7  C  e
8  C  f

In [99]:
ct = pd.crosstab(index=df['A'], columns = df['a'])
ct

Out[99]:
a  a  b  d  e  f  g  h
A                     
A  1  1  0  0  0  0  0
B  0  0  0  1  1  1  1
C  0  0  1  1  1  0  0
like image 21
EdChum Avatar answered Sep 20 '22 16:09

EdChum