trans_methods.py_1 6.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202
  1. # -*- coding: utf-8 -*-
  2. # @Time : 2024/5/16
  3. # @Author : 魏志亮
  4. import datetime
  5. from os import *
  6. import shutil
  7. import warnings
  8. import chardet
  9. import pandas as pd
  10. from utils.log.trans_log import trans_print
  11. warnings.filterwarnings("ignore")
  12. # 获取文件编码
  13. def detect_file_encoding(filename):
  14. # 读取文件的前1000个字节(足够用于大多数编码检测)
  15. with open(filename, 'rb') as f:
  16. rawdata = f.read(1000)
  17. result = chardet.detect(rawdata)
  18. encoding = result['encoding']
  19. trans_print("文件类型:", filename, encoding)
  20. if encoding is None:
  21. encoding = 'gb18030'
  22. if encoding.lower() in ['utf-8', 'ascii', 'utf8']:
  23. return 'utf-8'
  24. return 'gb18030'
  25. def del_blank(df=pd.DataFrame(), cols=list()):
  26. for col in cols:
  27. if df[col].dtype == object:
  28. df[col] = df[col].str.strip()
  29. return df
  30. # 切割数组到多个数组
  31. def split_array(array, num):
  32. return [array[i:i + num] for i in range(0, len(array), num)]
  33. def find_read_header(file_path, trans_cols):
  34. df = read_file_to_df(file_path, nrows=20)
  35. count = 0
  36. header = None
  37. for col in trans_cols:
  38. if col in df.columns:
  39. count = count + 1
  40. if count >= 2:
  41. header = 0
  42. break
  43. count = 0
  44. values = list()
  45. for index, row in df.iterrows():
  46. values = list(row.values)
  47. if type(row.name) == tuple:
  48. values.extend(list(row.name))
  49. for col in trans_cols:
  50. if col in values:
  51. count = count + 1
  52. if count > 2:
  53. header = index + 1
  54. break
  55. read_cols = []
  56. for col in values:
  57. if col in trans_cols:
  58. read_cols.append(col)
  59. return header, read_cols
  60. # 读取数据到df
  61. def read_file_to_df(file_path, read_cols=list(), trans_cols=None, nrows=None):
  62. begin = datetime.datetime.now()
  63. trans_print('开始读取文件', file_path)
  64. header = 0
  65. find_cols = list()
  66. if trans_cols:
  67. header, find_cols = find_read_header(file_path, trans_cols)
  68. trans_print(path.basename(file_path), "读取第", header, "行")
  69. if header is None:
  70. message = '未匹配到开始行,请检查并重新指定'
  71. trans_print(message)
  72. raise Exception(message)
  73. read_cols.extend(find_cols)
  74. try:
  75. df = pd.DataFrame()
  76. if str(file_path).lower().endswith("csv") or str(file_path).lower().endswith("gz"):
  77. encoding = detect_file_encoding(file_path)
  78. end_with_gz = str(file_path).lower().endswith("gz")
  79. if read_cols:
  80. if end_with_gz:
  81. df = pd.read_csv(file_path, encoding=encoding, usecols=read_cols, compression='gzip', header=header,
  82. nrows=nrows)
  83. else:
  84. df = pd.read_csv(file_path, encoding=encoding, usecols=read_cols, header=header,
  85. on_bad_lines='warn', nrows=nrows)
  86. else:
  87. if end_with_gz:
  88. df = pd.read_csv(file_path, encoding=encoding, compression='gzip', header=header, nrows=nrows)
  89. else:
  90. df = pd.read_csv(file_path, encoding=encoding, header=header, on_bad_lines='warn', nrows=nrows)
  91. else:
  92. xls = pd.ExcelFile(file_path, engine="calamine")
  93. # 获取所有的sheet名称
  94. sheet_names = xls.sheet_names
  95. for sheet_name in sheet_names:
  96. if read_cols:
  97. now_df = pd.read_excel(xls, sheet_name=sheet_name, header=header, usecols=read_cols, nrows=nrows)
  98. else:
  99. now_df = pd.read_excel(xls, sheet_name=sheet_name, header=header, nrows=nrows)
  100. now_df['sheet_name'] = sheet_name
  101. df = pd.concat([df, now_df])
  102. xls.close()
  103. trans_print('文件读取成功:', file_path, '数据数量:', df.shape, '耗时:', datetime.datetime.now() - begin)
  104. except Exception as e:
  105. trans_print('读取文件出错', file_path, str(e))
  106. message = '文件:' + path.basename(file_path) + ',' + str(e)
  107. raise ValueError(message)
  108. return df
  109. def __build_directory_dict(directory_dict, path, filter_types=None):
  110. # 遍历目录下的所有项
  111. for item in listdir(path):
  112. item_path = path.join(path, item)
  113. if path.isdir(item_path):
  114. __build_directory_dict(directory_dict, item_path, filter_types=filter_types)
  115. elif path.isfile(item_path):
  116. if path not in directory_dict:
  117. directory_dict[path] = []
  118. if filter_types is None or len(filter_types) == 0:
  119. directory_dict[path].append(item_path)
  120. elif str(item_path).split(".")[-1] in filter_types:
  121. if str(item_path).count("~$") == 0:
  122. directory_dict[path].append(item_path)
  123. # 读取路径下所有的excel文件
  124. def read_excel_files(read_path):
  125. if path.isfile(read_path):
  126. return [read_path]
  127. directory_dict = {}
  128. __build_directory_dict(directory_dict, read_path, filter_types=['xls', 'xlsx', 'csv', 'gz'])
  129. return [path for paths in directory_dict.values() for path in paths if path]
  130. # 读取路径下所有的文件
  131. def read_files(read_path):
  132. directory_dict = {}
  133. __build_directory_dict(directory_dict, read_path, filter_types=['xls', 'xlsx', 'csv', 'gz', 'zip', 'rar'])
  134. return [path for paths in directory_dict.values() for path in paths if path]
  135. def copy_to_new(from_path, to_path):
  136. is_file = False
  137. if to_path.count('.') > 0:
  138. is_file = True
  139. create_file_path(to_path, is_file_path=is_file)
  140. shutil.copy(from_path, to_path)
  141. # 创建路径
  142. def create_file_path(path, is_file_path=False):
  143. if is_file_path:
  144. path = path.dirname(path)
  145. if not path.exists(path):
  146. makedirs(path, exist_ok=True)
  147. if __name__ == '__main__':
  148. datas = read_excel_files(r"D:\data\清理数据\招远风电场\WOF053600062-WOB000009_ZYFDC000012\minute")
  149. for data in datas:
  150. print(data)
  151. print("*" * 20)
  152. datas = read_excel_files(r"D:\data\清理数据\招远风电场\WOF053600062-WOB000009_ZYFDC000012\minute\WOG00066.csv.gz")
  153. for data in datas:
  154. print(data)