Csv missing columns with Pandas Dataframe

Question

I want to read a csv as dataframe into Pandas.

My csv file has the following format

a b c d
0 1 2 3 4 5
1 2 3 4 5 6

When I read the csv with Pandas I get the following dataframe

    a b c d
0 1 2 3 4 5
1 2 3 4 5 6

When I execute print df.columns I get something like :

Index([u'a', u'b', u'c', u'd'], dtype='object')

And when I execute print df.iloc[0] I get :

a  2
b  3
c  4
d  5
Name: (0, 1)

I would like to have something a dataframe like

a b c d col1 col2
0 1 2 3 4    5
1 2 3 4 5    6

I don't know how many columns I will have to had. But I need as many columns as the number of value in the first line after the header. How can I achieve that ?

This answer could help

floatingpurr
– floatingpurr

2017-09-14 16:00:47 +00:00
Commented Sep 14, 2017 at 16:00 — floatingpurr
– floatingpurr, Commented Sep 14, 2017 at 16:00

Ted Petrou · Accepted Answer · 2017-09-15 14:05:19Z

3

One way to do this would be to read in the data twice. Once with the first row (the original columns) skipped and the second with only the column names read (and all the rows skipped)

df = pd.read_csv(header=None, skiprows=1)
columns = pd.read_csv(nrows=0).columns.tolist()
columns

Output

['a', 'b', 'c', 'd']

Now find number of missing columns and use a list comprehension to make new columns

num_missing_cols = len(df.columns) - len(columns)
new_cols = ['col' + str(i+1) for i in range(num_missing_cols)]
df.columns = columns + new_cols
df

   a  b  c  d  col1  col2
0  0  1  2  3     4     5
1  1  2  3  4     5     6

edited Sep 15, 2017 at 14:05

answered Sep 14, 2017 at 16:03

Ted Petrou

62.4k19 gold badges139 silver badges139 bronze badges

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

1 Comment

Jean-Paul Goodwin Over a year ago

Thank you that worked great. I just had to change the pd.read_clipboard() to pd.read_csv()

Collectives™ on Stack Overflow

Csv missing columns with Pandas Dataframe

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related