Move column data to under another column

[halloleo]

I have CSV files with data like this:

SenderName	SenderAddress	ReceiverName	ReceiverAddress	OtherInfo
.	.	.	.	.
.	.	.	.	.

Now I would like to extract/transform this into a table with only two columns:

NAME	ADDRESS
.	.
.	.

where NAME contains all cells under SenderName and ReceiverName and ADDRESS contains everything under SenderAddress and ReceiverAddress.

Is there an easy way to do this in vd?

[saulpw]

Hi @halloleo, I think for what you want, the easiest way is to make derived columns like e.g. =' '.join(SenderName, ReceiverName) or SenderName + '\n' + ReceiverName.

You could also look into gM ("big melt") which takes a regex to parse the column headings, and then would produce 2 rows per initial row, one for the Sender and one for the Receiver, with a new column to distinguish between them.

[halloleo]

Thx @saulpw ! Will try this out.

PS: For my current, individual use case I employed a solution with pandas, but it was more complicated than I had hoped. - So now I will do some tests with VisiData: I hope it’ll be a bit quicker due to Visidata’s visual nature — plus it also gives me reproducibly through the replay feature. 😄

[halloleo]

I played a bit with melting - and the plain melt does the job; it just tackles the problem from a different angle to what i had expected:

I thought of having first all SenderName rows with the addresses and then all ReceiverName rows. (And I implemented it like that in the Pandas solution.) visidata's melt whereas inserts after each SenderName row the associated ReceiverName row appears.

But maybe that is just owed to the way I choose key columns and none-key columns...

Anyway, thanks heaps for the pointer! melt can do it!

---

PS: I didn't see a direct way how I could use the melt-regex command though: From my brief look it splits columns into parts, so would I need to join the columns SenderName and ReceiverName first in order to then split them with melt-regex?

[saulpw]

Cool, @halloleo, I'm glad that worked for you. and to your PSS: the regex for melt-regex applies to the column name, not the values. So in this case (.*)Name should give you a column that has Sender and Receiver in it.