<jupyter_start><jupyter_text>DataFrame and SeriesDataFrame and Series are the major structures in PandasDataFrame is like a table, and Series is each columnor we can say DataFrame is a dictionary of Series<jupyter_code>df = make_toy_df('ABC', range(5))
print('type of {}: {}'.format('df', type(df)))
df
print('type of {}: {}'.format("df['A']", type(df['A'])))
df['A']
## dictionary-style & attribute-style to access a column
print(df['A'])
print(df.A)
print(df['A'] is df.A)
## add a new column
df['shape'] = list(range(df.shape[0]))

display_command("df['A'] is df.A")
display_command("df['shape'] is df.shape")  # if already have this method
df
## drop column
df = df.drop(labels='shape', axis=1)
df<jupyter_output><empty_output><jupyter_text>Index and Select<jupyter_code>## we can use column_name_list to select multi columns
display_df('df', "df[['A']]", "df[['A', 'C']]")<jupyter_output><empty_output><jupyter_text>Array-style indexing[ ]: select row  iloc: select [row, col] by python-index (start from 0)  loc: select [row, col] by name<jupyter_code>## iloc & loc
display_df('df[2:4]', 'df.iloc[2:4, 1:3]', "df.loc[2:4, 'A':'C']")<jupyter_output><empty_output><jupyter_text>select by condition (index Boolean mask)<jupyter_code>df['Q1'] = np.random.randint(10, size=len(df))
df['Q2'] = np.random.randint(10, size=len(df))
display_df('df', "df[df['Q1']>5]", "df[(df['Q1']>5) & (df['Q2']<5)]")<jupyter_output><empty_output><jupyter_text>multiple conditions: intersection (&), union (|), symmetric difference (^)<jupyter_code>display_df("df[(df['Q1']>5) & (df['Q2']<5)]", "df[(df['Q1']>5) | (df['Q2']<5)]", "df[(df['Q1']>5) ^ (df['Q2']<5)]")<jupyter_output><empty_output><jupyter_text>check in list (`isin()`)<jupyter_code>df['name'] = ['Kai', 'Jim', 'Toby', 'Frank', 'YiHsin']
boy_list = ['Kai', 'Jim', 'Toby', 'Frank']
display_df("df", "df[df['name'].isin(boy_list)]", "df[~df['name'].isin(boy_list)]")<jupyter_output><empty_output><jupyter_text>check NaN (`isna()` or `isnull()`)<jupyter_code>df['NaN_test'] = [100, None, np.nan, '', np.nan]
display_df("df", "df[df['NaN_test'].isna()]", "df[~df['NaN_test'].isna()]")<jupyter_output><empty_output><jupyter_text>(＊) avoid using '==' to check NaN<jupyter_code>display_command('None == None')
display_command('np.nan == np.nan')
display_command('np.nan == None')<jupyter_output>> None == None:
 True

> np.nan == np.nan:
 False

> np.nan == None:
 False<jupyter_text>Concat and Merge  concat (append)<jupyter_code>df1 = make_toy_df('AB', range(3))
df2 = make_toy_df('AB', range(2))
display_df('df1', 'df2', 'pd.concat([df1, df2])', 
           'pd.concat([df1, df2], ignore_index=True)', 'pd.concat([df1, df2], axis=1)')<jupyter_output><empty_output><jupyter_text>merge (join)<jupyter_code>df1 = make_toy_df('AB', range(5))
df2 = make_toy_df('AC', range(2, 6))
display_df('df1', 'df2', "pd.merge(df1, df2, on='A')", "pd.merge(df1, df2, how='inner')", 
           "pd.merge(df1, df2, how='outer')", "pd.merge(df1, df2, how='left')")<jupyter_output><empty_output><jupyter_text>Apply function 5 methodologies for applying a function to a Pandas DataFrame:1. `Crude looping` over DataFrame rows using indices2. Looping with `iterrows()`3. Looping with `apply()`4. Vectorization with `Pandas series`5. Vectorization with `NumPy arrays`<jupyter_code>def my_func(x, y):
    return (x**2 + y**2)**0.5

## prepare data
size = 30000
df = pd.DataFrame({'x': np.random.randint(100, size=size), 
                   'y': np.random.randint(100, size=size)})

display_command('df.shape')
df.head()<jupyter_output>> df.shape:
 (30000, 2)<jupyter_text>1. Crude looping over DataFrame rows using indices<jupyter_code>%%timeit

distance_list = []
for i in range(0, len(df)):
    d = my_func(df.iloc[i]['x'], df.iloc[i]['y'])
    distance_list.append(d)

df['distance'] = distance_list<jupyter_output>8.95 s ± 306 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)<jupyter_text>2. Looping with iterrows()<jupyter_code>%%timeit

distance_list = []
for index, row in df.iterrows():
    distance_list.append(my_func(row['x'], row['y']))
    
df['distance'] = distance_list<jupyter_output>1.62 s ± 59.2 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)<jupyter_text>3. Looping with apply()<jupyter_code>%%timeit

df['distance'] = df.apply(lambda row: my_func(row['x'], row['y']), axis=1)<jupyter_output>844 ms ± 28 ms per loop (mean ± std. dev. of 7 runs, 1 loop each)<jupyter_text>4. Vectorization with Pandas series<jupyter_code>%%timeit

df['distance'] = my_func(df['x'], df['y'])<jupyter_output>1.49 ms ± 67.1 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)<jupyter_text>5. Vectorization with NumPy arrays<jupyter_code>%%timeit

df['distance'] = my_func(df['x'].values, df['y'].values)<jupyter_output>1.07 ms ± 23.8 µs per loop (mean ± std. dev. of 7 runs, 1000 loops each)<jupyter_text>Some conclusion1. Try to avoid using `for-loop` or `iter()` to iterate over all the DataFrame!  2. Method 4 & 5 only can use when `my_func()` is build by `numpy-function`    3. If `my_func()` is more complex, I would try to use `apply()` (method 3).       But need to watch out for `axis` argument.      Multiprocessing apply() example<jupyter_code>import pandas as pd
import numpy as np

data_path = './data_0627/urcos20180626.pd'
df_urcos = pd.read_pickle(data_path)

column_name = ['id', 'userid', 'username', 'prodid', 
               'prodname', 'score', 'type', 'season', 
               'skin', 'age', 'content', 'effects', 'reviewid', 
               'pageview', 'likes', 'pdate', 'crawl_time']

df_urcos.columns = column_name
print(df_urcos.shape)
import jieba

dict_path = './word_dict/try_dict.txt'
jieba.load_userdict(dict_path)

def my_func2(x):
    return jieba.lcut(x)

print(my_func2('智慧捷運零元月票'))
print(my_func2('忠孝東路高架化'))
print(my_func2('忠孝東路路下化'))
print(my_func2('忠孝東路直接中斷'))
%%time

df_urcos['content_cut'] = df_urcos['content'].apply(lambda x: my_func2(x))
print(df_urcos['content_cut'].head(), '\n')
%%time

import multiprocessing
 
def _apply_df(args):
    df, func, kwargs = args
    return df.apply(func, **kwargs)

def apply_by_multiprocessing(df, func, **kwargs):
    workers = kwargs.pop('workers')
    pool = multiprocessing.Pool(processes=workers)
    result = pool.map(_apply_df, [(d, func, kwargs) for d in np.array_split(df, workers)])
    pool.close()
    return pd.concat(list(result))


## Number of CPU cores on your system
cores = multiprocessing.cpu_count() 

df_urcos['content_cut'] = apply_by_multiprocessing(df_urcos['content'], my_func2, workers=cores)

print(df_urcos['content_cut'].head(), '\n')<jupyter_output>0    [寶藝, 保濕, 冷敷, 劑, 試用, 寶藝, 的, 經典, 明星, 產品, ，, 可以, ...
1    [這次, 的, 保濕, 冷敷, 劑, ，, 是, 由, 一個, 朋友, 介紹, 的, ，, ...
2    [寶藝, 的, 酵素, 冷膜,  , &, 保溼, 冷敷, 劑, ，, 在, 10, 幾, ...
3    [會, 買, 這罐, 產品, 完全, 是, 被, 藝人燒, 到, 阿, ~, ~, ~, ~...
4    [兩年, 多前, 從倫敦, 回台灣, ，, 膚況, 不是, 很, 穩定, ～, 本來, 就,...
Name: content_cut, dtype: object 

CPU times: user 8.48 s, sys: 2.3 s, total: 10.8 s
Wall time: 1min 33s<jupyter_text>use apply() in conditioncan deal with more complex condition, e.g. regular expression<jupyter_code>def my_func3(x, y):
    return x + y > 195

df[df.apply(lambda row: my_func3(row['x'], row['y']), axis=1)]<jupyter_output><empty_output><jupyter_text>Groupby* **Splitting** the data into groups based on some criteria.* **Applying** a function to each group independently.* **Combining** the results into a data structure.<jupyter_code>groupby_pic() # this function build at the end
df_friends = pd.DataFrame([['<NAME>','party','2017-08-04 08:00:00',51],
                           ['<NAME>','coffee','2017-08-04 13:00:00',60],
                           ['<NAME>','party','2017-08-04 15:00:00',59],
                           ['<NAME>','coffee','2017-08-04 16:00:00',40],
                           ['<NAME>','party','2017-08-04 09:00:00',54],
                           ['<NAME>','party','2017-08-04 10:00:00',67],
                           ['<NAME>','football','2017-08-04 08:00:00',84],
                           ['Gunther','work','2017-08-04 14:00:00']],
                           columns=['name','activity','timestamp','money_spent'])

df_friends['timestamp'] = pd.to_datetime(df_friends['timestamp'], format='%Y-%m-%d %H:%M:%S')
df_friends
## iter group
for index, group in df_friends.groupby(['name']):
    print('index: {}'.format(index))
    IPython.display.display(group)
    print()<jupyter_output>index: <NAME><jupyter_text>Aggregate* `count()`:	Total number of items* `first()`, `last()`:	First and last item* `mean()`, `median()`:	Mean and median* `min()`, `max()`:	Minimum and maximum* `std()`, `var()`:	Standard deviation and variance* `mad()`:	Mean absolute deviation* `prod()`:	Product of all items* `sum()`:	Sum of all items<jupyter_code>display_df("df_friends.groupby(['name']).count()", 
           "df_friends.groupby(['name']).count().reset_index()")  # count() don't count the NaN
display_df("df_friends.groupby(['name', 'activity']).mean()", 
           "df_friends.groupby(['name', 'activity']).mean().reset_index()")
## add_suffix(), add_prefix()

display_df("df_friends.groupby(['name'])['money_spent'].agg([np.sum, np.mean, np.max])", 
           "df_friends.groupby(['name'])['money_spent'].agg([np.sum, np.mean, np.max]).add_suffix('_money').reset_index()")<jupyter_output><empty_output><jupyter_text>Filterfilter the original DataFrame by group-condition<jupyter_code>def filter_func(x):
    return x['money_spent'].mean() > 60

display_df("df_friends", "df_friends.groupby(['name']).filter(filter_func)")<jupyter_output><empty_output><jupyter_text>Apply  Use group result to calculatenot change the DF form<jupyter_code>def centralize(x):
    x['money_centralize'] = x['money_spent'] - x['money_spent'].mean()
    return x

display_df("df_friends", "df_friends.groupby(['name']).apply(lambda x: centralize(x))")<jupyter_output><empty_output><jupyter_text>Calculate and combine the same groupchange the DF form<jupyter_code>def coffee_money(df):
    df_coffee = df[df['activity']=='coffee']
    df_other = df[df['activity']!='coffee']
    return pd.Series([df.shape[0], 
                      df_coffee.shape[0], df_other.shape[0],
                      df_coffee['money_spent'].mean(), df_other['money_spent'].mean(),  
                      df_coffee['money_spent'].sum(), df_other['money_spent'].sum()], 
                      index=['total_count', 'coffee_count', 'other_count', 
                             'coffee_money_mean', 'other_money_mean',
                             'coffee_money_sum', 'other_money_sum'])

display_df("df_friends", "df_friends.groupby(['name']).apply(lambda x: coffee_money(x)).reset_index()")<jupyter_output><empty_output><jupyter_text>Magic Commands (from IPython)Usually use:* `%time`: Time the execution of a single statement* `%timeit`: Time repeated execution of a single statement for more accuracy* `%prun`: Run code with the profiler* `%lprun`: Run code with the line-by-line profiler* `%memit`: Measure the memory use of a single statement* `%mprun`: Run code with the line-by-line memory profilerNote: `%`: line-magic, `%%`: cell-magic<jupyter_code>## list all magic commands
%lsmagic<jupyter_output><empty_output><jupyter_text>Referenceshttp://pandas.pydata.org/pandas-docs/stable    https://jakevdp.github.io/PythonDataScienceHandbook  https://towardsdatascience.com/pandas-tips-and-tricks-33bcc8a40bb9  https://engineering.upside.com/a-beginners-guide-to-optimizing-pandas-code-for-speed-c09ef2c6a4d6  https://gist.github.com/yong27/7869662<jupyter_code>%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import seaborn as sns

# from:
# https://jakevdp.github.io/PythonDataScienceHandbook/06.00-figure-code.html#Split-Apply-Combine

def draw_dataframe(df, loc=None, width=None, ax=None, linestyle=None,
                   textstyle=None):
    loc = loc or [0, 0]
    width = width or 1

    x, y = loc

    if ax is None:
        ax = plt.gca()

    ncols = len(df.columns) + 1
    nrows = len(df.index) + 1

    dx = dy = width / ncols

    if linestyle is None:
        linestyle = {'color':'black'}

    if textstyle is None:
        textstyle = {'size': 12}

    textstyle.update({'ha':'center', 'va':'center'})

    # draw vertical lines
    for i in range(ncols + 1):
        plt.plot(2 * [x + i * dx], [y, y + dy * nrows], **linestyle)

    # draw horizontal lines
    for i in range(nrows + 1):
        plt.plot([x, x + dx * ncols], 2 * [y + i * dy], **linestyle)

    # Create index labels
    for i in range(nrows - 1):
        plt.text(x + 0.5 * dx, y + (i + 0.5) * dy,
                 str(df.index[::-1][i]), **textstyle)

    # Create column labels
    for i in range(ncols - 1):
        plt.text(x + (i + 1.5) * dx, y + (nrows - 0.5) * dy,
                 str(df.columns[i]), style='italic', **textstyle)
        
    # Add index label
    if df.index.name:
        plt.text(x + 0.5 * dx, y + (nrows - 0.5) * dy,
                 str(df.index.name), style='italic', **textstyle)

    # Insert data
    for i in range(nrows - 1):
        for j in range(ncols - 1):
            plt.text(x + (j + 1.5) * dx,
                     y + (i + 0.5) * dy,
                     str(df.values[::-1][i, j]), **textstyle)


#----------------------------------------------------------
# Draw figure

import pandas as pd

def groupby_pic():
    df = pd.DataFrame({'data': [1, 2, 3, 4, 5, 6]},
                       index=['A', 'B', 'C', 'A', 'B', 'C'])
    df.index.name = 'key'


    fig = plt.figure(figsize=(8, 6), facecolor='white')
    ax = plt.axes([0, 0, 1, 1])

    ax.axis('off')

    draw_dataframe(df, [0, 0])

    for y, ind in zip([3, 1, -1], 'ABC'):
        split = df[df.index == ind]
        draw_dataframe(split, [2, y])

        sum = pd.DataFrame(split.sum()).T
        sum.index = [ind]
        sum.index.name = 'key'
        sum.columns = ['data']
        draw_dataframe(sum, [4, y + 0.25])

    result = df.groupby(df.index).sum()
    draw_dataframe(result, [6, 0.75])

    style = dict(fontsize=14, ha='center', weight='bold')
    plt.text(0.5, 3.6, "Input", **style)
    plt.text(2.5, 4.6, "Split", **style)
    plt.text(4.5, 4.35, "Apply (sum)", **style)
    plt.text(6.5, 2.85, "Combine", **style)

    arrowprops = dict(facecolor='black', width=1, headwidth=6)
    plt.annotate('', (1.8, 3.6), (1.2, 2.8), arrowprops=arrowprops)
    plt.annotate('', (1.8, 1.75), (1.2, 1.75), arrowprops=arrowprops)
    plt.annotate('', (1.8, -0.1), (1.2, 0.7), arrowprops=arrowprops)

    plt.annotate('', (3.8, 3.8), (3.2, 3.8), arrowprops=arrowprops)
    plt.annotate('', (3.8, 1.75), (3.2, 1.75), arrowprops=arrowprops)
    plt.annotate('', (3.8, -0.3), (3.2, -0.3), arrowprops=arrowprops)

    plt.annotate('', (5.8, 2.8), (5.2, 3.6), arrowprops=arrowprops)
    plt.annotate('', (5.8, 1.75), (5.2, 1.75), arrowprops=arrowprops)
    plt.annotate('', (5.8, 0.7), (5.2, -0.1), arrowprops=arrowprops)

    plt.axis('equal')
    plt.ylim(-1.5, 5)
    plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.194996664442962, 'fraction_numerical': 0.034156104069379585, 'mean_word_length': 3.9540647719762063, 'pattern_counts': {'":': 0, '<': 104, '<?xml version=': 0, '>': 111, 'https://': 5, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '25', 'dir': 'jupyter-structured-clean-dedup', 'id': '426119', 'license': 'MIT', 'n_tokens_mistral': 6175, 'n_tokens_neox': 5754, 'n_words': 1236, 'path': 'other/ray_workshop_0723.ipynb', 'repo_name': 'thisray/MS_Course', 'size': '15049'}