python - 将数据框列与列表值匹配,并附加数据框与匹配的行
问题描述
我有两个不同的 csv,我在两个数据帧中读取。我想将列 df1['building_type] 与 df2['model'] 匹配,并将相应的行附加到 df1。
数据框 1:
data = [{'length': '34', 'width': '58.5', 'height': '60.2', 'building_type': ['concrete','wood','steel','laminate']},
{'length': '42', 'width': '33', 'height': '23', 'building_type': ['concrete_double','wood_double','steel_double']}]
df1 = pd.DataFrame(data)
print(df1)
数据框 2:
data2 = [{'type': 'A1', 'floor': '2', 'model': ['wood','laminate','concrete','steel']},
{'type': 'B3', 'floor': '4', 'model': ['wood_double','concrete_double','steel_double']}]
df2=pd.DataFrame(data2)
print(df2)
最终数据框:
length width height building_type type floor
0 34 58.5 60.2 [concrete, wood, steel, laminate] A1 2
1 42 33 23 [concrete_double, wood_double, steel_double] B3 4
解决方案
pd.merge
似乎是这里必要的工具,但我们需要一个不可变的 dtype。list
是可变的,不能加入。我们可以将list
(mutable) 转换为tuple
or frozenset
,这两者都是不可变的,可以用来加入。由于示例输出显示顺序无关紧要,我选择了frozenset
.
这是代码:
import pandas as pd
data = [{'length': '34', 'width': '58.5', 'height': '60.2', 'building_type': ['concrete','wood','steel','laminate']},
{'length': '42', 'width': '33', 'height': '23', 'building_type': ['concrete_double','wood_double','steel_double']}]
df1 = pd.DataFrame(data)
print(df1)
data2 = [{'type': 'A1', 'floor': '2', 'model': ['wood','laminate','concrete','steel']},
{'type': 'B3', 'floor': '4', 'model': ['wood_double','concrete_double','steel_double']}]
df2=pd.DataFrame(data2)
print(df2)
# Note: Merge fails on mutable dtype
# pd.merge(df1, df2, left_on='building_type', right_on='model')
# Produces `TypeError: unhashable type: 'list'`
# Convert mutable type to immutable type and merge.
# `tuple` is best if order matters for you. I am assuming that the
# order doesn't matter based on the sample output, so `frozenset` is more
# appropriate.
df1['building_type'] = df1['building_type'].apply(frozenset)
df2['model'] = df2['model'].apply(frozenset)
# Now, merge. Note that since column names are different both
# 'building_type' and 'model' would be retained. You can remove one of them.
final_df = pd.merge(df1, df2, left_on='building_type', right_on='model')
final_df = final_df.drop(['model'], axis=1)
print(final_df)
我机器上的输出:
length width height building_type
0 34 58.5 60.2 [concrete, wood, steel, laminate]
1 42 33 23 [concrete_double, wood_double, steel_double]
type floor model
0 A1 2 [wood, laminate, concrete, steel]
1 B3 4 [wood_double, concrete_double, steel_double]
length width height building_type type floor
0 34 58.5 60.2 (laminate, wood, steel, concrete) A1 2
1 42 33 23 (concrete_double, steel_double, wood_double) B3 4
推荐阅读
- javascript - 单击新链接时顶部的进度条
- go - Golang cookie not deleting
- c++ - C++中C的双指针等价物是什么?
- solr - SOLRCloud 7.3.1:CDCR 问题
- synchronization - 在 OpenCL 中为矩阵乘法示例实现互斥锁
- android - 如何从另一个片段中调用一个片段?
- dax - 不同表中两列的加权平均值
- sql - *** `./text_buffer' 中的错误:损坏的双链表:0x089cbd98 **
- ios - 在 GCD 中,何时使用全局并发队列与自定义并发队列?
- php - 使用 Twitch 的 API 一次请求获取多个频道