How to add header row to a pandas DataFrame

Question

I am reading a csv file into pandas. This csv file consists of four columns and some rows, but does not have a header row, which I want to add. I have been trying the following:

Cov = pd.read_csv("path/to/file.txt", sep='\t')
Frame = pd.DataFrame([Cov], columns = ["Sequence", "Start", "End", "Coverage"])
Frame.to_csv("path/to/file.txt", sep='\t')

But when I apply the code, I get the following Error:

ValueError: Shape of passed values is (1, 1), indices imply (4, 1)

What exactly does the error mean? And what would be a clean way in python to add a header row to my csv file/pandas df?

Here is a different interpretation of your question: Add another header to an existing Dataframe to create a MultiIndex. — cs95, May 24 '19 at 06:44

score 437 · Accepted Answer · edited May 24 '19 at 06:42

437

You can use names directly in the read_csv

names : array-like, default None List of column names to use. If file contains no header row, then you should explicitly pass header=None

Cov = pd.read_csv("path/to/file.txt", 
                  sep='\t', 
                  names=["Sequence", "Start", "End", "Coverage"])

edited May 24 '19 at 06:42

cs95

379,657
97
704
746

answered Dec 04 '15 at 15:43

Leb

15,483
10
56
75

Anton Protopopov · Answer 2 · 2016-03-25T21:55:48.587

197

Alternatively you could read you csv with header=None and then add it with df.columns:

Cov = pd.read_csv("path/to/file.txt", sep='\t', header=None)
Cov.columns = ["Sequence", "Start", "End", "Coverage"]

edited Mar 25 '16 at 21:55

answered Dec 04 '15 at 17:27

Anton Protopopov

30,354
12
88
93

1

Code is good - but I found out that on an empty dataframe it will not work. In that case, Python throws "ValueError: Length mismatch: Expected axis has 0 elements, new values have ... elements". You might need something like https://stackoverflow.com/questions/44513738/pandas-create-empty-dataframe-with-only-column-names – Alexandre Jean Jul 31 '21 at 03:26

score 28 · Answer 3 · edited Mar 17 '22 at 14:34

28

col_Names=["Sequence", "Start", "End", "Coverage"]
my_CSV_File= pd.read_csv("yourCSVFile.csv",names=col_Names)

having done this, just check it with:

my_CSV_File.head()

edited Mar 17 '22 at 14:34

vvvvv

25,404
19
49
81

answered Jan 24 '18 at 03:08

Bhardwaj Joshi

401
4
3

score 21 · Answer 4 · edited Aug 19 '21 at 08:42

21

Simple And Easy Solution:

import pandas as pd

df = pd.read_csv("path/to/file.txt", sep='\t')
headers =  ["Sequence", "Start", "End", "Coverage"]
df.columns = headers

NOTE: Make sure your header length and CSV file header length should not mismatch.

edited Aug 19 '21 at 08:42

Mugen

8,301
10
62
140

answered Jul 28 '21 at 05:47

Shoaib Muhammad Arif

626
8
13

I applaud you since you are the only(?) one who actually answered the question instead of suggesting to avoid the problem in the first place – KansaiRobot Oct 06 '22 at 05:55
1

I believe there is an issue with this solution. After you read the CSV file, the first row gets declared as the columns. So, when you *re-declare* the columns with the `headers` list, you are not *adding* a new row rather you are replacing the first row (which was declared as a header) with the header list. So you end up removing the literal first row of data. – Reincoder Nov 11 '22 at 20:28

score 13 · Answer 5 · answered Feb 05 '18 at 06:35

13

To fix your code you can simply change [Cov] to Cov.values, the first parameter of pd.DataFrame will become a multi-dimensional numpy array:

Cov = pd.read_csv("path/to/file.txt", sep='\t')
Frame=pd.DataFrame(Cov.values, columns = ["Sequence", "Start", "End", "Coverage"])
Frame.to_csv("path/to/file.txt", sep='\t')

But the smartest solution still is use pd.read_excel with header=None and names=columns_list.

answered Feb 05 '18 at 06:35

romulomadu

627
6
9

When we give columns_list, can we add default values for selected columns? – YoungSheldon Jan 06 '20 at 18:14

score 2 · Answer 6 · answered Jan 24 '23 at 07:22

When reading a file without headers, existing answers correctly say that header= parameter should be set to None, but none explain why. It's because by default, header=0, which means the first row of the file is inferred as the header. For example, the following code overwrites the first row with col_names because the first row was read as the header and it was replaced by col_names.

Note that it's assumed that the columns are separated by a space ' ' here.

col_names = ["Sequence", "Start", "End", "Coverage"]
df = pd.read_csv("path/to/file.txt", sep=' ')                   # <--- wrong
df.columns = col_names

To get the correct output, you'll need to set header=None:

df = pd.read_csv("path/to/file.txt", sep=' ', header=None)      # <--- OK
df.columns = col_names

or use names= parameter to assign column names in one function call:

df = pd.read_csv("path/to/file.txt", sep=' ', names=col_names)  # <--- OK

header=None way is often preferred if the number of columns is not known (because it is vital that len(col_names) is equal to the number of columns inferred from the file) or if the specific column names are not important. For example, calling add_prefix() after read_csv can add prefix to the default column names:

df = pd.read_csv("path/to/file.txt", sep=' ', header=None).add_prefix('col')

score 0 · Answer 7 · answered Mar 13 '22 at 12:55

Since this is mentioned that we are reading from a csv, so the delimiter should be ','[as default, not need to mention]' and the given file has no header so header=None`

Sample Code :

import pandas as pd
data = pd.read_csv('path/to/file.txt',header=None)
data.columns = ["Sequence", "Start", "End", "Coverage"]
print(data.head()) #Print the first rows

How to add header row to a pandas DataFrame

7 Answers7

Linked

Related