Python dataframe implementation of Excel merge cell _python

This article mainly for you in detail the Python dataframe implementation of Excel merged cells, with a certain reference value, interested in small partners can refer to

In the work often encountered the need to export data to Excel, and need to merge some of these cells, such as the table below, you need to merge columns B and C according to the value of column A, the corresponding cells

The To_excel method in pandas can only merge the indexes, while Xlsxwriter, while providing a Merge_range method, is only a basic method, each time it needs to write tedious tests to finally tune, and not very good reuse. So want to write a method of their own, combining dataframe and Merge_range. The approximate idea is:

1, define a My_dataframe class, inherit the Dataframe class, this can be very good use of pandas many features, without having to reorganize the data structure.
2. Define a My_mergewr_excel method with the following parameters: The path of the output Excel, the list of key_cols used to determine if a merge is required, the lists that indicate which columns the cells need to be merged
3. Package the My_dataframe as a my_module module for reuse.

The merging algorithm is as follows:

1, according to the "key column" of the given parameters, the group counts and sorts, add CN and RN two auxiliary columns
2, the judgment CN is greater than 1, the group needs to be merged, otherwise the group (row) does not need to merge (Cn=1 that the packet data row is unique, no need to merge)
3, corresponding to the grouping needs to be merged, to determine whether the current column is in the given parameter "merge column", is to use the merge to write Excel cells, otherwise it is normal to write Excel cells.
4, in the column that needs to be merged, if the rn=1 call Merge_range, write the CN cell once, if rn>1 skip the cell, because in Rn=1, the cell has been merged, if you repeat the call to Erge_range, An error occurs when you open an Excel document.

The figure is explained as follows:

The specific code is as follows:

#-*-Coding:utf-8-*-"" "Created on 20170301 @author: Ark-z" "" Import xlsxwriter Import pandas as PD class My_data Frame (PD. DataFrame): Def __init__ (self, data=none, Index=none, Columns=none, Dtype=none, Copy=false): PD. Dataframe.__init__ (self, data, index, columns, Dtype, copy) def my_mergewr_excel (self,path,key_cols=[],merge_cols=[]) : # sheet_name= ' Sheet1 ', na_rep= ', Float_format=none, Columns=none, Header=true, Index=true, Index_label=none, STARTR Ow=0, Startcol=0, Engine=none, Merge_cells=true, Encoding=none, inf_rep= ' inf ', verbose=true): Self_copy=my_dataframe ( Self,copy=true) line_cn=self_copy.index.size cols=list (self_copy.columns.values) if all ([v] cols for i,v in Enumerate (key_cols)]) ==false: Whether #校验key_cols中各元素 all contain columns with objects print ("Key_cols is not completely include object's column       S ") return False if all ([V-cols for i,v in Enumerate (merge_cols)]) ==false: #校验merge_cols中各元素 contains columns with objects Print ("Merge_cols is not completely INclude object ' s columns ") return False wb2007 = Xlsxwriter. Workbook (path) worksheet2007 = Wb2007.add_worksheet () Format_top = Wb2007.add_format ({' Border ': 1, ' bold ': True, ' text _wrap ': True}) Format_other = Wb2007.add_format ({' Border ': 1, ' valign ': ' vcenter '}) for I,value in Enumerate (cols): #写 Table header #print (value) worksheet2007.write (0,i,value,format_top) #merge_cols =[' B ', ' A ', ' C '] #key_cols =[' A ', ' B '] if Key_cols ==[]: #如果key_cols parameter does not pass value, you do not need to merge self_copy[' RN ']=1 self_copy[' CN ']=1 Else:sel f_copy[' RN ']=self_copy.groupby (key_cols,as_index=false). Rank (method= ' first '). ix[:,0] #以key_cols作为是否合并的依据 self_ copy[' CN ']=self_copy.groupby (key_cols,as_index=false). Rank (method= ' Max '). ix[:,0] #print (self) for I in range (Line_ CN): If self_copy.ix[i, ' CN ']>1: #print (' The row has cells that need to be merged ') for J,col in Enumerate (cols): #pr            Int (Self_copy.ix[i,col]) if col in (merge_cols): #哪些列需要合并 If self_copy.ix[i, ' RN ']==1: #合并写第一个单元格, the next first one will no longer write Worksheet2007.merge_range (I+1,j,i+int (self_copy.ix[i, ' CN ' ]), J, Self_copy.ix[i,col],format_other) # #合并单元格, judging by line_set[7] you need to merge several #worksheet2007. Write (I+1,j,df.ix[i,col ]) Else:pass #worksheet2007. Write (I+1,j,df.ix[i,j]) else:wor         Ksheet2007.write (I+1,j,self_copy.ix[i,col],format_other) #print (', ') Else: #print (' There is no need to merge cells for this row ') For J,col in Enumerate (cols): #print (Df.ix[i,col]) worksheet2007.write (i+1,j,self_copy.ix[i,col ],format_other) Wb2007.close () self_copy.drop (' CN ', Axis=1) self_copy.drop (' RN ', Axis=1)

Calling code:

Import My_module  df=my_dataframe ({' 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]})  DF out[120]:    A B C D 0 1 1 1 1 1 2 1 1 1 2 2 1 1 1 3 2 1 1 1 4 3 1 1 1 5 3 1 1 1  df.my_mergewr_excel (' 000_2.xlsx ', [' A '],[' B ', ' C '])

The effect is as follows:

You can also set merge a, column B:

Df.my_mergewr_excel (' 000_2.xlsx ', [' A '],[' a ', ' B '])

The effect is as follows: