8

I'm trying to work with data in a google spreadsheet, reading it into a csv and then working with it as a dataframe using pandas.read_csv().

I can get the csv read out into a variable (the variable "data" below), but cannot then use pandas.read_csv() on the variable. I've tried casting it as a string, using os.cwd(), etc.

r = requests.get('I put my google sheets url here')
data = r.text
print(data)

#csv is printed out properly

df = pd.read_csv(filepath_or_buffer = data, header = 1, usecols = ["Latitude", "Longitude"])
print(df)

No matter what I try, I always get a FileNotFoundException.

I'm a python newbie, so I'm probably missing something something really obvious. Thank you!

happyjoytotheworld
  • 83
  • 1
  • 4

2 Answers2

10

If the first parameter to read_csv is a string (as it is in your case) it treats it as a file path that it tries to open. Hence the FileNotFoundException.

You need your data in a file-like object. Try using io.StringIO like so:

import io

r = requests.get('I put my google sheets url here')
data = r.text
buffer = io.StringIO(data)

df = pd.read_csv(filepath_or_buffer = buffer, header = 1, usecols = ["Latitude", "Longitude"])
FoxMulder900
  • 1,272
  • 13
  • 27
6

You can do this with StringIO: 

import pandas as pd
import io
import requests
url="I put my google sheets csv url here"
s=requests.get(url).content
c=pd.read_csv(io.StringIO(s.decode('utf-8')))
Tal Avissar
  • 10,088
  • 6
  • 45
  • 70