用python对Excel每列不确定个数的元素转换为行

目的

如上图，每列有很多相同的数值，想要每个编号的病级转成一行

但手动复制太慢
先读取数据获取每个编号的个数

import pandas as pd
data = pd.read_excel(r'C:\Users\Desktop\data.xlsx')
count = data.groupby(by='编号').sum()

创建一个字典储存结果，对data进行循环，先循环编号，再循环编号内数据

result = {}
value = []
h=0
for i in range(len(count)):
    for j in range(count['株数'].iloc[i]):
        h+=1
        value.append(data['病级'].iloc[h-1])
    result[str(count.index[i])]=value
    value = []
excel= pd.DataFrame.from_dict(result, orient='index')
excel.to_excel(r'C:\Users\Desktop\result.xlsx')

最终输出结果

对于固定的行转列，则直接间隔取样即可，如三个重复的

import pandas as pd


data = pd.read_excel(r'C:\Users\Administrator\Desktop\千粒重.xlsx')
count = data.groupby(by='处理', sort=False).mean()
count['百粒鲜重1'] = list(data['百粒鲜重'][0::3])
count['百粒鲜重2'] = list(data['百粒鲜重'][1::3])
count['百粒鲜重3'] = list(data['百粒鲜重'][2::3])
count['百粒干重1'] = list(data['百粒干重'][0::3])
count['百粒干重2'] = list(data['百粒干重'][1::3])
count['百粒干重3'] = list(data['百粒干重'][2::3])
count.to_excel(r'C:\Users\Administrator\Desktop\resultdd2.xlsx')