create multiple index for a dataframe in pandas

Question

I have a dataframe dat1:

Asset   Returns
DJ  0.1
SP  0.2
USDJPY  0.03
USDMXN  1.2

I have another dataframe dat2:

Country Class   Asset
USA E   DJ
USA E   SP
USA FX  USDJPY
USA FX  USDMXN

How do I use dat2 to create an index for dat1; dat1 and dat2 have a common column "Asset"

>new_dat=dat_corr.merge(dat_class,on="Asset",how="right").set_index(['Country','‌Class','Asset'])
>new_dat.shape
(89, 89) 
>temp1='UNITEDSTATES' 
>temp2='Equity'
>new_dat.loc[ (new_dat.index.get_level_values('Country').isin([temp1]) &   new_dat.index.get_level_values('Class').isin([temp2]))]'

This gives me [3 rows x 89 columns]. My 89 columns is a mix of Equity/FX/FI/Commodities. If I want only USA Equities vs all other equity and not the entire 89 columns how do I do it? So I thought if I can create an index for the columns as well and use a similar approach?

Sorry are you asking to merge the 2 dfs on `Asset` column? Can you show desired output — EdChum, Jan 23 '15 at 16:05

alacy · Accepted Answer · 2015-01-23T20:42:55.263

0

Perhaps this is what you are looking for:

df1 = pd.DataFrame(np.array([
    ['DJ', 0.1],
    ['SP', 0.2],
    ['USDJPY', 0.03]]),
    columns=['Asset', 'Returns'])
df2 = pd.DataFrame(np.array([
    ['USA', 'E', 'DJ'],
    ['USA', 'E', 'SP'],
    ['USA', 'FX', 'USDJPY']]),
    columns=['Country', 'Class', 'Asset'])

df1.merge(df2, on="Asset")

Which produces an output of

   Asset Returns Country Class
0      DJ     0.1     USA     E
1      SP     0.2     USA     E
2  USDJPY    0.03     USA    FX

Since your wording is not completely clear if you wish to maintain an integer index or if you wish to use Asset as your main index and per the suggestion of @JBradley you can do:

df1.merge(df2, on="Asset").set_index(['Country','Class','Asset'])

which will give you a final data frame that looks like:

   Asset Returns Country Class
      DJ     0.1     USA     E
      SP     0.2     USA     E
  USDJPY    0.03     USA    FX

edited Jan 23 '15 at 20:42

answered Jan 23 '15 at 18:59

alacy

4,972
8
30
47

`df1.merge(df2, on="Asset").set_index(['Country','Class','Asset'])` if you want dat2 as the index – JAB Jan 23 '15 at 20:38
1

@JBradley Very true. The OP's wording is a bit ambiguous as they state they wish to make `Asset` "AN index" rather than a more explicit wording like "THE index". I'll add your comment as an edit. – alacy Jan 23 '15 at 20:41
Thank you very much for the response. That is exactly what i wanted (dat2 as the index). being a newbie i dont understand why you had to use np.array when it seems to work directly with two pd.dataframes – user2819398 Jan 26 '15 at 09:48
Just a followup question: Is it possible to select only USA and E using df1.ix['USA','E']? – user2819398 Jan 26 '15 at 11:18
I found the answer here: http://stackoverflow.com/questions/20754746/using-boolean-indexing-for-row-and-column-multiindex-in-pandas but this led to another question. Is it possible to have index for both rows and columns? – user2819398 Jan 26 '15 at 13:13
@user2819398 What do you mean by having an index on both? And I didn't need to use `np.arrays`, the code I happened to copy and edit for that bit was implemented with `np.arrays`. – alacy Jan 26 '15 at 14:13
"new_dat=dat_corr.merge(dat_class,on="Asset",how="right").set_index(['Country','Class','Asset']) new_dat.shape (89, 89) temp1='UNITEDSTATES' temp2='Equity' new_dat.loc[ (new_dat.index.get_level_values('Country').isin([temp1]) & new_dat.index.get_level_values('Class').isin([temp2]))]. This gives me [3 rows x 89 columns]. My 89 columns is a mix of Equity/FX/FI/Commodities. If i want only USA Equities vs all other equity and not the enitre 89 columns how do i do it? So i thought if i can create an index for the columns as well and use a similar approach? – user2819398 Jan 26 '15 at 14:27
@user2819398 you should add that code to the original question with an "edit" and with some context of what it is you're trying to do because it's almost impossible to read in unformatted comment. – alacy Jan 26 '15 at 14:31
@user2819398 I suggest making a new question regarding the edit that you made. You would have a much higher chance of receiving an answer. – alacy Jan 27 '15 at 01:49

create multiple index for a dataframe in pandas

1 Answers1