0

Problem

I have a .csv file that was not filled in properly, which has missing column information.

The file looks something like this (whitespace is for clarity):

  C1,  C2,  C3,  C4,
R1C1,R1C2,R1C3,R1C4,
R2C1,R2C3,    ,    ,
R3C1,R3C4,    ,    ,
...

Where Cx is the column header and shares a string value with RyCx. For example,

Name     , Date       , Address           , Email              ,
Name Alex, Date Sept 3, Address 123 Madeup, Email Alex@mail.com,
Name Jenn, Date Sept 4, Email Jenn@mail.co,                    ,

Where now Email Jenn@mail.co is incorrectly in the Address column.

There is any number of null spaces after the data. The data is laid out so that each row is in order [R1C1,R1C2,R1C3 ...] unless the data does not exist - in which case the columns shift left but their tag Cx does not change. This is because the program outputting the data did not create null cells.

There are no other patterns to this data.

I would like to reorganize this data into the appropriate column as:

  C1,  C2,  C3,  C4,
R1C1,R1C2,R1C3,R1C4,
R2C1,    ,R2C3,    ,
R3C1,    ,    ,R3C4,
...

or in the example as

Name     , Date       , Address           , Email              ,
Name Alex, Date Sept 3, Address 123 Madeup, Email Alex@mail.com,
Name Jenn, Date Sept 4,                   , Email Jenn@mail.co ,

I cannot go back to where the information was gathered, it's older supercomputer simulation results.


Solution

Thanks to FKEinternet and urcodebetterznow.

while IFS= read -r line; do # read input line by line
IFS=, read -ra fields <<<"$line" #separate fields by commas

    j=0; 
        for i in $(cat headers.txt); do #I wrote the headers to a different file

            if [ "${fields[j]}" == "$i" ]; then #replaced with grep -o result because fields are not exact matches for the header, but contain exact matches
                val="${fields[j]}"; : $((j += 1)); 
            else  val=''; 
            fi; 

            printf '%s,' "$val"; #I later used sed to erase the redundant information already in the header
         done

done < datafile.txt > solution.csv

where a file headers.txt looks like:

a
b
c
d
e
f
g
h

And the data looks like datafile.txt:

a,b,c,d,e,
a,c,e
b,d,f,g,h
c,d,g,h
d,h
b,f,g
a,d,f,g

Running the bash script I get (whitespace for clarity):

a,b,c,d,e, , , ,
a, ,c, ,e, , , ,
 ,b, ,d, ,f,g,h,
 , ,c,d, , ,g,h,
 , , ,d, , , ,h,
 ,b, , , ,f,g, ,
a, , ,d, ,f,g, ,

which was the desired result.

12
  • Ok so there are ambiguities here. Consider R1C1, R1Cx / R2C1, R2Cy. At this point we don't know whether { x=2, y=3 } or { x=3, y=2 }. The next row R3Cz, R3Cx would have to cause a big rethink where x and y were no longer 2 and 3 but were now 3 and 4 because z would have to squeeze in before x. And we still wouldn't know for { x, y } which was 3 and which was 4. Solving this will not be a trivial piece of code
    – roaima
    Sep 3, 2021 at 22:02
  • Is there any chance at all you could go back to the original data source?
    – roaima
    Sep 3, 2021 at 22:05
  • 1
    To do this, you'd need to know what type of data is in each column and how to recognise it (and distinguish it from the data in other columns). For some data types, this will be easy - e.g. a date is easily distinguished from an email address. For others, it will be much harder or even impossible - especially if you have multiple columns that have the same kind of data - e.g. if columns 5,6,7,...,X all have numeric data then it's impossible to tell which columns are missing. In short: GIGO: you have garbage data as input, so the best you can expect of the output is more garbage.
    – cas
    Sep 4, 2021 at 4:39
  • 1
    whoever generated that data set was either clueless or deliberately trying to obfuscate their results so they couldn't be re-used. Technically complying with a requirement to show their data, but doing so in a manner that is completely useless.
    – cas
    Sep 4, 2021 at 4:40
  • 1
    @Angelo: Increment j only on match. Replace the 2 for loops with: j=0; for i in $(cat headers.txt); do if [ "${fields[j]}" == "$i" ]; then val="${fields[j]}"; : $((j += 1)); else val=''; fi; printf '%s,' "$val"; done Sep 4, 2021 at 19:55

2 Answers 2

1

Your rewritten question is now conceptually pretty easy to answer: You have an array of tags that may or may not be present in each row of data. You want to read in each row, and go through the columns in order, checking to see if the tag in that column is the one that's expected. If not, insert a blank cell, and check the next column. Once you get to the end of the list of expected tags, emit the reconstructed row.

Here's some pseudocode, which you can implement in the language of your choice:

read the first row
split the text on commas to create the array of expected tags
read the next row
    if no more data, exit
    split the text on commas to create a row data array
    for each expected tag
        check the current column in the row's data
        if the tag matches
            write the column data to the output
            advance the current column in the row data
        else
            write a blank column to the output
        terminate the output line
0
1

I just noticed that each column of your data actually begins with the name of the column. I must have missed that when I first saw your question. That makes it not only possible but fairly easy to reformat the data.

#!/usr/bin/perl

use strict;
my @headers; # array to hold the headers in the order they were seen.
my @search;  # array to hold a copy of @headers sorted by string length

while(<>) {
  chomp;    # remove newline character at end-of-line

  if ($. == 1) {
    next if (scalar @headers); # only process headers for first file
    # Split the first line into @headers array, removing any
    # leading or trailing spaces from each column
    @headers = split '\s*,\s*';

    # In case one key might be a substring of another key, copy the
    # @headers array, sorted by length, so we can compare the data
    # with the longest header names first.
    @search = sort { length($b) <=> length($a) } @headers;

    print join(",", @headers), "\n";

  } else {
    my %columns = ();
    # Loop over each column of the input line (row), inserting it into
    # the %columns hash, using the appropriate column name as the key.
    foreach my $c (split '\s*,\s*') {
      my $found = 0;
      foreach my $h (@search) {
        # If the current column ($c) begins with a header
        # name ($h), we've found the right key for it.
        if ($c =~ s/^$h\s+//i) { # match and remove header from column
        #if ($c =~ m/^$h\s+/i) { # or just match without removing header
          $columns{$h} = $c;
          $found = 1;
        };
      };
      warn "Unknown column '$c' in line $. of $ARGV\n" if
        ($c ne '' && ! $found);
    };

    # Output every column in the same order as in the header line.
    # Columns not actually present in a row are output as an empty field
    print join(",", @columns{@headers}), "\n";
  };

  # Reset the line counter at the end of each input file if
  # there's more than one
  close(ARGV) if eof;
}

The regular expressions to match each column are matched case-insensitively. If your data has columns with upper and lower or mixed case versions of the same name, then remove the /i modifier from the regexes.

Save this with a suitable name, e.g. ./fix-data.pl, and make it executable with chmod +x ./fix-data.pl.

Sample output:

$ ./fix-data.pl datafile.txt 
Name,Date,Address,Email
Alex,Sept 3,123 Madeup,Alex@mail.com
Jenn,Sept 4,,Jenn@mail.co

or, using the commented-out alternative if statement:

$ ./fix-data.pl datafile.txt 
Name,Date,Address,Email
Name Alex,Date Sept 3,Address 123 Madeup,Email Alex@mail.com
Name Jenn,Date Sept 4,,Email Jenn@mail.co

I don't know why anyone would want this second format because the column names are already in the headers line, and every output row has every column in the right order....but it was easy enough to do if that's what you want.

BTW, you can format the output as a table with same-width columns by piping it into column:

$ ./fix-data.pl datafile.txt | column -t -s , -o ', '
Name, Date  , Address   , Email
Alex, Sept 3, 123 Madeup, Alex@mail.com
Jenn, Sept 4,           , Jenn@mail.co

Using column with ' | ' as the output separator is IMO more human-readable (and still easily imported into a spreadsheet or parsed by other programs)

$ ./fix-data.pl datafile.txt | column -t -s , -o ' | '
Name | Date   | Address    | Email
Alex | Sept 3 | 123 Madeup | Alex@mail.com
Jenn | Sept 4 |            | Jenn@mail.co

column can even output the data as valid json, for example:

$ ./fix-data.pl datafile.txt |
  tail -n +2 |
  column --json -s , \
      --table-columns "$(sed -n -e '1s/ *, */,/gp' datafile.txt)"
{
   "table": [
      {
         "name": "Alex",
         "date": "Sept 3",
         "address": "123 Madeup",
         "email": "Alex@mail.com"
      },{
         "name": "Jenn",
         "date": "Sept 4",
         "address": null,
         "email": "Jenn@mail.co"
      }
   ]
}

(On Debian, at least, column is in the bsdextrautils package. On other distros, it's probably in util-linux)

miller and datamash are also useful command-line tools for working with your data once you've got it into a sane format.


NOTE: The script assumes that the data is just a simple comma-delimited format, not properly-formed CSV (see, e.g., RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files) with the possibility of quoted string fields and even embedded commas within quoted fields. If any of rows have quoted columns, you'd need to use a CSV parser instead of simply splitting each input line on commas. e.g. perl's Text::CSV module. I don't think this is likely to be needed as your data is in a weird not-actually-CSV format apparently invented by whoever generated it (and if they knew about CSV they would probably have used it...or messed up the data even worse than it already is).

This caveat would apply to any implementation in any language, because the issue would be a result of the janky data, not the code.

column won't work with CSV containing embedded commas, either.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.