Excel format processing and association with Python

hanxiucao_ yes 2022-05-14 13:48:36 阅读数:206

excelformatprocessingassociationpython
import pandas as pd
# 1. first excel Documentation and processing , If you read other sheet Parameters to be added, such as ,sheet_name='test'
excel_data=pd.read_excel('/Users/PycharmProjects/test_file/test01.xlsx')
# print(excel_data.columns)
# print(excel_data)
# The cells in the comment column are divided according to the newline character
df1=excel_data[' remarks '].str.split('\n',expand=True).stack()
df1=df1.reset_index(level=1,drop=True).rename(' remarks ')
# print(df1)
df1.rename(' remarks — split ',inplace=True)
# print(df1)
excel_data=excel_data.join(df1)
# print(excel_data.columns)
# Filter the row whose column is not empty
excel_data=excel_data.loc[excel_data[' remarks — split ']!='']
# Delete all characters before the first space , Such as 1. 2. Such characters
excel_data[' remarks — split 2']=excel_data[' remarks — split '].str.replace('^.*?(?= )','',regex=True)
# Remove spaces
excel_data[' Hint ']=excel_data[' remarks — split 2'].map(str.strip)
# print(excel_data)
# 2. the second excel file
tips_data=pd.read_excel('/Users/PycharmProjects/test_file/test02.xlsx')
# 3. Associate two files
target_data=excel_data.merge(tips_data,on=' Hint ',how='left',indicator=True)
# Remove unnecessary fields
target_data.drop(columns=[' Field ',' remarks — split ', ' remarks — split 2'],inplace=True)
# Sort by field
target_data=target_data.sort_values(by=' Serial number ')
target_data.to_excel('/Users/PycharmProjects/test_file/test03.xlsx')

版权声明:本文为[hanxiucao_ yes]所创,转载请带上原文链接,感谢。 https://pythonmana.com/2022/134/202205141335370868.html