Hot answers tagged

82

The real first question is why are people more productive with DataFrame abstractions than pure SQL abstractions. TLDR; SQL is not geared around the (human) development and debugging process, DataFrames are. The main reason is that DataFrame abstractions allow you to construct SQL statements whilst avoiding verbose and illegible nesting. The pattern of ...


43

As much as there is overlap in the application of these two things, this is comparing apples to oranges. pandas is a data analysis toolkit implemented in Python, a general purpose programming language. SQL is a domain-specific language for querying relational data (usually in an relational database management system which SQLite, MySQL, Oracle, SQL Server, ...


28

First, pandas is not that much popular. I use both pandas and SQL. First I try to understand the task- if it can be done in SQL, I prefer SQL because it is more efficient than pandas. Try working on a large data (10,000,000 x 50). Try to do some groupby operation in both SQL and pandas. You will understand. I use pandas where it comes handy- like splitting ...


20

If you want to tackle the problem from another perspective, with an end to end learning, such that you don't specify ahead of time this large pipeline you've mentioned earlier, all you care about is the mapping between sentences and their corresponding SQL queries. Tutorials: How to talk to your database Papers: Seq2SQL: Generating Structured ...


15

I'm one of those people who would use (in my case) R's dplyr (the language, not necessarily the tool) in every case if I could even though I know my SQL. The major benefit I see in Pandas/dplyr/data.table pipelines is that the operations are atomic and can be read top to bottom. In SQL you need to parse the whole script, jumping around (what's being ...


13

R and SQL are two completely different beasts. SQL is a language that you can use to query data that is stored in databases as you already experienced. The benefits of SQL versus R lays mostly in the fact of the database server (MS SQL, Oracle, PostgreSQL, MySQL, etc.). Most, if not all, modern database servers permit multiple users to query data from the ...


10

For data load, Postgre outperforms MongoDB. MongoDB is almost always faster when returning query counts. PostgreSQL is almost always faster for queries using indexes. Check out this website and this one too for more info. They have very detailed explanations.


9

I'm fairly new to Pandas/Python but have 20+ years as a SQLServer DBA, architect, administrator, etc.. I love Pandas and I'm pushing myself to always try to make things work in Pandas before returning to my comfy, cozy SQL world. Why RDBMS's are Better: The advantage of RDBMS's are their years of experience optimizing query speed and data read operations. ...


8

You're an online retailer. Like Amazon. You keep your purchase data for different categories of items in different tables, but all website users have one account with one ID. Inner Join: You have two datasets, one with User IDs and purchases of clothing data, the second dataset has User IDs and purchases of books data. You want to find out who purchases ...


7

Things Pandas can do, that SQL can't do df.describe() Plotting, e.g. df['population'].plot(kind='hist') Use a dataframe directly for training machine learning algorithms Things Pandas can do, I wasn't aware that SQL can do as well Export to csv: df.to_csv('foobar.csv'). This is important when you want to show something to a business owner who wants to ...


6

Based on the documentation 0.22 and 0.24.1, the flavor does not exist in the argument list of the to_sql method. You're probably running the 0.24.1 version which does not need flavor argument.


6

There is no need to download data in CSV format, in most cases it is actually bad practice. Consider cases where data size > 1GB and is updated daily. This would add a considerable overhead and is not easy to automate. Instead you can take a look at various R packages that exist to fetch data from your SQL database. Using RODBC, SQLite, sqldf or other ...


5

I suppose you could do this, but if your goal is simply to store 15 boolean values in a single column you are complicating things unnecessarily. Instead of going to all the trouble to compute the prime factors of the stored value, why don't you just store the flags as a bit string? Your example of 15 different possible values could be stored in a single ...


5

I found a package called pandasql, which is based on sqldf for R. It seems quite a bit slower than doing the transformations with the pandas package, but it gets the job done. Just put the SQL query into a string like this: query_string = """ select * from df """ Then use the string in the pandasql.sqldf package, as follows: new_dataframe = pandasql....


5

NLTK has an excellent step by step guide on everything you need to convert human language to an SQL query using the nltk package in python. It’s rudimentary, but it answers your question.


4

Here is a suggestion: Code your analysis in such a way that it can be run on sub-samples. Code a complementary routine which can sample, either randomly, or by time, or by region, or ... This may be domain-specific. This is where your knowledge enters. Combine the two and see if the results are stable across subsamples.


4

Can't you create a hash for each classes, and then merge rows by rows, field by field only the classes where the hash changed ? It should be faster if most of the classes don't change.. Or a hash of each rows or maybe columns.. depending on how the data normally change..


4

If you need SQL code that runs various outlier detection methods against any arbitrary table, check out my series of articles and code samples geared towards SQL Server. I provide some preliminary code for Grubb's Tests, Z-Scores and Modified Z-Scores, Interquartile Range, Dixon's Q-Test, GESD, the Tietjen-Moore Test, Pierce's Criterion, Chauvenet's ...


4

"Premature optimization is the root of all evil". --Knuth You could do this, but why? You definitely don't want to do this, if you're going to feed the result into a classifier: most classifiers will perform worse after this transformation. There's no point to do this, if you're trying to save space in a database: hard disks can store hundreds of ...


4

I'll attempt to answer this question based on my own experience. In contrast to the other answers, I prefer Sql for deep learning and big-data-related things. There are numerous reasons for that. As it can be seen here, Pandas provides an intuitive, powerful, and fast data analysis experience on tabular data. However, because Pandas uses only one thread ...


4

If you are looking to use US Census data, the American FactFinder website: https://factfinder.census.gov/faces/nav/jsf/pages/index.xhtml allows you to search for Census data in different ways and then download tables in csv format. For example, on the Community Facts site, you can enter a state, county, city, town, or zip code and obtain the population of ...


3

These aren't even comparable, really. SQL is a language meant for accessing data, R is a language meant for working with data. SQL isn't an effective tool for munging because it it's difficult to see intermediate steps and when it throws errors, it isn't likely to address the form/quality/structure of your data. My workflow is typically: Get raw data ...


3

If you want to approach this from a SQL perspective, then broadly I would identify any classification variables that cause different behaviour. Then perform something like the following on a number of analysis variables. SELECT ClassificationVar1, ClassificationVar2, MIN(AnalysisVar1) as Min_AnalysisVar1, MAX(AnalysisVar1) as Max_AnalysisVar1,...


3

Sounds interesting. Could the solution be to dump the data out, build a fast custom processing thingie to run it through and then import it back to the database? I've seen some blazing fast Java-based text processing tools for topic modeling that handle millions of lines of text per second. If it's an option then you can build a shell script to first dump ...


3

I would use SQL and create a set of structured fields that are common across all applications (name, school, years of experience, job they are applying for, etc.) with a field containing the raw application for you 'semi-structured' part of the data. You can always do something fancy with the raw application field, but if you want to get some summary ...


3

I used R for several years but have since moved to Python and so I have a hard time understanding output.OLS's data hierarchy. Nonetheless, here are my thoughts. In-Memory Databases: If you're struggling to fit your R object in memory, then I'm guessing the memory requirement is too great for your laptop (i.e., the problem isn't due a 32-bit installation of ...


3

I thought I would add that I do a lot of time-series based data analysis, and the pandas resample and reindex methods are invaluable for doing this. Yes, you can do similar things in SQL (I tend to create a DateDimension table for helping with date-related queries), but I just find the pandas methods much easier to use. Also, as others have said, the rest ...


3

What do you mean exactly with competitive as a data scientist? Unfortunately, many employers will have different expectations of someone they hire to be a Data Scientist, so there isn't a single answer! In any case, I think it is a good idea to know three components to be effective with databases: Managing a connection: how to create and connect to a ...


2

You might try Azure Table Storage. Since you can't lock yourself down to a specific schema (since one data product might be aggregates whereas another might be time series or something else), Azure Table storage would give you the flexibility of storing data from multiple sources, each having their own format. This would also lend itself to making a system ...


Only top voted, non community-wiki answers of a minimum length are eligible