1

I read some Google Sheet data through GSpread and Pandas; however, Pandas gives my dtype as object and I cannot change it.

I'm sure that my Google Sheet values are numbers, apart from the headers, which are strings. Matplotlib will not allow me to plot a graph, as it throws a type error.

The issue is solved if I download the file as CSV but I would like to read the file directly from the google sheet.

Here is the code:

main_worksheet=sh.worksheet('Sheet3')

data = main_worksheet.get_all_values()
headers = data.pop(0)

df = pd.DataFrame(data, columns=headers, dtype='int64')

df['week'].astype(str).astype(int)

print(df['week'])

And the result:

0     28
1     29
2     30
3     31
4     32
5     33
6     34
7     35
8     36
9     37
10    38
11    39
12    40
13    41
14    42
15    43
16    44
17    45
18    46
19    47
20    48
Name: week, dtype: object
Marc
  • 3,259
  • 4
  • 30
  • 41
zeppelin11
  • 103
  • 2
  • 3
  • 9
  • Is it possible to make a reproducible example? If you can make a sheet to share with us will be great. You could read about [mcve](/help/mcve). – rpanai Dec 04 '19 at 11:29
  • 4
    you dont change the type using `df['week'].astype(str).astype(int)`. use `df['week'] = df['week'].astype(str).astype(int)` and check the type again – luigigi Dec 04 '19 at 11:31
  • 1
    You don't assign to df['week']. – Oleg O Dec 04 '19 at 11:31
  • ok that solves the issue but why pandas get the datatype as object in the first place? – zeppelin11 Dec 04 '19 at 11:40

1 Answers1

0

Having the same problem. Apparently, when reading a google sheet with pandas, it doesn't allow having different data types in the same column. If it finds different data types in the same column it converts everything into a string/object, but it's strange because when you check specific values that are strings, now they are defaulted to NaN.

To solve that problem you need to convert the entire column into the same format "Plain text" from google sheets and then if you need to change the data type as it was you can do it using the apply method once you have read the dataframe.

Problem:

enter image description here

Reading with pandas

enter image description here

SOLUTION:

enter image description here

Reading with pandas

enter image description here

Giovanni
  • 1
  • 1