Matrix sorting and graphical analysis

Hello I am not very good at Matlab but I am having a problem figuring out how to code.

So basically I have a excel document with 18+ columns but only the 1st 2nd and 5th column I care about. These columns are Date (in M/D/Y), Shift (1,2, or 3 as an integer), and Net Weight (integer). I want to figure out the total weights for each shift on each day to then put into a line chart. So there would be 3 lines on the chart, the x-axis being date and y-axis being weight.

This needs to be done for 5 different machines in the same excel file but on different workbooks. I am just trying to figure it out for one machine right now. here is the code for my excel data

wt136 = sortrows(xlsread('Waste Tracking.xlsx', '3131-136'),[1 2]);

This however doesn't give me the dates that are in the 1st column of the excel spreadsheet (if anyone knows why please lmk, matlab doesn't even have NaN's in its place just deleted the entire row) so I made another column converting the dates to #'s in excel -- this worked.

But after this stage I am not sure how to procede. I know I should use loops, but can't wrap my head around where to start/where to go. I was thinking of iterating and checking if the dates matched the previous dates for each row but then didn't know how to go after that.

Answer 1

[Michael Spivak]

Alright so I was able to finish this myself, here is the code for anyone else who comes across this and might have ways to improve it.

1. Main problem is in the while loop, shift1/2/3 change size with every loop, which will slow down the program over time and as data set gets bigger (no fix as of yet)

%initializing the data and sorting it by date then shift
%FUTURE CHANGES -- change the format so that it doesn't have to rewrite shit1-3
%as this can cause slow speeds of processing

wt136 = sortrows(xlsread('Waste Tracking.xlsx', '3131-136'),[1 2]);
wt134 = sortrows(xlsread('Waste Tracking.xlsx', '3121-134'),[1 2]);
wt122 = sortrows(xlsread('Waste Tracking.xlsx', '4561-122'),[1 2]);
wtevol = sortrows(xlsread('Waste Tracking.xlsx', 'EVOL'),[1 2]);
wtdro = sortrows(xlsread('Waste Tracking.xlsx', 'DRO'),[1 2]);
wtcorr = sortrows(xlsread('Waste Tracking.xlsx', 'Corrugator'),[1 2]);
names = sheetnames('Waste Tracking.xlsx');

for z = 1:6
index = 1;
index2 = 1;
shift1 = [];
shift2 = [];
shift3 = [];
s1i = 1;
s2i = 1;
s3i = 1;
if z == 1
    a = wt136;
elseif z == 2
    a = wt136;
elseif z == 3
    a = wt122;
elseif z == 4
    a = wtevol;
elseif z ==5
    a = wtdro;
else
    a = wtcorr;
end
shift1T = zeros(length(a(:,1)),2);
shift2T = zeros(length(a(:,1)),2);
shift3T = zeros(length(a(:,1)),2);

while(~isnan(a(index)))
    phold = a(index,1);
    phold2 = phold;
    
    while(phold2 == phold)
        
        if a(index,2) == 1  %adding together the total for the day for each shift
            
            shift1(s1i) = (a(index,5));
            s1i = s1i +1;
            
        elseif a(index,2) == 2
            
            shift2(s2i) = (a(index,5));
            s2i = s2i +1;
            
        elseif a(index,2) == 3
            
            shift3(s3i) = (a(index,5));
            s3i = s3i +1;
            
        end
        
        index = index +1;%increasing index of data value in spreadsheet
        phold2 = a(index,1);
        
    end
    
    shift1T(index2,:) = [a(index-1,1),sum(shift1)];
    shift2T(index2,:) = [a(index-1,1),sum(shift2)];
    shift3T(index2,:) = [a(index-1,1),sum(shift3)];
    
    shift1 = [];%reseting back to 0 so that days don't add together
    shift2 = [];
    shift3 = [];
    
    index2 = index2 +1;
end

%this gets rid of unnessesary 0 columns in the data
shift1T = shift1T(~all(shift1T == 0, 2),:);
shift2T = shift2T(~all(shift2T == 0, 2),:);
shift3T = shift3T(~all(shift3T == 0, 2),:);

%converting date numbers to dates, gets the first and last date
d1 = datetime(shift1T(1,1), 'ConvertFrom', 'datenum', 'Format', 'MM-dd-yy');
c = length(shift1T(:,1));
d2 = datetime(shift1T(c,1), 'ConvertFrom', 'datenum', 'Format', 'MM-dd-yy');
d = d1:d2;

%PLOTTING TIME
figure(z);
grid on
hold on
plot(d,shift1T(:,2), '-s');
plot(d,shift2T(:,2),'-s');
plot(d,shift3T(:,2), '-s');
xlabel("Date");
ylabel("Waste (lbs)");
title("Waste per Day per Shift for "+names(z+1)+"");
legend('Shift 1', 'Shift 2', 'Shift 3', 'Location', 'northwest');
end

[Michael Spivak]

I know I can clean it up by getting rid of some variables like "c" and what not but not a huge deal to me atm.

[Michael Spivak]

Here is some updated stuff I added so that the excel file doesn't mess with the code

%finds any row where date = 0 and removes that row
indices = a(:,1)==0;
a(indices,:) = [];

%finds any rows containing NaN and removes, then adds 1 NaN row at the end
%so that index doesn't go past max #'s in matrix
addrow = nan(1,length(a(1,:)));
indices = isnan(a(:,1)==0);
a(indices,:) = [];
a = [a;addrow];
%in the future could just have the while loop ture while index < length(matrix)
%but this would be annoying to fix in this case as the initial while loop
%goes until a NaN is detected

[Michael Spivak]

I also rewrote this in Python as that is what the company wanted to be able to use it themselves and here is that code. Python allows for the ,append() function which makes this 10x easier

import matplotlib.dates as mdates
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
'''
This program is designed to graph the waste for each machine by day for each shift.
Wanted to be able to have each day with a for 0 waste for a specific shift not be graphed as a zero and instead a line
between two outside values.
'''
xl = pd.read_excel('Waste Tracking.xlsx', sheet_name=[1, 2, 3, 4, 5, 6])
sheet_range = range(1, 7)
sheet_names = pd.ExcelFile('Waste Tracking.xlsx').sheet_names

for y in sheet_range:

    a = xl[y].drop(labels=[0, 1, 2], axis=0)  # drops the first 3 rows to get rid of unwanted text in excel file
    a = a[~a['Unnamed: 1'].isin([0, 1])]  # gets rid of all rows with 0's or 1's in the date num column
    a = a.drop(columns=a.columns[[range(6, len(a.columns))]])  # trims off unwanted columns
    b = a.sort_values(['Unnamed: 1'])  # sorts by first column, dates
    data = {'Date': b.iloc[:, 1], 'Waste': b.iloc[:, 5], 'Shift': b.iloc[:, 2]}  # grabs dates, waste, and shift columns
    b = pd.DataFrame(data=data)  # puts it into a pandas dataframe
    dates = b.iloc[:, 0].unique()  # finds all unique dates for this dataframe
    index = 0
    shift1_total = []
    shift2_total = []
    shift3_total = []

    for x in range(len(dates)):

        h = b.loc[b['Date'] == dates[x]]
        shift1 = []
        shift2 = []
        shift3 = []
        index += 1

        for ind, value in h['Shift'].iteritems():  # iterates through each index and value for the shift column

            if value == 1:  # figures out if it is shift 1,2,3 then appends the waste
                shift1.append(h['Waste'].loc[ind])
            elif value == 2:
                shift2.append(h['Waste'].loc[ind])
            elif value == 3:
                shift3.append(h['Waste'].loc[ind])
            else:
                print('No shift found')

        if sum(shift1) > 0:  # Sums up the totals for each shift and dates them only if waste > 0 for that day
            shift1_total.append([dates[x]-2, sum(shift1)])  # have to subtract 2 from the dates to get correct value
        if sum(shift2) > 0:
            shift2_total.append([dates[x]-2, sum(shift2)])
        if sum(shift3) > 0:
            shift3_total.append([dates[x]-2, sum(shift3)])

    shift1_total = np.array(shift1_total)  # changes lists into arrays for processing
    shift2_total = np.array(shift2_total)
    shift3_total = np.array(shift3_total)

    ax = plt.gca()
    formatter = mdates.DateFormatter("%m-%d")  # creates a formatter
    ax.xaxis.set_major_formatter(formatter)  # sets the x-axis formatter as the created formatter
    locator = mdates.DayLocator()
    ax.xaxis.set_major_locator(locator)
    plt.subplots_adjust(bottom=0.175)
    plt.xticks(rotation=80)
    plt.plot(shift1_total[:, 0:1], shift1_total[:, 1:2], label='Shift 1')
    plt.plot(shift2_total[:, 0:1], shift2_total[:, 1:2], label='Shift 2')
    plt.plot(shift3_total[:, 0:1], shift3_total[:, 1:2], label='Shift 3')
    ax.set_xlabel('Date')
    ax.set_ylabel('Waste (lbs)')
    ax.set_title('Waste per Day per Shift for ' + str(sheet_names[y]))
    plt.grid()
    ax.legend(loc='upper left')
    ax.set_ylim(ymin=0)
    plt.show()
    

So yeah about 75 lines of code including imports and comments