读取EXCEL中不同两列的相同的元素

for example:

• common UE Id 是第一列和第二列中重复出现的项

图片.png

code print

图片.png

Python code

import pandas as pd


# 读取Excel文件并将其转换为DataFrame
# df = pd.read_excel('example.xlsx')
# C:\PF1E5DG3-Data\xianaz\Desktop\5G_features\PRS_all\2023\7\03171293 DRB Drop Rate
#excelPath = r'C:\PF1E5DG3-Data\xianaz\Desktop\5G_features\PRS_all\2023\7\03171293 DRB Drop Rate\Book1.xlsx'
import os
current_path = os.getcwd()
print("current path is : ", current_path)
current_path = current_path + '\\readexcel'
print("current path 1 is : ", current_path)


#engine = "openpyxl"
#df = pd.read_excel("Book1.xlsx", engine=engine)
df = pd.read_excel(r"Book1.xlsx")

#print(df)
# 从DataFrame中选择要比较的两列数据
col1 = df['Column1']
print(col1)
col2 = df['Column2']

print("col1[0] value is: ", col1[0])

set1 = set(col1)
set2 = set(col2)
common_elements = set1 & set2


print("common elements: ")
print(common_elements)

common_elements = list(common_elements)
newCommonUEsList = type(col1)(common_elements)
print("col1 type is : ")
print(type(col1))
print(newCommonUEsList)
#col1 = newCommonUEsList
#print(col1)
#print(newCommonUEsList)
#{'key': ['A', 'B', 'C']
#df.add({'common UE Id': [common_elements]})

#df['common UE Id'] = common_elements  


#df["common UE Id"] = newCommonUEsList[:len(df)].reindex(df.index)  # 使用reindex()函数调整索引以匹配数据结构的长度
df["common UE Id"] = newCommonUEsList[:len(df)].reindex(df.index)



print(df)
# 将数据写回Excel文件
writer = pd.ExcelWriter("Book1.xlsx")
df.to_excel(writer, index=False)
writer._save()

#df[{'common UE Id': [common_elements]}]



#print(col1[0])
# 将这两列数据合并为一个新的DataFrame，只保留相同的值
#duplicates = pd.merge(col1, col2, how='inner')

# 将相同的值存储到一个列表中
#values = duplicates.values.tolist()

# 输出相同的值
print("Duplicate values: \n", df["common UE Id"])
#for value in values:
#    print(value)