python - Mindstorm

DATA ANALYSIS September 29, 2023

Sales Analysis with Python and Excel

Use ‘Sheet1’ (data) in excel file: sales_jan_feb_mar_2021_d.xlsx and do the following: 1) Read all columns of the excel file: sales_jan_feb_mar_2021_d.xlsx – 9 columns 2) Read specific columns of the excel file (Month, Actual_Sales, Customer, Salesperson): sales_jan_feb_mar_2021_d.xlsx – 4 columns 3) Create new excel file: Actual_Sales Mean by Salesperson.xlsx 4) Create new excel file: Actual_Sales Mean by Customer.xlsx 5) Create new excel file: Actual_Sales Mean by Month.xlsx 6) Create new tab: ActSalesMean by Salesperson in existing excel file: sales_jan_feb_mar_2021_d.xlsx and find actual sales mean by salesperson 7) Create new tab: ActSalesMean by Customer in existing excel file: sales_jan_feb_mar_2021_d.xlsx and find actual sales mean by customer 8) Create new tab: ActSalesMean by Month in existing excel file: sales_jan_feb_mar_2021_d.xlsx and find actual sales mean by month 9) Plot Actual_Sales Mean for 3, 4 and 5 10) Change styling of columns (font, alignment) for tabs: ‘ActSalesMean by Salesperson’, ‘ActSalesMean by Customer’, ‘ActSalesMean by Month’…

DATA ANALYSIS August 14, 2023

Use of chatGPT and YouTube to become a Data Analyst

Use of chatGPT and YouTube to become a Data Analyst – Χρήση του chatGPT και του YouTube για να κάνει κάποιος ανάλυση δεδομένων Code file: app.py Folder: C:\PythonPrograms\data_analysis_with_chatGPT 1) Merge multiple excel files – Συγχώνευση πολλών αρχείων excel data1.xlsx data2.xlsx data3.xlsx data4.xlsx 2) Clean data – Καθαρισμός δεδομένων 3) Create two excel reports (Total Revenue, Expenses and Profit by Category) and (Total Revenue, Expenses and Profit by Country) – Δημιουργία δύο αρχείων excel (category_report.xlsx and country_report.xlsx) 4) Add a chart to category_report.xlsx – Προσθήκη γραφήματος στο αρχείο excel category_report.xlsx 5) Add a chart to country_report.xlsx – Προσθήκη γραφήματος στο αρχείο excel country_report.xlsx 6) Build two interactive plots (category_chart.html and country_chart.html) 7) Create a streamlit dashboard with 2 graphs – Δημιουργία ενός πίνακα προβολής και διαχείρισης streamlit με 2 γραφήματα με τη βοήθεια της γλώσσας Python – with Python. Changed code by Trifonas Papadopoulos: #Code starts…

PYTHON August 6, 2023

Python and Excel

Scenario for using Python and Excel Request 1 Take the following 3 excel files: sales_jan_2022.xlsx sales_feb_2022.xlsx sales_mar_2022.xlsx and create a new excel: sales.xlsx and a new csv file: sales.csv with all the records from these 3 excel files. Request 2 Take the following 3 excel files: sales_jan_2022.xlsx sales_feb_2022.xlsx sales_mar_2022.xlsx and create a new excel: total_sales.xlsx and a new csv file: total_sales.csv with total of Actual_Quantity for each month. Request 3 Take the following 3 excel files: sales_jan_2022.xlsx sales_feb_2022.xlsx sales_mar_2022.xlsx and create a new excel: total_sales_by_salesperson.xlsx and a new csv file: total_sales_by_salesperson.csv with total of Actual_Sales per month for each salesperson. Request 1, 2 and 3 are completed with python script: main.py. See it in action . . . (watch the video below) Request 4 Take the following excel files: sales.xlsx total_sales_by_salesperson.xlsx and change the styling so it is easier to read them. Request 4 is…

ALL July 3, 2023

How to Create an Excel Data Entry Form in 10 Minutes Using Python (No VBA) | Easy & Simple

Did you know you can use Python code to create an Excel Data Entry Form and avoid duplicate records? Πως να δημιουργήσετε μια φόρμα εισαγωγής δεδομένων σε 10 λεπτά με τη γλώσσα προγραμματισμού PYTHON (No VBA) Πως να διασφαλίσετε ότι δεν θα κάνετε διπλοεγγραφές Excel fields : Name, City, Favorite Color, German, Spanish, English, Children Excel before data entry Data entry form Excel after data entry ‘Python code starts here. import PySimpleGUI as sg import pandas as pd # Add some color to the window sg.theme(‘DarkTeal9’) EXCEL_FILE = ‘data_entry.xlsx’ df = pd.read_excel(EXCEL_FILE) layout = [ [sg.Text(‘Please fill out the following fields:’)], [sg.Text(‘Name’, size=(15,1)), sg.InputText(key=’Name’)], [sg.Text(‘City’, size=(15,1)), sg.InputText(key=’City’)], [sg.Text(‘Favorite Color’, size=(15,1)), sg.Combo([‘Green’, ‘Blue’, ‘Red’], key=’Favorite Color’)], [sg.Text(‘I speak’, size=(15,1)), sg.Checkbox(‘German’, key=’German’), sg.Checkbox(‘Spanish’, key=’Spanish’), sg.Checkbox(‘English’, key=’English’)], [sg.Text(‘No. of Children’, size=(15,1)), sg.Spin([i for i in range(0,16)], initial_value=0, key=’Children’)], [sg.Submit(), sg.Button(‘Clear’), sg.Exit()] ] window = sg.Window(‘Simple data entry form’, layout) def clear_input(): for key…

ALL October 4, 2022

How to select rows and columns in pandas (Python)

Πως να επιλέγετε γραμμές και στήλες με τη βιβλιοθήκη pandas της γλώσσας προγραμματισμού Python See below the data and the code for the result shown on image . . . How to select rows and columns in pandas # Credit : Trifonas Papadopoulos, Oct 4th, 2022, Version 2.0 # start of code —————————————————————————————– import pandas as pd import numpy as np # data data = [[‘A0001′,”,”,”,0,’variable’,0,0], [‘A0001′,’WHITE’,’S’,’Small’,1,’variation’,0,0], [‘A0001′,’WHITE’,’M’,’Medium’,2,’variation’,0,0], [‘A0001′,’WHITE’,’L’,’Large’,3,’variation’,0,0], [‘A0001′,’WHITE’,’XL’,’Extra Large’,4,’variation’,0,0], [‘A0001′,’BLACK’,’L’,’Large’,5,’variation’,0,0], [‘A0003′,”,”,”,6,’variable’,0,0], [‘A0003′,’BLACK’,’XL’,’Extra Large’,7,’variation’,0,0], [‘A0002′,”,”,”,8,’variable’,0,0], [‘A0002′,’BLACK’,’L’,’Large’,9,’variation’,0,0], [‘A0002′,’BLACK’,’XL’,’Extra Large’,10,’variation’,0,0], [‘A0004′,”,”,”,12,’variable’,0,0], [‘A0004′,’WHITE’,’S’,’Small’,12,’variation’,0,0], [‘A0004′,’RED’,’S’,’Small’,13,’variation’,0,0], [‘A0004′,’RED’,’M’,’Medium’,14,’variation’,0,0], [‘A0001′,’WHITE’,’XXL’,’2 Extra Large’,15,’variation’,0,0], ] # print list of columns print(‘–list of columns———————————————————–‘) cols = list(df.columns.values) print(cols) # dataframe with defined column names df = pd.DataFrame(data, columns=[‘parent’, ‘color’, ‘size’, ‘size_desc’, ‘order’, ‘type’, ‘quantity’,’price’]) print(”) print(‘–dataframe with defined column names—————————————‘) print(df) # end of code —————————————————————————————– If you want to see more ways to select rows and columns in pandas (Python), read my code (Version…

ALL August 18, 2022

Data Analysis of Bugdet and Actual Sales with Python

Excel file 1 : sales_january_2021_d.xlsx – Download : https://lnkd.in/dyT6zrCX Excel file 2 : sales_feb_2021_d.xlsx – Download : https://lnkd.in/d5aaNWiq Excel file 3 : sales_march_2021_d.xlsx – Download : https://lnkd.in/d-G2hNiM Task Collection of data from the above mentioned three different excel files and creation of a new one : sales_jan_feb_mar_2021_d.xlsx – Download : https://lnkd.in/d-PTEPtv with three different sheets : sales_data month_productivity salesperson_productivity We did a comparison between Budget and Actual Sales. The reason we did this is to know the month productivity and the salesperson productivity for months Jan, Feb and Mar 2021. All sheets, data, graphs and styling in file : sales_jan_feb_mar_2021_d.xlsx have been created automatically with python coding. Tools used : Excel, Python #programming #coding #python #productivity #data_analysis #mindstormGR / www.mindstorm.gr Code for this project from ctypes.wintypes import WORD import pandas as pd import numpy as np import matplotlib.pyplot as plt importopenpyxl from openpyxl.styles import PatternFill, Border, Side, colors, Alignment, Protection,…