英文:
how in pandas groupby custom sort columns by value
问题
I have a dataframe and I try the results from Groupby export to excel by custom sort values one, two or more columns:
- id sorted by ['bv', 'cl', 'an']
- color sorted by ['blue', 'red', 'orange', 'green']
import pandas as pddata = {'color': ['green', 'red', ' orange', 'red','blue',' orange', 'red','blue', 'red', ' orange', 'red','red','green'],'Name': ['Tom', 'nick', 'krish', 'jack','bob','Tom', 'nick', 'krish', 'nick', 'krish', 'jack','nick','Tom'],'form': [ 'a', 'b', 'c', 'd','e','b', 'c', 'd','d','e','b', 'c', 'b'],'id': [ 'an', 'bv', 'cl', 'cl','an','bv', 'an', 'cl','cl','an','bv','bv','an'],}df = pd.DataFrame(data)print(df)df_1 = df.groupby(['color','Name','id'])['form'].agg('count')print(df_1)writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')df_1.to_excel(writer, sheet_name='Sheet1')writer.save()print('I try the df to excel have custom sort values in columns')print('id sort by [bv,cl,an]')print('color sort by [blue,red,orange,green]')print('how can make it?')
(Note: I have provided the code without translation as requested.)
英文:
I have a dataframe and i try the results from Groupby export to excel by custom sort values one , two or more columns :
- id sorted by
['bv','cl','an'] - color sorted by
['blue','red','orange','green']
import pandas as pddata = {'color': ['green', 'red', ' orange', 'red','blue',' orange', 'red','blue', 'red', ' orange', 'red','red','green'],'Name': ['Tom', 'nick', 'krish', 'jack','bob','Tom', 'nick', 'krish', 'nick', 'krish', 'jack','nick','Tom'],'form': [ 'a', 'b', 'c', 'd','e','b', 'c', 'd','d','e','b', 'c', 'b'],'id': [ 'an', 'bv', 'cl', 'cl','an','bv', 'an', 'cl','cl', 'an','bv','bv','an'],}df = pd.DataFrame(data)print(df)df_1 = df.groupby(['color','Name','id'])['form'].agg('count')print(df_1)writer = pd.ExcelWriter('test.xlsx', engine='xlsxwriter')df_1.to_excel(writer, sheet_name='Sheet1')writer.save()print(' I try the df to excel have custom sort values in columns')print('id sort by [bv,cl,an]')print('color sort by [blue,red,orange,green]')print('how can make it?')
答案1
得分: 1
You can use 分类数据 然后 sort_index ：
ids = pd.CategoricalDtype(["bv", "cl", "an"], ordered=True)colors = pd.CategoricalDtype(["blue", "red", "orange", "green"], ordered=True)# 如有需要，添加更多类别out = (df.astype({"id": ids, "color": colors}).groupby(["color", "Name", "id"], observed=True)["form"].agg("count").sort_index(level=[0, 2]) # 如有需要，添加更多级别)with pd.ExcelWriter("test.xlsx", engine="xlsxwriter") as writer:out.to_excel(writer, sheet_name="Sheet1")# 如有需要，添加自定义样式/格式化
输出：
print(out)color Name idblue krish cl 1bob an 1red jack bv 1nick bv 2jack cl 1nick cl 1an 1orange Tom bv 1krish cl 1an 1green Tom an 2Name: form, dtype: int64
英文:
You can use Categorical Data then sort_index :
ids = pd.CategoricalDtype(["bv", "cl", "an"], ordered=True)colors = pd.CategoricalDtype(["blue", "red", "orange", "green"], ordered=True)#add here more categories if neededout = (df.astype({"id": ids, "color": colors}).groupby(["color", "Name", "id"], observed=True)["form"].agg("count").sort_index(level=[0, 2]) #in case, add here more levels)with pd.ExcelWriter("test.xlsx", engine="xlsxwriter") as writer:out.to_excel(writer, sheet_name="Sheet1")# add here a custom styling/formatting if needed
Output :
print(out)color Name idblue krish cl 1bob an 1red jack bv 1nick bv 2jack cl 1nick cl 1an 1orange Tom bv 1krish cl 1an 1green Tom an 2Name: form, dtype: int64
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论