1

The following code is meant to populate a pandas DataFrame. Unfortunately the output is coming out weird.

output = {'Revenue' : [1456216, 549514, 489461], 'Cost of Revenue' : [1565486, 498464, 156131], 'Gross Profit' : [456465, 565165, 651613] ... ... ... }

years = ['year1', 'year2', 'year3']

df = pd.DataFrame(output.values(), index=output.keys(), columns=years)

print(df)

The dictionary output has list values. I want to use output.keys() as the DataFrame index, the list years for the columns and output.values() as the data within the data frame. Ultimately I want the ouput to be as follows

                    Year 1          Year 2          Year 3
Revenue             1456216         549514          489461
Cost of Revenue     1565486         498464          156131
Gross Profit        456465          565165          651613
...                 ...             ...             ...
...                 ...             ...             ...

How do I do this?

Current Output

                                                                                    year1  \
Total Revenue                           ([2218767, 1528545, 972309], [293797, 202908, ...   
Cost of Revenue                         ([2218767, 1528545, 972309], [293797, 202908, ...   
Gross Profit                            ([2218767, 1528545, 972309], [293797, 202908, ...   
Research Development                    ([2218767, 1528545, 972309], [293797, 202908, ...   
Selling General and Administrative      ([2218767, 1528545, 972309], [293797, 202908, ...   
Non Recurring                           ([2218767, 1528545, 972309], [293797, 202908, ...   
Others                                  ([2218767, 1528545, 972309], [293797, 202908, ...   
Total Operating Expenses                ([2218767, 1528545, 972309], [293797, 202908, ...   
Operating Income or Loss                ([2218767, 1528545, 972309], [293797, 202908, ...   
Total Other Income/Expenses Net         ([2218767, 1528545, 972309], [293797, 202908, ...   
Earnings Before Interest And Taxes      ([2218767, 1528545, 972309], [293797, 202908, ...   
Interest Expense                        ([2218767, 1528545, 972309], [293797, 202908, ...   
Income Before Tax                       ([2218767, 1528545, 972309], [293797, 202908, ...   
Income Tax Expense                      ([2218767, 1528545, 972309], [293797, 202908, ...   
Minority Interest                       ([2218767, 1528545, 972309], [293797, 202908, ...   
Net Income From Continuing Ops          ([2218767, 1528545, 972309], [293797, 202908, ...   
Discontinued Operations                 ([2218767, 1528545, 972309], [293797, 202908, ...   
Extraordinary Items                     ([2218767, 1528545, 972309], [293797, 202908, ...   
Effect Of Accounting Changes            ([2218767, 1528545, 972309], [293797, 202908, ...   
Other Items                             ([2218767, 1528545, 972309], [293797, 202908, ...    
Net Income                              ([2218767, 1528545, 972309], [293797, 202908, ...   
Preferred Stock And Other Adjustments   ([2218767, 1528545, 972309], [293797, 202908, ...   
Net Income Applicable To Common Shares  ([2218767, 1528545, 972309], [293797, 202908, ...   

                                                                                year2  \
Total Revenue                           ([2218767, 1528545, 972309], [293797, 202908, ...   
Cost of Revenue                         ([2218767, 1528545, 972309], [293797, 202908, ...   
Gross Profit                            ([2218767, 1528545, 972309], [293797, 202908, ...   
Research Development                    ([2218767, 1528545, 972309], [293797, 202908, ...   
Selling General and Administrative      ([2218767, 1528545, 972309], [293797, 202908, ...   
Non Recurring                           ([2218767, 1528545, 972309], [293797, 202908, ...   
Others                                  ([2218767, 1528545, 972309], [293797, 202908, ...   
Total Operating Expenses                ([2218767, 1528545, 972309], [293797, 202908, ...   
Operating Income or Loss                ([2218767, 1528545, 972309], [293797, 202908, ...   
Total Other Income/Expenses Net         ([2218767, 1528545, 972309], [293797, 202908, ...   
Earnings Before Interest And Taxes      ([2218767, 1528545, 972309], [293797, 202908, ...   
Interest Expense                        ([2218767, 1528545, 972309], [293797, 202908, ...   
Income Before Tax                       ([2218767, 1528545, 972309], [293797, 202908, ...   
Income Tax Expense                      ([2218767, 1528545, 972309], [293797, 202908, ...   
Minority Interest                       ([2218767, 1528545, 972309], [293797, 202908, ...   
Net Income From Continuing Ops          ([2218767, 1528545, 972309], [293797, 202908, ...   
Discontinued Operations                 ([2218767, 1528545, 972309], [293797, 202908, ...   
Extraordinary Items                     ([2218767, 1528545, 972309], [293797, 202908, ...   
Effect Of Accounting Changes            ([2218767, 1528545, 972309], [293797, 202908, ...   
Other Items                             ([2218767, 1528545, 972309], [293797, 202908, ...   
Net Income                              ([2218767, 1528545, 972309], [293797, 202908, ...   
Preferred Stock And Other Adjustments   ([2218767, 1528545, 972309], [293797, 202908, ...   
Net Income Applicable To Common Shares  ([2218767, 1528545, 972309], [293797, 202908, ...   

                                                                                year3  
You get the idea


OrderedDict([('Total Revenue', [2218767, 1528545, 972309]), ('Cost of Revenue', [293797, 202908, 125521]), ('Gross Profit', [1924970, 1325637, 846788]), ('Research Development', [536184, 395643, 257179]), ('Selling General and Administrative', [1115705, 747666, 452898]), ('Non Recurring', ['0', '0', '0']), ('Others', [236946, 134516, 79849]), ('Total Operating Expenses', ['0', '0', '0']), ('Operating Income or Loss', [36135, 47812, 56862]), ('Total Other Income/Expenses Net', [-4930, 1416, 252]), ('Earnings Before Interest And Taxes', [31205, 49228, 57114]), ('Interest Expense', ['0', '0', '0']), ('Income Before Tax', [31205, 49228, 57114]), ('Income Tax Expense', [46525, 22459, 35504]), ('Minority Interest', [-427, '0', '0']), ('Net Income From Continuing Ops', [-15747, 26769, 21610]), ('Discontinued Operations', ['0', '0', '0']), ('Extraordinary Items', ['0', '0', '0']), ('Effect Of Accounting Changes', ['0', '0', '0']), ('Other Items', ['0', '0', '0']), ('Net Income', [-15747, 26769, 21610]), ('Preferred Stock And Other Adjustments', ['0', '0', '0']), ('Net Income Applicable To Common Shares', [-15747, 26769, 21610])])
Community
  • 1
  • 1
Aran Freel
  • 3,085
  • 5
  • 29
  • 42

1 Answers1

2

Instead of passing output.values(), you could pass list(output.values()):

>>> pd.DataFrame(list(output.values()), index=output.keys(), columns=years)
                   year1   year2   year3
Cost of Revenue  1565486  498464  156131
Gross Profit      456465  565165  651613
Revenue          1456216  549514  489461

You could also write

>>> df = pd.DataFrame.from_dict(output, orient='index')
>>> df.columns = years
>>> df
                   year1   year2   year3
Cost of Revenue  1565486  498464  156131
Gross Profit      456465  565165  651613
Revenue          1456216  549514  489461

The DataFrame constructor is asked to do a lot of things, and sometimes I think it's simpler to break it up.

As for why this is happening, there's a lot of special-casing on type in the constructor, and this branch:

    elif isinstance(data, (list, types.GeneratorType)):

isn't picking up the dict_values object because it's neither a list nor a generator.

DSM
  • 342,061
  • 65
  • 592
  • 494