Thanks for contributing.

• [ x] Tick to sign-off your agreement to the Developer Certificate of Origin (DCO) 1.1

Description

Added an implementation of Transpose. It has the restriction that columns must be of the same type. This is an initial version for feedback

Testing

Added a unit test for the feature

Hi, I am new to Tablesaw. I am exploring options for recreating some Pyhton DataFrame operations in Tablesaw. For example, I have a DataFrame object called data1 and I use existing columns of this data frame to create new ones (and update existing ones). Here is a couple of lines of Pyhton codes:

data1['days'] = data1['buyDate'].apply(lambda x: (today - x).days) ... data1['CAGR'] = ((data1['curValue'] / data1['bookValue']) ** (1.0 / data1['nYears']) - 1.0) * 100

Is there a way to implement something similar using Tablesaw?

Thanks a lot in advance.

I've finally got my use case using tablesaw to an initial build and tried a few runs, and it is rather slow compared to a python version I wrote using pandas previously. I migrated to Java to get better concurrency in the hope of making it go faster.

Profiling it I see why -- it is spending 75% of its time in tech.tablesaw.table.Rows.copy(). This is because of some Table.where() calls that are designed to filter down the input data.

Briefly, the background is my input data is price history for certain financial assets. Having established a price at a certain time (a simulated trade entry point) I then want to see if the market went up or down by a certain amount from that point. I am currently doing so using a filter like:

useData.where(useData.numberColumn("High").isGreaterThanOrEqualTo(target).or(useData.numberColumn("Low").isLessThanOrEqualTo(stop)))

(method names from memory so may have slight inaccuracies)

That gives me a filtered table, and then selecting the first row from this gives me the first instance that fulfilled the criteria. I'm only ever interested in the first row matching the criteria but can't think of a way to get tablesaw to stop once that row is found, so instead get the entire table and take the first row (or rather first value of each column) as needed. This kind of approach is applied repeatedly to further filter price data depending on what is found at each stage. The result is a lot of calls to Table.where().

As I say the code ends up spending a lot of time copying rows, presumably from the original data to the return value of the where() method. Is there a way to get tablesaw to take a "view" approach as pandas would in this situation, that is not actually copy the data but simply copy references to the data, which would be faster? This obviously comes along with issues if one later tries to modify the filtered result represented by the view, because it would be impossible to do so without also modifying the original; pandas handles that by making attempts to alter a view an error, forcing the user to use an operation that would force a copy when that is what they want to do. For my particular use case, since the data is only read (in this particular case I don't even summarise it) I don't mind not being able to modify.

Does tablesaw have anything analogous to this view concept from pandas? Again, the goal is to avoid needlessly copying a lot of data.

Thanks for contributing.

• [x] Tick to sign-off your agreement to the Developer Certificate of Origin (DCO) 1.1

Description

Enhanced API to accept multiple names of columns to join on. Supported for all joins: inner, leftOuter, rightOuter, and fullOuter. Added javadoc where missing for public methods.

Testing

Added dozens of junit tests to cover all changed API. Ran coverage tool in eclipse to ensure testing/exercising of all changes was being done. Reached 100% coverage for all but one method. Goal to reach 100% coverage exposed failure of LONG types to be parse from cvs input so added missing support for that.

I'm debugging some code where columns in a table can be of different sizes. To help diagnose the problem I'm printing the table. However, Relation's toString() method, that uses a DataFramePrinter, throws an IndexOutOfBoundsException because the frame.size() is based on the first column's size, while others are shorter. A toString-method should be very careful to not throw exceptions, since it's typically used during debugging. I suggest making DataFramePrinter more fault tolerant when fetching column values, e.g. in line 192: data[i][j] = frame.getString(i, j);

A null value is sometimes returned by a ColumnType constant. Whether or not the value is null depends on the order in which other code is executed.

To recreate, I built an array of ColumnTypes and printed the array. Sometimes the values are all initialized correctly, sometimes not:

Correct:

[STRING, LOCAL_DATE_TIME, INTEGER]

Incorrect:

[STRING, null, INTEGER]

The different results can be had by adding or removing a line of code just before printing. The correct result is printed when the line is removed.

Here is the main class with the offending line included:

class DummyClass {

    public static void main(String[] args) {
        long missing = DateTimeColumn.MISSING_VALUE;
        new DummyPrinter().printColumnTypes();
    }
} 

When I comment out line 1 in main, the code works as expected. Also, the code that implements printing must be in a separate class for the error to occur. Here's that class:

class DummyPrinter {

    void printColumnTypes() {
        System.out.println(Arrays.toString(columnTypes));
    }

    private static final ColumnType[] columnTypes = {
        STRING,
        LOCAL_DATE_TIME,
        INTEGER,
    };
}

The array itself is a literal constant. The values inserted into the array are also constants. They are declared in the ColumnType interface in the line shown below:

DateTimeColumnType LOCAL_DATE_TIME = DateTimeColumnType.INSTANCE;

In that line, the values are provided by a constant (INSTANCE) declared in DateTimeColumnType.

Here is the code from that class where INSTANCE is created:

public static final DateTimeColumnType INSTANCE =
        new DateTimeColumnType(BYTE_SIZE, "LOCAL_DATE_TIME", "DateTime");

The line of code that turns the error on and off causes the class DateTimeColumnType to load in a different order when it's present than it does when absent.

Using xChart or JavaFX Charts create an interface that enables easy rendering of the plot from a tablesaw table. See the implementation of Bar Plots (which use JavaFX) for a very similar example.

https://github.com/jtablesaw/tablesaw/blob/master/plot/src/main/java/tech/tablesaw/api/plot/Bar.java

Thanks for contributing.

• [x] Tick to sign-off your agreement to the Developer Certificate of Origin (DCO) 1.1

Description

1. Added support for reading and writing Apache ORC file format Fixes #620

Testing

Unit Test cases added

Maybe I'm looking in the wrong place, but it doesn't seem like there is sufficient information for a newcomer to get plotting working—and this is the main reason I'm looking at this library.

1. First of all, a suggestion: the page https://jtablesaw.github.io/tablesaw/userguide/introduction says nothing at all about the dependency needed for plotting. One has to know to look back at https://github.com/jtablesaw/tablesaw to figure this out. But this is a minor issue.

2. Much bigger is that the first code at https://jtablesaw.github.io/tablesaw/userguide/Introduction_to_Plotting does not work. Not even close. It won't even compile. It doesn't even have balanced parentheses. Fixing the parentheses still won't get it to compile, and certainly won't show the plot that was promised.

Figure fig = BubblePlot.create("Average retail price for champagnes by year and rating",
                champagne,					// table name
                "highest pro score",		// x variable column name
                "year",						// y variable column name
                "Mean Retail"				// bubble size
               ));

3. So I skip the "introduction" page and go straight to the "real" code at https://jtablesaw.github.io/tablesaw/userguide/BarsAndPies .

First we load the Tornado dataset:

Table tornadoes = Table.read().csv("Tornadoes.csv");

What is "the Tornado dataset" and where can I find it? An earlier paragraph mentioned, "we’ll use a Tornado dataset from NOAA", so I went to the NOAA site and downloaded the most promising looking data file named 1950-2017_all_tornadoes.csv. I tried loading that in Tablesaw:

…
Caused by: java.lang.ArrayIndexOutOfBoundsException: Index 10000 out of bounds for length 10000
	at com.univocity.parsers.common.ParserOutput.valueParsed(ParserOutput.java:327)
	at com.univocity.parsers.csv.CsvParser.parseRecord(CsvParser.java:176)
	at com.univocity.parsers.common.AbstractParser.parseNext(AbstractParser.java:560)
	... 7 more

(So you are using the uniVocity parsers. I'm familiar with them. But they aren't so "plug-and-play" as they make them out to be, as you can see here.)

I searched the web for "Tornadoes.csv". One unrelated site implied that 2018_torn_prelim.csv might be more promising, but that didn't work. https://gist.github.com/darrenjaworski/5874227 mentioned a tornadoes.csv, and it was some Google spreadsheet, which I downloaded to a CSV file, but that gave me:

Exception in thread "main" java.lang.IllegalArgumentException: Cannot add column with duplicate name Short column: state number to table tornadoes.csv
	at tech.tablesaw.api.Table.validateColumn(Table.java:161)
	at tech.tablesaw.api.Table.addColumns(Table.java:144)
	at tech.tablesaw.io.csv.CsvReader.read(CsvReader.java:147)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:62)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:58)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:34)
	…

I finally realized you that this GitHub repository has some "tornado" CSV files. I downloaded and renamed tornadoes 1950-2014.csv, but no go:

Exception in thread "main" tech.tablesaw.io.csv.AddCellToColumnException: Error while adding cell from row 41658 and column Crop Loss(position:10): Error adding value to column Crop Loss: For input string: "0.4"
	at tech.tablesaw.io.csv.CsvReader.addRows(CsvReader.java:244)
	at tech.tablesaw.io.csv.CsvReader.read(CsvReader.java:156)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:62)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:58)
	at tech.tablesaw.io.DataFrameReader.csv(DataFrameReader.java:34)
	…
Caused by: java.lang.NumberFormatException: Error adding value to column Crop Loss: For input string: "0.4"
	at tech.tablesaw.api.ShortColumn.appendCell(ShortColumn.java:353)
	at tech.tablesaw.api.ShortColumn.appendCell(ShortColumn.java:27)
	at tech.tablesaw.io.csv.CsvReader.addRows(CsvReader.java:242)
	... 5 more

This is frustrating. This is what a new user is faced with.

If this turns out to be a good library, believe me I'll pitch in and help with the documentation and probably even the code. But I'm stuck just in the first lines! Could someone help me?

Hello tablesaw team. I've question regarding setup of data type for the Table if I don't know the amount of colums. I have 30_000x2000 feature csv file with 0.0 and some other amount of Double numbers. If I call csv parsing via:

CsvReadOptions options = CsvReadOptions.builder(csv)
                    .header(false)
                    .maxNumberOfColumns(50_000).build();

Table t = Table.read().csv(options);

I got Number format exception, as all 0.0 number are treated as Short 0. So when reader gets to real numbers like 13.5 if throws NFE.

But if I add sample(false) to reader options if takes about 2:40 to parse such file.

How can I setup data type for whole Table, as far as I can see only by setting columnType in parser option, but it's won't work as I don't know a number of columns on csv file?

P.S. I used com.univocity.parsers.csv.CsvParser separately to read the same file so it takes 2:20 for parser.parsAll and 1:20 for parsing file by row.

Hi guys! I'm trying to migrate from python+pandas to kotlin+tablesaw. Some parts of my code are already working fast (like csv parsing, x2 times faster than in pandas) But also i've noticed that inner join operation is pretty slow (~ 2 times slower than in pandas)

Then i tried to optimize my code and use isIn Selection instead of simple join. Unfortunately it uses strings.toArray(new String[0]) under the hood for input parameter collection. It would be more sense to use HashSet to quicker lookup. So i wrote my own predicate:

val customersSet = customers.toHashSet()    // for faster lookup
val idColumn = transactions.stringColumn("customer_id")
idColumn.eval { it in customersSet }

Which is x15 times faster than original inner join. At least on my huge dataset. Of course this is much simpler than join operation since i haven't appended columns etc. But still the difference is huge. I didn't investigate joins code yet, but i hope there is space for improvements there. My key point is: kotlin+jvm should be at least not slower than python+pandas What do you guys think?

ps: do you use hash indexing on table columns?

The below one is my table,

RowNo | RCDTYP | SNDARLIN | RCVARLIN | FILTYP | DELSEQNUM | CRTDAT | FILR1 |

 1  |       0  |        AV  |        IB  |  HANDBACK  |     000002  |  20220426  |         |

tablename.getstring(rowno,columnname)

takes "IB" only, i want it as " IB"

can somebody please help?

It would be useful to be able to use the following: SqlResultSetReader.mapJdbcTypeToColumnType(java.sql.Types.BINARY, ColumnType.SKIP)

Unfortunately, currently, this results in

java.lang.UnsupportedOperationException: Column type SKIP doesn't support column creation
        at tech.tablesaw.columns.SkipColumnType.create(SkipColumnType.java:29)
        at tech.tablesaw.io.jdbc.SqlResultSetReader.read(SqlResultSetReader.java:105)
        at tech.tablesaw.io.DataFrameReader.db(DataFrameReader.java:160)```

Alternatively, it would be useful to be able to skip a column by name for the SqlResultSetReader, but i found no such options. It seams the ReadOptions don't support reading from SQL currently.

hi~,您好。tablesaw 非常的棒，但是在使用tablesaw过程中遇见了一些问题，希望能够给予帮助。 问题描述： 因为doubleColunm不能splitOn，所以我将doubleColunm as stringColumn后进行了分组切片，最后将stringColumn还原成doubleColumn的时候，值却出现了问题。 code ：

@Test
public void test000() {
    DoubleColumn doubleColumn = DoubleColumn.create("year", 2022.0, 2022.0);
    StringColumn stringColumn = doubleColumn.asStringColumn();
    System.out.println(stringColumn.print());
    DoubleColumn asDoubleColumn = stringColumn.asDoubleColumn();
    System.out.println(asDoubleColumn.print());
}

输出： Column: year strings 2022.0 2022.0

Column: year strings 0 0

再次表示感谢🙏

final Table tbl2= Table.read() .csv( Joiner.on(System.lineSeparator()) .join("abc,cds,eee", "005,001,003" ), "Table2");

content1 and content 2 contains two comma seperated lists, i want to treat every columns as string values eg : if content is 005 , I want to add as 005 not 5. Is there any additional method to treat as string?

Hi

After executing any kind of table manipulation, eg add columns/rows, where clauses, you almost always want to see what the table looks like via the print/toString methods. Unfortunately it doesn't also display the shape and so most of these calls always has a following call to shape.

The shape is important when you have a larger dataset and you can see the changes in the number of rows/columns

ps Pandas equivalent function prints the shape when displaying table contents

Hi

I read in a file with the following structure

 Index  |   Column Name    |  Column Type  |
--------------------------------------------
     0  |              id  |      INTEGER  |
     1  |            date  |   LOCAL_DATE  |
     2  |            time  |       STRING  |
     3  |    country_name  |       STRING  |
     4  |  state/province  |       STRING  |
     5  |      population  |      INTEGER  |
     6  |  landslide_type  |       STRING  |
     7  |         trigger  |       STRING  |
     8  |      fatalities  |      INTEGER  |

when I do a summary, I get the following

  Summary   |         id          |     date     |  time  |  country_name   |  state/province  |      population      |  landslide_type  |  trigger   |  fatalities  |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Count  |               1693  |        1693  |  1693  |           1693  |            1693  |                1693  |            1693  |      1693  |        1693  |
       sum  |            7017532  |              |        |                 |                  |           158226757  |                  |            |              |
      Mean  |  4145.027761370351  |              |        |                 |                  |   93459.39574719437  |                  |            |              |
       Min  |                 34  |              |        |                 |                  |                   0  |                  |            |           0  |
       Max  |               7541  |              |        |                 |                  |            12294193  |                  |            |         280  |
     Range  |               7507  |              |        |                 |                  |            12294193  |                  |            |         280  |
  Variance  |  5003014.595564535  |              |        |                 |                  |  273112413878.66046  |                  |            |              |
  Std. Dev  |  2236.741959986564  |              |        |                 |                  |  522601.58235376637  |                  |            |              |
   Missing  |                     |           3  |        |                 |                  |                      |                  |            |              |
  Earliest  |                     |  2007-03-02  |        |                 |                  |                      |                  |            |              |
    Latest  |                     |  2016-03-02  |        |                 |                  |                      |                  |            |              |
    Unique  |                     |              |   159  |             28  |             227  |                      |              15  |        17  |              |
       Top  |                     |              |        |  United States  |        Kentucky  |                      |       Landslide  |  Downpour  |              |
 Top Freq.  |                     |              |  1065  |            986  |             124  |                      |             866  |       866  |              |

The missing value is only shown for date and it does work, I can verify there were 3 missing date values. However I also can see missing values for fatalities but it does not appear here. If I do the following

table.intColumn("fatalities").isMissing().size()  // returns > 0

I suspect that missing is not implemented on the summary() call for IntColumn types because if I simply select that single column and do a summary() on it, I get the following with no missing statistic

Column: fatalities  
 Measure   |  Value  |
----------------------
    Count  |   1690  |
      sum  |         |
     Mean  |         |
      Min  |      0  |
      Max  |    280  |
    Range  |    280  |
 Variance  |         |
 Std. Dev  |         |

Could we get that statistic filled in and for those statistics that aren't supported by the column type, could we add something like 'N/A' so that it is clear? Thanks.

ps this is a great lib. Really appreciate what you've done here!