Excel data processing on Python

Question

I am new to phython and have very limited knowledge of it. I have a work related problem which i want to solve with python but i am not sure where to start.

Basically i have a list of over 2500 products that are stored in multiple branches. In some branches products are selling well whereas in others there is no demand and a risk of writing off stock if action is not taken ontime.

I have attached picture for example. I want to load data from excel and program in python, so it can recommend me materials and quantities to move from slow moving branches to branches that have demand.

Data in orange is plant requirement and data in green is excess which i am intending to move to plant that has demand.

Appreciate if you can help.

enter image description here

Answer 1

You might find the Pandas library useful. It would allow you to:

• import data from Excel with relative ease;
• do all kinds of slicing and statistical analysis;
• create basic visualizations;
• reshape data, and combine data from different sources;
• and even prepare data for use in machine learning models!

Pandas is quite feature-rich, well-documented and user friendly.

Alternatively, if you need a lower-level interface, you can check out xlrd for older *.xls files or openpyxl (tends to work better, but only supports Excel 2010-present *.xlsx format).

Answer 2

openpyxl is my favorite library for excel-python process. I've used it into my company project for importing and exporting data from towards excel.

Python library for reading and writing Excel (with extension xlsx/xlsm/xltx/xltm) files.

First, to install this package, you need to terminate this command:

sudo pip3 install openpyxl

Let's give you an example for how it works.

Input Excel File

Python Code

Print the first column value

# importing openpyxl module 
import openpyxl 
  
# Give the location of the file 
path = "C:\\Users\\Admin\\Desktop\\demo.xlsx"
  
# workbook object is created 
wb_obj = openpyxl.load_workbook(path) 
  
sheet_obj = wb_obj.active 
m_row = sheet_obj.max_row 
  
# Loop will print all values 
# of first column  
for i in range(1, m_row + 1): 
    cell_obj = sheet_obj.cell(row = i, column = 1) 
    print(cell_obj.value)

OUTPUT

STUDENT 'S NAME
ANKIT RAI
RAHUL RAI
PRIYA RAI
AISHWARYA
HARSHITA JAISWAL

Reference: Reading an excel file using Python openpyxl module