[已解决]pandas处理文件

王大可2000

运用pandas
1.现有Excel文件一，二，三，四，五是2003及以前版本生成的文件格式，需要转为 excel2007及以后版本生成的文件格式，数字格式默认为数值保留一位小数.
2.添加time列，格式要求yyyy-(/)mm-(/)dd，删除年月日列（默认存储在Sheet1）。
2.新建sheet，名为Sheet2，将sheet1中D列处理为月尺度存储在sheet2中（sheet1中A列为年B列为月C列为日，分别将某年某月的D列数据求和），
3.统计相关指标（在Sheet2中），五六七八列插入表头（默认第一行）”月降水均值，月降水中值，月降水极大值，月降水极小值“，(第二行)添加指标具体指标数据
4.存储为新excel，命名"N"+filename(原文件名)

最佳答案

月排行榜 / 总排行榜

歌者文明清理员

2023-5-16 17:34:48

• 转换文件格式并保留一位小数：
  
  1. 
     
  2. import pandas as pd
     
  3. 
     
  4. # 读取 Excel 文件
     
  5. file_path = "文件路径/文件名.xls"
     
  6. df = pd.read_excel(file_path, sheet_name=None, dtype='object')
     
  7. 
     
  8. # 将数据写入新的 Excel 文件中
     
  9. writer = pd.ExcelWriter('新文件路径/新文件名.xlsx', engine='xlsxwriter')
     
  10. for sheet_name in df.keys():
      
  11.     # 转换文件格式
      
  12.     df[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
      
  13.    
      
  14.     # 自动设置数字格式为数值，保留一位小数
      
  15.     workbook = writer.book
      
  16.     worksheet = writer.sheets[sheet_name]
      
  17.     num_format = workbook.add_format({'num_format': '0.0'})
      
  18.     for i, width in enumerate(get_col_widths(df[sheet_name])):
      
  19.         worksheet.set_column(i, i, width, num_format)
      
  20. 
      
  21. writer.save()

  复制代码
  
  
• 添加和删除列：
  
  1. # 添加 time 列
     
  2. df['Sheet1']['time'] = pd.to_datetime(df['Sheet1'][['年', '月', '日']])
     
  3. df['Sheet1']['time'] = df['Sheet1']['time'].dt.strftime('%Y-%m-%d')
     
  4. 
     
  5. # 删除年月日列
     
  6. df['Sheet1'] = df['Sheet1'].drop(columns=['年', '月', '日'])

  复制代码
  
  处理 D 列并存储到 Sheet2：
  
  1. # 提取年月信息，然后按照年月分组，将 D 列数据求和
     
  2. df['Sheet2'] = df['Sheet1'].groupby(['年', '月'])['D'].sum().reset_index()
     
  3. 
     
  4. # 在 Sheet2 中添加表头和相关指标数据
     
  5. df['Sheet2'].columns = ['年', '月', '月降水']
     
  6. df['Sheet2']['time'] = pd.to_datetime(df['Sheet2'][['年', '月']].assign(day=1))
     
  7. df['Sheet2']['月降水均值'] = df['Sheet2']['月降水'].mean()
     
  8. df['Sheet2']['月降水中值'] = df['Sheet2']['月降水'].median()
     
  9. df['Sheet2']['月降水极大值'] = df['Sheet2']['月降水'].max()
     
  10. df['Sheet2']['月降水极小值'] = df['Sheet2']['月降水'].min()
      
  11. 
      
  12. # 设置时间列格式为 yyyy-(/)mm-(/)dd
      
  13. df['Sheet2']['time'] = df['Sheet2']['time'].dt.strftime('%Y-%m-%d')
      
  14. 
      
  15. # 重新排列 Sheet2 中的列的顺序
      
  16. df['Sheet2'] = df['Sheet2'][['time', '月降水', '月降水均值', '月降水中值', '月降水极大值', '月降水极小值']]

  复制代码
  
  
• 存储新文件：
  
  1. # 将新的 Excel 文件保存
     
  2. new_file_path = '新文件路径/N' + file_path.split('/')[-1].replace('.xls', '') + '.xlsx'
     
  3. writer = pd.ExcelWriter(new_file_path, engine='xlsxwriter')
     
  4. for sheet_name in df.keys():
     
  5.     df[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
     
  6.    
     
  7.     # 自动设置数字格式为数值，保留一位小数
     
  8.     workbook = writer.book
     
  9.     worksheet = writer.sheets[sheet_name]
     
  10.     num_format = workbook.add_format({'num_format': '0.0'})
      
  11.     for i, width in enumerate(get_col_widths(df[sheet_name])):
      
  12.         worksheet.set_column(i, i, width, num_format)
      
  13. 
      
  14. writer.save()

  复制代码

跳转到最佳答案楼层

歌者文明清理员

• 转换文件格式并保留一位小数：
  
  1. 
     
  2. import pandas as pd
     
  3. 
     
  4. # 读取 Excel 文件
     
  5. file_path = "文件路径/文件名.xls"
     
  6. df = pd.read_excel(file_path, sheet_name=None, dtype='object')
     
  7. 
     
  8. # 将数据写入新的 Excel 文件中
     
  9. writer = pd.ExcelWriter('新文件路径/新文件名.xlsx', engine='xlsxwriter')
     
  10. for sheet_name in df.keys():
      
  11.     # 转换文件格式
      
  12.     df[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
      
  13.    
      
  14.     # 自动设置数字格式为数值，保留一位小数
      
  15.     workbook = writer.book
      
  16.     worksheet = writer.sheets[sheet_name]
      
  17.     num_format = workbook.add_format({'num_format': '0.0'})
      
  18.     for i, width in enumerate(get_col_widths(df[sheet_name])):
      
  19.         worksheet.set_column(i, i, width, num_format)
      
  20. 
      
  21. writer.save()

  复制代码
  
  
• 添加和删除列：
  
  1. # 添加 time 列
     
  2. df['Sheet1']['time'] = pd.to_datetime(df['Sheet1'][['年', '月', '日']])
     
  3. df['Sheet1']['time'] = df['Sheet1']['time'].dt.strftime('%Y-%m-%d')
     
  4. 
     
  5. # 删除年月日列
     
  6. df['Sheet1'] = df['Sheet1'].drop(columns=['年', '月', '日'])

  复制代码
  
  处理 D 列并存储到 Sheet2：
  
  1. # 提取年月信息，然后按照年月分组，将 D 列数据求和
     
  2. df['Sheet2'] = df['Sheet1'].groupby(['年', '月'])['D'].sum().reset_index()
     
  3. 
     
  4. # 在 Sheet2 中添加表头和相关指标数据
     
  5. df['Sheet2'].columns = ['年', '月', '月降水']
     
  6. df['Sheet2']['time'] = pd.to_datetime(df['Sheet2'][['年', '月']].assign(day=1))
     
  7. df['Sheet2']['月降水均值'] = df['Sheet2']['月降水'].mean()
     
  8. df['Sheet2']['月降水中值'] = df['Sheet2']['月降水'].median()
     
  9. df['Sheet2']['月降水极大值'] = df['Sheet2']['月降水'].max()
     
  10. df['Sheet2']['月降水极小值'] = df['Sheet2']['月降水'].min()
      
  11. 
      
  12. # 设置时间列格式为 yyyy-(/)mm-(/)dd
      
  13. df['Sheet2']['time'] = df['Sheet2']['time'].dt.strftime('%Y-%m-%d')
      
  14. 
      
  15. # 重新排列 Sheet2 中的列的顺序
      
  16. df['Sheet2'] = df['Sheet2'][['time', '月降水', '月降水均值', '月降水中值', '月降水极大值', '月降水极小值']]

  复制代码
  
  
• 存储新文件：
  
  1. # 将新的 Excel 文件保存
     
  2. new_file_path = '新文件路径/N' + file_path.split('/')[-1].replace('.xls', '') + '.xlsx'
     
  3. writer = pd.ExcelWriter(new_file_path, engine='xlsxwriter')
     
  4. for sheet_name in df.keys():
     
  5.     df[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)
     
  6.    
     
  7.     # 自动设置数字格式为数值，保留一位小数
     
  8.     workbook = writer.book
     
  9.     worksheet = writer.sheets[sheet_name]
     
  10.     num_format = workbook.add_format({'num_format': '0.0'})
      
  11.     for i, width in enumerate(get_col_widths(df[sheet_name])):
      
  12.         worksheet.set_column(i, i, width, num_format)
      
  13. 
      
  14. writer.save()

  复制代码

isdkz

你可以使用Python的pandas库和openpyxl库来完成这个任务。以下是一个示例程序，它按照你的要求进行操作：

1. import pandas as pd
   
2. import openpyxl
   
3. from datetime import datetime
   
4. 
   
5. # 遍历所有的文件
   
6. for i in range(1, 6):
   
7.     filename = f"文件{i}"
   
8.     df = pd.read_excel(filename + ".xls")  # 读取2003版本的excel文件
   
9. 
   
10.     # 转换数值列为保留一位小数的格式
    
11.     for col in df.select_dtypes(include=['float64', 'int64']).columns:
    
12.         df[col] = df[col].round(1)
    
13. 
    
14.     # 添加time列，并删除年月日列
    
15.     df["time"] = pd.to_datetime(df[['年', '月', '日']].astype(str).agg('-'.join, axis=1))
    
16.     df = df.drop(columns=['年', '月', '日'])
    
17. 
    
18.     # 将D列处理为月尺度，存储在新的dataframe中
    
19.     df['month_year'] = pd.to_datetime(df['time']).dt.to_period('M')
    
20.     df_month = df.groupby('month_year')['D'].sum().reset_index()
    
21. 
    
22.     # 计算相关指标，并添加到新的dataframe中
    
23.     df_month_stats = df.groupby('month_year')['D'].agg(['mean', 'median', 'max', 'min']).reset_index()
    
24.     df_month_stats.columns = ['month_year', '月降水均值', '月降水中值', '月降水极大值', '月降水极小值']
    
25. 
    
26.     # 创建一个ExcelWriter对象，并将两个dataframe写入到不同的sheets中
    
27.     with pd.ExcelWriter(f"N{filename}.xlsx", engine='openpyxl') as writer:
    
28.         df.to_excel(writer, sheet_name='Sheet1', index=False)
    
29.         df_month.to_excel(writer, sheet_name='Sheet2', index=False)
    
30.         df_month_stats.to_excel(writer, sheet_name='Sheet2', startcol=5, index=False)
    

复制代码

这个程序首先读取旧的Excel文件，然后将所有的数值列转换为保留一位小数的格式。然后，它创建一个新的"time"列，并删除"年"，"月"，"日"列。接下来，它将"D"列处理为月尺度，并存储在一个新的dataframe中。然后，它计算相关的统计指标，并将这些指标添加到新的dataframe中。最后，它将两个dataframes写入到新的Excel文件的不同sheets中。

注意，这个程序假设"年"，"月"，"日"列的名称分别为"年"，"月"，"日"，并且"D"列的名称为"D"。如果实际的列名称不同，你需要相应地修改这个程序。

此外，这个程序没有处理可能出现的错误，比如文件不存在，列名不正确等。在实际使用中，你可能需要添加错误处理的代码。

isdkz

1. 使用 pandas 的 read_excel 方法读取 Excel 文件，再使用 to_excel 方法将文件转换为 Excel2007 及以后版本的文件格式，并设置数字格式为保留一位小数。

1. import pandas as pd
3. # 读取 Excel 文件
4. df = pd.read_excel('文件名.xls', sheet_name='Sheet1')
6. # 转换文件格式并设置数字格式
7. df.to_excel('文件名.xlsx', sheet_name='Sheet1', index=False, float_format='%.1f')

复制代码

2. 使用 pandas 的 to_datetime 方法将年月日列转换为时间格式，并添加 time 列，再使用 drop 方法删除年月日列。

1. # 转换时间格式并添加 time 列
2. df['time'] = pd.to_datetime(df['年月日列'], format='%Y-%m-%d')
4. # 删除年月日列
5. df = df.drop(columns=['年月日列'])

复制代码

3. 使用 pandas 的 groupby 方法对 A 列和 B 列进行分组，并使用 sum 方法求和，再将结果存储到 Sheet2 中。

1. # 分组求和并存储到 Sheet2
2. df.groupby([df['A列'], df['B列']])['D列'].sum().reset_index(name='月尺度').to_excel('文件名.xlsx', sheet_name='Sheet2', index=False)

复制代码

4. 使用 pandas 的 DataFrame 方法创建一个新的 DataFrame，再使用 insert 方法插入表头和具体指标数据，最后使用 to_excel 方法将结果存储到新 Excel 文件中。

1. # 创建新的 DataFrame
2. new_df = pd.DataFrame(columns=['月降水均值', '月降水中值', '月降水极大值', '月降水极小值'])
4. # 插入表头和具体指标数据
5. new_df.insert(0, '月降水均值', [1, 2, 3, 4])
6. new_df.insert(1, '月降水中值', [5, 6, 7, 8])
7. new_df.insert(2, '月降水极大值', [9, 10, 11, 12])
8. new_df.insert(3, '月降水极小值', [13, 14, 15, 16])
10. # 存储为新 Excel 文件
11. new_df.to_excel('N文件名.xlsx', sheet_name='Sheet1', index=False)

复制代码

Axiujiu

等学到较高级的pandas和excel交互再过来认真学习。看能看懂，但不能写出来，是不是缺练？

王大可2000

歌者文明清理员 发表于 2023-5-16 17:34

转换文件格式并保留一位小数：

添加和删除列：

请问为什么get_col_widths会被标红
且提示ModuleNotFoundError: No module named 'xlsxwriter'
该如何解决

王大可2000

歌者文明清理员 发表于 2023-5-16 17:34

转换文件格式并保留一位小数：

添加和删除列：

请问get_col_widths函数是什么意思，为什么显示定义错误