Pandas: How to add missing header columns

Question

Hello I have csv (tab separated) with header and I would like to load it into Pandas DataFrame in Python 3.5. Problem is, that some headers missing (there are columns with data but without header - as in example below). I tried to load it by read_csv function but if I dont skip first line, it will merge first data lines to match number of headers and number of data columns.

Is there any way, how to read this csv and automatically add missing headers? Like Header4 and Header 5 in example below?

Thank you

| Header1 | Header2 | Header3 |        |        |
|---------|---------|---------|--------|--------|
| value1  | value2  | value3  | value4 | value5 |
| value1  | value2  | value3  | value4 | value5 |

Possible duplicate of read_csv with missing/incomplete header or irregular number of columns — Shaido
– Shaido, Commented Oct 3, 2017 at 8:09

jezrael · Accepted Answer · 2017-10-03 08:17:08Z

4

You can rename columns in post processing by dict:

print (df)
  Header1 Header2 Header3 Unnamed: 3 Unnamed: 4
0  value1  value2  value3     value4     value5
1  value1  value2  value3     value4     value5


df = df.rename(columns = {'Unnamed: 3':'Header4','Unnamed: 4':'Header5'})
print (df)
  Header1 Header2 Header3 Header4 Header5
0  value1  value2  value3  value4  value5
1  value1  value2  value3  value4  value5

Or by custom function:

f = lambda x: 'Header{}'.format(int(x.split()[1])+1) if 'Unnamed' in x else x
df = df.rename(columns = f)
print (df)
  Header1 Header2 Header3 Header4 Header5
0  value1  value2  value3  value4  value5
1  value1  value2  value3  value4  value5

Or use parameters names and header or names and skiprows:

df = pd.read_csv('file', names=['Header1','Header2','Header3','Header4','Header5'], header=0)
print (df)
  Header1 Header2 Header3 Header4 Header5
0  value1  value2  value3  value4  value5
1  value1  value2  value3  value4  value5

Or:

df = pd.read_csv('file',
                 names=['Header1','Header2','Header3','Header4','Header5'],
                 skiprows=1)
print (df)
  Header1 Header2 Header3 Header4 Header5
0  value1  value2  value3  value4  value5
1  value1  value2  value3  value4  value5

edited Oct 3, 2017 at 8:17

answered Oct 3, 2017 at 8:08

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

007mrviper Over a year ago

I tried your last solution only but it replaced the existing first row of the table.

Mohamed Ali JAMAOUI · Accepted Answer · 2020-04-30 21:22:40Z

1

You can achieve that as follows:

df.columns = df.columns[:3].tolist()  + ['Header4', 'Header5']

output:

    Header1    Header2    Header3    Header4   Header5
0   value1     value2     value3     value4    value5 
1   value1     value2     value3     value4    value5

edited Apr 30, 2020 at 21:22

answered Oct 3, 2017 at 9:07

Mohamed Ali JAMAOUI

14.8k14 gold badges79 silver badges124 bronze badges

Collectives™ on Stack Overflow

Pandas: How to add missing header columns

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related