Module my_autopylot.excel39

Functions

def authenticate_google_spreadsheet(credential_file_path='')

Description

Authenticates Google Spreadsheet.

Args

credential_file_path : str, optional
Path of credential file. Defaults to "".

Returns

[status, data] status (bool): Whether the function is successful or failed. data (object): Google Spreadsheet Auth object.

def dataframe_to_excel(df, output_folder='', output_filename='', output_sheetname='Sheet1', mode='a')

Description

Converts dataframe to excel

Args

df : pandas dataframe
Dataframe of the excel file.
output_folder : str, optional
Folder path of the output file. Defaults to "".
output_filename : str, optional
Filename of the output file. Defaults to "".
output_sheetname : str, optional
Sheetname of the output file. Defaults to "Sheet1".
mode : str, optional
Mode of the output file. Defaults to "a" or "x"

Returns

[status] status (bool): Whether the function is successful or failed.

def df_convert_column_to_type(df, column_name: str, column_type: str)

Description

Converts a column type of a dataframe to a given type Column type doesn't persist after writing to excel

Args

df : dataframe

column_name : Single column name or list of column names

column_type : column type to be converted to like string, int, float, date, boolean, complex, bytes, etc.

Returns

[data]
data
The modified dataframe object
def df_drop_rows(df, row_start: int, row_end: int)

Description

Drops a range of rows from a dataframe including the row_start and row_end rows.

Args

df : dataframe row_start : start row number row_end : end row number

Returns

[data]
data
dataframe object
def df_extract_sub_df(df, row_start: int, row_end: int, column_start: int, column_end: int)

Description

Extracts a sub dataframe from a dataframe

Args

df : dataframe row_start : start row number row_end : end row number column_start : start column number column_end : end column number

Returns

[data]
data
dataframe object
def df_from_list(list_of_lists, column_names=None)

Description

Creates a dataframe from a list of lists

Args

list_of_lists : list of lists column_names : list of column names

Returns

[data]
data
dataframe object
def df_from_string(df_string: str, word_delimeter=' ', line_delimeter='\n', column_names=None)

Description

Creates a dataframe from a string

Args

df_string : string word_delimeter : word delimeter line_delimeter : line delimeter column_names : list of column names

Returns

[data]
data
dataframe object
def df_vlookup(df1, df2, column_name: str, how: str = 'left')

Description

Performs vlookup on 2 dataframes

Args

df1 : dataframe df2 : dataframe column_name : column name to perform vlookup on how : how to perform vlookup like inner, left, right, outer

Returns

[data]
data
The modified dataframe object
def excel_apply_format_as_table(input_filepath='', table_style='TableStyleMedium21', input_sheetname='Sheet1')

Description

Applies table format to the used range of the given excel. Just it takes an path and converts it to table here you can change the table style below. if you want to change the table style just change the styles by refering excel

Args

input_filepath : path of the excel file table_style : table style to be applied input_sheetname : sheet name of the excel file

Returns

[status]
status
True if the function is successful, False otherwise
def excel_apply_template_format(input_filepath='', input_sheetname='Sheet1', input_template_filepath='', input_template_sheetname='Sheet1', same_file=True, output_folder='', output_filename='')

Description

Converts given excel to Template Excel This function uses pandas and just write the required columns to new excel. if you don't know columns, just pass the excel file which have the columns you want it automatically makes own list and remove other columns.

Args

input_filepath : path of the excel file input_sheetname : sheet name of the excel file input_template_filepath : path of the template excel file input_template_sheetname : sheet name of the template excel file same_file : if True, then the output excel file will be same as the input excel file. output_folder : folder path where the output excel file will be saved. output_filename : name of the output excel file.

Returns

[status]
status
True if the function is successful, False otherwise
def excel_clear_sheet(df)

Description

Clears the contents of given excel files keeping header row intact

Args

df : dataframe

Returns

[status, data]
status
True if the function is successful, False otherwise
data
dataframe with the cleared contents
def excel_concat_all_sheets_of_given_excel(excel_file_path, sheet_names_as_list=None)

Description

Concatenates all sheets of an excel file

Args

excel_file_path : excel file path

Returns

[data]
data
dataframe object
def excel_copy_range_from_sheet(input_filepath='', input_sheetname='Sheet1', start_row=1, start_col=1, end_row=1, end_col=1)

Description

Copies the specific range from the provided excel sheet and returns copied data as a list

Args

input_filepath :"Full path of the excel file with double slashes" input_sheetname :"Source sheet name from where contents are to be copied" start_col :"Starting column number (index starts from 1) from where copying starts" start_row :"Starting row number (index starts from 1) from where copying starts" end_col :"Ending column number ex:4 upto where cells to be copied" end_row :"Ending column number ex:5 upto where cells to be copied"

Returns

[status, data] status (bool): Whether the function is successful or failed. data (list): Range of cells as a list.

def excel_create_file(output_folder='', output_filename='', output_sheetname='Sheet1')

Description

Creates an Excel file.

Args

output_folder : str, optional
Folder where file will be created. Defaults to "".
output_filename : str, optional
Name of file. Defaults to "".
output_sheetname : str, optional
Name of sheet. Defaults to "Sheet1".

Returns

[status] status (bool): Whether the function is successful or failed.

def excel_drop_columns(df, cols='')

Description

Drops the desired column from the given excel file

Parameters

df : dataframe cols : column names to be dropped

Returns

[status, data]
status
True if the function is successful, False otherwise
data
dataframe with the dropped columns
def excel_get_all_header_columns(df)

Description

Gives you all column header names of the given excel sheet.

Args

df : pandas dataframe
Dataframe of the excel file.

Returns

[status, data] status (bool): Whether the function is successful or failed. data (list): List of all column header names of the excel file.

def excel_get_all_sheet_names(input_filepath='')

Description

Gives you all names of the sheets in the given excel sheet.

Parameters

input_filepath (str) : Path of the excel file.

returns : [status, data] status (bool): Whether the function is successful or failed. data (list): List of all sheet names of the excel file.

def excel_get_dataframe_from_google_spreadsheet(auth, spreadsheet_url='', sheet_name='Sheet1')

Description

Get dataframe from google spreadsheet.

Args

URL : str, optional
(Only in Windows)Name of Window you want to activate.
Eg
Notepad. Defaults to "".

Returns

[status, data] status (bool): Whether the function is successful or failed. data (object): Dataframe object.

def excel_get_row_column_count(df)

Description

Returns the row and column count of the dataframe.

Args

df : pandas dataframe
Dataframe of the excel file.

Returns

[status, data] status (bool): Whether the function is successful or failed. data (list): [row_count, column_count]

def excel_get_single_cell(df, header=1, column_name='', cell_number=1)

Description

Gets the text from the desired column/cell number of the given excel file

Args

df : pandas dataframe
Dataframe of the excel file.
header : int, optional
Header of the excel file. Defaults to 0.
column_name : str, optional
Column name of the excel file. Defaults to "".
cell_number : int, optional
Cell number of the excel file. Defaults to 0.

Returns

[status, data] status (bool): Whether the function is successful or failed. data (str): Data from the desired column/cell number of the excel file.

def excel_group_by_column_values_n_split(df, column_name='', output_folder='', output_filename='', show_output=False)

Description

This function groups the dataframe by the given column and splits the dataframe into multiple dataframes.

Parameters

df : dataframe column_name : column name to be grouped output_folder : folder path to save the split dataframes output_filename : filename to save the split dataframes

Returns

[status]
status
True if the function is successful, False otherwise
def excel_if_value_exists(df, cols='', value='')

Description

Check if a given value exists in given excel. Returns True / False

Args

df : dataframe cols : column name from which the value is to be checked value : value to be checked

Returns

[status]
status
True if the value exists, False otherwise
def excel_merge_all_files(input_folder_path='', output_folder='', output_filename='')

Description

Merges all the excel files in the given folder

Args

input_folder_path :"Full path of the folder with double slashes" output_folder :"Full path of the folder with double slashes" output_filename :"Filename to save the merged excel file"

Returns

[status]
status
True if the function is successful, False otherwise
def excel_paste_range_to_sheet(input_filepath='', input_sheetname='Sheet1', start_row=1, start_col=1, copied_data=[])

Description

Pastes the copied data in specific range of the given excel sheet.

Args

input_filepath :"Full path of the excel file with double slashes" input_sheetname :"Source sheet name from where contents are to be copied" start_col :"Starting column number (index starts from 1) from where copying starts" start_row :"Starting row number (index starts from 1) from where copying starts" copied_data :"The copied data to be pasted"

Returns

[status, data] status (bool): Whether the function is successful or failed. data (list): Range of cells as a list.

def excel_remove_duplicates(df, column_name='')

Description

Drops the duplicates from the desired Column of the given excel file

Args

df : dataframe column_name : column name from which duplicates are to be removed

Returns

[status, data]
status
True if the function is successful, False otherwise
data
dataframe with the duplicates removed
def excel_set_single_cell(df, column_name='', cell_number=1, text='')

Description

Writes the given text to the desired column/cell number for the given excel file

Args

df : pandas dataframe
Dataframe of the excel file.
column_name : str, optional
Column name of the excel file. Defaults to "".
cell_number : int, optional
Cell number of the excel file. Defaults to 1.
text : str, optional
Text to be written to the excel file. Defaults to "".

Returns

[status, data] status (bool): Whether the function is successful or failed. data (df): Modified dataframe

def excel_tabular_data_from_website(website_url='', table_number='')

Description

Gets Website Table Data Easily as an Excel using Pandas. Just pass the URL of Website having HTML Tables.

Args

website_url : str, optional
URL of Website. Defaults to "".
table_number : int, optional
Table Number. Defaults to all.

Returns

[status, data] status (bool): Whether the function is successful or failed. data (object): Dataframe object.

def excel_to_dataframe(input_filepath='', input_sheetname='Sheet1', header=1)

Description

Converts excel to dataframe

Args

input_filepath (str) : Complete path to the excel file. input_sheetname (str) : Sheet name of the excel file. header (int) : Row number of the header.

Returns

[status, data] status (bool): Whether the function is successful or failed. data (pandas dataframe): Dataframe of the excel file.

def excel_upload_dataframe_to_google_spreadsheet(auth, spreadsheet_url='', sheet_name='Sheet1', df='')

Description

Uploads dataframe to google spreadsheet.

Args

URL : str, optional
(Only in Windows)Name of Window you want to activate.
Eg
Notepad. Defaults to "".

Returns

[status] status (bool): Whether the function is successful or failed.

def get_value_in_df(df, row_number: int, column_number: int)

Description

Gets a value from a dataframe

Parameters

df : dataframe row_number : row number column_number : column number

Returns

[data]
data
value from dataframe
def isNaN(value='')

Description

Returns TRUE if a given value is NaN False otherwise

Parameters

value : value to be checked

Returns

[status]
status
True if the value is NaN, False otherwise
def set_value_in_df(df, row_number: int, column_number: int, value)

Description

Sets a value in a dataframe

Args

df : dataframe row_number : row number column_number : column number value : value to be set

Returns

[data]
data
dataframe object