4
$\begingroup$

I joined a data science learning community in my college and we are using linux terminal commands and awk commands to practice gathering some information from big datasets stored in csv files. About 7140596 columns by 29 rows in a single file.

A sample question would be: "What was the average arrival delay (in minutes) for flights in 2005?" where we have to sum the value of the delay for every row and divide by the total number or rows.

I know that similar data manipulation can be done in Pandas in a Jupyter notebook and was wondering what are the advantages and disadvantages of each method.

Thank you!

$\endgroup$

2 Answers 2

1
$\begingroup$

Pandas dataframes have many many more high level functions integrated right into the base classes that store the data for you.

Some of the commandline tools can be pretty powerful for manipulating text efficiently (Perl in particular), but I would argue that the learning curve is quite steep and the interactive experience is not as friendly. For one thing, it isn't easy to simply get a glimpse of your data or create an attractive plot.

While I admit that I am not a pro awk/sed or Perl user, I am pretty sure it will be a little less intuitive in those tools/languages to do something like this hypothetical computation, which involves numerical data and text:

In [1]: import pandas as pd
In [2]: import numpy as np

# Create a DataFrame holding some data over a time range

In [3]: df = pd.DataFrame({'A' : ['foo', 'bar', 'foo', 'bar',
                                  'foo', 'bar', 'foo', 'foo']*4,
                           'B' : ['one', 'one', 'two', 'three',
                                  'two', 'two', 'one', 'three']*4,
                           'C' : np.random.randn(32)},
                          index=pd.date_range('01.01.2018', periods=32))

In [4]: df.head()
Out[4]:
              A      B         C
2018-01-01  foo    one  0.965554
2018-01-02  bar    one  0.053814
2018-01-03  foo    two  1.075539
2018-01-04  bar  three -0.999941
2018-01-05  foo    two -1.940361

Now imagine we want to group the rows so we have just rows with column A contains foo in one table, and another with just the rows containing foo.

From those two tables, we only care about column C. We want to compute the moving average over a 5 day time-frame. The moving average will leave some NaN values at the beginning, so we want to drop those time-steps.

Oh, and we want to visualise that!

In[5]: df.groupby('A')['C'].rolling(5).mean().dropna().plot(grid=True, legend=True)

From that one line of code, we get this:

Not pretty

The above also highlights the abundance of other powerful and specialised packages avavilable within the Python environment - here I used numpy in conjunction with Pandas.


For manipulating text files, perhaps cleaning up scraped text and parsing large amounts of text using regular expressions, it might be faster to use one of the commandline options, but as soon as you want to do any data science, I would really recommend using some specialised tools, like Pandas.

$\endgroup$
2
  • $\begingroup$ Very informative! So, as I understand, Linux is ok for some basic manipulation, but is inefficient when it comes to getting elaborate inferences from data? $\endgroup$
    – boinka
    Sep 11, 2018 at 23:18
  • $\begingroup$ Correct. Pandas just has so many useful tools built in, that you save a lot of time. Just a point on terminology here: Linux in general is the entire operating system. When you say "commandline tools", it really only means tools such as awk, sed, perl and so on, when used in the terminal. . In my opinion, Linux is the best operating system for data science. You can use Pandas, however, in other operating systems (Windows/Mac OSX). $\endgroup$
    – n1k31t4
    Sep 12, 2018 at 9:05
1
$\begingroup$

If they are using bash directly to go through CSV files then this is very inefficient and will lead to very long query times as the database grows. Furthermore, bash quickly gets very complicated when you want to do analysis that is not standard, for this reason you definitely want to be working in Python not in bash.

What I suggest:

  • Use Linux as your operating system
  • Set up a database such as postgres http://postgresguide.com/setup/install.html
  • Build a scraper that inputs your data stored into the CSV files into a table in postgres.
  • Create some indices on the data based on the queries you will perform to significantly decrease query latency.

You can then use Python to query the postgres tables using the following.

import pandas as pd
import psycopg2

sql = "SELECT arrival_delay FROM table_name WHERE year = 2005"
conn = psycopg2.connect(**params)
df = pd.read_sql(sql, conn)
$\endgroup$
2
  • $\begingroup$ Than you for your response. I will look into the database. So, importing the csv file into a df is a bad idea? $\endgroup$
    – boinka
    Sep 11, 2018 at 23:17
  • $\begingroup$ It's just very slow. So as you will get more data this will be an unfeasible solution. $\endgroup$
    – JahKnows
    Sep 11, 2018 at 23:21

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