3
$\begingroup$

I have a very large csv file which Apple Numbers won't open. I can open it with TextEdit, but each row is so long it forms multiple lines in the document and makes the document difficult to understand. Is there a tool for opening a csv and exploring different rows and columns?

$\endgroup$
  • $\begingroup$ You could try Microsoft Excel (if you have it installed on your computer) or similar open source software, such as OpenOffice/LibreOffice Calc or Gnumeric: en.wikipedia.org/wiki/Comparison_of_spreadsheet_software. $\endgroup$ – Aleksandr Blekh Oct 5 '15 at 2:47
  • 2
    $\begingroup$ sigh what is "very large" (bytes, fields per line, lines, columns)? This "multiple lines" thing, are you sure its not just that the data really is on multiple lines in the CSV? $\endgroup$ – Spacedman Oct 5 '15 at 14:20
5
$\begingroup$

When you hit the limits of an application like Apple Numbers, or Excel, you need to start using a programming language like Python, R or C. Using a programming language, you write your own application, which is not constrained by arbitrary limits like 220 rows by 214 columns. (Of course, you are still constrained by the physical memory and the way that a particular language addresses it: R and Python tend to be more resource-intensive than C).

Let me give you an example using Python. You can install Python for your system from the official site. If you install one of the latest versions, you will already get pip as an installer of further Python packages. Install Pandas by starting a terminal (on OS X, there is a 'Terminal' application, on Windows this used to be a MS-DOS window ...), and typing into that terminal:

pip install pandas

You can then start Python in an interactive session by typing python in a terminal. In the Python shell, you can read the data as mentioned in @tasos 's answer:

>>> import pandas as pd
>>> df = pd.read_csv('name_of_your_file.csv')

Here you can use the describe method of the df DataFrame to get some statistics about the read data. These numbers are for a randomly generated data frame:

>>> df.describe()
          0         1         2
count  3.000000  3.000000  3.000000
mean  -0.150869  0.444380 -0.117066
std    0.751421  0.697880  0.565328
min   -1.017424 -0.356764 -0.506030
25%   -0.386514  0.206466 -0.441313
50%    0.244396  0.769696 -0.376595
75%    0.282409  0.844952  0.077416
max    0.320422  0.920208  0.531427

Then you can generate plots for different columns, one against the other, using the Pandas plotting functions. Check the many examples in the linked documentation. Here is a small example from there:

import matplotlib.pyplot as plt
import pandas as pd
import bumpy as np

df = pd.DataFrame({'a': np.random.randn(1000) + 1, 'b': np.random.randn(1000),
                   'c': np.random.randn(1000) - 1}, columns=['a', 'b', 'c'])
plt.figure()
df.plot(kind='hist', alpha=0.5)

Usually, you will need to use another call more in order to save it to a file:

plt.save('output.png')  ## or *.svg, *.pdf

In the file you should find something similar to this figure.

$\endgroup$
2
$\begingroup$

Unfortunately, the highest number of rows in both Numbers and Microsoft excel is 1,048,576. So, if you have a file bigger than this, you are not able to open it.

Some ideas:

  1. Connect your file in an SQL database and work with it from there.
  2. Use another tool like delimitware which let you open files up to 2 billions rows.
  3. Use Python Pandas with pandas.read_csv('filename') and then df.head(5) to check the first 5 rows.
  4. Split the file in smaller files with less than 1m rows per each chunk and use Numbers or Microsoft Excel to read it.
  5. Upload it to Azure ML and work on a new experiment with it.
$\endgroup$
2
$\begingroup$

You may install R - you are limited only with your RAM

 ## read csv file
 df <- read.csv("l.csv")

 ## column names
 > colnames(df) 
 [1] "a" "b"

 > head(df)
     a  b
   1 1  1
   2 2  4
 ...

 # quick overview
 > summary(df)
        a                 b            
  Min.   :    1.0   Min.   :        1  
  1st Qu.: 2500.8   1st Qu.:  6253751  
  Median : 5000.5   Median : 25005000  
  Mean   : 5000.5   Mean   : 33338334  
  3rd Qu.: 7500.2   3rd Qu.: 56253750  
  Max.   :10000.0   Max.   :100000000  

 > plot(df2,type='l')

enter image description here

Check docu for other graphic and statistics features.

$\endgroup$
1
$\begingroup$

Since you are on OS X you can use the terminal to explore your file without storing everything in memory. The head -5 filename.csv command for example will display the first 5 lines of your file. You could even do head -10 filename.csv > newfile.csv to store the first 10 lines in a new file and open it with Apple Numbers to examine what's inside.

For anything more than that I'm afraid you would need to write a script to run through each line of the file one by one and compute the relevant descriptive statistics.

$\endgroup$
  • $\begingroup$ An awk script to sample 1 in N of the lines either at random or in order is pretty trivial. $\endgroup$ – Spacedman Oct 5 '15 at 14:24
  • $\begingroup$ I'd rather "less -S filename.csv", as suggested here superuser.com/questions/272818/…. $\endgroup$ – Valentas Oct 5 '15 at 19:17
  • $\begingroup$ @Spacedman not everybody is familiar with awk though. You should probably add it as an answer so that this question covers a wider array of possible ways of exploring data. $\endgroup$ – Jérémie Clos Oct 6 '15 at 10:34
  • $\begingroup$ I'll wait until the OP has engaged with us - given the current four answers have such wide breadth and the lack of OP comments I do not see the point of further activity at this time. $\endgroup$ – Spacedman Oct 6 '15 at 12:12
1
$\begingroup$

There is a neat online tool called RAWGraphs which will chart your data in your browser. You drag and drop your file and it will try and make sense of it. It's made for web designers, so in the end you export an HTML representation of your visualization, but it's a fine tool in its own right.

enter image description here

$\endgroup$
  • $\begingroup$ Have you tried loading a large data set into that (not that the OP has said what "large" means yet)? Go on, feed it a million rows and see what happens... $\endgroup$ – Spacedman Oct 7 '15 at 17:29

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.