7

I have read data from csv file into a data frame consisting of more than 25000 rows and 15 columns and I need to move all rows (including the left-most -> index) one column to the right, so that I get an empty index and be able to fill it with integers. Names of columns, however, should stay at the same place. So, basically I need to move everything except column names one place to the right.

enter image description here

I tried to reindex it, but got an error: 

ValueError: cannot reindex from a duplicate axis

Is there any way to do this?

puk789
  • 322
  • 2
  • 8
  • 28

4 Answers4

11

In pandas you can only create a column to the right, unless you do join between two dataframe. Then you can re-arrange however you like.

import pandas as pd

df = pd.read_csv('data.csv', header=None, names = ['A','B','C'])

print(df)

    A   B   C
0   1   2   3
1   4   5   6
2   7   8   9
3  10  11  12

df['D'] = pd.np.nan # this creates an empty series
                    # and appends to the right

print(df)

    A   B   C   D
0   1   2   3 NaN
1   4   5   6 NaN
2   7   8   9 NaN
3  10  11  12 NaN

df = df[['D','A','B','C']] # rearrange as you like

print(df)

    D   A   B   C
0 NaN   1   2   3
1 NaN   4   5   6
2 NaN   7   8   9
3 NaN  10  11  12
Leb
  • 15,483
  • 10
  • 56
  • 75
  • `df['D'] = pd.np.nan` would suffice, instead of `df['D'] = pd.Series(data=None,index=df.index)`? – Zero Oct 19 '15 at 14:58
  • @puk789 it seems like you're reading your data incorrectly, can you post a sample of raw data from that csv file? Just a few rows. – Leb Oct 19 '15 at 17:34
7

I would first add a new column with:

df['new'] = df.index

than take the names of the columns of your dataframe in a list with:

colnames = df.columns.tolist()

Then you can rearrange them as you need, for instance changing the order so you get the last 'new' column as first and move the remains one position to the right:

colnames = colnames[-1:] + colnames[:-1]

and reassign:

df = df[colnames]
Fabio Lamanna
  • 20,504
  • 24
  • 90
  • 122
0
df = YourDataFrame
col = "Your Column You Want To Move To The Start Of YourDataFrame" 
df = pd.concat([df[col],df.drop(col,axis=1)], axis=1)
Snowde
  • 91
  • 1
  • 1
0

You could use .shift() method in order to roll dataframe values both column/row-wise by an integer value.

Not exactly your case but you could find a few usage cases here for the method : Shift column in pandas dataframe up by one?

I observed that

df.reset_index().shift(1,axis=1)

would drop the values in the index column and turn them into NaN.

A workaround would be:

df = df.reset_index()
values = df.iloc[:,0].values
df = df.shift(1,axis=1)
df.iloc[:,1] = values

It's quite a bit of code, but I think would fit the purpose.

Edit: We could avoid creating the variable 'values' and make it in two lines with:

new_df = df.reset_index().shift(1,axis=1)
new_df.iloc[:,1] = df.reset_index().values[:,0]
Pedro Bronze
  • 11
  • 1
  • 4