Given this MultiIndex Dataframe:
arrays = [np.array(['A', 'A', 'B', 'B', 'C', 'C']),
np.array(['one', 'two', 'one', 'two', 'one', 'two'])]
df = pd.DataFrame(np.random.randn(6), index=arrays, columns=['col1'])
I would like to add a new row (inner index) to every row in the outer index.
df.loc[(slice(None),'three'),:] = {'A':3, 'B':4, 'C':5}
However this gives me an error: KeyError: 'three'
How can I accomplish this?
EDIT: All values in the row are not the same.
MultiIndex.from_product + reindexa, b = df.index.levels
res = df.reindex(pd.MultiIndex.from_product([a, [*b, 'three']]))
res[res.index.get_level_values(1) == 'three'] = 3
col1
A one -1.011201
two 0.376914
three 3.000000
B one 0.465666
two -0.634804
three 3.000000
C one -0.348338
two 1.295683
three 3.000000
An update to this answer to account for your desire to add specific values. Replace the last line with this code snippet:
d = {'A':3, 'B':4, 'C':5}
s = res.index.get_level_values(0).map(d)
res.col1.where(res.col1.notnull(), s.values)
A one -2.542087
two 0.966193
three 3.000000
B one -0.126671
two 0.864258
three 4.000000
C one 0.063544
two -0.401936
three 5.000000
Name: col1, dtype: float64
Possibly verbose, but you can construct a new dataframe, concatenate, then sort by index:
idx = pd.MultiIndex.from_tuples([(i, 'three') for i in df.index.levels[0]])
df_new = pd.DataFrame(3, index=idx, columns=df.columns)
df = pd.concat([df, df_new]).sort_index()
print(df)
col1
A one -0.810362
three 3.000000
two 0.014020
B one 0.700392
three 3.000000
two 0.189968
C one -1.214194
three 3.000000
two 1.199316
Using concat
s=pd.Series({'A':3, 'B':4, 'C':5}).to_frame('col1').assign(index='three')
pd.concat([df,s.set_index('index',append=True)]).sort_index(level=0)
Out[205]:
col1
A one 0.529647
three 3.000000
two -1.763707
B one -0.673773
three 4.000000
two -0.706385
C one 1.105963
three 5.000000
two 1.291009
