Pandas: move row (index and values) from last to first

Question

I would like to move an entire row (index and values) from the last row to the first row of a DataFrame. Every other example I can find either uses an ordered row index (to be specific - my row index is not a numerical sequence - so I cannot simply add at -1 and then reindex with +1) or moves the values while maintaining the original index. My DF has descriptions as the index and the values are discrete to the index description.

I'm adding a row and then would like to move that into row 1. Here is the setup:

df = pd.DataFrame({
    'col1' : ['A', 'A', 'B', 'F', 'D', 'C'],
    'col2' : [2, 1, 9, 8, 7, 4],
    'col3': [0, 1, 9, 4, 2, 3],
}).set_index('col1')

#output
In [7]: df
Out[7]: 
      col2  col3
col1            
A        2     0
A        1     1
B        9     9
F        8     4
D        7     2
C        4     3

I then add a new row as follows:

df.loc["Deferred Description"] = pd.Series([''])

In [9]: df
Out[9]: 
                      col2  col3
col1                            
A                      2.0   0.0
A                      1.0   1.0
B                      9.0   9.0
F                      8.0   4.0
D                      7.0   2.0
C                      4.0   3.0
Deferred Description   NaN   NaN

I would like the resulting output to be:

In [9]: df
Out[9]: 
                      col2  col3
col1                            
Defenses Description   NaN   NaN
A                      2.0   0.0
A                      1.0   1.0
B                      9.0   9.0
F                      8.0   4.0
D                      7.0   2.0
C                      4.0   3.0

I've tried using df.shift() but only the values shift. I've also tried df.sort_index() but that requires the index to be ordered (there are several SO examples using df.loc[-1] = ... then then reindexing with df.index = df.index + 1). In my case I need the Defenses Description to be the first row.

It is - I missed the accepted answer and was focused on the second answer. thanks. — Bill Armstrong, Jun 25 '18 at 02:50

cs95 · Accepted Answer · 2018-06-25T03:02:10.593

Your problem is not one of cyclic shifting, but a simpler one—one of insertion (which is why I've chosen to mark this question as duplicate).

Construct an empty DataFrame and then concatenate the two using pd.concat.

pd.concat([pd.DataFrame(columns=df.columns, index=['Deferred Description']), df])

                     col2 col3
Deferred Description  NaN  NaN
A                       2    0
A                       1    1
B                       9    9
F                       8    4
D                       7    2
C                       4    3

If this were columns, it'd have been easier. Funnily enough, pandas has a DataFrame.insert function that works for columns, but not rows.

Generalized Cyclic Shifting
If you were curious to know how you'd cyclically shift a dataFrame, you can use np.roll.

# apply this fix to your existing DataFrame
pd.DataFrame(np.roll(df.values, 1, axis=0), 
     index=np.roll(df.index, 1), columns=df.columns
)

                     col2 col3
Deferred Description  NaN  NaN
A                       2    0
A                       1    1
B                       9    9
F                       8    4
D                       7    2
C                       4    3

This, thankfully, also works when you have duplicate index values. If the index or columns aren't important, then pd.DataFrame(np.roll(df.values, 1, axis=0)) works well enough.

score 1 · Answer 2 · answered Jun 25 '18 at 02:42

You can using append

pd.DataFrame({'col2':[np.nan],'col3':[np.nan]},index=["Deferred Description"]).append(df)
Out[294]: 
                      col2  col3
Deferred Description   NaN   NaN
A                      2.0   0.0
A                      1.0   1.0
B                      9.0   9.0
F                      8.0   4.0
D                      7.0   2.0
C                      4.0   3.0

Pandas: move row (index and values) from last to first

2 Answers2