I have a multi-index data frame with rows "bar" and "baz" and each of this rows has a row "one" and "two". I now want to add a row "three" to each row "bar" and foo".
Is there an elegant way to do so?
For example:
import pandas as pd
import numpy as np
arrays = [["bar", "bar", "baz", "baz"],
["one", "two", "one", "two"]]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=["first", "second"])
df = pd.DataFrame(np.random.randn(3, 4), index=["A", "B", "C"], columns=index)
In [38]: df
Out[38]:
first bar baz
second one two one two
A 0.357392 -1.880279 0.099014 1.354570
B 0.474572 0.442074 -1.173530 -1.362059
C -0.980140 -0.173440 -1.490654 -0.539123
and i want something like this:
first bar baz
second one two three one two three
A -0.096890 0.012150 nan -0.749569 -0.965033 nan
B -0.854206 0.118473 nan 0.263058 -0.025849 nan
C -0.688007 -0.258569 nan 0.127305 -0.955044 nan
I don't know how Python-like it is, but there are two ways to do this: simple substitution and using inserts.
df[('bar','three')] = np.NaN
df[('baz','three')] = np.NaN
df.insert(2,('bar','three'),np.NaN)
df.insert(5,('baz','three'),np.NaN)
first bar baz
second one two three one two three
A -0.973338 -0.233507 NaN 0.777288 -2.282688 NaN
B -0.377486 0.080627 NaN 0.401302 0.355696 NaN
C 0.481056 0.651335 NaN 0.161145 1.001937 NaN