0

I have two DataTables. I would like one table joined by the column strike which they share? I would like one table, where the columns of table1 and table2 are "merged/joined" with only one strike column, one symbol column, one expiry column, one timestamp column.

Table 1:

symbol expiry strike option_type timestamp option_mid option_trade_count option_prev_day_close iv open_interest option_volume delta vega theta rho
AAPL 2022-05-20 140.0 C 15:59:59.000 17.325 12 16.1 0.2824 193 43 0.6709 0.3904 -0.0294 43.0797
AAPL 2022-05-20 145.0 C 15:59:59.000 14.425 11 13.25 0.2778 350 217 0.6067 0.415 -0.0307 39.6381
AAPL 2022-05-20 150.0 C 15:59:59.000 11.825 22 10.8 0.2742 2551 63 0.5397 0.4284 -0.0312 35.8007
AAPL 2022-05-20 155.0 C 15:59:59.000 9.5 10 8.7 0.2728 735 28 0.473 0.4295 -0.0311 31.7564

Table 2:

symbol expiry strike option_type timestamp option_mid option_trade_count option_prev_day_close iv open_interest option_volume delta vega theta rho
AAPL 2022-05-20 140.0 P 15:59:59.000 7.325 3 8.05 0.2801 375 22 -0.3285 0.3901 -0.0288 -29.2865
AAPL 2022-05-20 145.0 P 15:59:59.000 9.3 9 10.2 0.2749 903 46 -0.3931 0.4149 -0.03 -35.3233
AAPL 2022-05-20 150.0 P 15:59:59.000 11.675 14 12.75 0.272 2424 182 -0.4606 0.4284 -0.0307 -41.8097
AAPL 2022-05-20 155.0 P 15:59:59.000 14.475 3 15.65 0.2696 216 21 -0.5281 0.4295 -0.0304 -48.4802

EDIT 1

public class OptionFields
{
    public string root { get; set; }
    public string expiry { get; set; }
    public decimal strike { get; set; }
...

}

If I say this, I just get the values in table2 (table1/table2 are now List<OptionFields>)

var result = from dataRows1 in table1
join dataRows2 in table2 on dataRows1.strike equals dataRows2.strike 
into lj
from r in lj.DefaultIfEmpty()
select r;
Ivan
  • 7,448
  • 14
  • 69
  • 134
  • Can you please mention the expected output – Vivek Nuna Nov 14 '21 at 19:29
  • The fields f table2 two would essentially be to the right of table 1 on the strike being the same, but not duplicating the columns listed on the OP – Ivan Nov 14 '21 at 19:30
  • Oof, that's way more code than I want to write on a cellphone. Have you set a primary key on either table? If so, use it; loop on Ver one table and lookup the matching row from the other table, then extend the first table with all the extra columns the second one has and copy the new values over. If you have no PK, make a dictionary to index the Strike column to a particular row (loop over every row adding to index) then loop over the other looking up the strikes as you go – Caius Jard Nov 14 '21 at 19:36
  • You can also take a look at putting suitable keys on each table and then calling Merge with missingschemaaction of Add - https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.merge?view=net-5.0#System_Data_DataTable_Merge_System_Data_DataTable_System_Boolean_System_Data_MissingSchemaAction_ – Caius Jard Nov 14 '21 at 19:41
  • And some duplicates for you : https://stackoverflow.com/questions/20760681/linq-join-two-datatables - https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp – Caius Jard Nov 14 '21 at 19:47

1 Answers1

0

I think you like to do a union client side. And with Datatables you can achieve this with merge. https://learn.microsoft.com/en-us/dotnet/api/system.data.datatable.merge?view=net-5.0

EricW
  • 56
  • 3
  • I believe that merge adds rows to the table. In my case, there are no new rows created, but instead more columns. I want to take table2 and "paste" it based on strike, to the right of table1. So I want calls and puts on the same row on the same table, based on strke (and others but I will take strike). In the vernacular, this is called a "Straddle View" – Ivan Nov 15 '21 at 13:57
  • For example: https://finance.yahoo.com/quote/AAPL/options?p=AAPL&straddle=true – Ivan Nov 15 '21 at 14:00
  • 1
    Ok, sorry for misunderstanding. https://stackoverflow.com/questions/665754/inner-join-of-datatables-in-c-sharp – EricW Nov 16 '21 at 14:06