Python实现对三维数据表格的整理

目录

前言

一、数据展示

二、数据处理

1.输入数据

2.整理数据

3.输出数据

 三、补充整理

1.函数归纳

2.扩充整理

---

前言

        通常我们对数据的存储，都是分时间段进行的。当我们想要分析某些指标关于时间的变化趋势时，就涉及到需要调用历年的数据。

        本文则演示了用excel表所存储的三维数据的一个整理。

---

一、数据展示

        在一个excel表“Data.xlsx”中，含有以下三个工作表，表名分别是“2021”，“2022”和“2023”，各表中都展示了当年三个城市的3个指标，而每个表中城市的排序是不一致的。

城市	指标1	指标2	指标3
北京	6.8	0.7	5.24
上海	9.23	0.83	8.94
重庆	4.58	0.89	2.91

城市	指标1	指标2	指标3
上海	8.6	0.73	2.73
北京	6.83	0.66	9.36
重庆	4.61	0.85	5.21

城市	指标1	指标2	指标3
重庆	4.78	0.85	5.31
北京	6.19	0.59	11.35
上海	8.91	0.75	2.60

二、数据处理

1.输入数据

导入所需库pandas

输入整个excel表，赋值给 excel_file

各工作表的名称，赋值给 worksheet_names

第一个工作表，赋值给 first_df

import pandas as pd

excel_file = pd.ExcelFile('Data.xlsx')
worksheet_names = excel_file.sheet_names

first_df = pd.read_excel(excel_file, sheet_name = 0,index_col=0)# 首个工作表

2.整理数据

        这一部分主要包括两个循环：

• 内层循环得到一个工作表的每一列，并存储在dfs中。
• 外层循环将每个工作表内的列合并，并将各工作表依次存储在results中。

# 创建空列表以存储每个工作表
results = []
for city in first_df.index:
    
    dfs = [] # 清空列表，用于存储一个工作表的各列
    for year in worksheet_names:
        
        # 导入工作表
        df = pd.read_excel(excel_file, sheet_name = year,index_col=0)

        ser = df.loc[city]         # 找到对应的城市
        new_df = pd.DataFrame(ser) # 转化为DataFrame
        new_df.columns = [year]    # 设置列名

        dfs.append(new_df)
    
    result = pd.concat(dfs, axis=1)# 合并表格
    result = result.T              # 转置

    results.append(result)

3.输出数据

        首先，创建一个ExcelWriter对象 writer ；然后循环依次导出工作表；同时可以使用write方法，补充你想在表格里添加的内容；最后保存文件。

# 创建一个ExcelWriter对象
writer = pd.ExcelWriter('Output.xlsx')

# 将每个dataframe写入Excel文件中的工作表
for i,result in enumerate(results):
    result.to_excel(writer, sheet_name = first_df.index[i])# 导出工作表
    
    # 添加索引的列名
    worksheet = writer.sheets[first_df.index[i]]
    worksheet.write(0,0,"年份")# 在（0,0）位置上写入“年份”两字
    
# 保存Excel文件 
writer.save()

        于是，就可以得到如下表格（下图为输出的excel表内的第一个工作表“北京”的数据展示）。

 三、补充整理

1.函数归纳

        我将上方第二部分的代码整理成了如下函数，你只要输入所需整理的excel表和输出的文件名即可完成整理。

def year_to_city(path,output_name):# path文件所在位置，output_name 输出的文件名
    
    '''输入数据'''
    excel_file = pd.ExcelFile(path)# 导入excel表
    worksheet_names = excel_file.sheet_names# 每个工作表的名字
    first_df = pd.read_excel(excel_file, sheet_name = 0,index_col=0)# 导入第一个工作表
    
    '''处理数据'''
    # 创建空列表以存储每个工作表
    results = []
    for city in first_df.index:

        dfs = [] # 清空列表，用于存储一个工作表的各列
        for year in worksheet_names:

            # 导入工作表
            df = pd.read_excel(excel_file, sheet_name = year,index_col=0)

            ser = df.loc[city]         # 找到对应的城市
            new_df = pd.DataFrame(ser) # 转化为DataFrame
            new_df.columns = [year]    # 设置列名

            dfs.append(new_df)

        result = pd.concat(dfs, axis=1)# 合并表格
        result = result.T              # 转置

        results.append(result)
    
    '''输出数据'''
    # 创建一个ExcelWriter对象
    writer = pd.ExcelWriter(output_name)

    # 将每个dataframe写入Excel文件中的工作表
    for i,result in enumerate(results):
        result.to_excel(writer, sheet_name = first_df.index[i])# 导出工作表

        # 添加索引的列名
        worksheet = writer.sheets[first_df.index[i]]
        worksheet.write(0, 0, "年份")

    # 保存Excel文件 
    writer.save()

path = 'Data.xlsx'
output_name  = 'Output.xlsx'
year_to_city(path,output_name)

2.扩充整理

        当然，我们还可以整理成多级索引的表格，在第二部分的第2步后，可以再运行以下代码，将所有工作表整合到同一个工作表中。

df_z = pd.concat(results, axis=1)# 合并表格

# 创建一个带有顶级列名的MultiIndex
columns = pd.MultiIndex.from_product([first_df.index,first_df.columns])
df_z.columns = columns

df_z.columns.names = ['城市', '指标']
df_z.index.names = ["年份"]

df_z

        显示的df_z表格如下：

        另外，我们还能通过以下方法，进一步整理表格 。

1. stack() 和unstack()：实现索引轴向转换
2. swaplevel()：交换索引层级

df_z = df_z.stack(level=0) # 将顶级列名转换为内层行名

df_z = df_z.swaplevel(0,1) # 交换第0和1层行名的层级

    举例演示，下方左图为使用上方第一行代码后的df_z表，右图为使用上方两行代码后的df_z表。