将数据帧与列值匹配相结合

Question

如何使用python代码获得以下预期结果？

[DF1]                                    [DF2]
**Name** **Configure**                 **Name**
MD0001       2G                         MD0001
MD0002       3G                         MD0001
MD0003       4G                         MD0001
MD0002
MD0002
MD0003
MD0003
MD0003

预期结果：

Name    Configure
MD0001  2G
MD0001  2G
MD0001  2G
MD0002  3G
MD0002  3G
MD0003  4G
MD0003  4G
MD0003  4G

这是我到目前为止的代码：

data_xlsx = pd.read_excel(data,skiprows=1,sheet_name='KPI')
new_xlsx = pd.read_excel(new, skiprows=1,sheet_name='KPI')
data_df = pd.Dataframe[data_xlsx ]
new_xlsx = pd.Dataframe[new_xlsx ]

Answer 1

您应该使用merge或join。这是Panda实现类似SQL连接的方法。

# mock up the test dataframes
df1 = pd.DataFrame.from_records(
columns=["Name", "Configure"],
data = [
("MD0001", "2G"),
("MD0002", "3G"),
("MD0003", "4G")
]
)
df2 = pd.DataFrame.from_records(
columns = ["Name"],
data = [
("MD0001",),
("MD0001",),
("MD0001",),
("MD0002",),
("MD0002",),
("MD0003",),
("MD0003",),
("MD0003",)
]
)
# index df1 on Name
df1 = df1.set_index("Name")
# then join it to df2
df2.join(df1['Configure'], on="Name")

输出：

Name    Configure
0   MD0001  2G
1   MD0001  2G
2   MD0001  2G
3   MD0002  3G
4   MD0002  3G
5   MD0003  4G
6   MD0003  4G
7   MD0003  4G