Convert header and values list to pandas dataframe

Question

I'm reading data from Google Sheets and was able to successfully get it in:

header = result.get('values', [])[0] #First line is column names
values = result.get('values', [])[1:] #Everything else is data

Then after that I'm doing this:

if not values:
    print('No data found.')
else:
    df = pd.DataFrame(data=values, columns=header)
    print(df.head(10))

but I'm getting an error:

AssertionError: 26 columns passed, passed data had 14 columns

If I print header or values I'm getting the data in List successfully.

len(values) is 5297 and len(header) is 26. I might not want to hard code it as the data will be added to it every now and then. — Chaudhry Talha
– Chaudhry Talha, Commented Jun 17, 2019 at 13:47
The amount of columns in header does not align with the amount of columns in values. Hence the 26 vs 14. Try this first len(pd.DataFrame(data=values).columns). — Erfan
– Erfan, Commented Jun 17, 2019 at 13:48
The output of that is 14. SO on google sheet 14 columns has data to them and columns from 15 to 26 does not have any data yet. But the column names are there, so pandas should just add blank to its values right? — Chaudhry Talha
– Chaudhry Talha, Commented Jun 17, 2019 at 13:51
That doesn't work that way: Can you try: df = pd.DataFrame(data=values, columns=header[:14]) — Erfan
– Erfan, Commented Jun 17, 2019 at 13:56
This works perfectly. So what about the rest of the columns that are there but have no value? What should I do with them? Wait for the data or is there a way? — Chaudhry Talha
– Chaudhry Talha, Commented Jun 17, 2019 at 13:59

Erfan · Accepted Answer · 2019-06-17 14:22:58Z

1

First you can make your dataframe with the present values, after that create a dataframe with the rest of the columns with NaN and concat them together:

df = pd.DataFrame(data=values, columns=header[:14])
df2 = pd.DataFrame({head : [np.NaN]*len(df) for head in header[14:]})

df_final = pd.concat([df, df2], axis=1)

Q: how did you know that the columns will be 14 from 5297

A: Because your values contains rows, so 5297 is the amount of rows in your data.

answered Jun 17, 2019 at 14:22

Erfan

43.3k10 gold badges75 silver badges86 bronze badges

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

2 Comments

Chaudhry Talha Over a year ago

Thank you so much. Just want to share that where you've written 14 I've added len(pd.DataFrame(data=values).columns) so that I don't have to worry when the data is updated and it'll automatically pick the number of rows.

Erfan Over a year ago

Yes, good point. And no problem, goodluck with Python and Pandas :)

Collectives™ on Stack Overflow

Convert header and values list to pandas dataframe

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related