CODEWITHSUNDEEP
pandasdataframe
Aran Freel
Aran Freel

Reputation: 3215

How to append data to pandas multi-index dataframe

How can I append data to a Pandas Multi-Index DataFrame? I currently use the following code to successfully create a dataframe from my data.

df = pd.DataFrame.from_dict(output, orient='index')

I am thinking maybe something like this...

df = pd.DataFrame['MMM', 'IncomeStatement'].from_dict(output, orient='index')

DataFrame to merge

                                            0          1          2
Total Revenue                           182795000  170910000  156508000
Cost of Revenue                         112258000  106606000   87846000
Gross Profit                             70537000   64304000   68662000
Research Development                      6041000    4475000    3381000
Selling General and Administrative       11993000   10830000   10040000
Non Recurring                                   0          0          0
Others                                          0          0          0
Total Operating Expenses                        0          0          0
Operating Income or Loss                 52503000   48999000   55241000
Total Other Income/Expenses Net            980000    1156000     522000
Earnings Before Interest And Taxes       53483000   50155000   55763000
Interest Expense                                0          0          0
Income Before Tax                        53483000   50155000   55763000
Income Tax Expense                       13973000   13118000   14030000
Minority Interest                               0          0          0
Net Income From Continuing Ops           39510000   37037000   41733000
Discontinued Operations                         0          0          0
Extraordinary Items                             0          0          0
Effect Of Accounting Changes                    0          0          0
Other Items                                     0          0          0
Net Income                               39510000   37037000   41733000
Preferred Stock And Other Adjustments           0          0          0
Net Income Applicable To Common Shares   39510000   37037000   41733000

Multi-Index / Parent DataFrame

MMM     IncomeStatemen
        BalanceSheet   
        CashFlows      
ABT     IncomeStatement
        BalanceSheet   
        CashFlows      
ABBV    IncomeStatement
        BalanceSheet   
        CashFlows      
ACN     IncomeStatement
        BalanceSheet   
        CashFlows

Result

MMM     IncomeStatement        Total Revenue                           182795000  170910000  156508000
                               Cost of Revenue                         112258000  106606000   87846000
                               Gross Profit                             70537000   64304000   68662000
                               Research Development                      6041000    4475000    3381000
                               Selling General and Administrative       11993000   10830000   10040000
                               Non Recurring                                   0          0          0
                               Others                                          0          0          0
                               Total Operating Expenses                        0          0          0
                               Operating Income or Loss                 52503000   48999000   55241000
                               Total Other Income/Expenses Net            980000    1156000     522000
                               Earnings Before Interest And Taxes       53483000   50155000   55763000
                               Interest Expense                                0          0          0
                               Income Before Tax                        53483000   50155000   55763000
                               Income Tax Expense                       13973000   13118000   14030000
                               Minority Interest                               0          0          0
                               Net Income From Continuing Ops           39510000   37037000   41733000
                               Discontinued Operations                         0          0          0
                               Extraordinary Items                             0          0          0
                               Effect Of Accounting Changes                    0          0          0
                               Other Items                                     0          0          0
                               Net Income                               39510000   37037000   41733000
                               Preferred Stock And Other Adjustments           0          0          0
                               Net Income Applicable To Common Shares   39510000   37037000   41733000                                       



        BalanceSheet   
        CashFlows      
ABT     IncomeStatement
        BalanceSheet   
        CashFlows      
ABBV    IncomeStatement
        BalanceSheet   
        CashFlows      
ACN     IncomeStatement
        BalanceSheet   
        CashFlows

Upvotes: 7

Views: 17467

Answers (1)

Ami Tavory
Ami Tavory

Reputation: 76297

Am using simplified versions of your DataFrames.

Suppose you start with:

import pandas as pd
import numpy as np

arrays = [np.array(['bar', 'bar', 'baz', 'baz', 'foo', 'foo', 'qux', 'qux']),
    np.array(['one', 'two', 'one', 'two', 'one', 'two', 'one', 'two'])]

s = pd.DataFrame(index=arrays)

so that

>> s
bar one
    two
baz one
    two
foo one
    two
qux one
    two

(this is your parent)

and also

c = pd.DataFrame(index=['one', 'two'], data=[23, 33])

so that 

>> c
    0
one     23
two     33

(this is your first DataFrame)

So, a merge + groupby give

>> pd.merge(s.reset_index(), c, left_on='level_1', right_index=True).groupby(['level_0', 'level_1']).sum()
        0
level_0     level_1     
bar one     23
    two     33
baz one     23
    two     33
foo one     23
    two     33
qux one     23
    two     33

Upvotes: 3

Related Questions