How can I insert into a specific location of a MultiIndex DataFrame?

user_1613983 0
Viewed 1849

Suppose I have a pandas DataFrame that looks similar to the following in structure. However in practice it might be much larger and the number of level 1 indexes, as well as the number of level 2 index (per level 1 index) will vary, so the solution shouldn't make assumptions about this:

index = pandas.MultiIndex.from_tuples([
    ("a", "s"),
    ("a", "u"),
    ("a", "v"),
    ("b", "s"),
    ("b", "u")])

result = pandas.DataFrame([
    [1, 2],
    [3, 4],
    [5, 6],
    [7, 8],
    [9, 10]], index=index, columns=["x", "y"])

Which looks like this:

      x   y
a s   1   2
  u   3   4
  v   5   6
b s   7   8
  u   9  10

Now let's say I want to create a "total" row for each of the "a" and "b" levels. So given the above as input I would want my code to produce something like this:

      x   y
a s   1   2
  u   3   4
  v   5   6
  t   9  12
b s   7   8
  u   9  10
b t  16  18

Here's the code I have so far:

# Calculate totals
for level, _ in result.groupby(level=0):

    # work out the global total for that desk:
    x_sum = result.loc[level]["x"].sum()
    y_sum = result.loc[level]["y"].sum()

    result = result.append(pandas.DataFrame([[x_sum, y_sum]], columns=result.columns, index=pandas.MultiIndex.from_tuples([(level, "t")])))

But this results in the "total" columns being appended to the end:

      x   y
a s   1   2
  u   3   4
  v   5   6
b s   7   8
  u   9  10
a t   9  12
b t  16  18

Sorting using result.sort_index() doesn't do what I want either:

      x   y
a s   1   2
  t   9  12
  u   3   4
  v   5   6
b s   7   8
  t  16  18
  u   9  10

What am I doing wrong?

pandas python
3 Answers

A better solution would be to convert the the level to categorical type so that the MultiIndex will be is_monotonic_increasing. This preserves order and the performance of MultiIndex will be better since its sorted.

Input:

      x   y
a s   1   2
  u   3   4
  v   5   6
b s   7   8
  u   9  10
a t   9  12
b t  16  18

Convert level to categorical to preserve order.

result.index = result.index.set_levels(pd.CategoricalIndex(result.index.levels[1], categories=['s', 'u', 'v', 't'], ordered=True), level=1)
result.sort_index()

Output:

      x   y
a s   1   2
  u   3   4
  v   5   6
  t   9  12
b s   7   8
  u   9  10
  t  16  18
user_105454260
Related