Pandas vs Linux Datascience

Question

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!

score 1 · Accepted Answer · answered Sep 11 '18 at 23:00

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:

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.

score 1 · Answer 2 · answered Sep 11 '18 at 23:09

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)

Pandas vs Linux Datascience

2 Answers2