合并数据框中的值以在Excel中编写 [英] Merging values in dataframe to write in excel
问题描述
我有一个看起来像的数据框
I have a dataframe that looks like
column1 column2 column3 colum4 column5
1 r_n_1 r_s_1 r_n_2 r_s_3 r_n_3
2 r_n_1 r_s_1 r_n_4 r_s_4 r_n_5
3 r_n_1 r_s_1 r_n_6 r_s_5 r_n_7
4 r_n_1 r_s_1 r_n_6 r_s_6 r_n_9
5 r_n_10 r_s_7 r_n_11 r_s_8 r_n_12
6 r_n_10 r_s_9 r_n_11 r_s_10 r_n_13
我想合并数据框中的单元格，以便我可以在excel中编写看起来像
And I would like to merge cells in data frame so I could write in excel that could look like
因此基本上可以合并在excel中具有相同值的单元格.我猜我可以 使用熊猫的MultiIndex，但我不知道该怎么做.
So basically merge cells that have same value in excel. I am guessing I can use MultiIndex from pandas but I don't know how to do that.
我获取此数据帧的代码就像.
my code to get this data frame is like.
new_list = []
for k1 in remove_empties_from_dict(combined_dict):
curr_dict = remove_empties_from_dict(combined_dict)[k1]
for k2 in curr_dict:
curr_dict_2=curr_dict[k2]
for k3 in curr_dict_2:
curr_dict_3=curr_dict_2[k3]
for k4 in curr_dict_3:
curr_dict_4=curr_dict_3[k4]
new_dict= {'c1': k1, 'c2': k2, 'c3': k3, 'c4': k4,'c5': curr_dict_4}
new_list.append(new_dict)
df = pd.DataFrame(new_list)
推荐答案
我找不到直接函数来合并具有相似值的单元格，因此，我编写了执行此操作的代码.
I couldn't find a direct function that does the merging of cells with similar values so instead, I have written a code that does that.
print(df)
column1 column2 column3 column4 column5
0 r_n_1 r_s_1 r_n_2 r_s_3 r_n_3
1 r_n_1 r_s_1 r_n_4 r_s_4 r_n_5
2 r_n_1 r_s_1 r_n_6 r_s_5 r_n_7
3 r_n_1 r_s_1 r_n_6 r_s_6 r_n_9
4 r_n_10 r_s_7 r_n_11 r_s_8 r_n_12
5 r_n_10 r_s_9 r_n_11 r_s_10 r_n_13
这是我必须使用的df.但是，为了做到这一点，我迭代了一次以检查哪些值相似，然后将其替换为-.我之所以没有将其设置为NoneType的原因是因为表下方的单元格具有NoneType值，因此代码的另一部分将继续进行无限迭代.我所做的是:
This is the df I have to work with. But in order to do that what I did was, I iterated it once to check which values are similar, and replaced the with a -. The reason I did not make it NoneType was because the cells below the table have a NoneType value, so the further part of the code will keep on iterating infinitely. What I did was:
for i in df.columns:
for j in range(len(df[i])):
for k in range(j+1,len(df[i])):
if df[i][j]== df[i][k]:
df[i][k]='-'
所以现在我的df看起来像:
So now my df looks something like:
print(df)
column1 column2 column3 column4 column5
0 r_n_1 r_s_1 r_n_2 r_s_3 r_n_3
1 - - r_n_4 r_s_4 r_n_5
2 - - r_n_6 r_s_5 r_n_7
3 - - - r_s_6 r_n_9
4 r_n_10 r_s_7 r_n_11 r_s_8 r_n_12
5 - r_s_9 - r_s_10 r_n_13
现在，我在数据框中具有所有唯一值，我将检查df元素是有效输入还是-.并且-的单元格将与其上限值合并.我是通过以下方式做到的:
Now that I have all unique values in the Data Frame, I will check whether the df element is a valid input or a -. And the cells that are - will be merged with its upper value. I did that by:
from openpyxl.workbook import Workbook
exportPath = r'C:\Users\T01144\Desktop\PythonExport.xlsx'
wb= Workbook()
ws=wb.active
rowInd=1
colInd=1
colList=['-', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H','I'] # Continue if there are more columns
for i in df.columns:
for j in range(0,len(df[i])):
if(df[i][j]!='-'):
ws.cell(row=rowInd,column=colInd,value=df[i][j])
else:
count=0
for l in range(j+1,len(df[i])):
count+=1
if df[i][l]!='-':
count-=1
break
ws.merge_cells(str(str(colList[colInd]+str(rowInd-1))+":"+str(colList[colInd]+str(rowInd+count))))
rowInd+=1
colInd+=1
rowInd=1
我现在的输出是:
可以找到整个代码这里.
注意:某些人在创建Excel后可能会收到此错误:
NOTE: Some of you may get this error after creating the Excel:
我们在'PythonExport.xlsx'中发现了某些内容的问题.您是否希望我们尽力恢复原状?如果您信任此工作簿的来源，请单击是".
We found a problem with some content in 'PythonExport.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.
只需忽略此错误，然后单击是.
Just ignore this error and click Yes.
这篇关于合并数据框中的值以在Excel中编写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
