+关注
已关注

分类  

暂无分类

标签  

暂无标签

日期归档  

2019-07(1)

2019-08(109)

2019-09(120)

2019-10(17)

2019-11(1)

利用pandas+xlwt 合并单元格

发布于2020-06-30 22:51     阅读(243)     评论(0)     点赞(20)     收藏(4)


0

1

2

3

4

需求:根据某一列的值对指定的其他列做合并操作(根据A列,对BC列作合并单元格)

  1. import xlwt
  2. import pandas as pd
  3. class Merge_cell():
  4. '''
  5. 目标:根据某列值对指定列值进行合并
  6. '''
  7. def __init__(self, excel_path, df, key_col, col2):
  8. self.excel_path = excel_path
  9. self.df = (df.drop(columns='index') if 'index' in df.columns else df)
  10. self.key_col = key_col
  11. self.col2 = col2
  12. self.wb = xlwt.Workbook(excel_path)
  13. self.worksheet = self.wb.add_sheet('sheet1')
  14. def _get_idx(self):
  15. groups = self.df.groupby(self.key_col)
  16. idxs = [[groups.get_group(i).index.min() + 1, groups.get_group(i).index.max() + 1] for i in groups.size().index]
  17. return idxs, len(idxs)
  18. def _get_content(self, idx, key):
  19. """
  20. :param idxs: 索引 [[1,2],[3,4]]
  21. :return: 暂时保存合并单元的值
  22. """
  23. import numpy as np
  24. if not pd.isna(self.df.at[idx, key]):
  25. temp = self.df.at[idx, key]
  26. else:
  27. temp = None
  28. return temp
  29. def merged(self):
  30. import numpy as np
  31. if not self.key_col: # 如果key_cols 参数不传值,则无需合并
  32. self.df.to_excel(self.excel_path, index=False)
  33. return
  34. idxs, length = self._get_idx()
  35. line_cn = self.df.index.size
  36. cols = list(self.df.columns.values)
  37. column_number = {col: idx for idx, col in enumerate(cols)}
  38. if self.key_col not in cols: # 校验key_cols中各元素 是否都包含与对象的列
  39. print("key_cols is not completely include object's columns")
  40. return False
  41. if not all([v in cols for i, v in enumerate(self.col2)]): # 校验merge_cols中各元素 是否都包含与对象的列
  42. print("merge_cols is not completely include object's columns")
  43. return False
  44. for value, i in column_number.items(): # 写表头
  45. self.worksheet.write(0, i, value)
  46. for key, idx in column_number.items():
  47. if key not in self.col2:
  48. for i in range(line_cn):
  49. value = self.df.loc[i, key]
  50. if not pd.isna(value):
  51. self.worksheet.write(i + 1, idx, str(value))
  52. else:
  53. pass
  54. else:
  55. for j in idxs:
  56. value = self._get_content(j[0] - 1, key)
  57. if value:
  58. self.worksheet.write_merge(j[0], j[1], idx, idx, value)
  59. else:
  60. pass
  61. self.wb.save(self.excel_path)
  62. if __name__ == '__main__':
  63. te = {'A': [1, 2, 2, 2, 3, 3], 'B': [1, 1, 1, 1, 1, 1], 'C': [1, 1, 1, 1, 1, 1], 'D': [1, 1, 1, 1, 1, 1]}
  64. t_f = pd.DataFrame(te)
  65. DF = Merge_cell('000_1.xls', t_f, 'A', ['B', 'C'])
  66. DF.merged()

 

参考:https://blog.csdn.net/cakecc2008/article/details/59203980

0

1

2

3

4

5

6

7



所属网站分类: 技术文章 > 博客

作者:你太美丽

链接: https://www.pythonheidong.com/blog/article/431046/70f1994b206d0220a0c2/

来源: python黑洞网

任何形式的转载都请注明出处,如有侵权 一经发现 必将追究其法律责任

20 0
收藏该文
已收藏

评论内容:(最多支持255个字符)