Import csv file which contains columns of different lengths --> How can I delete empty fields?

Question

I have the following problem: I want to import a csv file into Mathematica. I file as a lot of rows and columns, but the column lengths differ for certain rows. 2 columns always belong together, forming x,y values.

The import works fine, BUT the size of my csv "matrix" seems to be interpreted as: number of row * number of LONGEST column. I therefore get a lot of empty spaces {} in all columns that are shorter.

What I want to do in Mathematica is the following: Import the file. Rearrange the csv matrix such, that I get {{x1,y1},{x2,y2},....} values for different experiments.

My code looks as following:

imp=Import["filepath...csv"]
imp//Transpose

I am stuck there, because I get a lot of {{,},{,},{,},....} at the end of the shorter lists. How can I remove them?

Thanks a lot in advance!

Answer 1

If you look at the InputForm of the List you get from Import, you will notice that all those "empty" entries are actually an empty string, i.e. "". You can then replace the string with an empty Sequence[], and the empty sequence will automatically be flattened out of the list.

I generated a .CSV file with your pattern of pairs of columns of different lengths, then imported it

data = Import["sample.csv"]

(*Out:
{{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},
{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},
{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},
{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,4,4},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},
{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},
{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},
{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},{1,1,2,2,3,3,,},
{1,1,,,3,3,,},{1,1,,,3,3,,},{1,1,,,3,3,,},{1,1,,,3,3,,},{1,1,,,3,3,,},
{1,1,,,3,3,,},{1,1,,,3,3,,},{1,1,,,,,,},{1,1,,,,,,},{1,1,,,,,,},
{1,1,,,,,,},{1,1,,,,,,},{1,1,,,,,,},{1,1,,,,,,},{1,1,,,,,,},{1,1,,,,,,}}
*)

If you check the InputForm for that expression, you will see a lot of the following:

...  List[1, 1, 2, 2, 3, 3, "", ""],  List[1, 1, 2, 2, 3, 3, "", ""], ...

We can then do the replacement:

cleaneddata = data /. "" -> Sequence[];
cleaneddata // TableForm

(*
{{1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4},
{1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, 
{1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, 
{1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, 
{1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3, 4, 4}, {1, 1, 2, 2, 3, 3},
{1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, 
{1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, 
{1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, 
{1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3}, {1, 1, 2, 2, 3, 3},
{1, 1, 2, 2, 3, 3}, {1, 1, 3, 3}, {1, 1, 3, 3}, {1, 1, 3, 3}, 
{1, 1, 3, 3}, {1, 1, 3, 3}, {1, 1, 3, 3}, {1, 1, 3, 3}, {1, 1},
{1, 1}, {1, 1}, {1, 1}, {1, 1}, {1, 1}, {1, 1}, {1, 1}, {1, 1}}
*)

You should then be able to extract pairs of columns from this data set using [[ ]], etc.