Save API data into CSV format using Python

In this article, we are going to see how can we fetch data from API and make a CSV file of it, and then we can perform various stuff on it like applying machine learning model data analysis, etc. Sometimes we want to fetch data from our Database Api and train our machine learning model and it was very real-time by applying this method we can train our machine learning model using updated data, so our model’s predictions are accurate. Here we used the requests library in Python to fetch data from our API. 

Fetching Data from API using Request Library

Step 1: Importing necessary libraries

Here we require two library requests to make API calls, and Pandas to make a DataFrame.

Step 2: Call the API using the requests library 

In this step we are going to call out TMDB API using a requests response then we got a response from it. This line of code is making a GET request to the TMDB API endpoint for top-rated movies.  The response will be a JSON object containing information about the top-rated movies, such as the movie title, overview, release date, popularity, vote average, and vote count. The response object also contains other information such as the status code and headers of the response.

Step 3: Creating a new DataFrame

Here we are going to create a new DataFrame using Pandas in which we store our result fetch from the API.

Step 4:  Putting the Results fetch from our API to the Dataframe

In this step we are  using the requests library to make GET requests to the Movie Database (TMDB) API to retrieve the top rated movies. It starts by checking if the initial request has a status code of 200 (which indicates a successful response), and if it does, it enters a loop that runs 399 times(it means we are going to fetch the data of first 400 pages). In each iteration of the loop, it makes a request to the API for the next page of top-rated movies and appends the relevant data (movie id, title, overview, release date, popularity, vote average, and vote count) to a DataFrame called “temp_df”. After each iteration, it appends the “temp_df” to another DataFrame called “df” using the .append() method. If the initial request has a status code other than 200, then it prints an error message with the status code.

Step 5: Printing first five rows of our DataFrame

The below code prints the shape of our dataset. it means it going to print how many rows and columns have present in our data frame. We are going to print the first five rows of our dataset.

Output:

 

Step 6: Converting our Dataframe into a CSV file and store it

We are going to save the dataframe df to a CSV file named ‘movies.csv’ and then download it to our computer.

Output:

 

Complete Code

Output:

 

Fetching Data from API using urllib Library

Imports the pandas, urllib.request, and json libraries. Initializes an empty pandas DataFrame called df. Uses a for loop to loop through pages 1 to 399 of the TMDb API’s top rated movies endpoint. For each iteration of the loop, the code constructs a URL that specifies the API key and language, and the page number to retrieve. The code then sends a GET request to the URL using urllib.request.urlopen(), and reads the response into a variable response. The json library is used to parse the response into a dictionary called data. The code then creates a temporary DataFrame temp_df from a subset of the data obtained from the API, specifically the ‘results’ key in the data dictionary. The subset includes the columns ‘id’, ‘title’, ‘overview’, ‘release_date’, ‘popularity’, ‘vote_average’, and ‘vote_count’. The temporary DataFrame is then appended to the final DataFrame df using df.append(). After the for loop is completed, the code prints the shape of the final DataFrame df, the first five rows of the DataFrame, and then saves the DataFrame as a CSV file. Finally, the code uses the files.download() function to download the CSV file to the local machine.

Note: The API key used in this code is an example and might not work. To use this code, you will need to obtain a valid API key from TMDb and use that in the URL.

Output: