Wonderful tool

[ronstone2000]

Hi,

I stumbled across VisiData quite by accident a few days ago and can't stop playing with it. I am not a data scientist or particularly interested in data sleuthing (at least I wasn't), but I see such a thoughtful design and so much utility in the tool that keep trying out new things, which brings me to my questions ...

I have some dirty data that was extracted from a PDF using Tabula.

1 - Entries in the description column span two rows, everything else for the record is on the first of the two rows. Is there. a trick I can apply globally to concatenate the text in the second row onto the first?

2 - Similarly, Tabula was confused by column boundaries on a couple of rows and placed text that should be in col A in a spurious col B. Is there a way for VisiData to clean this up by merging B onto A?

Finally, a question about key combinations: I am having problems getting combinations that involve the shift key to work as described in the docs. For example, I expect z| (z + ^ + |) to select rows matching an expression, but it seems to be overloaded so I always split the screen instead. Am I misunderstanding the notation for the key combinations? (I am on a Mac).

Thanks for creating such a great tool.

[frosencrantz]

Some quick answers:

1. So you want the value of cell to be odd row value + even row value?

   1. i addcol-incr (used to find even/odd rows) adds a new column with a default first name will be A
   2. ] move the cursor to the new column before running this command to reverse the order of the rows. This is done since fill-col only goes in the down direction.
   3. = columnname if A%0 else None adds a new column with values from the even rows (and nulls for odd rows)
   4. ' move the cursor to this new column and freeze the column (fill doesn't work on a calc'd column)
   5. gs to select all the rows (Fill only works on selected rows)
   6. f fill nulls.
   7. Use the trick below to join the columns.
   8. Remove any extra columns or rows you don't need from the steps above (like even rows, A column)
   9. z| A%0 " To remove the extra even rows

2. From the ColumnSheet ('C') you can join two or more columns by selecting the columns and running the join-cols command &

3. The command keystroke is z| . Make sure to only tap z once if you are splitting the screen.

[ronstone2000]

Thanks for this. I'm still having issues with 1 and 3 above:

1.iii
= company if A%0
produces a new column full of errors (red exclamations)
but
= company if A % 2 == 0 else None
populates the new column
1.vii f (after freeze and gs) fills alternate (null) lines with duplicates of the previous (even) rows

I'm also unclear on how the = operation gets me closer to merging even and odd rows. It copies alternating company rows to the same row number in the new column. If I then use the join trick, I get that text duplicated rather than the odd+even row concatenation I am looking for. It seems that I need something like:

= company.row.next if A % 2 == 0 else None
to put the following row's text across from the previous line in the new column. Is the a method that does this?

3. z|, and some other character combinations, is not working as pressed: z+SHIFT+|. I expect this to select rows matching an expression.

[saulpw]

@ronstone2000, these kinds of workflow questions can be difficult to answer in an asynchronous forum like this. It really helps to see the exact format of the data you're working with, and the exact keys you're pressing when something doesn't work. I hold regular office hours and would be happy to help you figure some things out. Feel free to schedule a time slot and we'll have some fun with it!

And thanks so much for the helpful answers and ideas, @frosencrantz! I think these suggestions are in general the right set of ideas to help him out, though the exact commands may take some finagling to work in his specific situation (they usually do).

[frosencrantz]

@ronstone2000 were you able to clean your data with VisiData?

I would expect the data to be dirty it is own way, and so those commands would only be a starting point.

[ronstone2000]

Not yet. I did see Saul's generous offer to help offline but have not had time to look at this again and won't until June. I'll follow up then.

[frosencrantz]

I made a couple asciinema animations of cleaning the data. Showing these since I made some mistakes explaining the steps to perform:

• https://asciinema.org/a/415793 - Cleaning dirty data. One column's value is split over two rows.
• https://asciinema.org/a/415792 - Create sample dirty data from help-commands

As Saul says I don't expect your data to be so precisely dirty. But these can give you some ideas on steps you can perform to clean the data.

There are notes in the animations of the steps performed. Plus if you look at the bottom line on the right, VisiData will report the command names run, and the keystrokes.

[frosencrantz]

@ronstone2000, there is a lot of finagling in my solution. This is needed just for this simple case of trying to merge a cell that is split across two rows, because there is no company.row.next. There is no command to add a column that has the values of another column rolled up/down a row. I think if there was an addcol-rollcol command which would copy the contents of the company column with the values moved up/down n rows, it would make that cleanup simpler.

[frosencrantz]

@ronstone2000 were you able to meet? Was there anything you are willing to share?

[ronstone2000]

No, I am busier than expected and don’t know when I’ll have leisure time for this. Thanks for the follow up.

…

On Jun 27, 2021, at 2:26 PM, Felix Rosencrantz ***@***.***> wrote: @ronstone2000 <https://github.com/ronstone2000> were you able to meet? Was there anything you are willing to share? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#977 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AT742H3MBVAHGXCTUM23P33TU5UOXANCNFSM44NKBINA>.