Efficient way to get value from a dataframe and append new dataframe I have a dataframe that have about 200 million rows. the example of dataframe is like this:

date         query
29-03-2019   SELECT * FROM table WHERE ..
30-03-2019   SELECT * FROM ... JOIN ... ON ...WHERE ..
....         ....
20-05-2019   SELECT ...


I have a function to get table(s) name, attribute(s) name from dataframe above and append to new dataframe.

import sqlparse
from sqlparse.tokens import Keyword, DML
def getTableName(sql):
    def getTableKey(parsed):
        findFrom = False
        wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
        for word in parsed.tokens:
            if word.is_group:
                for f in getTableKey(word):
                    yield f
            if findFrom:
                if isSelect(word):
                    for f in getTableKey(word):
                        yield f
                elif word.ttype is Keyword:
                    findFrom = False
                    StopIteration
                else:
                    yield word
            if word.ttype is Keyword and word.value.upper() in wordKey:
                findFrom = True
    tableName = []
    query = (sqlparse.parse(sql))
    for word in query:
        if word.get_type() != 'UNKNOWN':
            stream  = getTableKey(word)
            table   = set(list(getWord(stream)))
            for item in table:
                tabl = re.sub(r'^.+?(?&lt;=[.])','',item)
                tableName.append(tabl)
    return tableName


and the function to get attribute is just like getTableName the different is the wordKey. 

function to process dataframe is like this:

import pandas as pd
def getTableAttribute(dataFrame, queryCol, date):
    tableName       = []
    attributeName   = []
    df              = pd.DataFrame()
    for row in dataFrame[queryCol]:
        table       = getTableName(row)
        tableJoin   = getJoinTable(row)
        attribute   = getAttribute(row)
        #append into list
        tableName.append(table+tableJoin)
        attributeName.append(attribute)
    df = dataFrame[[date]].copy()
    df['tableName']      = tableName
    df['attributeName']  = attributeName
    print('Done')
    return df


The result of the function is like this:

date        tableName  attributeName
29-03-2019  tableN     attributeM
30-03-2019  tableA     attributeB
....        ...        ...
20-05-2019  tableF     attributeG 


But as this is my first try, I need an opinion about what I've tried, because my code runs slow with large file.
getTableKey

I'm not sure it's good style to define functions inside other functions unless you are implementing some sort of closure:

# this is much easier to read as a separate function
# and you don't incur the cost of defining it every time
# you call the encapsulating function
def getTableKey(parsed):
    findFrom = False
    wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
    for word in parsed.tokens:
        if word.is_group:
            for f in getTableKey(word):
                yield f
        if findFrom:
            if isSelect(word):
                for f in getTableKey(word):
                    yield f
            elif word.ttype is Keyword:
                findFrom = False
                StopIteration
            else:
                yield word
        if word.ttype is Keyword and word.value.upper() in wordKey:
            findFrom = True


def getTableName():
    tableName = []
    query = (sqlparse.parse(sql))
    for word in query:
        if word.get_type() != 'UNKNOWN':
            stream  = getTableKey(word)
            table   = set(list(getWord(stream)))
            for item in table:
                tabl = re.sub(r'^.+?(?&lt;=[.])','',item)
                tableName.append(tabl)
    return tableName


yield from syntax

Furthermore, instead of using for f in getTableKey(word): yield f, later versions of python3 introduced the yield from syntax:

def getTableKey(parsed):
    findFrom = False
    wordKey = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']
    for word in parsed.tokens:
        if word.is_group:
            yield from getTableKey(word)

        # combine this, since it's exactly this combination that will yield
        # f, there's no elif or else
        if findFrom and isSelect(word):
            yield from getTableKey(word)
        # rest of func


This leverages less function calls and is faster:

import dis

def f():
    for i in range(10000):
        yield i

def g()
    yield from range(10000)

dis.dis(f)
2           0 SETUP_LOOP              22 (to 24)
              2 LOAD_GLOBAL              0 (range)
              4 LOAD_CONST               1 (10000)
              6 CALL_FUNCTION            1
              8 GET_ITER
        &gt;&gt;   10 FOR_ITER                10 (to 22)
             12 STORE_FAST               0 (i)

  3          14 LOAD_FAST                0 (i)
             16 YIELD_VALUE
             18 POP_TOP
             20 JUMP_ABSOLUTE           10
        &gt;&gt;   22 POP_BLOCK
        &gt;&gt;   24 LOAD_CONST               0 (None)
             26 RETURN_VALUE

dis.dis(g)
2           0 LOAD_GLOBAL              0 (range)
              2 LOAD_CONST               1 (10000)
              4 CALL_FUNCTION            1
              6 GET_YIELD_FROM_ITER
              8 LOAD_CONST               0 (None)
             10 YIELD_FROM
             12 POP_TOP
             14 LOAD_CONST               0 (None)
             16 RETURN_VALUE



To show the speed gain:

python -m timeit -s 'from somefile import f, g' 'list(f())'
1000 loops, best of 3: 507 usec per loop

python -m timeit -s 'from somefile import f, g' 'list(g())'
1000 loops, best of 3: 396 usec per loop


set vs list membership tests

Checking for membership in a list over and over is slow, worst case being O(N). To fix this, make word_list a set, which yields O(1) lookup:

python -m timeit -s "x = ['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN']" "'FULL JOIN' in x"
10000000 loops, best of 3: 0.0781 usec per loop

python -m timeit -s "x = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])" "'FULL JOIN' in x"
10000000 loops, best of 3: 0.0246 usec per loop


So create the set like:

def getTableName(...):
   ~snip~
   wordKey = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])


Though it might be even better to move this out of the getTableKey function entirely so you aren't paying for the re-construction of this set during every iteration:

# add a positional arg for it
def getTableKey(parsed, wordKey):
    # rest of func


And define it in getTableAttribute like:

def getTableAttribute(dataFrame, queryCol, date):
    wordKey = set(['FROM','JOIN', 'LEFT JOIN', 'INNER JOIN', 'RIGHT JOIN', 'OUTER JOIN', 'FULL JOIN'])
    ~snip~
    for row in dataFrame:
        table_name = getTableName(row, wordKey)


getTableName

There's no need to enclose sqlparse.parse in parens, as it will just default to whatever the enclosed value is:

x = (5)
x
5


You are losing speed calling set(list(iterable)), since set will consume any iterable, and it looks like getWord(stream) is an iterable already:

    table   = set(getWord(stream))


re.compile

If you are going to call a regex many times, it is better to compile it once, then call compiled.sub where compiled is the output of re.compile("&lt;expression&gt;"):

 python -m timeit -s 'import re; x = "abc123"' 'for i in range(100000): re.match("\w\d", x)'
10 loops, best of 3: 67.5 msec per loop

python -m timeit -s 'import re; x = "abc123"; y = re.compile("\w\d")' 'for i in range(100000): y.match(x)'
10 loops, best of 3: 28.1 msec per loop


To make this work, you might consider adding an arg in getTableName to allow for a compiled regex:

# somewhere in getTableAttribute.py file
import re
def getTableAttribute(dataFrame, queryCol, date):
    tableName       = []
    attributeName   = []
    table_re        = re.compile(r'^.+?(?&lt;=[.])')
    df              = pd.DataFrame()
    for row in dataFrame[queryCol]:
        table       = getTableName(row, table_re)
        # rest of code


def getTableName(sql, re_expr):
    ...
    for item in table:
        tabl = re_expr.sub('', item)
        # rest of code