I have a `DataFrame` that looks like

      Emp1    Empl2           date       Company
    0    0        0     2012-05-01         apple
    1    0        1     2012-05-29         apple
    2    0        1     2013-05-02         apple
    3    0        1     2013-11-22         apple
    18   1        0     2011-09-09        google
    19   1        0     2012-02-02        google
    20   1        0     2012-11-26        google
    21   1        0     2013-05-11        google

I want to pass the company and date for setting a `MultiIndex` for this `DataFrame`. Currently it has a default index. I am using `df.set_index([&#39;Company&#39;, &#39;date&#39;], inplace=True)`

    df = pd.DataFrame()
    for c in company_list:
            row = pd.DataFrame([dict(company = &#39;%s&#39; %s, date = datetime.date(2012, 05, 01))])
            df = df.append(row, ignore_index = True)
            for e in emp_list:
                dataset  = pd.read_sql(&quot;select company, emp_name, date(date), count(*) from company_table where  = &#39;&quot;+s+&quot;&#39; and emp_name = &#39;&quot;+b+&quot;&#39; group by company, date, name LIMIT 5 &quot;, con)
                    if len(dataset) == 0:
                    row = pd.DataFrame([dict(sitename=&#39;%s&#39; %s, name = &#39;%s&#39; %b, date = datetime.date(2012, 05, 01), count = np.nan)])
                    dataset = dataset.append(row, ignore_index=True)
                dataset = dataset.rename(columns = {&#39;count&#39;: &#39;%s&#39; %b})
                dataset = dataset.groupby([&#39;company&#39;, &#39;date&#39;, &#39;emp_name&#39;], as_index = False).sum()
                
                dataset = dataset.drop(&#39;emp_name&#39;, 1)
                df = pd.merge(df, dataset, how = &#39;&#39;)
                df = df.sort(&#39;date&#39;, ascending = True)
                df.fillna(0, inplace = True)

    df.set_index([&#39;Company&#39;, &#39;date&#39;], inplace=True)            
    print df

But when I print this `DataFrame`, it prints `None`. Is this not the correct way of doing it? Also I want to shuffle the positions of the columns company and date so that company becomes the first index, and date becomes the second in Hierarchy. Any ideas on this?

Set MultiIndex of an existing DataFrame in pandas

**What are the most common pandas ways to select/filter rows of a [dataframe whose index is a MultiIndex](https://pandas.pydata.org/pandas-docs/stable/user_guide/advanced.html#)?**

 - Slicing based on a single value/label
 - Slicing based on multiple labels from one or more levels
 - Filtering on boolean conditions and expressions
 - Which methods are applicable in what circumstances

Assumptions for simplicity:

 1. input dataframe does not have duplicate index keys
 1. input dataframe below only has two levels. (Most solutions shown here generalize to N levels)

----


### Example input:
 
&gt;     mux = pd.MultiIndex.from_arrays([
&gt;         list(&#39;aaaabbbbbccddddd&#39;),
&gt;         list(&#39;tuvwtuvwtuvwtuvw&#39;)
&gt;     ], names=[&#39;one&#39;, &#39;two&#39;])
&gt;     
&gt;     df = pd.DataFrame({&#39;col&#39;: np.arange(len(mux))}, mux)
&gt;     
&gt;              col
&gt;     one two     
&gt;     a   t      0
&gt;         u      1
&gt;         v      2
&gt;         w      3
&gt;     b   t      4
&gt;         u      5
&gt;         v      6
&gt;         w      7
&gt;         t      8
&gt;     c   u      9
&gt;         v     10
&gt;     d   w     11
&gt;         t     12
&gt;         u     13
&gt;         v     14
&gt;         w     15

## Question 1: Selecting a Single Item       
How do I select rows having &quot;a&quot; in level &quot;one&quot;? 

             col
    one two     
    a   t      0
        u      1
        v      2
        w      3

Additionally, how would I be able to drop level &quot;one&quot; in the output?

         col
    two     
    t      0
    u      1
    v      2
    w      3


**Question 1b**    
How do I slice all rows with value &quot;t&quot; on level &quot;two&quot;?
 
             col
    one two     
    a   t      0
    b   t      4
        t      8
    d   t     12

## Question 2: Selecting Multiple Values in a Level        
How can I select rows corresponding to items &quot;b&quot; and &quot;d&quot; in level &quot;one&quot;?


             col
    one two     
    b   t      4
        u      5
        v      6
        w      7
        t      8
    d   w     11
        t     12
        u     13
        v     14
        w     15

**Question 2b**    
How would I get all values corresponding to &quot;t&quot; and &quot;w&quot; in level &quot;two&quot;?

             col
    one two     
    a   t      0
        w      3
    b   t      4
        w      7
        t      8
    d   w     11
        t     12
        w     15


## Question 3: Slicing a Single Cross Section `(x, y)`      
How do I retrieve a cross section, i.e., a single row having a specific values for the index from `df`? Specifically, how do I retrieve the cross section of `(&#39;c&#39;, &#39;u&#39;)`, given by

             col
    one two     
    c   u      9

## Question 4: Slicing Multiple Cross Sections `[(a, b), (c, d), ...]`       
How do I select the two rows corresponding to `(&#39;c&#39;, &#39;u&#39;)`, and `(&#39;a&#39;, &#39;w&#39;)`?

             col
    one two     
    c   u      9
    a   w      3

## Question 5: One Item Sliced per Level       
How can I retrieve all rows corresponding to &quot;a&quot; in level &quot;one&quot; or &quot;t&quot; in level &quot;two&quot;?

             col
    one two     
    a   t      0
        u      1
        v      2
        w      3
    b   t      4
        t      8
    d   t     12

## Question 6: Arbitrary Slicing    
How can I slice specific cross sections? For &quot;a&quot; and &quot;b&quot;, I would like to select all rows with sub-levels &quot;u&quot; and &quot;v&quot;, and for &quot;d&quot;, I would like to select rows with sub-level &quot;w&quot;.

             col
    one two     
    a   u      1
        v      2
    b   u      5
        v      6
    d   w     11
        w     15


&gt; Question 7 will use a unique setup consisting of a numeric level:
&gt; 
&gt;     np.random.seed(0)
&gt;     mux2 = pd.MultiIndex.from_arrays([
&gt;         list(&#39;aaaabbbbbccddddd&#39;),
&gt;         np.random.choice(10, size=16)
&gt;     ], names=[&#39;one&#39;, &#39;two&#39;])
&gt;     
&gt;     df2 = pd.DataFrame({&#39;col&#39;: np.arange(len(mux2))}, mux2)
&gt;     
&gt;              col
&gt;     one two     
&gt;     a   5      0
&gt;         0      1
&gt;         3      2
&gt;         3      3
&gt;     b   7      4
&gt;         9      5
&gt;         3      6
&gt;         5      7
&gt;         2      8
&gt;     c   4      9
&gt;         7     10
&gt;     d   6     11
&gt;         8     12
&gt;         8     13
&gt;         1     14
&gt;         6     15


## Question 7: Filtering by numeric inequality on individual levels of the multiindex      
How do I get all rows where values in level &quot;two&quot; are greater than 5?

             col
    one two     
    b   7      4
        9      5
    c   7     10
    d   6     11
        8     12
        8     13
        6     15

-----
Note: This post will *not* go through how to create MultiIndexes, how to perform assignment operations on them, or any performance related discussions (these are separate topics for another time). 

Select rows in pandas MultiIndex DataFrame

I have a dataframe with 2 index levels:


                             value
    Trial    measurement
        1              0        13
                       1         3
                       2         4
        2              0       NaN
                       1        12
        3              0        34 

Which I want to turn into this:

    Trial    measurement       value
        
        1              0        13
        1              1         3
        1              2         4
        2              0       NaN
        2              1        12
        3              0        34 

How can I best do this?   
           


I need this because I want to aggregate the data [as instructed here][1], but I can&#39;t select my columns like that if they are in use as indices.


  [1]: https://stackoverflow.com/a/20108446/1893275

Turn Pandas Multi-Index into column

I&#39;m starting with input data like this

    df1 = pandas.DataFrame( { 
        &quot;Name&quot; : [&quot;Alice&quot;, &quot;Bob&quot;, &quot;Mallory&quot;, &quot;Mallory&quot;, &quot;Bob&quot; , &quot;Mallory&quot;] , 
        &quot;City&quot; : [&quot;Seattle&quot;, &quot;Seattle&quot;, &quot;Portland&quot;, &quot;Seattle&quot;, &quot;Seattle&quot;, &quot;Portland&quot;] } )

Which when printed appears as this:

       City     Name
    0   Seattle    Alice
    1   Seattle      Bob
    2  Portland  Mallory
    3   Seattle  Mallory
    4   Seattle      Bob
    5  Portland  Mallory


Grouping is simple enough:

    g1 = df1.groupby( [ &quot;Name&quot;, &quot;City&quot;] ).count()

and printing yields a `GroupBy` object:

                      City  Name
    Name    City
    Alice   Seattle      1     1
    Bob     Seattle      2     2
    Mallory Portland     2     2
            Seattle      1     1


But what I want eventually is another DataFrame object that contains all the rows in the GroupBy object. In other words I want to get the following result:


                      City  Name
    Name    City
    Alice   Seattle      1     1
    Bob     Seattle      2     2
    Mallory Portland     2     2
    Mallory Seattle      1     1

I can&#39;t quite see how to accomplish this in the pandas documentation. Any hints would be welcome.


Converting a Pandas GroupBy output from Series to DataFrame

I have a multi-index data frame with columns  &#39;A&#39; and &#39;B&#39;. 

Is there is a way to select rows by filtering on one column of the multi-index without resetting the index to a single column index? 

For Example.

    # has multi-index (A,B)
    df
    #can I do this? I know this doesn&#39;t work because the index is multi-index so I need to     specify a tuple

    df.ix[df.A ==1]



selecting from multi-index pandas

Suppose I have a nested dictionary &#39;user_dict&#39; with structure:

 - **Level 1:** UserId (Long Integer)
 - **Level 2:** Category (String)
 - **Level 3:** Assorted Attributes (floats, ints, etc..)

For example, an entry of this dictionary would be:

    user_dict[12] = {
        &quot;Category 1&quot;: {&quot;att_1&quot;: 1, 
                       &quot;att_2&quot;: &quot;whatever&quot;},
        &quot;Category 2&quot;: {&quot;att_1&quot;: 23, 
                       &quot;att_2&quot;: &quot;another&quot;}}

each item in `user_dict` has the same structure and `user_dict` contains a large number of items which I want to feed to a pandas DataFrame, constructing the series from the attributes. In this case a hierarchical index would be useful for the purpose.

Specifically, my question is whether there exists a way to to help the DataFrame constructor understand that the series should be built from the values of the &quot;level 3&quot; in the dictionary?

If I try something like:

    df = pandas.DataFrame(users_summary)

The items in &quot;level 1&quot; (the UserId&#39;s) are taken as columns, which is the opposite of what I want to achieve (have UserId&#39;s as index). 

I know I could construct the series after iterating over the dictionary entries, but if there is a more direct way this would be very useful. A similar question would be asking whether it is possible to construct a pandas DataFrame from json objects listed in a file. 

Construct pandas DataFrame from items in nested dictionary

I have a dataset with multi-index columns in a pandas df that I would like to sort by values in a specific column.  I have tried using sortindex and sortlevel but haven&#39;t been able get the results I am looking for.  My dataset looks like:

        Group1    Group2
        A B C     A B C
    1   1 0 3     2 5 7
    2   5 6 9     1 0 0
    3   7 0 2     0 3 5 

I want to sort all data and the index by column C in Group 1 in descending order so my results look like:

        Group1    Group2
        A B C     A B C
     2  5 6 9     1 0 0
     1  1 0 3     2 5 7
     3  7 0 2     0 3 5 


Is it possible to do this sort with the structure that my data is in, or should I be swapping Group1 to the index side?


Multi Index Sorting in Pandas

I would like to add a column to the second level of a multiindex column dataframe.

    In [151]: df
    Out[151]: 
    first        bar                 baz           
    second       one       two       one       two 
    A       0.487880 -0.487661 -1.030176  0.100813 
    B       0.267913  1.918923  0.132791  0.178503
    C       1.550526 -0.312235 -1.177689 -0.081596 

The usual trick of direct assignment does not work:

    In [152]: df[&#39;bar&#39;][&#39;three&#39;] = [0, 1, 2]
    
    In [153]: df
    Out[153]: 
    first        bar                 baz           
    second       one       two       one       two 
    A       0.487880 -0.487661 -1.030176  0.100813
    B       0.267913  1.918923  0.132791  0.178503
    C       1.550526 -0.312235 -1.177689 -0.081596

How can I add the third row to under &quot;bar&quot;?

Pandas: add a column to a multiindex column dataframe

I have the following pd.DataFrame:


    Name	0	                    1	                   ...
    Col	    A	        B	        A	         B         ...
    0	    0.409511	-0.537108	-0.355529	 0.212134  ...
    1	    -0.332276	-1.087013	 0.083684	 0.529002  ...
    2	    1.138159	-0.327212	 0.570834	 2.337718  ...

It has MultiIndex columns with `names=[&#39;Name&#39;, &#39;Col&#39;]` and hierarchical levels. The `Name` label goes from 0 to n, and for each label, there are two `A` and `B` columns. 

I would like to subselect all the `A` (or `B`) columns of this DataFrame. 


pandas dataframe select columns in multiindex

Code example:

    In [171]: A = np.array([1.1, 1.1, 3.3, 3.3, 5.5, 6.6])
    
    In [172]: B = np.array([111, 222, 222, 333, 333, 777])
    
    In [173]: C = randint(10, 99, 6)
    
    In [174]: df = pd.DataFrame(zip(A, B, C), columns=[&#39;A&#39;, &#39;B&#39;, &#39;C&#39;])
    
    In [175]: df.set_index([&#39;A&#39;, &#39;B&#39;], inplace=True)
    
    In [176]: df
    Out[176]: 
              C
    A   B      
    1.1 111  20
        222  31
    3.3 222  24
        333  65
    5.5 333  22
    6.6 777  74 

Now, I want to retrieve A values:  
**Q1**: in range [3.3, 6.6] - expected return value: [3.3, 5.5, 6.6] or [3.3, 3.3, 5.5, 6.6] in case last inclusive, and [3.3, 5.5] or [3.3, 3.3, 5.5] if not.  
**Q2**: in range [2.0, 4.0] - expected return value: [3.3] or [3.3, 3.3]  

Same for any other *MultiIndex* dimension, for example B values:  
**Q3**: in range [111, 500] with repetitions, as number of data rows in range - expected return value: [111, 222, 222, 333, 333]    

More formal:

Let us assume T is a table with columns A, B and C. The table includes *n* rows. Table cells are numbers, for example A double, B and C integers. Let&#39;s create a *DataFrame* of table T, let us name it DF. Let&#39;s set columns A and B indexes of DF (without duplication, i.e. no separate columns A and B as indexes, and separate as data), i.e. A and B in this case *MultiIndex*.  
  
Questions:  

 1. How to write a query on the index, for example, to query the index A (or B), say in the labels interval [120.0, 540.0]? Labels 120.0 and 540.0 exist. I must clarify that I am interested only in the list of indices as a response to the query!
 2. How to the same, but in case of the labels 120.0 and 540.0 do not exist, but there are labels by value lower than 120, higher than 120 and less than 540, or higher than 540?
 3. In case the answer for Q1 and Q2 was unique index values, now the same, but with repetitions, as number of data rows in index range.

I know the answers to the above questions in the case of columns which are not indexes, but in the indexes case, after a long research in the web and experimentation with the functionality of *pandas*, I did not succeed. The only method (without additional programming) I see now is to have a duplicate of A and B as data columns in addition to index.


How to query MultiIndex index columns values in pandas

Say I have a dictionary that looks like this:

    dictionary = {&#39;A&#39; : {&#39;a&#39;: [1,2,3,4,5],
                         &#39;b&#39;: [6,7,8,9,1]},
    
                  &#39;B&#39; : {&#39;a&#39;: [2,3,4,5,6],
                         &#39;b&#39;: [7,8,9,1,2]}}
and I want a dataframe that looks something like this:

 

         A   B
         a b a b
      0  1 6 2 7
      1  2 7 3 8
      2  3 8 4 9
      3  4 9 5 1
      4  5 1 6 2

Is there a convenient way to do this? If I try:

    In [99]:
    
    DataFrame(dictionary)
    
    Out[99]:
         A	             B
    a	[1, 2, 3, 4, 5]	[2, 3, 4, 5, 6]
    b	[6, 7, 8, 9, 1]	[7, 8, 9, 1, 2]

I get a dataframe where each element is a list. What I need is a multiindex where each level corresponds to the keys in the nested dict and the rows corresponding to each element in the list as shown above. I think I can work a very crude solution but I&#39;m hoping there might be something a bit simpler. 

Nested dictionary to multiindex dataframe where dictionary keys are column labels

I have some hierarchical data which bottoms out into time series data which looks something like this:

    df = pandas.DataFrame(
        {&#39;value_a&#39;: values_a, &#39;value_b&#39;: values_b},
        index=[states, cities, dates])
    df.index.names = [&#39;State&#39;, &#39;City&#39;, &#39;Date&#39;]
    df

                                   value_a  value_b
    State   City       Date                        
    Georgia Atlanta    2012-01-01        0       10
                       2012-01-02        1       11
                       2012-01-03        2       12
                       2012-01-04        3       13
            Savanna    2012-01-01        4       14
                       2012-01-02        5       15
                       2012-01-03        6       16
                       2012-01-04        7       17
    Alabama Mobile     2012-01-01        8       18
                       2012-01-02        9       19
                       2012-01-03       10       20
                       2012-01-04       11       21
            Montgomery 2012-01-01       12       22
                       2012-01-02       13       23
                       2012-01-03       14       24
                       2012-01-04       15       25

I&#39;d like to perform time resampling per city, so something like
    
    df.resample(&quot;2D&quot;, how=&quot;sum&quot;)

would output

                                 value_a  value_b
    State   City       Date                        
    Georgia Atlanta    2012-01-01        1       21
                       2012-01-03        5       25
            Savanna    2012-01-01        9       29
                       2012-01-03       13       33
    Alabama Mobile     2012-01-01       17       37
                       2012-01-03       21       41
            Montgomery 2012-01-01       25       45
                       2012-01-03       29       49

as is, `df.resample(&#39;2D&#39;, how=&#39;sum&#39;)` gets me

    TypeError: Only valid with DatetimeIndex or PeriodIndex

Fair enough, but I&#39;d sort of expect this to work:

    &gt;&gt;&gt; df.swaplevel(&#39;Date&#39;, &#39;State&#39;).resample(&#39;2D&#39;, how=&#39;sum&#39;)
    TypeError: Only valid with DatetimeIndex or PeriodIndex


at which point I&#39;m really running out of ideas... is there some way stack and unstack might be able to help me?

Resampling Within a Pandas MultiIndex

I have a dataframe, `grouped`, with multiindex columns as below:

    import pandas as pd
    codes = [&quot;one&quot;,&quot;two&quot;,&quot;three&quot;];
    colours = [&quot;black&quot;, &quot;white&quot;];
    textures = [&quot;soft&quot;, &quot;hard&quot;];
    N= 100 # length of the dataframe
    df = pd.DataFrame({ &#39;id&#39; : range(1,N+1),
                        &#39;weeks_elapsed&#39; : [random.choice(range(1,25)) for i in range(1,N+1)],
                        &#39;code&#39; : [random.choice(codes) for i in range(1,N+1)],
                        &#39;colour&#39;: [random.choice(colours) for i in range(1,N+1)],
                        &#39;texture&#39;: [random.choice(textures) for i in range(1,N+1)],
                        &#39;size&#39;: [random.randint(1,100) for i in range(1,N+1)],
                        &#39;scaled_size&#39;: [random.randint(100,1000) for i in range(1,N+1)]
                       },  columns= [&#39;id&#39;, &#39;weeks_elapsed&#39;, &#39;code&#39;,&#39;colour&#39;, &#39;texture&#39;, &#39;size&#39;, &#39;scaled_size&#39;])
    grouped = df.groupby([&#39;code&#39;, &#39;colour&#39;]).agg( {&#39;size&#39;: [np.sum, np.average, np.size, pd.Series.idxmax],&#39;scaled_size&#39;: [np.sum, np.average, np.size, pd.Series.idxmax]}).reset_index()

    &gt;&gt; grouped
        code colour     size                           scaled_size                         
                        sum    average  size  idxmax            sum    average  size  idxmax
    0    one  black    1031  60.647059    17      81     185.153944  10.891408    17      47
    1    one  white     481  37.000000    13      53     204.139249  15.703019    13      53
    2  three  black     822  48.352941    17       6     123.269405   7.251141    17      31
    3  three  white    1614  57.642857    28      50     285.638337  10.201369    28      37
    4    two  black     523  58.111111     9      85      80.908912   8.989879     9      88
    5    two  white     669  41.812500    16      78      82.098870   5.131179    16      78
    [6 rows x 10 columns]

How can I flatten/merge the column index levels as: &quot;Level1|Level2&quot;, e.g. `size|sum`, `scaled_size|sum`. etc? If this is not possible, is there a way to `groupby()` as I did above without creating multi-index columns?




Pandas dataframe with multiindex column - merge levels

For example, I have:

    In [1]: df = pd.DataFrame([8, 9],
                              index=pd.MultiIndex.from_tuples([(1, 1, 1),
                                                               (1, 3, 2)]),
                              columns=[&#39;A&#39;])

    In [2] df
    Out[2]: 
           A
    1 1 1  8
      3 2  9

Is there a better way to remove the last level from the index than this:

    In [3]: pd.DataFrame(df.values,
                         index=df.index.droplevel(2),
                         columns=df.columns)
    Out[3]: 
         A
    1 1  8
      3  9

How to remove levels from a multi-indexed dataframe?

Given a dictionary of data frames like:

    dict = {&#39;ABC&#39;: df1, &#39;XYZ&#39; : df2}   # of any length...

where each data frame has the same columns and similar index, for example:

    data           Open     High      Low    Close   Volume
    Date                                                   
    2002-01-17  0.18077  0.18800  0.16993  0.18439  1720833
    2002-01-18  0.18439  0.21331  0.18077  0.19523  2027866
    2002-01-21  0.19523  0.20970  0.19162  0.20608   771149


What is the simplest way to combine all the data frames into one, with a multi-index like:

    symbol         ABC                                       XYZ
    data           Open     High      Low    Close   Volume  Open ...
    Date                                                   
    2002-01-17  0.18077  0.18800  0.16993  0.18439  1720833  ...
    2002-01-18  0.18439  0.21331  0.18077  0.19523  2027866  ...
    2002-01-21  0.19523  0.20970  0.19162  0.20608   771149  ...

I&#39;ve tried a few methods - eg for each data frame replace the columns with a multi-index like `.from_product([&#39;ABC&#39;, columns])` and then concatenate along `axis=1`, without success.

Concatenate Pandas columns under new multi-index level

I have the following pandas dataframe:

    token	 year	 uses  books
      386	xanthos	 1830	 3     3
      387	xanthos	 1840	 1     1
      388	xanthos	 1840	 2     2
      389	xanthos	 1868	 2     2
      390	xanthos	 1875	 1     1

I aggregate the rows with duplicate `token` and `years` like so:

    dfalph = dfalph[[&#39;token&#39;,&#39;year&#39;,&#39;uses&#39;,&#39;books&#39;]].groupby([&#39;token&#39;, &#39;year&#39;]).agg([np.sum])
    dfalph.columns = dfalph.columns.droplevel(1)

                   uses  books
    token    year		
    xanthos  1830	 3     3
             1840	 3     3
             1867	 2     2
             1868	 2     2
             1875	 1     1

Instead of having the &#39;token&#39; and &#39;year&#39; fields in the index, I would like to return them to columns and have an integer index. 

How to move pandas data from index to column after multiple groupby

I have a dataframe with Multiindex and would like to modify one particular level of the Multiindex. For instance, the first level might be strings and I may want to remove the white spaces from that index level:

    df.index.levels[1] = [x.replace(&#39; &#39;, &#39;&#39;) for x in df.index.levels[1]]

However, the code above results in an error:

    TypeError: &#39;FrozenList&#39; does not support mutable operations.

I know I can reset_index and modify the column and then re-create the Multiindex, but I wonder whether there is a more elegant way to modify one particular level of the Multiindex directly.


Pandas: Modify a particular level of Multiindex

I would like to run a pivot on a pandas `DataFrame`, with the index being two columns, not one. For example, one field for the year, one for the month, an &#39;item&#39; field which shows &#39;item 1&#39; and &#39;item 2&#39; and a &#39;value&#39; field with numerical values. I want the index to be year + month.

The only way I managed to get this to work was to combine the two fields into one, then separate them again. is there a better way?

Minimal code copied below. Thanks a lot!

PS Yes, I am aware there are other questions with the keywords &#39;pivot&#39; and &#39;multi-index&#39;, but I did not understand if/how they can help me with this question.

    import pandas as pd
    import numpy as np
    
    df= pd.DataFrame()
    month = np.arange(1, 13)
    values1 = np.random.randint(0, 100, 12)
    values2 = np.random.randint(200, 300, 12)
    
    
    df[&#39;month&#39;] = np.hstack((month, month))
    df[&#39;year&#39;] = 2004
    df[&#39;value&#39;] = np.hstack((values1, values2))
    df[&#39;item&#39;] = np.hstack((np.repeat(&#39;item 1&#39;, 12), np.repeat(&#39;item 2&#39;, 12)))
    
    # This doesn&#39;t work: 
    # ValueError: Wrong number of items passed 24, placement implies 2
    # mypiv = df.pivot([&#39;year&#39;, &#39;month&#39;], &#39;item&#39;, &#39;value&#39;)
    
    # This doesn&#39;t work, either:
    # df.set_index([&#39;year&#39;, &#39;month&#39;], inplace=True)
    # ValueError: cannot label index with a null key
    # mypiv = df.pivot(columns=&#39;item&#39;, values=&#39;value&#39;)
    
    # This below works but is not ideal: 
    # I have to first concatenate then separate the fields I need
    df[&#39;new field&#39;] = df[&#39;year&#39;] * 100 + df[&#39;month&#39;]
    
    mypiv = df.pivot(&#39;new field&#39;, &#39;item&#39;, &#39;value&#39;).reset_index()
    mypiv[&#39;year&#39;] = mypiv[&#39;new field&#39;].apply( lambda x: int(x) / 100)  
    mypiv[&#39;month&#39;] = mypiv[&#39;new field&#39;] % 100

pandas: how to run a pivot with a multi-index?

Does anyone know if it is possible to use the `DataFrame.loc` method to select from a `MultiIndex`? I have the following `DataFrame` and would like to be able to access the values located in the `Dwell` columns, at the indices of `(&#39;at&#39;, 1)`, `(&#39;at&#39;, 3)`, `(&#39;at&#39;, 5)`, and so on (non-sequential).

I&#39;d love to be able to do something like `data.loc[[&#39;at&#39;,[1,3,5]], &#39;Dwell&#39;]`, similar to the `data.loc[[1,3,5], &#39;Dwell&#39;]` syntax for a regular index (which returns a 3-member series of `Dwell` values).

My purpose is to select an arbitrary subset of the data, perform some analysis only on that subset, and then update the new values with the results of the analysis. I plan on using the same syntax to set new values for these data, so chaining selectors wouldn&#39;t really work in this case.

Here is a slice of the `DataFrame` I&#39;m working with: 

             Char    Dwell  Flight  ND_Offset  Offset
    QGram                                                           
    at    0     a      100     120   0.000000       0  
          1     t      180       0   0.108363       5  
          2     a      100     120   0.000000       0 
          3     t      180       0   0.108363       5 
          4     a       20     180   0.000000       0  
          5     t       80     120   0.108363       5
          6     a       20     180   0.000000       0   
          7     t       80     120   0.108363       5  
          8     a       20     180   0.000000       0  
          9     t       80     120   0.108363       5   
          10    a      120     180   0.000000       0  

All Multi Index Solutions on Gang of Coders

Total of 19 Multi Index Solutions