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
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.
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.
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 .
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.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With