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