Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pandas: Use multiple columns of a dataframe as index of another

I've got a large dataframe with my data in it, and another dataframe of the same first dimension that contains metadata about each point in time (e.g., what trial number it was, what trial type it was).

What I want to do is slice the large dataframe using the values of the "metadataframe". I want to keep these separate (rather than storing the metadataframe as a multi-index of the larger one).

Right now, I am trying to do something like this:

def my_func(container):
   container.big_df.set_index(container.meta_df[['col1', 'col2']])
   container.big_df.loc['col1val', 'col2val'].plot()

However, this returns the following error:

ValueError: Must pass DataFrame with boolean values only

Note that this works fine if I only pass a single column to set_index.

Can anyone figure out what's going wrong here? Alternatively, can someone tell me that I'm doing this in a totally stupid and hacky way, and that there's a much better way to go about it? :)

MY SOLUTION

Thanks for the ideas. I played around with the indexing a little bit, and this seems to be the easiest / fastest. I didn't like having to strip the index of its name, and transposing the values etc. seemed cumbersome. I realized something interesting (and probably worth easily fixing):

dfa.set_index(dfb[['col1', 'col2']]) 

doesn't work, but

dfa.set_index([dfb.col1, dfb.col2])

does.

So, you can basically turn dfb into a list of columns, making set_index work, by the following convention:

dfa.set_index([dfb[col] for col in ['col1', 'col2']])
like image 462
choldgraf Avatar asked Feb 18 '14 06:02

choldgraf


People also ask

Can a pandas index contain multiple columns?

MiltiIndex is also referred to as Hierarchical/multi-level index/advanced indexing in pandas enables us to create an index on multiple columns and store data in an arbitrary number of dimensions.

How do I make two columns an index?

CREATE INDEX [index name] ON [Table name]([column1, column2, column3,...]); Multicolumn indexes can: be created on up to 32 columns. be used for partial indexing.

How do you create a multilevel index in pandas?

Creating a MultiIndex (hierarchical index) object A MultiIndex can be created from a list of arrays (using MultiIndex. from_arrays() ), an array of tuples (using MultiIndex. from_tuples() ), a crossed set of iterables (using MultiIndex. from_product() ), or a DataFrame (using MultiIndex.


2 Answers

Use MultiIndex.from_arrays() to create the index object:

import pandas as pd
df1 = pd.DataFrame({"A":[1,2,3], "B":["a","b","c"]})
df2 = pd.DataFrame({"C":[100,200,300]})
df2.index = pd.MultiIndex.from_arrays(df1.values.T)

print df2

the result:

       C
1 a  100
2 b  200
3 c  300
like image 66
HYRY Avatar answered Oct 26 '22 22:10

HYRY


change your first line to:

container.big_df.index=pd.MultiIndex.from_arrays(container.meta_df[['col1', 'col2']].values.T, names=['i1','i2'])
like image 38
CT Zhu Avatar answered Oct 27 '22 00:10

CT Zhu