Pythus Pythus - 2 months ago 35
Python Question

how to add a row to a Datetime Multiindex Panda Dataframe


How would I go about adding a row to the top of this dataframe. This
is downloaded data. I cannot use a specific row index in the formula
because the first Datetime indice changes all the time. I cannot also use a
specific label for the inner index as it could be Datetime. Is there a
way to generalize this?

I tried this


df[df.index.min()[0])) - dt.timedelta(minutes=5), :] = [list to add]



however it only add a row at the end of the Dataframe. Sorting,


df.sort_index(inplace=True)



did not help because I guess I am dealing with 2 levels of index here;
which would explain why the row sticks to the bottom of the frame.


A B C D E

2006-04-28 00:00:00
A 69.62 69.62 6.518 65.09 69.62
B
C
2006-05-01 00:00:00
A 71.5 71.5 6.522 65.16 71.5
B
C
2006-05-02 00:00:00
A 72.34 72.34 6.669 66.55 72.34
B
C


The final result should look like this:
X
's being the elements in the list/array to be added.

A B C D E
NEWROW X1 X2 X3 X4 X5

2006-04-28 00:00:00
A 69.62 69.62 6.518 65.09 69.62
B
C
2006-05-01 00:00:00
A 71.5 71.5 6.522 65.16 71.5
B
C
2006-05-02 00:00:00
A 72.34 72.34 6.669 66.55 72.34
B
C

Answer

sort_index and sortlevel for me does not work with Multiindex:

So you can use little hack - first reset_index with second level Stats, then sort_index and last set_index back with parameter append=True:

df1 = df1.sort_index()

df1.loc[((df1.index.min()[0]) -  dt.timedelta(minutes=5), 'SUM'),:] = 
df1.loc[(slice(None), slice('price')),:].sum()

df1 = df1.reset_index('Stats')
df1 = df1.sort_index(axis=0).set_index('Stats', append=True)
print (df1)
                                 A       B       C       D       E
Date                Stats                                         
2006-04-27 23:55:00 SUM     213.46  213.46  19.709  196.80  213.46
2006-04-28 00:00:00 price    69.62   69.62   6.518   65.09   69.62
                    std        NaN     NaN     NaN     NaN     NaN
                    weight     NaN     NaN     NaN     NaN     NaN
2006-05-01 00:00:00 price    71.50   71.50   6.522   65.16   71.50
                    std        NaN     NaN     NaN     NaN     NaN
                    weight     NaN     NaN     NaN     NaN     NaN
2006-05-02 00:00:00 price    72.34   72.34   6.669   66.55   72.34
                    std        NaN     NaN     NaN     NaN     NaN
                    weight     NaN     NaN     NaN     NaN     NaN