Matrix sorting and graphical analysis

1 次查看（过去 30 天）
Michael Spivak
Michael Spivak 2021-5-20
评论： Michael Spivak 2021-6-7
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.

请先登录，再回答此问题。

采纳的回答

Michael Spivak
Michael Spivak 2021-5-21
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
  3 个评论
显示 1更早的评论
Michael Spivak
Michael Spivak 2021-5-21
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
Michael Spivak 2021-6-7
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

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Time Series Objects 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by