How to append data to pandas multi-index dataframe

Question

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

What does the DataFrame look like initially, what should it look like at the end? — Ami Tavory
– Ami Tavory, Commented Jun 18, 2015 at 17:36
Great, but the example still leaves much room for guesses. Your followup questions make this even worse. Sorry, but I'm downvoting this question for its being extremely unclear. If you edit it to something better, I'll gladly reverse it. — Ami Tavory
– Ami Tavory, Commented Jun 19, 2015 at 10:30

Ami Tavory · Accepted Answer · 2015-06-18 21:44:42Z

3

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

edited Jun 18, 2015 at 21:44

answered Jun 18, 2015 at 18:05

Ami Tavory

76.7k13 gold badges152 silver badges196 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Aran Freel Over a year ago

to create the multi-index I used fin_data = pd.MultiIndex.from_product(iterables, names=['Ticker', 'Financials']) which doesn't have reset_index available to it... Any thoughts?

Ami Tavory Over a year ago

Oh, you're just creating an index without a dataframe. Many ways to go from there. Shortest path to the beginning of my answer is just to create a dataframe with your fin_data as its index.

Ami Tavory Over a year ago

@AranFreel Not sure of what? pd.DataFrame(index=pd.MultiIndex.from_product([[0, 1], ['a', 'b', 'c']])).reset_index() works fine. However, I must say this is a very inefficient way of asking a question. If you want to help people help you, make the effort to make a short smidgen of code that shows the problem. Where in your question does pd.MultiIndex.from_product even appear? Sorry, but I can't keep guessing what you mean.

Aran Freel Over a year ago

What if I ran a for loop would their be a way to append and create the dataframe as I go? I think what I could do is append eachTicker to the array list then from there I could build a dataframe from the arrays :-D (epiphany moment)

Aran Freel Over a year ago

Well you actually answered my question I just had the code wrong for my multi-index

Collectives™ on Stack Overflow

How to append data to pandas multi-index dataframe

DataFrame to merge

Multi-Index / Parent DataFrame

Result

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

DataFrame to merge

Multi-Index / Parent DataFrame

Result

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related