CODEWITHSUNDEEP
pythonapibeautifulsoup
Shawn Schreier
Shawn Schreier

Reputation: 904

Trying to use API to get data into excel

I am trying to use an API for the first time and it has become clear I have no idea what I'm doing! For example, I am trying to get the volume of production of corn in bushels for MN from the USA quickstats database. I am attaching the code that includes my actual API key - I don't know if I shouldn't do this, but need some help. Currently, my code runs, but doesn't get it into the right format as columns are off, and it just isn't coming through right. Also, some of the data elements have commas so I'm not sure if I need to write this directly to excel or what I need to do. Here is my code - sorry this isn't the best written question but I need some guidance.

from bs4 import BeautifulSoup
import requests
import os, csv, sys, openpyxl
import openpyxl 
from openpyxl import load_workbook
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
import time
import contextlib
import itertools
import pandas as pd
from datetime import datetime, timedelta


print('working')
beginningTime=time.time()

file_name = 'USDA Corn Data.csv'


url = 'http://quickstats.nass.usda.gov/api/api_GET/?key=XXXXxxxxx&source_desc=CENSUS&short_desc=CORN, GRAIN - PRODUCTION, MEASURED IN BU&year__GE=2012&state_alpha=MN&format=CSV'

data = requests.get(url)

soup=BeautifulSoup(data.text, "html.parser")


######Have no idea if this next part is necessary but wasn't sure how to get the data that was returned from BeautifulSoup to write into a dataframe

soupData = []
for i in soup:
    soupData.append(i)
print(type(soupData))
print(soup)

df = pd.DataFrame(soupData)

df.to_csv(file_name, index=False)

Upvotes: 0

Views: 4137

Answers (3)

MusafirRahi
MusafirRahi

Reputation: 16

You can also use pandas to read the csv files, import pandas as pd DF = pd.read_csv(filename)

Upvotes: 0

cosmic_inquiry
cosmic_inquiry

Reputation: 2684

Try xlsxwriter with pandas. Docs: https://xlsxwriter.readthedocs.io/example_pandas_simple.html

import pandas as pd
# pip3 install xlsxwriter

df = pd.read_csv('http://quickstats.nass.usda.gov/api/api_GET/?key=XXXXXxxxxx&source_desc=CENSUS&short_desc=CORN, GRAIN - PRODUCTION, MEASURED IN BU&year__GE=2012&state_alpha=MN&format=CSV')

print("The sum is:",sum(pd.to_numeric(df.Value.replace({',':''},regex=True), errors='coerce').dropna()))
# The sum is: 19052800881.0

# Write dataframe to excel
writer = pd.ExcelWriter('corn.xlsx', engine='xlsxwriter')
df.to_excel(writer, sheet_name='Sheet1')
writer.save()

Upvotes: 1

vbuzze
vbuzze

Reputation: 940

Excel supports the read of CSV files (Comma-Separated Values). The easiest way to create an Excel file using Python is to simply create a text file in which your values are separated by commas (such as value1,value2,value3,..) and give it the extension of .csv instead of .txt. Of course, Python can also deal with CSV files as well.

Example : 

import csv

with open('names.csv', 'w') as csvfile:
    fieldnames = ['first_name', 'last_name']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames)

    writer.writeheader()
    writer.writerow({'first_name': 'Baked', 'last_name': 'Beans'})
    writer.writerow({'first_name': 'Lovely', 'last_name': 'Spam'})
    writer.writerow({'first_name': 'Wonderful', 'last_name': 'Spam'})

Note that Python also includes functions directly related to the Excel file formats and properties. Full documentation available here.

Upvotes: 0

Related Questions