a.k.a Debarred-List-Automater 🤖
Introducing PyDebarList: Effortlessly generate debarred student lists with Python. This streamlined tool leverages Python's efficiency to automate the process, ensuring accuracy and saving time. With user-friendly inputs, PyDebarList swiftly compiles and exports comprehensive rosters. Say goodbye to manual entry and hello to precise results, all thanks to the power of Python.
To implement a python program which automates the process of finding/selecting the data lying in the specified range and traversing them into a new sheet of the same Excel (.xlsx file)
This is a Python Code which will
a) traverse an ExcelFile (format.xlsx)
b) sort the data according to the range such that eg: (80<x<90)
c) now this sorted data is been appended on new sheet of the same (format.xlsx) file according to the mentioned range
d) the index of entries is also been maintained in a separate sheet called "Index". Where we have total number of entries & students
This Program was implemented by us which is used by my College for obtaining debarred students details by setting range to below 75 % ( Attendance Percentage ) from large student records so that the desired work is done by one click👆 ( just entering the file location & range ) Also to avoid time consumption and human errors while handling such large Datasets
This was a task assigned by our faculties where they wanted us to work on this project round the clock 🕒
Things were not easy it took us nearly 5 days to complete the code & achieve the solution for the problem statement
Actually this task made us more flexible toward time limit and managing the same to achieve our final goal 🥅
The Excel file that is supposed tp be used in this program should follow the Columnns Format exactly mentioned in the format xlsx file in the files section
Excel file before using the program 📁
Input & Output Terminal 🔳
Generated File after using the program 📁
Program's Generated Sheet in same excel file 📃
- python 3.9
- pandas Library
- tracemalloc library
- This Program will only work for the specified format file (format.xlsx).
- File generated By this program in .xlsx format cannot be opened using MS Excel As the generated file violates the File integrity policy of MS Excel.
- So to overcome this you can use google spreadsheet website i.e (Google Sheets)
- Now open generated .xlsx file in google sheets website to view the changes made by the python code
**The Generated file from this program cannot be opened in MS Excel as excel software fails to interpret howw the file is been modifiled hence resulting a File integrity policy violation
This policy violation corrupts the generated file but on other hand the same file can be opened in google sheets with no issue . So using google sheet will not cause any problem with this program . Hence Google Sheet is recommended to open the generated excel file
**
Availed good gripe on python pandas library
Acquired Skills in Handling excel dataset as DataFrame
Logic Building & Problem Solving
Suresh Mahalingam Konar
Manish Prasad
Plan to host the code over cloud editors like Google Collabs etc such that
- No Hardware limitation is achieved 👍
- The whole code will be available across all the platforms 👨💻
- The program no longer needs to be installed on local machines 💻
- Rectifying the MS Excel's Integrity policy while opening the modified file generated by the program
- No Hardware Limaitation Achieved By using GOOGLE COllAB
- Due to the usage of google collab the project versatility on various platforms is achieved
- Use of Google Collab has reduced the dependency on local machines 🖥️
Step 1 : Open Google Collab in any Web Browser. 🔗https://colab.research.google.com/ Step 2 : Upload the provided “debarred_analysis.ipynb” file through the upload tab in google collab
Step 3 : You will be able to see the notebook interface as shown below.
Step 4 : Now Upload the .xlsx i.e MS Excel file in the folder section at left sidebar panel.
** Note : Make sure that Excel File has same format as the “ Format.xlsx “ file included Particularly Make sure the following fields should be named as a) “ATTENDANCE PERCENTAGE” ( Attendance percentage of a student. ) b) “REG. NO.” ( Registration number of the concerned student. )**
Step 5 : Run the first cell for installing the prerequisites of the program.
Step 6 : After successful execution of the first cell ( “ Installed kernelspec py38, “ Refer the image below) . Reload the tab by using ctrl + R so that the prerequisites are installed properly.
Step 7 : Now , Run the Second cell to make sure that the required python version is running. Refer the Image below
Step 8 : Now , Run the 3rd & 4th cell to upgrade pandas library and install openpyxl library
Step 9 : Since, all the prerequisites necessary for the program are Successfully installed . Next run the 5th cell which contains the debarred analysis program
Step 10 : On running the 5th cell, you will be prompted to enter the file path of the excel file on which to operate. If you successfully uploaded the file according to the previous steps, just enter the filename of the uploaded excel file.
Step 11 : Now , Enter the attendance percentage range separated by space (Refer the image below.)
This will create separate sheets of student entries with following criteria:
- Attendance < 65%
- Attendance < 70%
- Attendance < 75%
- Attendance < 65% and Overall Avg Attendance < 65%
- Attendance < 70% and Overall Avg Attendance < 70%
- Attendance < 75% and Overall Avg Attendance < 75% Including an index sheet describing the count of students in all the above created sheets
Step 12 : Upon Successful execution (approx. 20 - 25 mins depending on dataset size), the uploaded .xlsx file will be updated with new sheets as described above. The user can download the same file (demo.xlsx for the current example) which they uploaded from the left side pane of google collab, and view it in 🔗https://docs.google.com/spreadsheets/u/0/?tgif=d
Alert : Since the file generated by the program is against MS excel software integrity policy due to the changes made to the data of the .xlsx file . The generated file will not be opened in MS excel . If opened It would say “ File is corrupted “. Hence we recommend using Google sheet to view the generated file.