Python 3.6 - add output as new column to dataframe and then output to excel

Question

I need to get the 3 largest commodities by county from an excel file, and I then, in turn, want to output these results to a new column in excel. I have gotten the sorting piece done with some help from this forum, but can't seem to figure out how I can export to it to excel - I was trying to make a new data frame column, which I would then write the data frame to excel, but if there is a more direct way, I'd appreciate it.

df = pd.read_excel('abc.xlsx')
d = df.set_index('county').to_dict('index')

for k, v in d.items():
    i = sorted(v.items(), key = lambda x: x[1], reverse=True)
    print([k, ', '.join([f'{com}: {value}' for, value in i[0:3]])])

I ultimately want to figure out how to change that print line to get it to output the 3 largest commodities and their values to excel. I have attached images of the current data structure, and what I want it to look like.

Here is the dictionary with the first few rows of data - hopefully this helps:

{1001: {'CATTLE_USDA_VALUE': 5988000.0, 'CORN_USDA_VALUE': 460000.0, 'COTTON_USDA_VALUE': 5465000.0, 'DAIRY_USDA_VALUE': 0.0, 'HOGS_USDA_VALUE': 3000.0, 'POULTRY_USDA_VALUE': 0.0, 'SOYBEANS_USDA_VALUE': 664000.0, 'WHEAT_USDA_VALUE': 679000.0, 'TOTAL_USDA_VALUE': 19811000.0, 'CATTLE_USDA_Share': 0.30225632224521731, 'CORN_USDA_SHARE': 0.023219423552571804, 'COTTON_USDA_SHARE': 0.2758568472060976, 'DAIRY_USDA_SHARE': 0.0, 'HOGS_USDA_SHARE': 0.00015143102316894655, 'POULTRY_USDA_SHARE': 0.0, 'SOYBEANS_USDA_SHARE': 0.033516733128060167, 'WHEAT_USDA_SHARE': 0.034273888243904899, 'CATTLE_FDIC_SHARE': 0.0, 'CATTLE_REPORTING_SHARE': 0.0, 'CORN_FDIC_SHARE': 0.0, 'CORN_REPORTING_SHARE': 0.0, 'COTTON_FDIC_SHARE': 0.0, 'COTTON_REPORTING_SHARE': 0.0, 'DAIRY_FDIC_SHARE': 0.0, 'DAIRY_REPORTING_SHARE': 0.0, 'HOGS_FDIC_SHARE': 0.0, 'HOGS_REPORTING_SHARE': 0.0, 'POULTRY_FDIC_SHARE': 36.031179424999898, 'POULTRY_REPORTING_SHARE': 74.074074074099897, 'SOYBEANS_FDIC_SHARE': 0.0, 'SOYBEANS_REPORTING_SHARE': 0.0, 'WHEAT_FDIC_SHARE': 0.0, 'WHEAT_REPORTING_SHARE': 0.0}, 1003: {'CATTLE_USDA_VALUE': 0.0, 'CORN_USDA_VALUE': 3818000.0, 'COTTON_USDA_VALUE': 7344000.0, 'DAIRY_USDA_VALUE': 220000.0, 'HOGS_USDA_VALUE': 4000.0, 'POULTRY_USDA_VALUE': 0.0, 'SOYBEANS_USDA_VALUE': 8977000.0, 'WHEAT_USDA_VALUE': 3680000.0, 'TOTAL_USDA_VALUE': 135562000.0, 'CATTLE_USDA_Share': 0.0, 'CORN_USDA_SHARE': 0.028164234815066171, 'COTTON_USDA_SHARE': 0.05417447367256311, 'DAIRY_USDA_SHARE': 0.001622873666661749, 'HOGS_USDA_SHARE': 2.9506793939304526e-05, 'POULTRY_USDA_SHARE': 0.0, 'SOYBEANS_USDA_SHARE': 0.066220622298284182, 'WHEAT_USDA_SHARE': 0.027146250424160162, 'CATTLE_FDIC_SHARE': 12.3912520289, 'CATTLE_REPORTING_SHARE': 82.142857142899899, 'CORN_FDIC_SHARE': 0.0, 'CORN_REPORTING_SHARE': 0.0, 'COTTON_FDIC_SHARE': 0.0, 'COTTON_REPORTING_SHARE': 0.0, 'DAIRY_FDIC_SHARE': 0.0, 'DAIRY_REPORTING_SHARE': 0.0, 'HOGS_FDIC_SHARE': 0.0, 'HOGS_REPORTING_SHARE': 0.0, 'POULTRY_FDIC_SHARE': 32.076141693899899, 'POULTRY_REPORTING_SHARE': 75.0, 'SOYBEANS_FDIC_SHARE': 0.0, 'SOYBEANS_REPORTING_SHARE': 0.0, 'WHEAT_FDIC_SHARE': 0.0, 'WHEAT_REPORTING_SHARE': 0.0}, 1005: {'CATTLE_USDA_VALUE': 5581000.0, 'CORN_USDA_VALUE': 1324000.0, 'COTTON_USDA_VALUE': 4904000.0, 'DAIRY_USDA_VALUE': 0.0, 'HOGS_USDA_VALUE': 0.0, 'POULTRY_USDA_VALUE': 83809000.0, 'SOYBEANS_USDA_VALUE': 0.0, 'WHEAT_USDA_VALUE': 0.0, 'TOTAL_USDA_VALUE': 104356000.0, 'CATTLE_USDA_Share': 0.05348039403580053, 'CORN_USDA_SHARE': 0.012687339491739814, 'COTTON_USDA_SHARE': 0.046992985549465294, 'DAIRY_USDA_SHARE': 0.0, 'HOGS_USDA_SHARE': 0.0, 'POULTRY_USDA_SHARE': 0.80310667331059071, 'SOYBEANS_USDA_SHARE': 0.0, 'WHEAT_USDA_SHARE': 0.0, 'CATTLE_FDIC_SHARE': 0.0, 'CATTLE_REPORTING_SHARE': 0.0, 'CORN_FDIC_SHARE': 0.0, 'CORN_REPORTING_SHARE': 0.0, 'COTTON_FDIC_SHARE': 0.0, 'COTTON_REPORTING_SHARE': 0.0, 'DAIRY_FDIC_SHARE': 0.0, 'DAIRY_REPORTING_SHARE': 0.0, 'HOGS_FDIC_SHARE': 0.0, 'HOGS_REPORTING_SHARE': 0.0, 'POULTRY_FDIC_SHARE': 0.0, 'POULTRY_REPORTING_SHARE': 0.0, 'SOYBEANS_FDIC_SHARE': 1.5549794663100001, 'SOYBEANS_REPORTING_SHARE': 64.2201834862, 'WHEAT_FDIC_SHARE': 1.0388869362099999, 'WHEAT_REPORTING_SHARE': 60.550458715600001}, 1007: {'CATTLE_USDA_VALUE': 1924000.0, 'CORN_USDA_VALUE': 0.0, 'COTTON_USDA_VALUE': 0.0, 'DAIRY_USDA_VALUE': 0.0, 'HOGS_USDA_VALUE': 0.0, 'POULTRY_USDA_VALUE': 0.0, 'SOYBEANS_USDA_VALUE': 0.0, 'WHEAT_USDA_VALUE': 0.0, 'TOTAL_USDA_VALUE': 0.0, 'CATTLE_USDA_Share': 0.0, 'CORN_USDA_SHARE': 0.0, 'COTTON_USDA_SHARE': 0.0, 'DAIRY_USDA_SHARE': 0.0, 'HOGS_USDA_SHARE': 0.0, 'POULTRY_USDA_SHARE': 0.0, 'SOYBEANS_USDA_SHARE': 0.0, 'WHEAT_USDA_SHARE': 0.0, 'CATTLE_FDIC_SHARE': 0.0, 'CATTLE_REPORTING_SHARE': 0.0, 'CORN_FDIC_SHARE': 3.70720825635, 'CORN_REPORTING_SHARE': 71.604938271600005, 'COTTON_FDIC_SHARE': 0.0, 'COTTON_REPORTING_SHARE': 0.0, 'DAIRY_FDIC_SHARE': 0.0, 'DAIRY_REPORTING_SHARE': 0.0, 'HOGS_FDIC_SHARE': 0.0, 'HOGS_REPORTING_SHARE': 0.0, 'POULTRY_FDIC_SHARE': 43.716573955800001, 'POULTRY_REPORTING_SHARE': 77.777777777799898, 'SOYBEANS_FDIC_SHARE': 4.7009247847799998, 'SOYBEANS_REPORTING_SHARE': 61.728395061699899, 'WHEAT_FDIC_SHARE': 0.0, 'WHEAT_REPORTING_SHARE': 0.0}, 1009: {'CATTLE_USDA_VALUE': 11721000.0, 'CORN_USDA_VALUE': 831000.0, 'COTTON_USDA_VALUE': 1203000.0, 'DAIRY_USDA_VALUE': 0.0, 'HOGS_USDA_VALUE': 28000.0, 'POULTRY_USDA_VALUE': 153682000.0, 'SOYBEANS_USDA_VALUE': 1490000.0, 'WHEAT_USDA_VALUE': 0.0, 'TOTAL_USDA_VALUE': 178191000.0, 'CATTLE_USDA_Share': 0.065777732882132095, 'CORN_USDA_SHARE': 0.0046635351953802379, 'COTTON_USDA_SHARE': 0.0067511827196659767, 'DAIRY_USDA_SHARE': 0.0, 'HOGS_USDA_SHARE': 0.00015713475989247492, 'POULTRY_USDA_SHARE': 0.86245657749269045, 'SOYBEANS_USDA_SHARE': 0.0083618140085638445, 'WHEAT_USDA_SHARE': 0.0, 'CATTLE_FDIC_SHARE': 0.0, 'CATTLE_REPORTING_SHARE': 0.0, 'CORN_FDIC_SHARE': 0.0, 'CORN_REPORTING_SHARE': 0.0, 'COTTON_FDIC_SHARE': 0.0, 'COTTON_REPORTING_SHARE': 0.0, 'DAIRY_FDIC_SHARE': 0.0, 'DAIRY_REPORTING_SHARE': 0.0, 'HOGS_FDIC_SHARE': 0.0, 'HOGS_REPORTING_SHARE': 0.0, 'POULTRY_FDIC_SHARE': 0.0, 'POULTRY_REPORTING_SHARE': 0.0, 'SOYBEANS_FDIC_SHARE': 0.0, 'SOYBEANS_REPORTING_SHARE': 0.0, 'WHEAT_FDIC_SHARE': 0.0, 'WHEAT_REPORTING_SHARE': 0.0}}

I then want an additional column that sorts by the three largest data points so the first row would match the bottom image.

Can you provide a **[mcve]**? Here, that means no images / links, just text. Some example output would be great too. — jpp, Aug 06 '18 at 13:48
JPP - how can i better present this? I thought showing the images was easiest to show what I needed. Would you prefer that I put each of the rows into a list or dictionary so you can easily copy it to your machine? — Shawn Schreier, Aug 06 '18 at 14:48
Your instincts are correct. More advice here: [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples). — jpp, Aug 06 '18 at 14:49

score 0 · Answer 1 · answered Aug 06 '18 at 13:57

0

try that

import pandas as pd

# you do here pd.read_excel('abc.xlsx')
cols = list('ABCD')
df = pd.DataFrame(pd.np.random.randint(0,1000,size=(10, 4)), columns=cols)
# set all but the largest 3 to 0
for c in cols:
    df.at[df[c].nsmallest(df.shape[0] - 3).index, c] = 0
df.to_excel(output.xlsx')

answered Aug 06 '18 at 13:57

user1889297

464
5
13

user1889297 - that doesn't get me what I need. I need to know the commodity name, followed by the figure and I'd like it in the last column of data. Are you able to tweak it to match what is shown in the image? – Shawn Schreier Aug 06 '18 at 14:47
I figured it out - what I did is the following - I created a blank list called 'y' - I then appended the last line to y as opposed to printing it out. I then set a new df column called data to = y ---> df['data'] = y. Now I can just write the dataframe to excel. – Shawn Schreier Aug 06 '18 at 15:54

Python 3.6 - add output as new column to dataframe and then output to excel

1 Answers1