Python轻松解决excel文件处理

请先下载好pandas和openpyxl两个库：

pip install -i https://pypi.tuna.tsinghua.edu.cn/simple pandas
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple openpyxl

提示：进行数据比对前或者关联前要进行左右两端去空格

from openpyxl import load_workbook

# 打开Excel文件
wb = load_workbook('your_file.xlsx')

# 选择要操作的工作表
sheet = wb['Sheet1']  # 将'Sheet1'替换为实际的工作表名

# 遍历所有单元格，清除左右两端的空格
for row in sheet.iter_rows():
    for cell in row:
        if cell.value:
            cell.value = cell.value.strip()

# 保存修改后的Excel文件
wb.save('modified_file.xlsx')

# 关闭Excel文件
wb.close()

1.取消excel里面的合并单元格

import pandas as pd
​
# 读取 Excel 文件
df = pd.read_excel('C:\\Users\\Desktop\\江西省4级行政4级流域分区表 (version 1).xls', sheet_name='Sheet5')
​
# 将合并单元格的数据向上移动到第一个单元格
df_shifted = df.applymap(lambda x: x if pd.notnull(x) else '').shift(0)
​
# 填充拆分后的单元格
df_filled = df_shifted.ffill()
​
# 输出取消合并单元格后的数据
print(df_filled)
​
# 将数据输出到 Excel 文件
df_filled.to_excel('demo1.xlsx', index=False)
​

2.取消excel里面的合并单元格,并且拆分的单元格数据都和合并之前的一样

import pandas as pd
from openpyxl import load_workbook
​
# 读取 Excel 文件
file_path = r'C:/Users/Desktop/test.xlsx'
df = pd.read_excel(file_path, sheet_name='Sheet1')
​
# 创建一个新的数据框，与原始数据框 df 的大小相同
df_expanded = pd.DataFrame(index=range(df.shape[0]), columns=range(df.shape[1]))
​
# 加载 Excel 文件以获取合并单元格信息
wb = load_workbook(file_path)
​
# 选择指定的工作表
ws_name = 'Sheet1'
ws = wb[ws_name]
merged_cells_ranges = ws.merged_cells
​
# 遍历原始数据框中的每个单元格
for row in range(df.shape[0]):
    for col in range(df.shape[1]):
        value = df.iloc[row, col]
​
        # 检查当前单元格是否处于一个合并单元格
        merged_range = None
        for cell_range in merged_cells_ranges:
            if (cell_range.min_row <= row + 1 <= cell_range.max_row and
                    cell_range.min_col <= col + 1 <= cell_range.max_col):
                merged_range = cell_range
                break
​
        # 如果单元格是合并的
        if merged_range:
            start_row, end_row, start_col, end_col = (merged_range.min_row, merged_range.max_row,
                                                      merged_range.min_col, merged_range.max_col)
​
            # 填充合并前的值到拆分后的单元格
            df_expanded.loc[start_row-1:end_row, start_col-1:end_col] = value
​
        # 如果单元格没有合并，直接复制到新的数据框
        elif pd.notnull(value):
            df_expanded.loc[row, col] = value
​
# 输出处理后的数据
print(df_expanded)
​
# 将数据输出到 Excel 文件
df_expanded.to_excel('demo2.xlsx', index=False)

3.消除所有单元格数据间存在空格的情况

import pandas as pd
import re
from openpyxl import load_workbook
​
# 读取 Excel 文件
file_path = r'C:/Users/Desktop/test.xlsx'
df = pd.read_excel(file_path, sheet_name='Sheet1')
​
# 创建一个新的数据框，与原始数据框 df 的大小相同
df_expanded = pd.DataFrame(index=range(df.shape[0]), columns=range(df.shape[1]))
​
# 加载 Excel 文件以获取合并单元格信息
wb = load_workbook(file_path)
​
# 选择指定的工作表
ws_name = 'Sheet1'
ws = wb[ws_name]
merged_cells_ranges = ws.merged_cells
​
# 遍历原始数据框中的每个单元格
for row in range(df.shape[0]):
    for col in range(df.shape[1]):
        value = df.iloc[row, col]
​
        # 检查当前单元格是否处于一个合并单元格
        merged_range = None
        for cell_range in merged_cells_ranges:
            if (cell_range.min_row <= row + 1 <= cell_range.max_row and
                    cell_range.min_col <= col + 1 <= cell_range.max_col):
                merged_range = cell_range
                break
​
        # 如果单元格是合并的
        if merged_range:
            start_row, end_row, start_col, end_col = (merged_range.min_row, merged_range.max_row,
                                                      merged_range.min_col, merged_range.max_col)
​
            # 填充合并前的值到拆分后的单元格，并去除空格
            value_no_space = re.sub(r'\s+', '', str(value))  # 使用正则表达式去除多个连续的空格
            df_expanded.loc[start_row-1:end_row, start_col-1:end_col] = value_no_space
​
        # 如果单元格没有合并，直接复制到新的数据框，并去除空格
        elif pd.notnull(value):
            value_no_space = re.sub(r'\s+', '', str(value))  # 使用正则表达式去除多个连续的空格
            df_expanded.loc[row, col] = value_no_space
​
# 输出处理后的数据
print(df_expanded)
​
# 将数据输出到 Excel 文件
df_expanded.to_excel('demo2.xlsx', index=False)

4.让空格数据向上填充空白单元格

import pandas as pd
​
# 读取 Excel 文件
file_path = r'C:\Users\PycharmProjects\untitled7\data\demo2.xlsx'
df = pd.read_excel(file_path, sheet_name='Sheet1')
​
# 使用 fillna() 方法向上填充空白单元格
df_filled = df.fillna(method='ffill')
​
# 输出处理后的数据
print(df_filled)
​
# 将数据输出到 Excel 文件
df_filled.to_excel('data\\demo3.xlsx', index=False)

5.去除某列数据间的空格

import pandas as pd
​
# 读取 Excel 文件
file_path = r'C:\Users\PycharmProjects\untitled7\data\demo3.xlsx'
df = pd.read_excel(file_path, sheet_name='Sheet1')
​
# 使用正则表达式去除 Unnamed: 2 列单元格数据内部的空格
df['Unnamed: 2'] = df['Unnamed: 2'].apply(lambda x: ''.join(x.split()) if isinstance(x, str) else x)
​
# 输出处理后的数据
print(df)
​
# 将数据输出到 Excel 文件，并将空值替代字符串为空字符串
df.to_excel('data\\demo5.xlsx', index=False, na_rep='')

6.对某字段重复的进行去重，取重复中的第一个行。

import pandas as pd
​
# 读取 Excel 文件
file_path = r'C:\Users\PycharmProjects\数据中台\data\demo3.xlsx'
df = pd.read_excel(file_path)
​
# 根据某列字段去除重复行，保留每个重复字段的第一个行数据
df = df.drop_duplicates(subset=['Unnamed: 6'], keep='first')
​
# 输出处理后的数据
print(df)
​
# 将数据输出到 Excel 文件
df.to_excel('data\\demo4.xlsx', index=False)

7.两表关联，取需要的字段。

import pandas as pd
​
# 读取 Excel 文件
file_path = r'C:\Users\PycharmProjects\数据中台\data\demo3.xlsx'
df = pd.read_excel(file_path)
​
# 根据某列字段去除重复行，保留每个重复字段的第一个行数据
df = df.drop_duplicates(subset=['Unnamed: 6'], keep='first')
​
# 输出处理后的数据
print(df)
​
# 将数据输出到 Excel 文件
df.to_excel('data\\demo4.xlsx', index=False)

8.合并两个excel中的某两列为新的一列

import pandas as pd
​
# 读取第一个Excel文件
df1 = pd.read_excel(r'C:\Users\PycharmProjects\数据中台\data\demo8.xlsx')
​
# 读取第二个Excel文件
df2 = pd.read_excel(r'C:\Users\PycharmProjects\数据中台\data\demo4.xlsx')
​
# 按列拼接两个DataFrame对象
merged_df = pd.concat([df1['Unnamed: 3'], df2['Unnamed: 6']], axis=0, ignore_index=True)
​
# 导出到Excel文件
merged_df.to_excel('data\\demo10.xlsx', index=False)

9.在其中一个excel中的某列和另一个excel中的某列进行对比，分别找出前面excel有的字段，后面那个excel没有的字段和后面那个excel有的字段，前面excel没有的字段进行分别输出

import pandas as pd
​
# 读取第一个Excel文件
df1 = pd.read_excel(r'C:\Users\PycharmProjects\数据中台\data\demo10.xlsx')
​
# 读取第二个Excel文件
df2 = pd.read_excel(r'C:\Users\PycharmProjects\数据中台\data\ATT_WRZ_BASE.xlsx')
​
# 合并两个DataFrame对象
merged_df = pd.merge(df1, df2, left_on='code', right_on='WRZ_CODE', how='outer')
​
# 提取前一个文件存在后一个文件不存在的字段
diff_df1 = merged_df.loc[merged_df['WRZ_CODE'].isnull(), 'code']
​
# 提取后一个文件存在前一个文件不存在的字段
diff_df2 = merged_df.loc[merged_df['code'].isnull(), 'WRZ_CODE']
​
# 导出到Excel文件
diff_df1.to_excel('data\\diff_output_df1.xlsx', index=False)
diff_df2.to_excel('data\\diff_output_df2.xlsx', index=False)