PandasAI is a Python library that extends the capabilities of pandas by providing natural language processing (NLP) capabilities. It uses a large language model (LLM) to generate Python code to answer questions about data, perform data analysis, and generate visualizations. In this answer, we will learn how to use PandasAI for data analysis with an Excel file.



# Reading an Excel file
First, we'll read the XLSX file using the `read_excel` function of pandas and then see how our data looks using `data.head()`. 

import pandas as pd

#Loading the "data.csv" file
data = pd.read_excel("/data.xlsx")
a = data.head()

print(a)

pand

# Installation
In order to use Excel files as a data source, we need to install the `pandasai[excel]` extra dependency using the following command.

``` python
pip install pandasai[excel]
```

# Steps
Here are the steps to use PandasAI with an Excel file. 

1. The first step, after installing the PandasAI library and its extension with Excel, is to import it into our Python code as follows:

``` python
from pandasai import SmartDataframe
```

2. Now, we can create a `SmartDataframe` object of our XLSX file. The `SmartDataframe` object is what we'll use to interact with our data in a natural language way.

``` python
data = SmartDataframe("excel_file.xlsx")
```

3. Once we have created a `SmartDataframe` object, we can start asking it questions about our data. For example, we can ask it the following question:

``` python
answer = df.chat('Which movie has the best IMDb rating but a shorter runtime?')
```

# Implementation
Now, we'll see the complete implementation of how to use PandasAI with an Excel file using an example.

Movie Title,Year,IMDb Rating,Runtime (minutes)
The Shawshank Redemption,1994,9.3,142
The Godfather,1972,9.2,175
Pulp Fiction,1994,8.9,154
The Dark Knight,2008,9.0,152
Forrest Gump,1994,8.8,142
Inception,2010,8.8,148
Schindler's List,1993,8.9,195
The Matrix,1999,8.7,136
Fight Club,1999,8.8,139
The Lord of the Rings: The Fellowship of the Ring,2001,8.8,178

from pandasai import SmartDataframe
from pandasai.llm import OpenAI
llm = OpenAI(api_token="OPENAI_API_KEY")

# You can instantiate a SmartDataframe with a path to a CSV file
df = SmartDataframe("../data.xlsx", config={"llm": llm})

answer = df.chat('Name the best movie after 20th century?')
print(answer)

import React from 'react';
import { shallow, configure } from 'enzyme';
import Adapter from 'enzyme-adapter-react-16';

configure({ adapter: new Adapter() });

import HelloWorld from './app';

var TestResult = function() {
    this.succeeded = false;
    this.reason = "";
    this.input = "";
    this.expected_output = "";
    this.actual_output = "";
}

export const executeTests = function() {

  var results = [];

  result = new TestResult();
  result.input = 'HelloWorld Component';
  result.expected_output = "span containing text 'Hello World'"

  let wrapper = shallow(<HelloWorld />);

  // Call your Challenge function here.

  let type = wrapper.type();
  let testSuccessful = true;
  let failureReason;

  if (type !== 'span') {
    testSuccessful = false;
    failureReason = "You need to render exactly one span HTML element";
  } else if (wrapper.props().children != "Hello World") {
     testSuccessful = false;
     failureReason = "You have rendered wrong message in your span element";
  }

  result.actual_output = wrapper.html();

  if (testSuccessful) {
    result.succeeded = true;
    result.reason = "Succeeded"
  } else {
    result.succeeded = false;
    result.reason = failureReason;
  }

  results.push(result);

  return results;
}


panddd

React

Dart

GoJS React

Typescript React

Vue.js

> **Note:** Make sure to replace `OPENAI_API_KEY` with your actual OpenAI API key. For further details on how to get an OpenAI API key, check out our Answer on [How to get API Key of GPT-3](https://www.educative.io/answers/how-to-get-api-key-of-gpt-3) answer.

# Code explanation
**Line 1:** We import `SmartDataframe` from `pandasai` to answer our questions from a XLSX file. 


**Lines 2--3:** We import and initialize the `OpenAI` language model (referred to as `llm` here) from the `pandasai.llm` module. 



**Line 6:** We instantiated a `SmartDataframe` object to interact with it in natural language to answer questions about our `data.xlsx` file.


> **Note:** The above example shows how to use PandasAI to get insights from an XLSX file quickly and easily. We can use PandasAI to answer various questions about our data and generate reports and visualizations.

pandxlsx.tar.gz

How to use PandasAI with an Excel file

Install and use PandasAI to read Excel files, enabling natural language data analysis and visualization with SmartDataframe.