Removing index column in pandas when reading a csv

Question

I have the following code which imports a CSV file. There are 3 columns and I want to set the first two of them to variables. When I set the second column to the variable "efficiency" the index column is also tacked on. How can I get rid of the index column?

df = pd.DataFrame.from_csv('Efficiency_Data.csv', header=0, parse_dates=False)
energy = df.index
efficiency = df.Efficiency
print efficiency

I tried using

del df['index']

after I set

energy = df.index

which I found in another post but that results in "KeyError: 'index' "

score 375 · Answer 1 · edited Sep 18 '21 at 15:06

375

When writing to and reading from a CSV file include the argument index=False and index_col=False, respectively. Follows an example:

To write:

 df.to_csv(filename, index=False)

and to read from the csv

df.read_csv(filename, index_col=False)

This should prevent the issue so you don't need to fix it later.

edited Sep 18 '21 at 15:06

Community

1
1

answered Apr 12 '16 at 11:31

Steve

4,388
3
17
25

12

Thanks a lot.This is exactly what is the question is looking for. – Ravindra S Jun 03 '17 at 07:47
2

"header = False" works for removing headers in the same way – J.D Oct 11 '17 at 09:21
how about when writing into json ?? – Pyd Oct 30 '17 at 09:39
54

should be `index_col=False`. – Vedda Apr 05 '18 at 04:13
5

Using `df.to_sql("table",cursor,if_exists="append",index=False)` also fixes the sqlite error `sqlite3.OperationalError: table message has no column named index` – cacti5 Jun 03 '18 at 00:55
3

@vedda it seems to be `index=False` for `to_excel()` and `index_col=False` with `read_csv()` in pandas 0.23.4. :-/ – matt wilkie Oct 11 '18 at 20:28
It should also be ```index_col=False``` for ```df.read_excel```. – Peter Lustig Nov 21 '19 at 21:11
1

@Vedda I am getting: TypeError: to_csv() got an unexpected keyword argument 'index_col' Any help? – Satyajit Das May 29 '20 at 23:30

score 127 · Answer 2 · edited Oct 03 '18 at 23:09

127

df.reset_index(drop=True, inplace=True)

edited Oct 03 '18 at 23:09

Asclepius

57,944
17
167
143

answered Mar 06 '18 at 10:57

Subhojit Mukherjee

1,355
1
9
2

3

This is actually my favorite solution, but not a very elaborate answer. The manual reads this about the argument `drop`: "Do not try to insert index into dataframe columns. This resets the index to the default integer index." https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html – tommy.carstensen Aug 31 '18 at 18:59
@tommy.carstensen Then how would you avoid getting the integers on the index as a replacement of the previous index? I think it is a misunderstanding of the text of your link. The question here *is to drop the index*. And this is reached here. You get the default integers, since there is no dateframe without an index, but you have dropped the previous index. That is why this answer should be the accepted answer, also because it uses the memory efficient `inplace=True`. – questionto42 Jul 31 '20 at 13:10

score 89 · Accepted Answer · edited May 29 '19 at 07:58

89

DataFrames and Series always have an index. Although it displays alongside the column(s), it is not a column, which is why del df['index'] did not work.

If you want to replace the index with simple sequential numbers, use df.reset_index().

To get a sense for why the index is there and how it is used, see e.g. 10 minutes to Pandas.

edited May 29 '19 at 07:58

Jean-François Corbett

37,420
30
139
188

answered Nov 20 '13 at 21:53

Dan Allan

34,073
6
70
63

1

Thanks! I decided to just import it a different way not using pandas. I have to perform some arithmetic on each of the columns and python wasn't liking have the index column attached. Pandas is certainly the easiest way to import data but not always the best I found out. – Bogdan Janiszewski Nov 21 '13 at 17:15
2

Did you try using Pandas to do the arithmetic? – Jamie Bull Sep 18 '14 at 14:38
2

can one remove the index name? – Quant Sep 26 '14 at 20:55
4

Yes, `index.name = None`. – Dan Allan Sep 26 '14 at 21:05
1

@BogdanJaniszewski, if you didn't use pandas, then why did you accept this as the answer? – multigoodverse Jan 29 '15 at 08:21
2

Yes, clearly the next answer should be the accepted one. – deadcode Jan 13 '18 at 18:03

Natheer Alabsi · Answer 4 · 2017-11-16T01:32:03.280

21

You can set one of the columns as an index in case it is an "id" for example. In this case the index column will be replaced by one of the columns you have chosen.

df.set_index('id', inplace=True)

edited Nov 16 '17 at 01:32

answered Dec 12 '16 at 04:18

Natheer Alabsi

2,790
4
19
28

Hmm, this didn't work for me. I got "None" as a console printout. – Azurespot Jul 15 '21 at 18:13

Bhanu Pratap Singh · Answer 5 · 2016-01-21T12:02:35.017

6

If your problem is same as mine where you just want to reset the column headers from 0 to column size. Do

df = pd.DataFrame(df.values);

EDIT:

Not a good idea if you have heterogenous data types. Better just use

df.columns = range(len(df.columns))

edited Jan 21 '16 at 12:02

answered Jan 21 '16 at 11:21

Bhanu Pratap Singh

1,017
1
12
15

score 3 · Answer 6 · answered Nov 20 '13 at 21:47

3

you can specify which column is an index in your csv file by using index_col parameter of from_csv function if this doesn't solve you problem please provide example of your data

answered Nov 20 '13 at 21:47

yemu

26,249
10
32
29

score 3 · Answer 7 · answered Sep 14 '18 at 14:02

3

One thing that i do is df=df.reset_index() then df=df.drop(['index'],axis=1)

answered Sep 14 '18 at 14:02

Lord Varis

57
1

Error: "labels ['index'] not contained in axis" – Vasin Yuriy Nov 11 '19 at 12:25
@VasinYuriy this is meant like `df.reset_index().drop(columns=['yourfirstindex', 'yoursecondindex'])`, it works with 'index' only in the standard case that the index does not have a name and then becomes a column called 'index' with `df.reset_index().drop(columns=['index'])`. The added parameter `axis=1` is the default. This method is not recommended, @SubhojitMukherjee's `reset_index(inplace=True)` works "inplace" and thus saves memory. – questionto42 Jul 31 '20 at 12:59

Ali Taheri · Answer 8 · 2021-08-07T03:41:46.893

To remove or not to create the default index column, you can set the index_col to False and keep the header as Zero. Here is an example of how you can do it.

recording = pd.read_excel("file.xls",
                     sheet_name= "sheet1",
                     header= 0,
                     index_col= False)

The header = 0 will make your attributes to headers and you can use it later for calling the column.

score 1 · Answer 9 · answered Jun 18 '23 at 16:30

I tried index_col=False, and index_col=None, from the answers posted for this question but none worked.
But index_col=0 worked.

So do like this when reading a file if you want to drop the unwanted index column.
df = pd.read_csv('filename.csv', index_col=0)

score 0 · Answer 10 · answered Aug 23 '22 at 21:49

0

It works for me this way:

Df = data.set_index("name of the column header to start as index column" )

answered Aug 23 '22 at 21:49

Francis Ezeani

1
1

Removing index column in pandas when reading a csv

10 Answers10

Linked

Related