将表转换为状态/缺席矩阵python

Question

从两个表格文件：

文件1.txt

name1  house1
name2  house1
name3  house1
name4  house2
name5  house2
name6  house2

和file2.txt

^{pr2}$

我想使用这两个字典中的信息来创建一个像这样的存在/缺席矩阵。在

       car  motorcycle  bike  boat  skate
house1  1       0         1     0     1
house2  1       1         0     1     0

这是我的代码：

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

这样，我得到了以下输出：

    house                transport
0  house1       [car, bike, skate]
1  house2  [car, motorcycle, boat]

Answer 1

有一种方法。在

设置

# df1
  individual   house
0      name1  house1
1      name2  house1
2      name3  house1
3      name4  house2
4      name5  house2
5      name6  house2

# df2
  individual   transport
0      name1         car
1      name2        bike
2      name3       skate
3      name4         car
4      name5  motorcycle
5      name6        boat

解决方案

^{pr2}$

结果

    house  bike  boat  car  motorcycle  skate
0  house1     1     0    1           0      1
1  house2     0     1    1           1      0

说明

有三个步骤：

1. 将'house'列添加到df2，映射自df1。在
2. 集体送货上门。在
3. 使用pd.get_dummies将transports列展开为伪列。在