1

I have a dataframe like below:

import pandas as pd
import numpy as np
np.random.seed(22)
df = pd.DataFrame.from_dict({'a': np.random.rand(200), 'b': np.random.rand(200), 'x': np.tile(np.concatenate([np.repeat('F', 5), np.repeat('G', 5)]), 20)})
df.index = pd.MultiIndex.from_product([[1, 2], list(range(0, 10)), [1, 2, 3, 4, 5, 1, 2, 3, 4, 5]])
df.index.names = ['g_id', 'm_id', 'object_id']

I'd like to shift the values for the entire groups defined by ['g_id', 'm_id'] so that for example:

  • the value of ['a', 'b'] columns in index (1, 1, 1) of the new data frame would be the value from index (1, 0, 1) of the original dataframe, i.e. [0.208461, 0.980866]
  • the value of ['a', 'b'] columns in index (2, 4, 3) of the new data frame would be the value from index (2, 3, 3) of the original data frame, i.e. [0.651138, 0.559126].

The operation is similar to the one covered in this topic. However, I need to do this with multiple columns and I had no luck trying to generalise the provided solution.

jakes
  • 1,964
  • 3
  • 18
  • 50
  • What is supposed to appear in the first row of the new dataframe? – dkritz Apr 14 '20 at 20:00
  • 0 at best, but if it will be `NaN` then I suppose I could easily fill it with `.fillna`. Actually, the value would be missing for not only first row, but all ten referring to `m_id == 0` – jakes Apr 14 '20 at 20:03
  • this might sound lazy, but could you convert the columns you want to shift to a dict, pop out the rows you want to move up or down by, use the result to recreate a df, reset the index, and use concatenate with the original datafame? – dkritz Apr 14 '20 at 20:32

0 Answers0