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
– EdChum, Commented Jan 23, 2015 at 16:05

alacy · Accepted Answer · 2015-01-23 20:42:55Z

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, 2015 at 20:42

answered Jan 23, 2015 at 18:59

alacy

5,0748 gold badges33 silver badges47 bronze badges

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

9 Comments

JAB Over a year ago

df1.merge(df2, on="Asset").set_index(['Country','Class','Asset']) if you want dat2 as the index

alacy Over a year ago

@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.

user2819398 Over a year ago

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 Over a year ago

Just a followup question: Is it possible to select only USA and E using df1.ix['USA','E']?

user2819398 Over a year ago

I found the answer here: stackoverflow.com/questions/20754746/… but this led to another question. Is it possible to have index for both rows and columns?

|

Collectives™ on Stack Overflow

create multiple index for a dataframe in pandas

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related