title: Convert tables to presence/absence matrix python
body: <p>From two tabular files:</p>
  
  <p>file1.txt</p>
  
  <pre><code>name1  house1
  name2  house1
  name3  house1
  name4  house2
  name5  house2
  name6  house2
  </code></pre>
  
  <p>and file2.txt</p>
  
  <pre><code>name1  car
  name2  bike
  name3  skate
  name4  car
  name5  motorcycle
  name6  boat
  </code></pre>
  
  <p>I want to use the information from both dictionaries to create a presence/absence matrix like this.</p>
  
  <pre><code>       car  motorcycle  bike  boat  skate
  house1  1       0         1     0     1
  house2  1       1         0     1     0
  </code></pre>
  
  <p>Here is my code:</p>
  
  <pre><code>import pandas as pd
  
  with open('file1.txt', 'r') as file1:
      col_names = ['name', 'house']
      df1 = pd.read_csv(file1, sep='\t', header=None, names=col_names)
  
  with open('file2.txt', 'r') as file2:
      col_names = ['name', 'transport']
      df2 = pd.read_csv(file2, sep='\t', header=None, names=col_names)
  
      # include the values from df1 into the df2 creating a new column        
      df2['house'] = df2['name'].map(df1.set_index('name')['house'])
  
      g = df2.groupby('house')['transport'].apply(list).reset_index()
  
  
      g.join(pd.get_dummies(g['transport'].apply(pd.Series).stack()).sum(level=0)).drop('transport', 1)
  
      print g
  </code></pre>
  
  <p>Doing this, I obtained this output:</p>
  
  <pre><code>    house                transport
  0  house1       [car, bike, skate]
  1  house2  [car, motorcycle, boat]
  </code></pre>