Python对xlsx文件的操作

pip install openpyxl
import pandas as pd

import numpy as np
import warnings
warnings.filterwarnings("ignore") #以下操作有警告，不必在意，也可以不写这个

# 读取 Excel 文件，并设置 keep_default_na=False
data = pd.read_excel('chuji.xlsx', header=None, keep_default_na=False)

# 将数据类型统一为字符串类型
data = data.astype(str)

# 合并第0行和第1行的内容
data.iloc[0] = data.iloc[0].fillna('').astype(str) + data.iloc[1].fillna('').astype(str)
# data
# 在第8列和第9列的第0行加入新的列名
data.iloc[0, 8] = "国际旅游收入2018"
data.iloc[0, 9] = "国际旅游收入2019"
# data
# 删除第1行
data = data.drop(1).reset_index(drop=True) # 删除行后重新设置索引
# data
# 重新设置列名
data.columns = data.iloc[0]
# data
# 删除第0行（含列名）
data = data.drop(0).reset_index(drop=True) # 再次重新设置索引
data
# 去除第0列列名中的空格和问号
data.columns = data.columns.str.replace(r'[?\s]+', '', regex=True)

# 打印修改后的列名

print(data.columns[0])

 

# 去除第0列（地区列）中的空格和问号

data['地区'] = data['地区'].str.replace(r'[?\s]+', '', regex=True)

# 确认地区列是否在DataFrame中

print(data['地区'])

 

# 去除每一行首尾空白字符

data = data.map(lambda x: x.strip() if isinstance(x, str) else x)

 

# 去除空行

data = data[data.iloc[:, 0] != ''].reset_index(drop=True)

print(data['地区'])

data

 

# 删除重复行

data = data.drop_duplicates().reset_index(drop=True)

print(data['地区'])

data

# 将第三、四、五列转换为数值型

data.iloc[:, 2:5] = data.iloc[:, 2:5].apply(pd.to_numeric, errors='coerce')

 

# 计算第三、四、五列的和，并填充到第二列中

data['规模/限额以上文化及相关产业营业总收入（万元）'] = data.iloc[:, 2:5].sum(axis=1)

 

# 填充第二、三、四、五列中的空值

data.iloc[:, 1:5] = data.iloc[:, 1:5].fillna(0)

 

# 打印前几行确认结果

data

# 将第六列中的非数字字符串转换为 NaN 值

data.iloc[:, 5] = pd.to_numeric(data.iloc[:, 5], errors='coerce')

 

# 计算第六列的平均值

mean_value = data.iloc[:, 5].mean()

 

# 使用平均值填充第六列的空值

data.iloc[:, 5].fillna(mean_value, inplace=True)

data

# 计算第7列的中位数

data.iloc[:, 6] = pd.to_numeric(data.iloc[:, 6], errors='coerce')

median_value = data.iloc[:, 6].median()

 

# 使用中位数填充第7列的空值

data.iloc[:, 6] = data.iloc[:, 6].fillna(median_value)

data

 

#使用四分位法对第8列数据进行异常值处理，并将异常值设置为该列均值。

 

# 将第八列数据转换为数值型数据

data.iloc[:, 7] = pd.to_numeric(data.iloc[:, 7], errors='coerce')

 

# 删除转换后产生的缺失值

data = data.dropna(subset=[data.columns[7]])

 

# 计算第八列数据的第一四分位数（Q1）和第三四分位数（Q3）

Q1 = data.iloc[:, 7].quantile(0.25)

Q3 = data.iloc[:, 7].quantile(0.75)

 

# 计算四分位间距（IQR）

IQR = Q3 - Q1

 

# 计算异常值的上限和下限

upper_limit = Q3 + 1.5 * IQR

lower_limit = Q1 - 1.5 * IQR

print(upper_limit,lower_limit)

# 将大于上限或小于下限的值替换为该列的均值

mean_value = data.iloc[:, 7].mean()

data.iloc[:, 7] = data.iloc[:, 7].apply(lambda x: mean_value if x > upper_limit or x < lower_limit else x)

# 打印前几行确认结果

data

 

# 向前填充和向后填充

# data['国际旅游收入2018']

data.iloc[:, 8] = pd.to_numeric(data.iloc[:, 8], errors='coerce')

data.iloc[:, 9] = pd.to_numeric(data.iloc[:, 9], errors='coerce')

columns_to_fill = [ '国际旅游收入2018', '国际旅游收入2019'] # 根据实际情况填写列名  

data[columns_to_fill] = data[columns_to_fill].ffill(limit=1).bfill(limit=1)

data

 

  

# 将需要四舍五入的列转换为数值类型，并处理可能的NaN值  

for col in columns_to_round:  

    data[col] = pd.to_numeric(data[col], errors='coerce') # 转换为数值，无效值变为NaN  

    data[col].fillna(0, inplace=True)

  

# 现在可以安全地对这些列进行四舍五入  

data[columns_to_round] = data[columns_to_round].applymap(lambda x: round(x, 1) if not pd.isnull(x) else np.nan)  

 

# data[columns_to_round] = data[columns_to_round].fillna('') # 如果NaN应该被替换为空字符串  

# data[columns_to_round] = data[columns_to_round].fillna(0) # 如果NaN应该被替换为0  

  

# 显示数据以验证结果

data