'Add specific rows to DataFrame C#
I'm currently trying to read out an Excel file and add specif rows to a C# DataFrame. This is my current code:
public static string Do_forecast(string file_guid)
{
PrimitiveDataFrameColumn<int> tecId = new PrimitiveDataFrameColumn<int>("TecID");
StringDataFrameColumn email = new StringDataFrameColumn("Email");
StringDataFrameColumn art_nr = new StringDataFrameColumn("Art Nr");
StringDataFrameColumn article_nr = new StringDataFrameColumn("Article Nr");
StringDataFrameColumn brand = new StringDataFrameColumn("Brand");
StringDataFrameColumn article_group = new StringDataFrameColumn("Article Group");
PrimitiveDataFrameColumn<DateTime> date = new PrimitiveDataFrameColumn<DateTime>("Date");
PrimitiveDataFrameColumn<DateTime> date_of_stock = new PrimitiveDataFrameColumn<DateTime>("Date of Stock");
PrimitiveDataFrameColumn<float> sales_base_line = new PrimitiveDataFrameColumn<float>("Sales base line");
PrimitiveDataFrameColumn<float> sales_upper_boundaries = new PrimitiveDataFrameColumn<float>("Sales upper boundaries");
PrimitiveDataFrameColumn<float> sales_lower_boundaries = new PrimitiveDataFrameColumn<float>("Sales lower boundaries");
PrimitiveDataFrameColumn<DateTime> simple_prediction_of_order_date = new PrimitiveDataFrameColumn<DateTime>("Simple Prediction of order date");
PrimitiveDataFrameColumn<int> order_value = new PrimitiveDataFrameColumn<int>("Order Value");
StringDataFrameColumn note = new StringDataFrameColumn("Note");
PrimitiveDataFrameColumn<int> stock = new PrimitiveDataFrameColumn<int>("Stock");
PrimitiveDataFrameColumn<int> stock_Limit_min = new PrimitiveDataFrameColumn<int>("Stock Limit min");
PrimitiveDataFrameColumn<int> stock_Limit_max = new PrimitiveDataFrameColumn<int>("Stock Limit max");
PrimitiveDataFrameColumn<int> open_Orders = new PrimitiveDataFrameColumn<int>("open Orders");
PrimitiveDataFrameColumn<int> reservations = new PrimitiveDataFrameColumn<int>("Reservations");
PrimitiveDataFrameColumn<int> delivery_days = new PrimitiveDataFrameColumn<int>("Estimated time in delivery days");
PrimitiveDataFrameColumn<int> sales = new PrimitiveDataFrameColumn<int>("Sales");
string tempFilePath = Path.GetTempPath();
string infile = file_guid + ".xlsx";
string outfile = file_guid + "_output.xlsx";
string local_in_file_path = Path.Combine(tempFilePath, infile);
string local_out_file_path = Path.Combine(tempFilePath, outfile);
FileInfo existingFile = new FileInfo(local_in_file_path);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
int colCount = worksheet.Dimension.End.Column; //get Column Count
int rowCount = worksheet.Dimension.End.Row; //get row count
List<string> articlelist = new List<string>();
for (int row = 1; row <= rowCount; row++)
{
if (articlelist.Contains(worksheet.Cells[row, 3].Value?.ToString().Trim()))
{
continue;
}
else
{
articlelist.Add(worksheet.Cells[row, 3].Value?.ToString().Trim());
}
}
for (int row = 1; row <= rowCount; row++)
{
foreach (var article in articlelist)
{
Console.WriteLine(article);
if (row != 1 && worksheet.Cells[row, 3].Value?.ToString().Trim() == article)
{
art_nr.Append(worksheet.Cells[row, 3].Value?.ToString().Trim());
date.Append(DateTime.Parse(worksheet.Cells[row, 4].Value?.ToString().Trim()));
sales.Append(Convert.ToInt32(worksheet.Cells[row, 5].Value));
brand.Append(worksheet.Cells[row, 6].Value?.ToString().Trim());
article_group.Append(worksheet.Cells[row, 7].Value?.ToString().Trim());
stock.Append(Convert.ToInt32(worksheet.Cells[row, 8].Value));
date_of_stock.Append(DateTime.Parse(worksheet.Cells[row, 9].Value?.ToString().Trim()));
reservations.Append(Convert.ToInt32(worksheet.Cells[row, 10].Value));
open_Orders.Append(Convert.ToInt32(worksheet.Cells[row, 11].Value));
stock_Limit_min.Append(Convert.ToInt32(worksheet.Cells[row, 12].Value));
stock_Limit_max.Append(Convert.ToInt32(worksheet.Cells[row, 13].Value));
delivery_days.Append(Convert.ToInt32(worksheet.Cells[row, 14].Value));
DataFrame df_to_forecast = new DataFrame(art_nr, date, sales, brand, article_group, stock, date_of_stock, reservations, open_Orders, stock_Limit_min, stock_Limit_max, delivery_days);
//DataFrame df_out = new DataFrame(tecId, email, article_nr, brand, date, sales_base_line, sales_upper_boundaries, sales_lower_boundaries, simple_prediction_of_order_date, order_value, note, stock, stock_Limit_min, stock_Limit_max, open_Orders, reservations);
Console.WriteLine(df_to_forecast);
}
}
}
}
return file_guid;
}
So I'm adding every unique article value from the 3rd Excel column to the articlelist. In the second foreach loop is now my problem: I try to add all rows just from one article to the DataFrame. In my current try all rows get added to the DataFrame, I can also understand why, so I know that it can't work like this. But I dont really know how I should do it different. How can I solve this problem?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
