0

Does anyone have a good solution for taking a CSV file and adjusting the column layout?

So for example, I have a file with headers + data:

foo,bar,baz
1,2,3

But I want to change the column layout to:

qux,foo,waldo,fred,bar,baz

Resulting in a data file which looks like:

qux,foo,waldo,fred,bar,baz
,1,,,2,3

I've been going in circles with CSVKit and other command-line tools - they seem more aimed towards merging/manipulating data sets vs column adjustments.

Any ideas for a generic and extensible solution?

  • So - what be your generic and extensible input? Are you going to provide the "new" header? – RudiC Dec 21 '18 at 18:25
2

There are many ways to complete this task. For ease of use and extensibility, I would recommend Python with the pandas library. The key functions you need are pandas.read_csv() and dataframe.to_csv() -- these are well documented in the pandas online reference.

import pandas as pd
df = pd.read_csv('input.csv')
df.reindex(columns=['qux','foo','waldo','fred','bar','baz']).to_csv('outfile.csv')
0

Hi with Miller is

mlr --csv put '$qux="";$waldo="";$fred=""' then reorder -f qux,foo,waldo,fred,bar,baz input.csv
-1
#!/bin/bash
IFS=','
while read foo bar baz waldo fred qux
do
    echo $qux','$foo','$waldo','$fred','$bar','$baz
done

explanation:

This is a bash shell script. It may work with other shells.

IFS is an environment variable which holds the field separator.
Set IFS to be the comma character.

The while statement loops over all of the lines of input.

The read function separates the line of input using the IFS (comma) into multiple variables.

The do line is part of the structure of the while statement. Every line between 'do' and 'done' is repeated for each line of input.

The echo command creates output. For each line of input we print out the contents of the variables as filled by 'read', in the desired order, divided by commas.

So these statements work on each line of input, dividing each line up into multiple fields then puts the line back together with the fields in a different order.

One potential problem with this is if there is an embedded comma in a data field, it is not smart enough to figure that out.

If you save this shell script in a file (for example X.sh), then you would send your CSV file in as standard input (stdin) and save the reordered CSV output in a new file.

cat mycsv.txt | X.sh > newcsv.txt

  • 1
    Can you add some explanations to the script. – Romeo Ninov Dec 22 '18 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy