fork download 
  1. import pandas as pd
  2.  
  3. # 1. DATA ENTRY
  4. # Raw data extracted from your screenshots
  5. data_weight = {
  6.     "Month": ["24-11", "24-12", "25-01", "25-02", "25-03", "25-04", "25-05", "25-06", "25-07", "25-08", "25-09", "25-10", "25-11", "25-12", "26-01"],
  7.     "0-1 KG": [4062, 3532, 4406, 4709, 6911, 6695, 3843, 7300, 4932, 6067, 3828, 7898, 3636, 4955, 3855],
  8.     "1-2 KG": [5164, 9193, 12083, 11147, 14293, 13358, 11079, 17301, 20253, 14370, 12808, 13615, 12068, 16299, 12746],
  9.     "2-3 KG": [20742, 14165, 15994, 18037, 22647, 15721, 18552, 22327, 21758, 15797, 23828, 17932, 15608, 26314, 33494],
  10.     "3-4 KG": [13908, 9391, 11256, 12037, 10433, 8372, 9539, 12262, 7563, 15472, 21274, 15590, 22965, 10197, 16520],
  11.     "4+ KG":  [195422, 183102, 204845, 114390, 130741, 84354, 91710, 56426, 64833, 65312, 108861, 89327, 76762, 104426, 106721],
  12.     "Grand Total Weight": [239296, 219383, 248584, 160320, 185025, 128500, 134721, 115616, 119338, 117018, 170599, 144361, 131038, 162191, 173336]
  13. }
  14.  
  15. data_qty = {
  16.     "Month": ["24-11", "24-12", "25-01", "25-02", "25-03", "25-04", "25-05", "25-06", "25-07", "25-08", "25-09", "25-10", "25-11", "25-12", "26-01"],
  17.     "0-1 PCS": [10954, 7170, 10253, 13118, 15957, 15808, 11001, 16819, 9717, 13952, 4969, 18975, 8024, 13199, 9786],
  18.     "1-2 PCS": [3664, 5953, 7989, 7371, 9254, 8856, 7411, 11557, 12707, 10078, 8153, 9431, 8274, 10901, 8262],
  19.     "2-3 PCS": [8670, 5797, 6503, 7382, 9479, 6693, 7891, 9851, 9063, 6668, 9744, 7538, 6668, 11088, 14439],
  20.     "3-4 PCS": [3819, 2634, 3148, 3396, 2871, 2391, 2647, 3400, 2162, 4351, 6001, 4323, 6348, 2954, 4455],
  21.     "4+ PCS":  [33622, 26756, 35461, 18661, 22436, 13749, 13331, 8991, 9897, 10159, 16774, 14259, 12270, 16107, 14798],
  22.     "Grand Total PCS": [60729, 48310, 63354, 49928, 59997, 47497, 42281, 50618, 43546, 45208, 45641, 54526, 41584, 54249, 51740]
  23. }
  24.  
  25. # 2. DATAFRAME CREATION
  26. df_weight = pd.DataFrame(data_weight)
  27. df_qty = pd.DataFrame(data_qty)
  28.  
  29. # 3. CALCULATED ANALYSIS (KPIs)
  30. # Create a new DataFrame for analysis
  31. df_analysis = pd.DataFrame()
  32. df_analysis["Month"] = df_weight["Month"]
  33. df_analysis["Total Weight (KG)"] = df_weight["Grand Total Weight"]
  34. df_analysis["Total Quantity (PCS)"] = df_qty["Grand Total PCS"]
  35.  
  36. # Calculate Average Weight per Piece (The key metric for management)
  37. df_analysis["Avg Weight/Piece (KG)"] = (df_analysis["Total Weight (KG)"] / df_analysis["Total Quantity (PCS)"]).round(2)
  38.  
  39. # Analyze the Mix % of Light vs Heavy Profiles
  40. df_analysis["Light Profiles (0-1kg) %"] = ((df_qty["0-1 PCS"] / df_qty["Grand Total PCS"]) * 100).round(1)
  41. df_analysis["Heavy Profiles (4kg+) %"] = ((df_qty["4+ PCS"] / df_qty["Grand Total PCS"]) * 100).round(1)
  42.  
  43. # Add comments based on thresholds
  44. def assess_status(row):
  45.     if row["Avg Weight/Piece (KG)"] < 2.8:
  46.         return "CRITICAL: Low Weight Mix"
  47.     elif row["Avg Weight/Piece (KG)"] > 3.5:
  48.         return "Good: Heavy Mix"
  49.     else:
  50.         return "Average"
  51.  
  52. df_analysis["Status"] = df_analysis.apply(assess_status, axis=1)
  53.  
  54. # 4. EXPORT TO EXCEL
  55. file_name = "Alumex_Production_Analysis.xlsx"
  56.  
  57. try:
  58.     with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
  59.         df_weight.to_excel(writer, sheet_name='Raw Data - Weight (KG)', index=False)
  60.         df_qty.to_excel(writer, sheet_name='Raw Data - Quantity (PCS)', index=False)
  61.         df_analysis.to_excel(writer, sheet_name='Management Analysis', index=False)
  62.  
  63.     print(f"Success! '{file_name}' has been created.")
  64.     print("This file contains three sheets:")
  65.     print("1. Raw Data - Weight (KG)")
  66.     print("2. Raw Data - Quantity (PCS)")
  67.     print("3. Management Analysis (includes Calculated Avg Weight)")
  68.  
  69. except Exception as e:
  70.     print(f"An error occurred: {e}")
Success #stdin #stdout 3.53s 79220KB
comments (?)
stdin 
Standard input is empty
stdout 
An error occurred: No module named 'openpyxl'
https://ideone.com/n66Uxl
language:
Python 3 (python  3.12)
created:
4 days ago
visibility:
public

Share or Embed source code

Discover > Sphere Engine API

The brand new service which powers Ideone!

Discover > IDE Widget

Widget for compiling and running the source code in a web browser!