Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Append two multiindexed pandas dataframes

Can you please help to append two multiindexed pandas dataframes? Trying to append df_future to df_current. COMPANY and DATE are the indexes.

df_current

                           VALUE
COMPANY     DATE            
            7/27/2015       1
A           7/28/2015       2
            7/29/2015       3
            7/30/2015       4
            7/27/2015       11
B           7/28/2015       12
            7/29/2015       13
            7/30/2015       14

df_future

                            VALUE
COMPANY     DATE            
A           8/1/2015        5
            8/2/2015        6
B           8/1/2015        15
            8/2/2015        16

Based on these dfs, want to see..

df_current_and_future

                            VALUE
COMPANY     DATE            
            7/27/2015       1
            7/28/2015       2
A           7/29/2015       3
            7/30/2015       4
            8/1/2015        5
            8/2/2015        6
            7/27/2015       11
            7/28/2015       12
B           7/29/2015       13
            7/30/2015       14
            8/1/2015        15
            8/2/2015        16
like image 404
E.K. Avatar asked Jul 30 '15 17:07

E.K.


People also ask

Can you combine two DataFrames in pandas?

Pandas' merge and concat can be used to combine subsets of a DataFrame, or even data from different files. join function combines DataFrames based on index or column. Joining two DataFrames can be done in multiple ways (left, right, and inner) depending on what data must be in the final DataFrame.

How do I merge two indexes in pandas?

concat() to Merge Two DataFrames by Index. You can concatenate two DataFrames by using pandas. concat() method by setting axis=1 , and by default, pd. concat is a row-wise outer join.

Can you merge more than 2 DataFrames in pandas?

We can use either pandas. merge() or DataFrame. merge() to merge multiple Dataframes. Merging multiple Dataframes is similar to SQL join and supports different types of join inner , left , right , outer , cross .


2 Answers

Use concat to concatenate the two DataFrames, and sort_index to reorder the first index level:

In [167]: pd.concat([df_current, df_future]).sort_index()
Out[167]: 
                   VALUE
COMPANY DATE            
A       7/27/2015      1
        7/27/2015     11
        7/28/2015      2
        7/29/2015      3
        7/30/2015      4
        8/1/2015       5
        8/2/2015       6
B       7/28/2015     12
        7/29/2015     13
        7/30/2015     14
        8/1/2015      15
        8/2/2015      16

Note: My original answer used sortlevel which is now deprecated. As firelynx shows, use sort_index instead.

like image 140
unutbu Avatar answered Oct 17 '22 08:10

unutbu


Appending in pandas is called concat. And is done with the pd.concat function.

The concat function works no matter if you have multiindex or not

df = pd.concat([df_current, future])

                   VALUE
COMPANY DATE            
A       7/27/2015      1
        7/28/2015      2
        7/29/2015      3
        7/30/2015      4
        7/27/2015     11
B       7/28/2015     12
        7/29/2015     13
        7/30/2015     14
A       8/1/2015       5
        8/2/2015       6
B       8/1/2015      15
        8/2/2015      16

And if the sorting is an issue, just use:

df.sort_index()

                   VALUE
COMPANY DATE            
A       7/27/2015      1
        7/27/2015     11
        7/28/2015      2
        7/29/2015      3
        7/30/2015      4
        8/1/2015       5
        8/2/2015       6
B       7/28/2015     12
        7/29/2015     13
        7/30/2015     14
        8/1/2015      15
        8/2/2015      16
like image 40
firelynx Avatar answered Oct 17 '22 08:10

firelynx