17149

copying a single-index DataFrame into a MultiIndex DataFrame

Edit: found my answer here: Building a hierarchically indexed DataFrame from existing DataFrames

Turns out I need to create a matching MultiIndex with the higher levels fixed

Original:

I confess, I don't understand the merges and joins yet, but I'm not sure they're what I want.

I have a DataFrame that has a single index, and a DataFrame that has a MultiIndex, the last level of which is the same as the single-index DataFrame.

I am trying to copy/graft the contents in:

In [1]: import pandas as pd In [2]: import numpy as np In [3]: import itertools In [4]: In [4]: inner = ('a','b') In [5]: outer = ((10,20), (1,2)) In [6]: cols = ('one','two','three','four') In [7]: In [7]: sngl = pd.DataFrame(np.random.randn(2,4), index=inner, columns=cols) In [8]: In [8]: index_tups = list(itertools.product(*(outer + (inner,)))) In [9]: index_mult = pd.MultiIndex.from_tuples(index_tups) In [10]: mult = pd.DataFrame(index=index_mult, columns=cols) In [11]: In [11]: sngl Out[11]: one two three four a 2.946876 -0.751171 2.306766 0.323146 b 0.192558 0.928031 1.230475 -0.256739 In [12]: mult Out[12]: one two three four 10 1 a NaN NaN NaN NaN b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN 20 1 a NaN NaN NaN NaN b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN In [13]: In [13]: mult.ix[(10,1)] = sngl In [14]: In [14]: mult Out[14]: one two three four 10 1 a NaN NaN NaN NaN b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN 20 1 a NaN NaN NaN NaN b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN In [15]:

What am I doing wrong?

Edit: it works when I do index by index, but that's not the pandas way, surely:

In [15]: mult.ix[(10,1,'a')] = sngl.ix['a'] In [16]: mult Out[16]: one two three four 10 1 a 2.946876 -0.7511706 2.306766 0.3231457 b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN 20 1 a NaN NaN NaN NaN b NaN NaN NaN NaN 2 a NaN NaN NaN NaN b NaN NaN NaN NaN

Answer1:

.ix and .loc are equivalent in this example (just more explicit)

In [48]: nm = mult.reset_index().set_index('level_2') In [49]: nm Out[49]: level_0 level_1 one two three four level_2 a 10 1 NaN NaN NaN NaN b 10 1 NaN NaN NaN NaN a 10 2 NaN NaN NaN NaN b 10 2 NaN NaN NaN NaN a 20 1 NaN NaN NaN NaN b 20 1 NaN NaN NaN NaN a 20 2 NaN NaN NaN NaN b 20 2 NaN NaN NaN NaN

This should probably work with a series on the rhs; this might be a buglet

In [50]: nm.loc['a',sngl.columns] = sngl.loc['a'].values In [51]: nm Out[51]: level_0 level_1 one two three four level_2 a 10 1 0.3738456 -0.2261926 -1.205177 0.08448757 b 10 1 NaN NaN NaN NaN a 10 2 0.3738456 -0.2261926 -1.205177 0.08448757 b 10 2 NaN NaN NaN NaN a 20 1 0.3738456 -0.2261926 -1.205177 0.08448757 b 20 1 NaN NaN NaN NaN a 20 2 0.3738456 -0.2261926 -1.205177 0.08448757 b 20 2 NaN NaN NaN NaN In [52]: nm.reset_index().set_index(['level_0','level_1','level_2']) Out[52]: one two three four level_0 level_1 level_2 10 1 a 0.3738456 -0.2261926 -1.205177 0.08448757 b NaN NaN NaN NaN 2 a 0.3738456 -0.2261926 -1.205177 0.08448757 b NaN NaN NaN NaN 20 1 a 0.3738456 -0.2261926 -1.205177 0.08448757 b NaN NaN NaN NaN 2 a 0.3738456 -0.2261926 -1.205177 0.08448757 b NaN NaN NaN NaN

Recommend

  • Extract Specific RANGE of columns in numpy array Python
  • correlation matrix in python
  • Numpy extract submatrix
  • How to count spring coil turns?
  • Get Distinct rows from a result of JOIN in SQL Server
  • Remove string quotes from array in Python
  • Opencv TypeError: points is not a numpy array, neither a scalar
  • Issue clearing the Tkinter Canvas widget using also matplotlib
  • How to render two pd.DataFrames in jupyter notebook side by side?
  • Select from multiple slices in Numpy
  • How do I conditionally select a field from one of two tables?
  • how to split and categorize value in a column of a pandas dataframe
  • Vectorizing the reshaping and cropping of images using PIL
  • In scipy why doesn't idct(dct(a)) equal to a?
  • Referring to individual variables in … with dplyr quos
  • Subclassing a Pandas DataFrame, updates?
  • Intel c/c++ compiler: “could not locate executable icc” (and ecc)
  • Parsing Data From Long to Wide Format in Python
  • Reshape array on xAxis and fill with mean value in Python?
  • Refresh other frame, from another frame. Jquery
  • Pandas groupby to to_csv
  • Get the last date of each month in a list of dates in Python
  • How to filter on year and quarter in pandas
  • Python function to read variable length blocks of data from file while open
  • Color time-series based on column values in pandas
  • Ruby 1.8.6 Array#uniq not removing duplicate hashes
  • Converting query results into DataFrame in python
  • Configure Spring's MappingJacksonHttpMessageConverter
  • vectorized indexing/slicing in numpy/scipy?
  • Wrong labels when plotting a time series pandas dataframe with matplotlib
  • Basic many-to-many left join query
  • R convert summary result (statistics with all dataframe columns) into dataframe
  • AppleScript : find open tab in safari by name and open it
  • Error when parsing timestamp with pandas read_csv
  • Matplotlib draw Spline from multiple points
  • Return words with double consecutive letters
  • VB.net deserialize, JSON Conversion from type 'Dictionary(Of String,Object)' to type '
  • Python: how to group similar lists together in a list of lists?
  • Busy indicator not showing up in wpf window [duplicate]
  • Sorting a 2D array using the second column C++