LLM-Powered Analyses of your GitHub Community using EvaDB
# coding=utf-8
# Copyright 2018-2023 EvaDB
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.


import os
import time

import pandas as pd
from retry import retry

from evadb.catalog.catalog_type import NdArrayType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import (
    PandasDataframe,
)
from evadb.utils.generic_utils import try_to_import_openai
from tqdm import tqdm


_VALID_CHAT_COMPLETION_MODEL = [
    "gpt-3.5-turbo",
    "gpt-3.5-turbo-16k",
    "gpt-4-0613",
]


class ChatGPT(AbstractFunction):
    """
    Arguments:
        model (str) : ID of the OpenAI model to use. Refer to '_VALID_CHAT_COMPLETION_MODEL' for a list of supported models.
        temperature (float) : Sampling temperature to use in the model. Higher value results in a more random output.

    Input Signatures:
        query (str)   : The task / question that the user wants the model to accomplish / respond.
        content (str) : Any relevant context that the model can use to complete its tasks and generate the response.
        prompt (str)  : An optional prompt that can be passed to the model. It can contain instructions to the model,
                        or a set of examples to help the model generate a better response.
                        If not provided, the system prompt defaults to that of an helpful assistant that accomplishes user tasks.

    Output Signatures:
        response (str) : Contains the response generated by the model based on user input. Any errors encountered
                         will also be passed in the response.

    Example Usage:
        Assume we have the transcripts for a few videos stored in a table 'video_transcripts' in a column named 'text'.
        If the user wants to retrieve the summary of each video, the ChatGPT UDF can be used as:

            query = "Generate the summary of the video"
            cursor.table("video_transcripts").select(f"ChatGPT({question}, text)")

        In the above UDF invocation, the 'query' passed would be the user task to generate video summaries, and the
        'content' passed would be the video transcripts that need to be used in order to generate the summary. Since
        no prompt is passed, the default system prompt will be used.

        Now assume the user wants to create the video summary in 50 words and in French. Instead of passing these instructions
        along with each query, a prompt can be set as such:

            prompt = "Generate your responses in 50 words or less. Also, generate the response in French."
            cursor.table("video_transcripts").select(f"ChatGPT({question}, text, {prompt})")

        In the above invocation, an additional argument is passed as prompt. While the query and content arguments remain
        the same, the 'prompt' argument will be set as a system message in model params.

        Both of the above cases would generate a summary for each row / video transcript of the table in the response.
    """

    @property
    def name(self) -> str:
        return "ChatGPT"

    @setup(cacheable=False, function_type="chat-completion", batchable=True)
    def setup(
        self,
        model="gpt-3.5-turbo",
        temperature: float = 0,
    ) -> None:
        assert (
            model in _VALID_CHAT_COMPLETION_MODEL
        ), f"Unsupported ChatGPT {model}"
        self.model = model
        self.temperature = temperature

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["query", "content", "prompt"],
                column_types=[
                    NdArrayType.STR,
                    NdArrayType.STR,
                    NdArrayType.STR,
                ],
                column_shapes=[(1,), (1,), (None,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=["response"],
                column_types=[
                    NdArrayType.STR,
                ],
                column_shapes=[(1,)],
            )
        ],
    )
    def forward(self, text_df):
        try_to_import_openai()
        import openai

        @retry(tries=6, delay=20)
        def completion_with_backoff(**kwargs):
            return openai.ChatCompletion.create(**kwargs)

        # Register API key
        openai.api_key = os.environ.get('OPENAI_KEY')
        assert len(openai.api_key) != 0, (
            "Please set your OpenAI API key in evadb.yml file (third_party,"
            " open_api_key) or environment variable (OPENAI_KEY)"
        )

        queries = text_df[text_df.columns[0]]
        content = text_df[text_df.columns[0]]
        if len(text_df.columns) > 1:
            queries = text_df.iloc[:, 0]
            content = text_df.iloc[:, 1]

        prompt = None
        if len(text_df.columns) > 2:
            prompt = text_df.iloc[0, 2]

        # openai api currently supports answers to a single prompt only
        completion_tokens = 0
        prompt_tokens = 0

        results = []
        for i, (query, content) in tqdm(enumerate(zip(queries, content))):
            if i != 0 and i % 100 == 0:
                print(f"Completed {i} rows")
                # Avoid hitting API limit
                time.sleep(30)
            params = {
                "model": self.model,
                "temperature": self.temperature,
                "messages": [],
            }

            def_sys_prompt_message = {
                "role": "system",
                "content": prompt
                if prompt is not None
                else (
                    "You are a helpful assistant that accomplishes user tasks."
                ),
            }

            params["messages"].append(def_sys_prompt_message)
            params["messages"].extend(
                [
                    {
                        "role": "user",
                        "content": f"Here is some context : {content}",
                    },
                    {
                        "role": "user",
                        "content": f"Complete the following task: {query}",
                    },
                ],
            )

            response = completion_with_backoff(**params)
            answer = response.choices[0].message.content
            results.append(answer)
            completion_tokens += response['usage']['completion_tokens']
            prompt_tokens += response['usage']['prompt_tokens']

        completion_tokens += response['usage']['completion_tokens']
        prompt_tokens += response['usage']['prompt_tokens']

        df = pd.DataFrame({"response": results})

        print(f"Total tokens used: {completion_tokens + prompt_tokens}")
        print(f"Completion tokens used: {completion_tokens}")
        print(f"Prompt tokens used: {prompt_tokens}")
        pricing = {
            'gpt-3.5-turbo': {'prompt': 0.0015, 'completion': 0.002},
            'gpt-3.5-turbo-16k': {'prompt': 0.003, 'completion': 0.004},
            'gpt-4-0613': {'prompt': 0.03, 'completion': 0.06},
        }
        print(f"Prompt tokens price: ${pricing[self.model]['prompt'] * prompt_tokens/1000}")
        print(f"Completion tokens price: ${pricing[self.model]['completion'] * completion_tokens/1000}")
        price = (pricing[self.model]['prompt'] * prompt_tokens + pricing[self.model]['completion'] * completion_tokens)/1000
        print(f"Total Price: ${price}")

        return df

functions/chatgpt.py
pchunduri6/stargazers-reloaded
# coding=utf-8
# Copyright 2018-2023 EvaDB
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.


import os
import time

import pandas as pd
from retry import retry

from evadb.catalog.catalog_type import NdArrayType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import (
    PandasDataframe,
)
from evadb.utils.generic_utils import try_to_import_openai
import tiktoken
from tqdm import tqdm

_VALID_CHAT_COMPLETION_MODEL = [
    "gpt-3.5-turbo",
    "gpt-3.5-turbo-16k",
    "gpt-4-0613",
]


class ChatGPTMultirow(AbstractFunction):
    """
    Arguments:
        model (str) : ID of the OpenAI model to use. Refer to '_VALID_CHAT_COMPLETION_MODEL' for a list of supported models.
        temperature (float) : Sampling temperature to use in the model. Higher value results in a more random output.

    Input Signatures:
        query (str)   : The task / question that the user wants the model to accomplish / respond.
        content (str) : Any relevant context that the model can use to complete its tasks and generate the response.
        prompt (str)  : An optional prompt that can be passed to the model. It can contain instructions to the model,
                        or a set of examples to help the model generate a better response.
                        If not provided, the system prompt defaults to that of an helpful assistant that accomplishes user tasks.

    Output Signatures:
        response (str) : Contains the response generated by the model based on user input. Any errors encountered
                         will also be passed in the response.

    Example Usage:
        Assume we have the transcripts for a few videos stored in a table 'video_transcripts' in a column named 'text'.
        If the user wants to retrieve the summary of each video, the ChatGPT UDF can be used as:

            query = "Generate the summary of the video"
            cursor.table("video_transcripts").select(f"ChatGPT({question}, text)")

        In the above UDF invocation, the 'query' passed would be the user task to generate video summaries, and the
        'content' passed would be the video transcripts that need to be used in order to generate the summary. Since
        no prompt is passed, the default system prompt will be used.

        Now assume the user wants to create the video summary in 50 words and in French. Instead of passing these instructions
        along with each query, a prompt can be set as such:

            prompt = "Generate your responses in 50 words or less. Also, generate the response in French."
            cursor.table("video_transcripts").select(f"ChatGPT({question}, text, {prompt})")

        In the above invocation, an additional argument is passed as prompt. While the query and content arguments remain
        the same, the 'prompt' argument will be set as a system message in model params.

        Both of the above cases would generate a summary for each row / video transcript of the table in the response.
    """

    @property
    def name(self) -> str:
        return "ChatGPT"

    @setup(cacheable=False, function_type="chat-completion", batchable=True)
    def setup(
        self,
        model="gpt-3.5-turbo",
        temperature: float = 0,
    ) -> None:
        assert (
            model in _VALID_CHAT_COMPLETION_MODEL
        ), f"Unsupported ChatGPT {model}"
        self.model = model
        self.temperature = temperature

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["query", "content", "prompt"],
                column_types=[
                    NdArrayType.STR,
                    NdArrayType.STR,
                    NdArrayType.STR,
                ],
                column_shapes=[(1,), (1,), (None,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=["response"],
                column_types=[
                    NdArrayType.STR,
                ],
                column_shapes=[(1,)],
            )
        ],
    )
    def forward(self, text_df):
        try_to_import_openai()
        import openai

        @retry(tries=6, delay=20)
        def completion_with_backoff(**kwargs):
            return openai.ChatCompletion.create(**kwargs)

        # Register API key
        openai.api_key = os.environ.get('OPENAI_KEY')
        assert len(openai.api_key) != 0, (
            "Please set your OpenAI API key in evadb.yml file (third_party,"
            " open_api_key) or environment variable (OPENAI_KEY)"
        )

        queries = text_df[text_df.columns[0]]
        content = text_df[text_df.columns[0]]
        if len(text_df.columns) > 1:
            queries = text_df.iloc[:, 0]
            content = text_df.iloc[:, 1]

        prompt = None
        if len(text_df.columns) > 2:
            prompt = text_df.iloc[0, 2]

        # openai api currently supports answers to a single prompt only
        completion_tokens = 0
        prompt_tokens = 0

        # divide content into batches of 20
        batch_size = 10
        content = content.tolist()
        content_batched = [
            content[i : i + batch_size] for i in range(0, len(content), batch_size)
        ]

        all_results = []
        for i, batch in tqdm(enumerate(content_batched)):
            if i % 40 == 0:
                print(f"Completed {i} batches")
                # Avoid hitting API limit
                time.sleep(30)
            all_content = ""
            for row in batch:
                all_content += row
                all_content += "\n\n"

            all_content = all_content[:-4]
            encoding = tiktoken.encoding_for_model(self.model)
            num_tokens = len(encoding.encode(all_content))
            num_tokens += len(encoding.encode(queries[0]))
            print(f"Estimated input prompt tokens: {num_tokens}")
            params = {
                "model": self.model,
                "temperature": self.temperature,
                "messages": [],
            }

            def_sys_prompt_message = {
                "role": "system",
                "content": prompt
                if prompt is not None
                else ("You are a helpful assistant that accomplishes user tasks."),
            }

            params["messages"].append(def_sys_prompt_message)
            params["messages"].extend(
                [
                    {
                        "role": "user",
                        "content": f"Here is some context : {all_content}",
                    },
                    {
                        "role": "user",
                        "content": f"Complete the following task: {queries[0]}",
                    },
                ],
            )

            response = completion_with_backoff(**params)
            answer = response.choices[0].message.content
            results = answer.split("\n\n")
            if len(results) != len(batch):
                raise Exception(
                    f"WARNING: batch size is {len(batch)} but results are {len(results)}"
                )

            all_results.extend(results)

            completion_tokens += response["usage"]["completion_tokens"]
            prompt_tokens += response["usage"]["prompt_tokens"]

        if len(all_results) != len(queries):
            raise Exception(
                "Length of results and queries do not match, please improve your prompt"
            )

        df = pd.DataFrame({"response": all_results})

        print(f"Total tokens used: {completion_tokens + prompt_tokens}")
        print(f"Completion tokens used: {completion_tokens}")
        print(f"Prompt tokens used: {prompt_tokens}")
        pricing = {
            "gpt-3.5-turbo": {"prompt": 0.0015, "completion": 0.002},
            "gpt-3.5-turbo-16k": {"prompt": 0.003, "completion": 0.004},
            "gpt-4-0613": {"prompt": 0.03, "completion": 0.06},
        }
        print(
            f"Prompt tokens price: ${pricing[self.model]['prompt'] * prompt_tokens/1000}"
        )
        print(
            f"Completion tokens price: ${pricing[self.model]['completion'] * completion_tokens/1000}"
        )
        price = (
            pricing[self.model]["prompt"] * prompt_tokens
            + pricing[self.model]["completion"] * completion_tokens
        ) / 1000
        print(f"Total Price: ${price}")

        return df

functions/chatgpt_batch.py
pchunduri6/stargazers-reloaded
import pandas as pd
from tqdm import tqdm
from github import Github

from evadb.catalog.catalog_type import NdArrayType, ColumnType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import PandasDataframe


class GithubStargazers(AbstractFunction):
    """
    Arguments:
        None

    Input Signatures:
        repo_url (str) : The URL of the GitHub repository to scrape stargazers from.
        github_token (str) : GitHub personal access token for authentication.

    Output Signatures:
        stargazers (str) : A list of GitHub usernames who have starred the repository.

    Example Usage:
        You can use this function to scrape stargazers of a GitHub repository as follows:

        repo_url = "https://github.com/owner/repo"
        github_token = "your_github_token" (Personal Access Token)
        cursor.function("GithubStargazersScraper", repo_url, github_token)
    """

    @property
    def name(self) -> str:
        return "GithubStargazers"

    @setup(cacheable=False, function_type="web-scraping")
    def setup(self) -> None:
        # Any setup or initialization can be done here if needed
        pass

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["repo_url", "github_token"],
                column_types=[ColumnType.TEXT, ColumnType.TEXT],
                column_shapes=[(1,), (1,), (1,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=["stargazers"],
                column_types=[NdArrayType.STR],
                column_shapes=[(None,)],
            )
        ],
    )
    def forward(self, input_df):
        # Ensure the URL is provided
        if input_df.empty or input_df.iloc[0, 0] is None:
            raise ValueError("Repository URL must be provided.")

        # Extract inputs from the DataFrame
        repo_url = input_df.iloc[0, 0]
        github_token = input_df.iloc[0, 1]

        # Initialize GitHub API client
        if github_token:
            github = Github(github_token)
        else:
            github = Github()

        try:
            # Parse the repository URL to extract owner and repo name
            parts = repo_url.strip("/").split("/")
            owner = parts[-2]
            repo_name = parts[-1]

            # Get the repository and its stargazers
            repository = github.get_repo(f"{owner}/{repo_name}")
            stargazers = []
            stargazers = [stargazer.login for stargazer in repository.get_stargazers()[:1000]]

        except Exception as e:
            print(f"Error: {str(e)}")

        df = pd.DataFrame({"github_username": stargazers})

        return df

functions/github_stargazers.py
pchunduri6/stargazers-reloaded
import pandas as pd
from github import Github
import time
import concurrent.futures

from evadb.catalog.catalog_type import ColumnType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import PandasDataframe


class GithubUserDetails(AbstractFunction):
    """
    Arguments:
        None

    Input Signatures:
        github_username (str) : The GitHub username for the user whose details you want to retrieve.
        github_token (str) : GitHub personal access token for authentication.

    Output Signatures:
        user_name (str) : The name of the GitHub user.
        user_login (str) : The login (username) of the GitHub user.
        user_following (int) : The number of users the GitHub user is following.
        user_followers (int) : The number of followers the GitHub user has.
        user_email (str) : The email address of the GitHub user.
        user_id (int) : The unique ID of the GitHub user.
        user_location (str) : The location of the GitHub user.
        user_bio (str) : The bio of the GitHub user.
        user_company (str) : The company associated with the GitHub user.
        user_blog (str) : The blog URL of the GitHub user.
        user_url (str) : The URL of the GitHub user's profile.
        user_twitter_username (str) : The Twitter username of the GitHub user.
        user_repos (list) : A list of dictionaries representing the user's repositories with 10+ stars.
        user_starred (list) : A list of dictionaries representing repositories starred by the user with 10+ stars.

    Example Usage:
        You can use this function to retrieve details about a GitHub user as follows:

        github_username = "username"
        github_token = "your_personal_access_token"
        cursor.function("GithubUserDetails", github_username, github_token)
    """

    @property
    def name(self) -> str:
        return "GithubUserDetails"

    @setup(cacheable=False, function_type="web-scraping")
    def setup(self) -> None:
        # Any setup or initialization can be done here if needed
        pass

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["github_username", "github_token"],
                column_types=[ColumnType.TEXT, ColumnType.TEXT],
                column_shapes=[(1,), (1,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=[
                    "user_name", "user_login", 
                    "user_following", "user_followers",
                    "user_email", 
                    "user_id", 
                    "user_location", "user_bio",
                    "user_company", "user_blog", "user_url", "user_twitter_username",
                    "user_repos", "user_starred",
                ],
                column_types=[
                    ColumnType.TEXT, ColumnType.TEXT,
                    ColumnType.INTEGER, ColumnType.INTEGER,
                    ColumnType.TEXT,
                    ColumnType.INTEGER,
                    ColumnType.TEXT, ColumnType.TEXT,
                    ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT,
                    ColumnType.TEXT,
                    ColumnType.TEXT,
                    ColumnType.TEXT
                ],
                column_shapes=[
                    (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,), (1,)
                    ],
            )
        ],
    )
    def forward(self, input_df):
        # Ensure the GitHub username is provided
        if input_df.empty or input_df.iloc[0, 0] is None:
            raise ValueError("GitHub username must be provided.")

        # Extract inputs from the DataFrame
        github_username = input_df.iloc[0, 0]
        github_token = input_df.iloc[0, 1]

        # Initialize GitHub API client
        if github_token:
            github = Github(github_token)
        else:
            github = Github()

        # Create an empty list to store user details
        user_details_list = []

        # Define a function to fetch user details for a range of rows
        def fetch_user_details_range(start_index, end_index):

            # Process a range of rows from start_index to end_index
            api_limit_error = False
            i = 0
            for index in range(start_index, end_index):
                i = i + 1

                # Avoid hitting API limit
                if i != 0 and i % 10 == 0:
                    print(f"Downloading details of user: {i}")
                    time.sleep(30)

                if api_limit_error == True:
                    api_limit_error = False
                    index = index - 1

                github_username = input_df.iloc[index]["github_username"]

                try:
                    # Retrieve the user object
                    user = github.get_user(github_username)

                    # Gather user details into separate variables
                    user_name = user.name
                    user_login = user.login
                    user_following = user.following
                    user_followers = user.followers
                    user_email = user.email
                    user_id = user.id
                    user_location = user.location
                    user_bio = user.bio
                    user_company = user.company
                    user_blog = user.blog
                    user_url = user.url
                    user_twitter_username = user.twitter_username

                    # Repos of user with 10+ stars
                    user_repos =  user.get_repos()
                    user_created_repos = []
                    for repo in user_repos:
                        if repo.fork is False:
                            if repo.stargazers_count > 10:
                                user_created_repos.append({
                                    repo.name,
                                    repo.description,
                                    repo.html_url,
                                    repo.language
                                })

                    # Repos starred by user with 10+ stars
                    starred_repos = user.get_starred()
                    user_starred_repos = []
                    j = 0
                    for repo in starred_repos:
                        j = j + 1
                        if j > 10:
                            break
                        if repo.stargazers_count > 100:
                            user_starred_repos.append({
                                repo.name,
                                repo.description,
                                repo.html_url,
                                repo.language
                            })

                    # Gather user details into a dictionary
                    user_details = {
                        "user_name": user.name,
                        "user_login": user.login,
                        "user_following": user.following,
                        "user_followers": user.followers,
                        "user_email": user.email,
                        "user_id": user.id,
                        "user_location": user.location,
                        "user_bio": user.bio,
                        "user_company": user.company,
                        "user_blog": user.blog,
                        "user_url": user.url,
                        "user_twitter_username": user.twitter_username,
                        "user_repos": f"{user_created_repos}",
                        "user_starred_repos": f"{user_starred_repos}"
                    }

                    # Append user details to the list
                    user_details_list.append(user_details)

                except Exception as e:
                    print(f"Error: {str(e)}")
                    api_limit_error = True
                    # sleep for 5 minutes
                    time.sleep(300)

        num_workers = 1
        num_rows = len(input_df)
        rows_per_worker = num_rows // num_workers

        print(f"Downloading details of {num_rows} users")

        # Create a list of tuples defining the ranges for each worker
        # Include any remaining rows in the last worker's range
        worker_ranges = [
            (i * rows_per_worker, (i + 1) * rows_per_worker) 
            for i in range(num_workers)
        ]
        worker_ranges[-1] = (worker_ranges[-1][0], num_rows)

        # Iterate over rows in the input DataFrame using ThreadPoolExecutor
        with concurrent.futures.ThreadPoolExecutor(max_workers=num_workers) as executor:
            for start_index, end_index in worker_ranges:
                executor.submit(
                    fetch_user_details_range, start_index, end_index
                )

        # Create a DataFrame from the list of user details
        user_details_df = pd.DataFrame(user_details_list)

        return user_details_df
functions/github_user_details.py
pchunduri6/stargazers-reloaded
import pandas as pd

from evadb.catalog.catalog_type import ColumnType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import PandasDataframe

class StringToDataframe(AbstractFunction):
    """
    Arguments:
        None

    Input Signatures:
        input_string (str) : A string containing structured data.

    Output Signatures:
        output_dataframe (DataFrame) : A DataFrame containing structured data.

    Example Usage:
        You can use this function to convert a structured string into a DataFrame.

        input_string = "Name: John\nAge: 30\nCountry: USA"
    """

    @property
    def name(self) -> str:
        return "StringToDataframe"

    @setup(cacheable=False)
    def setup(self) -> None:
        # Any setup or initialization can be done here if needed
        pass

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["extracted_text"],
                column_types=[ColumnType.TEXT],
                column_shapes=[(None,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=["name", "country", "city", "email",
                         "occupation",
                         "programming_languages", 
                         "topics_of_interest", 
                         "social_media"],
                column_types=[ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT, ColumnType.TEXT],
                column_shapes=[(None,), (None,), (None,), (None,), (None,), (None,), (None,), (None,)],
            )
        ],
    )
    def forward(self, input_df):
        # Ensure input is provided
        if input_df.empty or input_df.iloc[0] is None:
            raise ValueError("Input string must be provided.")

        # Initialize lists for columns
        keys_list = []
        values_list = []

        # Iterate over rows of the input DataFrame
        for _, row in input_df.iterrows():
            response = row["response"]

            # Split the input string into lines
            lines = response.strip().split("\n")

            # Initialize lists for columns in this row
            keys = []
            values = []

            # Parse the lines and extract key-value pairs
            for line in lines:
                parts = line.split(":")
                if len(parts) == 2:
                    key = parts[0].strip()
                    value = parts[1].strip()
                    keys.append(key)
                    values.append(value)
                if len(parts) > 2:
                    key = parts[0].strip()
                    # value = parts[1].strip() + parts[2].strip()
                    value = [parts[i].strip() for i in range(1, len(parts))]
                    value = "".join(value)
                    keys.append(key)
                    values.append(value)

            keys_list.append(keys)
            values_list.append(values)

        # Create a DataFrame from the parsed data
        output_dataframe = pd.DataFrame(values_list, columns=keys_list[0])

        return output_dataframe
functions/string_to_dataframe.py
pchunduri6/stargazers-reloaded
from selenium import webdriver
from selenium.webdriver.firefox.options import Options as FirefoxOptions
from selenium.webdriver.common.by import By
import concurrent.futures
import pandas as pd
import time
from evadb.catalog.catalog_type import ColumnType
from evadb.functions.abstract.abstract_function import AbstractFunction
from evadb.functions.decorators.decorators import forward, setup
from evadb.functions.decorators.io_descriptors.data_types import PandasDataframe

import easyocr
from tqdm import tqdm


reader = easyocr.Reader(["en"], gpu=True)


def scrape_user_page(url):
    try:
        options = FirefoxOptions()
        options.add_argument("--headless")

        driver = webdriver.Firefox(options=options)

        driver.set_window_size(1920, 1080)
        # Open the GitHub user page
        driver.get(f"https://github.com/{url}")
        # driver.execute_script("document.body.style.zoom='120%'")

        # Capture the user profile section
        user_info_blocks = []
        try:
            user_info_blocks.append(driver.find_element(By.CLASS_NAME, "h-card"))
        except:
            pass
        info_ids = ["user-profile-frame", "user-private-profile-frame"]
        for info_id in info_ids:
            try:
                user_info_blocks.append(driver.find_element(By.ID, info_id))
            except:
                pass

        extracted_text = ""
        for info_block in user_info_blocks:
            screenshot = info_block.screenshot_as_png
            # with torch.cuda.device(gpu_id):
            result = reader.readtext(screenshot, detail=0)
            for i in result:
                extracted_text += i + " "

        return extracted_text

    except Exception as e:
        print(f"Error for {url}: {str(e)}")
        return str(e)
    finally:
        driver.quit()


# Define a function to extract text from a set of URLs
def extract_text_from_url(url):
    try:
        # Scrape user page using Selenium and EasyOCR
        extracted_text = scrape_user_page(url)
    except Exception as e:
        error_msg = f"Error extracting text from {url}: {str(e)}"
        print(error_msg)
        return error_msg

    return extracted_text


class WebPageTextExtractor(AbstractFunction):
    """
    Arguments:
        None

    Input Signatures:
        urls (list) : A list of URLs from which to extract text.

    Output Signatures:
        extracted_text (list) : A list of text extracted from the provided URLs.

    Example Usage:
        You can use this function to extract text from a list of URLs like this:

        urls = ["https://example.com/page1", "https://example.com/page2"]
    """

    @property
    def name(self) -> str:
        return "WebPageTextExtractor"

    @setup(cacheable=False, function_type="web-scraping")
    def setup(self) -> None:
        # Any setup or initialization can be done here if needed
        pass

    @forward(
        input_signatures=[
            PandasDataframe(
                columns=["urls"],
                column_types=[ColumnType.TEXT],
                column_shapes=[(None,)],
            )
        ],
        output_signatures=[
            PandasDataframe(
                columns=["extracted_text"],
                column_types=[ColumnType.TEXT],
                column_shapes=[(None,)],
            )
        ],
    )
    def forward(self, input_df):
        # Ensure URLs are provided
        if input_df.empty or input_df.iloc[0] is None:
            raise ValueError("URLs must be provided.")

        print(input_df)

        # Extract URLs from the DataFrame
        urls = input_df["github_username"]

        # Use ThreadPoolExecutor for concurrent processing
        num_workers = 1
        # Note: CUDA errors in EasyOCR with more than 1 worker
        ## profiling
        # 1 worker: 218.00s
        # 4 workers: 147.44s
        # 8 workers: 134.55s
        # 12 workers: 149.89s

        num_urls = len(urls)

        print(f"Extracting text from {num_urls} URLs using {num_workers} workers")

        start = time.time()
        extracted_text_lists = []
        # Use ThreadPoolExecutor for concurrent processing
        with concurrent.futures.ThreadPoolExecutor(max_workers=num_workers) as executor:
            # Submit tasks to extract text from each URL
            extracted_text_lists = list(
                tqdm(executor.map(extract_text_from_url, urls), total=num_urls)
            )

        # Create a DataFrame from the extracted text
        extracted_text_df = pd.DataFrame({"extracted_text": extracted_text_lists})
        end = time.time()
        print("time taken: {:.2f}s".format(end - start))
        return extracted_text_df

functions/webpage_text_extractor.py
pchunduri6/stargazers-reloaded
#!/usr/bin/env python3
from dotenv import load_dotenv
import os
import pandas as pd
import evadb

pd.set_option("display.max_columns", None)  # Show all columns
pd.set_option("display.expand_frame_repr", False)
pd.set_option("display.max_colwidth", None)


if not load_dotenv():
    print(
        "Could not load .env file or it is empty. Please check if it exists and is readable."
    )
    exit(1)

# REPO DETAILS
repo_url = os.environ.get('REPO_URL')
github_pat = os.environ.get('GITHUB_API')

# Parse the repository URL to extract owner and repo name
parts = repo_url.strip("/").split("/")
repo_name = parts[-1]

DEFAULT_CSV_PATH = f"{repo_name}.csv"


if __name__ == "__main__":
    try:
        # establish evadb api cursor
        print("⏳ Connect to EvaDB...")
        cursor = evadb.connect().cursor()
        print("✅ Connected to EvaDB...")

        cursor.query(
            f"""
            CREATE OR REPLACE FUNCTION GithubStargazers
            INPUT (repo_url TEXT(1000), github_pat TEXT(1000))
            OUTPUT (github_username TEXT(1000))
            TYPE  Webscraping
            IMPL  'functions/github_stargazers.py';
        """
        ).df()

        cursor.query(
            f"""
            CREATE OR REPLACE FUNCTION WebPageTextExtractor
            INPUT (urls TEXT(1000))
            OUTPUT (extracted_text TEXT(1000))
            TYPE  Webscraping
            IMPL  'functions/webpage_text_extractor.py';
        """
        ).df()

        cursor.query(
            f"""
            CREATE OR REPLACE FUNCTION GithubUserdetails
            INPUT (github_username TEXT(1000), github_pat TEXT(1000))
            OUTPUT (
                user_name TEXT(1000),
                user_login TEXT(1000),
                user_following INTEGER,
                user_followers INTEGER,
                user_email TEXT(1000),
                user_id INTEGER,
                user_location TEXT(1000),
                user_bio TEXT(1000),
                user_company TEXT(1000),
                user_blog TEXT(1000),
                user_url TEXT(1000),
                user_twitter_username TEXT(1000),
                user_repos TEXT(1000),
                user_starred_repos TEXT(1000)
            )
            TYPE  Webscraping
            IMPL  'functions/github_user_details.py';
        """
        ).df()

        cursor.query(
            """
            CREATE OR REPLACE FUNCTION StringToDataframe
            INPUT (input_string TEXT(1000))
            OUTPUT (
                name TEXT(1000),
                country TEXT(1000),
                city TEXT(1000),
                email TEXT(1000),
                occupation TEXT(1000),
                programming_languages TEXT(1000),
                topics_of_interest TEXT(1000),
                social_media TEXT(1000)
            )
            TYPE  Webscraping
            IMPL  'functions/string_to_dataframe.py';
        """
        ).df()

        cursor.query(
            """CREATE OR REPLACE FUNCTION GPT35
                IMPL 'functions/chatgpt.py'
                MODEL 'gpt-3.5-turbo-16k'
            """
        ).df()

        cursor.query(
            """CREATE OR REPLACE FUNCTION GPT4
                IMPL 'functions/chatgpt_batch.py'
                MODEL 'gpt-4-0613'
            """
        ).df()

        print(
            cursor.query(
                f"""
           CREATE TABLE IF NOT EXISTS {repo_name}_StargazerList AS
           SELECT GithubStargazers("{repo_url}", "{github_pat}");
        """
            ).df()
        )

        select_query = cursor.query(
            f"SELECT * FROM {repo_name}_StargazerList;"
        ).df()

        print(select_query)

        print(
            cursor.query(
                f"""
           CREATE TABLE IF NOT EXISTS {repo_name}_StargazerDetails AS
           SELECT GithubUserdetails(github_username, "{github_pat}")
           FROM {repo_name}_StargazerList;
        """
            ).df()
        )

        select_query = cursor.query(
            f"""
        SELECT * FROM {repo_name}_StargazerDetails;
        """
        ).df()

        print(select_query)

        print(
            cursor.query(
                f"""
           CREATE TABLE IF NOT EXISTS {repo_name}_StargazerScrapedDetails AS
           SELECT github_username, WebPageTextExtractor(github_username)
           FROM {repo_name}_StargazerList;
        """
            ).df()
        )

        select_query = cursor.query(
            f"""
                SELECT *
                FROM {repo_name}_StargazerScrapedDetails;
        """
        ).df()

        print("Processing insights...")
        # cursor.query(f"DROP TABLE IF EXISTS {repo_name}_StargazerInsights;").df()

        LLM_prompt = """You are given a block of disorganized text extracted from the GitHub user profile of a user using an automated web scraper. The goal is to get structured results from this data.
                Extract the following fields from the text: name, country, city, email, occupation, programming_languages, topics_of_interest, social_media.
                If some field is not found, just output fieldname: N/A. Always return all the 8 field names. DO NOT add any additional text to your output.
                The topic_of_interest field must list a broad range of technical topics that are mentioned in any portion of the text.  This field is the most important, so add as much information as you can. Do not add non-technical interests.
                The programming_languages field can contain one or more programming languages out of only the following 4 programming languages - Python, C++, JavaScript, Java. Do not include any other language outside these 4 languages in the output. If the user is not interested in any of these 4 programming languages, output N/A.
                If the country is not available, use the city field to fill the country. For example, if the city is New York, fill the country as United States.
                If there are social media links, including personal websites, add them to the social media section. Do NOT add social media links that are not present.
                Here is an example (use it only for the output format, not for the content):

                name: logicx
                country: United States
                city: Atlanta
                email: abc@gatech.edu
                occupation: PhD student at Georgia Tech
                programming_languages: Python, Java
                topics_of_interest: Google Colab, fake data generation, Postgres
                social_media: https://www.logicx.io, https://www.twitter.com/logicx, https://www.linkedin.com/in/logicx
                """
        # GPT-35 fuzzy topics
        cursor.query(
            f"""
            CREATE TABLE IF NOT EXISTS {repo_name}_StargazerInsights AS
            SELECT StringToDataframe(
                GPT35("{LLM_prompt}", extracted_text
                )
            )
            FROM {repo_name}_StargazerScrapedDetails;
        """
        ).df()

        select_query = cursor.query(
            f"""
                SELECT *
                FROM {repo_name}_StargazerInsights;
        """
        ).df()

        print(select_query)

        select_query.to_csv(f"results/{repo_name}_insights_gpt35.csv", index=False)
        # cursor.query(f"DROP TABLE IF EXISTS {repo_name}_StargazerInsightsGPT4;").df()
        LLM_prompt = """You are given 10 rows of input, each row is separated by two new line characters.
                     Categorize the topics listed in each row into one or more of the following 3 technical areas - Machine Learning, Databases, and Web development. If the topics listed are not related to any of these 3 areas, output a single N/A. Do not miss any input row. Do not add any additional text or numbers to your output.
                     The output rows must be separated by two new line characters. Each input row must generate exactly one output row. For example, the input row [Recommendation systems, Deep neural networks, Postgres] must generate only the output row [Machine Learning, Databases].
                     The input row [enterpreneurship, startups, venture capital] must generate the output row N/A.
                     """

        cursor.query(
            f"""CREATE TABLE IF NOT EXISTS
                 {repo_name}_StargazerInsightsGPT4 AS
                    SELECT name,
                            country,
                            city,
                            email,
                            occupation,
                            programming_languages,
                            social_media,
                            GPT4("{LLM_prompt}", topics_of_interest)
                    FROM {repo_name}_StargazerInsights;
        """
        ).df()

        select_query = cursor.query(
            f"""
                SELECT *
                FROM {repo_name}_StargazerInsightsGPT4;
        """
        ).df()

        select_query.to_csv(f"results/{repo_name}_insights_gpt4.csv", index=False)

    except Exception as e:
        print(f"❗️ EvaDB Session ended with an error: {e}")

stargazers.py
pchunduri6/stargazers-reloaded
import os

import pandas as pd
from dotenv import load_dotenv
import numpy as np
import matplotlib.pyplot as plt
plt.rcParams['font.family'] = 'sans-serif'
plt.rcParams['font.sans-serif'] = 'DejaVu Sans Mono'

from wordcloud import WordCloud


if not load_dotenv():
    print(
        "Could not load .env file or it is empty. Please check if it exists and is readable."
    )
    exit(1)

# REPO DETAILS
repo_url = os.environ.get('REPO_URL')
# Parse the repository URL to extract owner and repo name
parts = repo_url.strip("/").split("/")
repo_name = parts[-1]


def plot_pie_chart(data, title, output_path):

    fig, ax = plt.subplots(figsize=(12, 8))

    # explode first slice if it is not Other
    explode = [0] * len(data)
    if data.index.tolist()[0] != "Other":
        explode[0] = 0.1

    colors = ["#005F73", "#AE2012", "#EE9B00", "#94D2BD"]
    colors_dict = {"Web development": colors[0], "Machine Learning": colors[1], "Databases": colors[2], "Other": colors[3]}
    colors = [colors_dict[x[0]] for x in data.index.tolist()]
    wedges, texts, autotexts = ax.pie(data, explode=explode, colors=colors, autopct=lambda pct: "{:.1f}%".format(pct),
                                    textprops=dict(color="w"), shadow=True, startangle=90)

    labels = [x[0] for x in data.index.tolist()]

    # if "Other is in the list, it will be placed at the end of the pie chart
    if "Other" in labels:
        order_indices = [labels.index(x) for x in labels if x != "Other"]
        other_index = labels.index("Other")
        order_indices.append(other_index)
    else:
        order_indices = list(range(len(labels)))

    ax.legend([wedges[x] for x in order_indices], [labels[x] for x in order_indices],
            loc="center left",
            fancybox=True, shadow=True,
            bbox_to_anchor=(1, 0, 0.5, 1), prop={"size": 18})

    # if any of the percentages is less than 10%, change the font size to 10
    textsize = 20
    for autotext in autotexts:
        if float(autotext.get_text().strip("%")) < 10:
            textsize = 15
            break
    # if the percentage is less  than 4%, don't show the percentage
    for autotext in autotexts:
        if float(autotext.get_text().strip("%")) < 4:
            autotext.set_visible(False)

    plt.setp(autotexts, size=textsize, weight="bold")

    ax.set_title(title, fontsize=20, weight="bold")

    # move title to the right
    ax.title.set_position([0.725, 0.5])
    ax.axis('equal')
    plt.tight_layout()
    fig.savefig(output_path, bbox_inches='tight')


def clean_topics_list(topics_list):
    # each row is a string of topics with various formats
    # extract the strings "Machine Learning", "Databases", and "Web Development" into a list
    # return the list
    output = []
    for row in topics_list:
        output_row = []
        if pd.isna(row):
            output_row.append("Other")
            output.append(output_row)
            continue
        if "Machine Learning".lower() in row.lower():
            output_row.append("Machine Learning")
        if "Databases".lower() in row.lower():
            output_row.append("Databases")
        if "Web development".lower() in row.lower():
            output_row.append("Web development")
        if len(output_row) == 0:
            output_row.append("Other")
        output.append(output_row)

    return output


if __name__ == '__main__':

    output_dir = "images"
    os.makedirs(output_dir, exist_ok=True)

    # 1. Visualize gpt-35 interests insights in a Word Cloud

    gpt35_insights_df = pd.read_csv(f"results/{repo_name}_insights_gpt35.csv", dtype=str)

    all_topics = gpt35_insights_df[f"{repo_name}_stargazerinsights.topics_of_interest"].tolist()
    all_topics = [x for x in all_topics if not pd.isna(x)]

    all_topics = pd.DataFrame(all_topics, columns=['Topics'])
    # Combine all topics into a single text
    all_topics_text = ', '.join(all_topics['Topics'].tolist())

    # Generate the word cloud
    wordcloud = WordCloud(width=1920, height=1080, background_color='white').generate(all_topics_text)
    # Display the word cloud
    plt.figure(figsize=(12, 8))
    plt.imshow(wordcloud, interpolation='bilinear')
    plt.title(f"Word Cloud of {repo_name} user interests", fontsize=20, weight="bold", loc="center", y=1.05)
    # move title to the right
    plt.axis('off')
    plt.tight_layout()
    plt.savefig(os.path.join(output_dir, f"{repo_name}_topics_wordcloud.png"), dpi=300)

    # 2. Visualize gpt-4 topic insights in a pie chart

    insights_df = pd.read_csv(f"results/{repo_name}_insights_gpt4.csv", dtype=str)
    topics_df = insights_df[f"{repo_name}_stargazerinsightsgpt4.response"].tolist()
    topics_list = clean_topics_list(topics_df)

    all_topics = []
    for topics in topics_list:
        all_topics.extend(topics)

    all_topics = pd.DataFrame(all_topics, columns=['Topics'])

    # Count the occurrences of each topic
    topic_counts = all_topics.value_counts()
    plot_pie_chart(data=topic_counts,
                   title=f"Topics of Interest Distribution for {repo_name} users",
                   output_path=os.path.join(output_dir, f"{repo_name}_topics_pie_chart.png"))
    print("Results saved to images folder.")

visualize_results.py
pchunduri6/stargazers-reloaded