extract_to_csv.py 3.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111
  1. # This Python file uses the following encoding: utf-8
  2. # @author runhey
  3. # github https://github.com/runhey
  4. import re
  5. import rich
  6. import csv
  7. import pandas as pd
  8. from utils import remove_symbols
  9. class Extracter:
  10. def __init__(self, data_file: str='data.csv'):
  11. self.data_file = data_file
  12. self.df = pd.read_csv(self.data_file)
  13. @staticmethod
  14. def parse_one(value: str) -> tuple:
  15. """
  16. 解析原始的一行数据,变成OAS使用的格式: question,answer
  17. :param value:
  18. :return:
  19. """
  20. if not value:
  21. print(f'value is None {value}')
  22. return None, None
  23. if not isinstance(value, str):
  24. print(f'-----------------------------------------------------------value is not str {value}')
  25. return None, None
  26. # 标点符号
  27. value = value.replace(' ', '').replace('?', '?').replace('!', '!').replace(',', ',').replace('—', '-')
  28. # 正则匹配
  29. result = re.search(r"(.*)?-(.*)", value)
  30. if result:
  31. return result[1], result[2]
  32. result = re.search(r"(.*)?--(.*)", value)
  33. if result:
  34. return result[1], result[2]
  35. result = re.search(r"(.*)?-------(.*)", value)
  36. if result:
  37. return result[1], result[2]
  38. result = re.search(r"(.*)?------(.*)", value)
  39. if result:
  40. return result[1], result[2]
  41. result = re.search(r"(.*)?-----(.*)", value)
  42. if result:
  43. return result[1], result[2]
  44. result = re.search(r"(.*)?----(.*)", value)
  45. if result:
  46. return result[1], result[2]
  47. result = re.search(r"(.*)------(.*)", value)
  48. if result:
  49. return result[1], result[2]
  50. result = re.search(r"(.*)-----(.*)", value)
  51. if result:
  52. return result[1], result[2]
  53. result = re.search(r"(.*)----(.*)", value)
  54. if result:
  55. return result[1], result[2]
  56. return None, None
  57. def xlsx2csv(self, input_file: str='data.xlsx'):
  58. """
  59. 将xlsx文件的数据添加到csv文件
  60. :param input_file:
  61. :return:
  62. """
  63. df_source = pd.read_excel(input_file, usecols='A')
  64. for index, row in df_source.iterrows():
  65. item_value = row[0]
  66. question, answer = self.parse_one(item_value)
  67. if question is None and answer is None:
  68. continue
  69. if len(question) < 3:
  70. continue
  71. # print(index, question, answer)
  72. # 非常重要,去除标点符号
  73. question = remove_symbols(question)
  74. answer = remove_symbols(answer)
  75. if self.appear_in_df(question, answer):
  76. continue
  77. print('New', index, question, answer)
  78. self.df.loc[len(self.df)] = [question, answer]
  79. self.df.to_csv(self.data_file, index=False, encoding='utf-8-sig')
  80. def appear_in_df(self, question: str, answer: str) -> bool:
  81. for index, row in self.df.iterrows():
  82. if row['question'] == question and row['answer'] == answer:
  83. return True
  84. return False
  85. def clear_symbols(self):
  86. for index, row in self.df.iterrows():
  87. row['question'] = remove_symbols(row['question'])
  88. row['answer'] = remove_symbols(row['answer'])
  89. self.df.to_csv(self.data_file, index=False, encoding='utf-8-sig')
  90. if __name__ == "__main__":
  91. Extracter().xlsx2csv()