CODEWITHSUNDEEP
pythonpandasdataframeconcatenation
Boosted_d16
Boosted_d16

Reputation: 14062

Move given row to end of DataFrame

I would like to take a given row from a DataFrame and prepend or append to the same DataFrame.

My code below does just that, but I'm not sure if I'm doing it the right way or if there is an easier, better, faster way?

testdf = df.copy()
#get row 
target_row = testdf.ix[[2],:]
#del row from df
testdf.drop([testdf.index[2]], axis=0, inplace=True)
#concat original row to end or start of df
newdf = pd.concat([testdf, target_row], axis=0)

Thanks

Upvotes: 9

Views: 19215

Answers (6)

Sergo055
Sergo055

Reputation: 144

In case you need to move a row by value (ex. you know the name of the index, but dont know its position). Then, you can use the following (assuming the value of the index is 2):

df.reindex([index for index in df.index if index != 2] + [2], axis=0)

The logic is the following:

1. [index for index in df.index if index != 2] # create a list of all indexes except for the one you want to move to the end
2. + [2] # append the index you want to move to the end
3. reindex across the index (axis=0)

Advantages of this approach:

  1. Can be used with any number of indexes you would like to move
  2. Can be easily modified to move the desired index to the front rather than the back
  3. No hardcoding: we move the index by value, not by its position
  4. List comprehension provides good performance

Upvotes: 0

DeepKling
DeepKling

Reputation: 329

Similar to what YH Wu wrote, you can do it in one line if you know the index (or indices). However, ix is deprecated, so use loc instead:

import pandas as pd
import numpy as np

df = pd.DataFrame({'a':np.arange(5)})

#    a
# 0  0
# 1  1
# 2  2
# 3  3
# 4  4

# move the line with index 2 to the end:
df2 = df.drop(2).append(df.loc[2])

#    a
# 0  0
# 1  1
# 3  3
# 4  4
# 2  2

# several indices, moves 3 and 2 to the end in that order:
to_move = [3, 2]
df2 = df.drop(to_move).append(df.loc[to_move])

#    a
# 0  0
# 1  1
# 4  4
# 3  3
# 2  2

.drop removes the lines with the index (or indices) you give as argument. With df.loc[x] you select the rows with the index (or indices) x. If you write df = df.drop... , you directly apply the changes to the original DataFrame. If you want to reset the index, you can do a ".reset_index(drop=True)" (drop=True if you do not want to keep the original index as a new column).

Upvotes: 0

EdChum
EdChum

Reputation: 394003

Rather than concat I would just assign directly to the df after shifting, then use iloc to reference the position you want to assign the row, you have to call squeeze so that you assign just the values and lose the original index value otherwise it'll raise a ValueError:

In [210]:
df = pd.DataFrame({'a':np.arange(5)})
df

Out[210]:
   a
0  0
1  1
2  2
3  3
4  4

In [206]:
target_row = df.ix[[2],:]
target_row

Out[206]:
   a
2  2

In [211]:
df = df.shift()
df.iloc[0] = target_row.squeeze()
df

Out[211]:
   a
0  2
1  0
2  1
3  2
4  3

EDIT

To insert at the end:

In [255]:
df = pd.DataFrame({'a':np.arange(5)})
target_row = df.ix[[2],:]
df = df.shift(-1)
df.iloc[-1] = target_row.squeeze()
df

Out[255]:
   a
0  1
1  2
2  3
3  4
4  2

Another update

Thanks to @AsheKetchum for pointing out that my earlier answer is incorrect, now looking at this 3 years later I realise you could just reindex the orig df:

If we take a copy of the index as a list:

In[24]:
idx = df.index.tolist()
idx

Out[24]: [0, 1, 2, 3, 4]

then we can pop the index of interest from this list:

In[25]:
idx.pop(2)
idx

Out[25]: [0, 1, 3, 4]

Now we can reindex by prepending to this list:

In[26]:
df.reindex([2] + idx)

Out[26]: 
   a
2  2
0  0
1  1
3  3
4  4

Or appending:

In[27]:    
df.reindex(idx+[2])

Out[27]: 
   a
0  0
1  1
3  3
4  4
2  2

Upvotes: 10

YH Wu
YH Wu

Reputation: 465

I'd just drop a row(s) and append at the end.

df = pd.DataFrame({'a':np.arange(5)})
df.drop(2).append(df.ix[2]).reset_index(drop=True) # move 3rd row
df.drop(df.head(2).index).append(df.head(2)).reset_index() # move first 2 rows

Upvotes: -1

Alexander
Alexander

Reputation: 109536

To improve performance, you may want to consider keeping a running list of all rows you want to move to the end of the DataFrame, and then move them all at once in a single pd.concat operation.

df = pd.DataFrame(np.random.rand(5, 3), columns=list('ABC'))
target_rows = [1, 3, 4]

a = df.iloc[[i for i in df.index if i not in target_rows], :]
b = df.iloc[target_rows, :]
>>> pd.concat([a, b])
          A         B         C
0  0.818722  0.174153  0.522383
2  0.581577  0.840306  0.985089
1  0.645752  0.238476  0.670922
3  0.198271  0.501911  0.954477
4  0.965488  0.735559  0.701077

Upvotes: 4

chrisaycock
chrisaycock

Reputation: 37930

I can reduce it to a one-liner:

pd.concat([df.ix[0:1], df.ix[3:], df.ix[[2]]])

I don't see any performance difference between your code and mine though. Presumably the copying is the biggest culprit.

Upvotes: 3

Related Questions