PandasAI Library from OpenAI

We spend a lot of time editing, cleaning, and analyzing data using various methodologies in today’s data-driven environment. Pandas is a well-known Python module that aids with data manipulation. It keeps data in structures known as dataframes and enables you to alter, clean up, or analyze data by carrying out various operations like generating a bar graph for the dataframe, adding a new row or column, or replacing some missing data. These duties frequently require a lot of time, which could be spent on other things. We now have PandasAI, a pandas library extension that can aid in more efficient data analysis and manipulation.

What is PandasAI?

Pandas AI is an extension to the pandas library using OpenAI’s generative AI models. It allows you to generate insights from your dataframe using just a text prompt. It works on the text-to-query generative AI developed by OpenAI. Data Scientists and data analysts spend a lot of time preparing the data for analysis. They can now move forward with their data analysis. Pandas AI now makes it possible for data experts to use many of the strategies and procedures they have researched to reduce the time required for data preparation. PandasAI should not be used in place of Pandas; rather, it should be utilized in addition to Pandas. You can pose these queries to PandasAI, and it will provide responses in the form of Pandas DataFrames, saving you the time of having to manually browse and respond to queries about the dataset. With the use of the OpenAI API, Pandas AI aims to achieve the goal of allowing you to virtually converse with a machine that will then provide the desired outcomes rather than having to program the task yourself. The outcome will be generated by the machine as machine-readable code (DataFrame), which is the language they use.

How to use PandasAI?

Step 1: Install pandasai and openai library

!pip install -q pandasai openai

Step 2: Import the necessary libraries

Output:

Note: You can also read data from CSV file using the command pd.read_csv(“file_location”).

Step 4: Initialize an Open AI Large-Language Model (LLM)

Since PandasAI works on OpenAI LLM, we need to store OpenAI API key in the environment using the following code:

If you do not have an OpenAI API key, you can create an account on OpenAI platform and generate a new API key here. Now we are all set to use our Generative model to generate insights or clean data using Pandas AI.

Step 5: Provide a text prompt and dataframe to PandaAI

Automate Pandas operations with pandasai

Now Let’s try some prompts on our custom dataset

Prompt 1: Performing sum operation

Output:

Running PandasAI with openai LLM...
Code generated:
```
import pandas as pd
# Creating the dataframe
data = {'country': ['Mumbai', 'Jaipur', 'Kolkata', 'Delhi', 'Chennai', 'Lucknow', 'Hyderabad', 'Ahmedabad', 'Bangalore', 'Pune'],
        'annual tax collected': [3274294604, 7159422858, 8155677164, 3688595185, 3679908367, 4567890123, 2345678901, 3456789012, 5678901234, 6789012345],
        'happiness_index': [6.98, 8.07, 8.07, 6.35, 7.16, 7.89, 7.45, 7.12, 8.56, 7.23]}
df = pd.DataFrame(data)
# Filtering north Indian cities
north_cities = ['Jaipur', 'Kolkata', 'Delhi', 'Lucknow', 'Ahmedabad']
north_df = df[df['country'].isin(north_cities)]
# Calculating total tax collected in north Indian cities
total_tax_collected = north_df['annual tax collected'].sum()
print(total_tax_collected)
```
Code running:
```
data = {'country': ['Mumbai', 'Jaipur', 'Kolkata', 'Delhi', 'Chennai',
    'Lucknow', 'Hyderabad', 'Ahmedabad', 'Bangalore', 'Pune'],
    'annual tax collected': [3274294604, 7159422858, 8155677164, 3688595185,
    3679908367, 4567890123, 2345678901, 3456789012, 5678901234, 6789012345],
    'happiness_index': [6.98, 8.07, 8.07, 6.35, 7.16, 7.89, 7.45, 7.12, 
    8.56, 7.23]}
north_cities = ['Jaipur', 'Kolkata', 'Delhi', 'Lucknow', 'Ahmedabad']
north_df = df[df['country'].isin(north_cities)]
total_tax_collected = north_df['annual tax collected'].sum()
print(total_tax_collected)
```
Answer: 72673421680

Prompt 2: Analyzing the dataset

Output:

Running PandasAI with openai LLM...
Code generated:
```
import pandas as pd
# Creating the dataframe
data = {'country': ['Kolkata', 'Jaipur', 'Delhi', 'Mumbai', 'Chennai', 'Bangalore', 'Hyderabad', 'Pune', 'Ahmedabad', 'Surat'],
        'annual tax collected': [3560469532, 9597107067, 4821092001, 9053727452, 3738210455, 6489321000, 5183920000, 2874610000, 3958200000, 3129400000],
        'happiness_index': [6.35, 8.07, 7.16, 6.98, 6.98, 7.89, 7.45, 7.12, 6.78, 6.55]}
df = pd.DataFrame(data)
# Sorting the dataframe by happiness index in descending order
df = df.sort_values(by='happiness_index', ascending=False)
# Selecting the top 5 happiest cities
top_5_happiest_cities = df.head(5)['country'].tolist()
print(top_5_happiest_cities)
```
Code running:
```
data = {'country': ['Kolkata', 'Jaipur', 'Delhi', 'Mumbai', 'Chennai',
    'Bangalore', 'Hyderabad', 'Pune', 'Ahmedabad', 'Surat'],
    'annual tax collected': [3560469532, 9597107067, 4821092001, 9053727452,
    3738210455, 6489321000, 5183920000, 2874610000, 3958200000, 3129400000],
    'happiness_index': [6.35, 8.07, 7.16, 6.98, 6.98, 7.89, 7.45, 7.12, 
    6.78, 6.55]}
top_5_happiest_cities = df.head(5)['country'].tolist()
print(top_5_happiest_cities)
```
Answer: ['Delhi', 'Mumbai', 'Kolkata', 'Chennai', 'Jaipur']

Prompt 3: Performing sort operation

Output:

Running PandasAI with openai LLM...
Code generated:
```
df_sorted = df.sort_values(by='happiness_index', ascending=True)
print(df_sorted)
```
Code running:
```
df_sorted = df.sort_values(by='happiness_index', ascending=True)
print(df_sorted)
```
Answer:      country  annual tax collected  happiness_index
9       Agra          146318441864             3.36
6       Pune           16074023894             4.23
5    Lucknow           11812051350             6.10
2    Kolkata           24112550372             6.35
8   Amritsar           43807565410             6.87
4     Jaipur           17454337886             6.98
1     Mumbai           28916155672             7.16
3    Chennai           34358173362             8.07
7  Bengaluru           14909678554             8.22
0      Delhi           19294482072             9.94

Prompt 4: Plotting a histogram

Output:

Running PandasAI with openai LLM…

histogram representing tax collected in north indian cities

Code generated: 
``` north_cities = ['Delhi', 'Jaipur'] 
    north_df = df[df['country'].isin(north_cities)] 
    import matplotlib.pyplot as plt plt.bar(north_df['country'], 
                                north_df['annual tax collected']) 
    plt.xlabel('Indian cities') 
    plt.ylabel('Tax collected') 
    plt.title('Tax collection of north Indian cities') 
    plt.show() 
``` Code running: 
         ``` north_cities = ['Delhi', 'Jaipur'] 
             north_df = df[df['country'].isin(north_cities)] 
             plt.bar(north_df['country'], north_df['annual tax collected']) 
             plt.xlabel('Indian cities') 
             plt.ylabel('Tax collected') 
             plt.title('Tax collection of north Indian cities') 
             plt.show() 
``` Answer:

Prompt 5: Performing groupby operation

Output:

Running PandasAI with openai LLM...
Code generated:
```
# Import pandas library
import pandas as pd
# Create the dataframe
data = {'country': ['Chennai', 'Delhi', 'Mumbai', 'Kolkata', 'Jaipur', 'Bangalore', 'Hyderabad', 'Pune', 'Ahmedabad', 'Surat'],
        'annual tax collected': [4115278226, 5211175683, 9898166675, 2429903829, 2640456722, 6329812345, 4781234567, 3214567890, 5678901234, 4321987654],
        'happiness_index': [6.98, 6.98, 8.07, 9.94, 7.16, 8.56, 7.89, 6.78, 8.23, 7.45]}
df = pd.DataFrame(data)
# Group the cities by their happiness index
grouped_df = df.groupby('happiness_index')
# Print the groups
for name, group in grouped_df:
    print("Happiness Index:", name)
    print(group)
```
Code running:
```
data = {'country': ['Chennai', 'Delhi', 'Mumbai', 'Kolkata', 'Jaipur',
    'Bangalore', 'Hyderabad', 'Pune', 'Ahmedabad', 'Surat'],
    'annual tax collected': [4115278226, 5211175683, 9898166675, 2429903829,
    2640456722, 6329812345, 4781234567, 3214567890, 5678901234, 4321987654],
    'happiness_index': [6.98, 6.98, 8.07, 9.94, 7.16, 8.56, 7.89, 6.78, 
    8.23, 7.45]}
grouped_df = df.groupby('happiness_index')
for name, group in grouped_df:
    print('Happiness Index:', name)
    print(group)
```
Answer: Happiness Index: 3.36
  country  annual tax collected  happiness_index
9    Agra          146318441864             3.36
Happiness Index: 4.23
  country  annual tax collected  happiness_index
6    Pune           16074023894             4.23
Happiness Index: 6.1
   country  annual tax collected  happiness_index
5  Lucknow           11812051350              6.1
Happiness Index: 6.35
   country  annual tax collected  happiness_index
2  Kolkata           24112550372             6.35
Happiness Index: 6.87
    country  annual tax collected  happiness_index
8  Amritsar           43807565410             6.87
Happiness Index: 6.98
  country  annual tax collected  happiness_index
4  Jaipur           17454337886             6.98
Happiness Index: 7.16
  country  annual tax collected  happiness_index
1  Mumbai           28916155672             7.16
Happiness Index: 8.07
   country  annual tax collected  happiness_index
3  Chennai           34358173362             8.07
Happiness Index: 8.22
     country  annual tax collected  happiness_index
7  Bengaluru           14909678554             8.22
Happiness Index: 9.94

Prompt 6: Describe the dataset

Output:

Running PandasAI with openai LLM...
Code generated:
```
import pandas as pd
# create the dataframe
data = {'country': ['Delhi', 'Chennai', 'Kolkata', 'Mumbai', 'Jaipur'],
        'annual tax collected': [6851245018, 5569913156, 497203726, 1780282822, 856852833],
        'happiness_index': [6.98, 6.35, 6.35, 6.98, 7.16]}
df = pd.DataFrame(data)
# describe the dataframe
print(df.describe())
```
Code running:
```
data = {'country': ['Delhi', 'Chennai', 'Kolkata', 'Mumbai', 'Jaipur'],
    'annual tax collected': [6851245018, 5569913156, 497203726, 1780282822,
    856852833], 'happiness_index': [6.98, 6.35, 6.35, 6.98, 7.16]}
print(df.describe())
```
Answer:        annual tax collected  happiness_index
count          1.000000e+01        10.000000
mean           3.570575e+10         6.728000
std            4.010314e+10         1.907149
min            1.181205e+10         3.360000
25%            1.641910e+10         6.162500
50%            2.170352e+10         6.925000
75%            3.299767e+10         7.842500
max            1.463184e+11         9.940000

Prompt 7: Check for missing values

Output:

Running PandasAI with openai LLM...
Code generated:
```
import pandas as pd
# Creating the dataframe
data = {'country': ['Jaipur', 'Delhi', 'Mumbai', 'Chennai', 'Kolkata'],
        'annual tax collected': [8203131465, 406012666, 6195812866, 8532100009, 2405598967],
        'happiness_index': [8.07, 6.98, 6.98, 7.16, 6.35]}
df = pd.DataFrame(data)
# Checking for missing values
print(df.isnull().values.any())
```
Code running:
```
data = {'country': ['Jaipur', 'Delhi', 'Mumbai', 'Chennai', 'Kolkata'],
    'annual tax collected': [8203131465, 406012666, 6195812866, 8532100009,
    2405598967], 'happiness_index': [8.07, 6.98, 6.98, 7.16, 6.35]}
print(df.isnull().values.any())
```
Answer: False

To learn more about Chat GPT, you can refer to:

• 7 Best AI Businesses to Start with ChatGPT
• ChatGPT vs Google BARD
• ChatGPT’s History Gone: Data Breach or AI Bug?

Frequently Asked Questions (FAQs)

1. What is Pandas?

Pandas is a robust Python framework for handling and analysing data. It offers data structures and operations to manage structured data effectively, including tabular or time series data.

2. How is PandasAI different from Pandas?

In Pandas, you have to perform operations on dataset by typing many lines of code which can be quite time consuming. PandasAI makes this task easier by performing operations on datasets from simple text prompts. It leverages the power of OpenAI LLMs and generates code to perform specified operations on provided datasets.

3. Is Pandas AI suitable for big data analysis?

While PandasAI comes in handy to perform many tasks by simply using a text prompt. It still cannot be used for big data analysis as it still uses dataframe as it’s data structure which has a large overhead causing RAM issues for big data.

4. Where can I find resources to learn PandasAI?

You can refer to the official github repository at https://github.com/gventuri/pandas-ai.

Conclusion

In this article, we looked at PandasAI’s advantages as a useful addition for pandas library users. PandasAI has several amazing capabilities, such as running language prompts that resemble SQL searches and producing visualizations directly from a DataFrame. It without a doubt increases productivity by automating several processes. It’s crucial to remember that even if PandasAI is a strong tool, the Pandas library still needs to be used. The pandas library’s capabilities are still necessary for some sophisticated operations, such as adding missing data to a DataFrame. Pandas’ extensive ecosystem and wide range of features continue to be crucial for managing challenging data manipulation and analysis tasks. Consequently, PandasAI is a useful addition that enhances the functionality of the pandas library and further augments the efficiency and convenience of working with data in Python.