<jupyter_start><jupyter_text>Group  Groupby groupby() is a very powerful function with a lot of variations.It makes the task of splitting the dataframe over some criteria really easy and efficient.Any groupby operation involves one of the following operations on the original object. They are −   - Splitting the Object   - Applying a function   - Combining the resultsIn many situations, we split the data into sets and we apply some functionality on each subset. In the apply functionality, we can perform the following operations −   - Aggregation − computing a summary statistic   - Transformation − perform some group-specific operation   - Filtration − discarding the data with some condition  Syntax<jupyter_code>.groupby(by=None, axis=0, level=None, as_index=True, sort=True, 
         group_keys=True, squeeze=False, **kwargs)

by :         mapping, function, str, or iterable
axis :       int, default 0
level :      If the axis is a MultiIndex (hierarchical), group by a particular level or levels
as_index :   For aggregated output, return object with group labels as the index. 
sort :       Sort group keys. Get better performance by turning this off. 
group_keys : When calling apply, add group keys to index to identify pieces
squeeze :    Reduce the dimensionality of the return type if possible
Pandas object can be split into any of their objects. 
There are multiple ways to split an object like −
   - obj.groupby('key')
   - obj.groupby(['key1','key2'])
   - obj.groupby(key,axis=1)<jupyter_output><empty_output><jupyter_text>Examples<jupyter_code>Examples of use

# Group by the name of sales rep
df.groupby(f['Sales Rep'].str.split(' ').str[0]).size()

# Grouping by whether or not there is a “William” in the name of the rep
df.groupby(df['Sales Rep'].apply(lambda x: 'William' in x)).size()

# Group by random series (for illustrative purposes only)
df.groupby(pd.Series(np.random.choice(list('ABCDG'),len(df)))).size()

# Grouping by three evenly cut “Val” buckets
df.groupby(pd.qcut(x=df['Val'],q=3,labels=['low','mid','high'])).size()

# Grouping by custom-sized “Val” buckets
df.groupby(pd.cut(df['Val'],[0,3000,5000,7000,10000])).size()

# Using Grouper for looking at dates 
df.groupby(pd.Grouper(key='Date',freq='Y')).size()    #(years here)
df.groupby(pd.Grouper(key='Date',freq='Q')).size()    #(quarters here)

# Grouping by multiple columns
df.groupby(['Sales Rep','Company Name']).size()<jupyter_output><empty_output><jupyter_text>Example 1 (group, get group, 2 levels of group)<jupyter_code>import pandas as pd 
df = pd.read_csv("nba.csv") 
df.head(3)
gk = df.groupby('Team') 
gk.head(10)                   #to print the first entries in all the group formed
gk.get_group('Boston Celtics') 
gkk = df.groupby(['Team', 'Position']) 
gkk.first()
Very good article on Groupby                       https://towardsdatascience.com/its-time-for-you-to-understand-pandas-group-by-function-cc12f7decfb9
Example of using Groupby with Unstack              https://cmdlinetips.com/2020/05/fun-with-pandas-groupby-aggregate-multi-index-and-unstack/
                                                   https://jakevdp.github.io/PythonDataScienceHandbook/03.05-hierarchical-indexing.html<jupyter_output><empty_output><jupyter_text>Selection<jupyter_code>get_group()      to select a single group
.size            to get the number of rowns in each group
pd.Grouper       when working with time-series<jupyter_output><empty_output><jupyter_text>Aggregations - An aggregated function returns a single aggregated value for each group. - Once the group by object is created, several aggregation operations can be performed on the grouped data.- An obvious one is aggregation via the aggregate or equivalent agg method<jupyter_code>Most used functions with agg

- 'size':              Counts the rows 
- 'sum':               Sums the column up
- 'mean'/'median':     Mean/Median of the column
- 'max'/'min':         Maximum/Minimum of the column
- 'idxmax'/'idxmin':   Index of the min/max of the column.
- pd.Series.nunique:   Counts unique values. this is a function actually (not a string)
import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

grouped = df.groupby('Year')
grouped['Points'].agg(np.mean)
#Attribute Access in Python Pandas
grouped = df.groupby('Team')
grouped.agg(np.size)
# Multiple Aggregation (sum, mean, stdev)
grouped = df.groupby('Team')
grouped['Points'].agg([np.sum, np.mean, np.std])<jupyter_output><empty_output><jupyter_text>pd.NamedAgg - When applying multiple aggregation functions to multiple columns the result gets a bit messy- The main issue: there is no control over the column names. - Situations like this are where pd.NamedAgg comes in handy. - pd.NamedAgg allows to specify the name of the target column.<jupyter_code># Long Form: Explictly specifying the NamedAgg
aggregation = {
    'Potential Sales': pd.NamedAgg(column='Val', aggfunc='size'),
    'Sales': pd.NamedAgg(column='Sale', aggfunc='sum'),
    'Conversion Rate': pd.NamedAgg(column='Sale', aggfunc=cr)
}
# Alternative: Since the NamedAgg is just a tuple, we can also pass regular tuples
aggregation = {
    'Potential Sales': ('Val','size'),
    'Sales': ('Sale','sum'),
    'Conversion Rate': ('Sale',cr)
}<jupyter_output><empty_output><jupyter_text>Transformation - Transformation returns an object that is indexed the same size of that is being grouped. - Thus, the transform should return a result that is the same size as that of a group chunk.<jupyter_code>import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

grouped = df.groupby('Team')
score = lambda x: (x - x.mean()) / x.std()*10
grouped.transform(score)
# percentage of the groups total by dividing by the group-wise sum.
df.groupby('Sales Rep')['Val'].transform(lambda x: x/sum(x))<jupyter_output><empty_output><jupyter_text>Filtration - Filtration filters the data on a defined criteria and returns the subset of data. - The filter() function is used to filter the data.<jupyter_code>import pandas as pd
import numpy as np

ipl_data = {'Team': ['Riders', 'Riders', 'Devils', 'Devils', 'Kings',
   'Kings', 'Kings', 'Kings', 'Riders', 'Royals', 'Royals', 'Riders'],
   'Rank': [1, 2, 2, 3, 3,4 ,1 ,1,2 , 4,1,2],
   'Year': [2014,2015,2014,2015,2014,2015,2016,2017,2016,2014,2015,2017],
   'Points':[876,789,863,673,741,812,756,788,694,701,804,690]}
df = pd.DataFrame(ipl_data)

# In the above filter condition, we are asking to return 
# the teams which have participated three or more times in IPL
df.groupby('Team').filter(lambda x: len(x) >= 3)
Using groupby and filter together
    df.groupby('AAA').filter(lambda x: len(x) > 120)<jupyter_output><empty_output><jupyter_text>Merge,  Join. Concatenate - One of the main ways that databases are structured is to be normalized. - Data analysis is more straightforward if all of the data is in a single table. - df1.append(df2)             stack vertically- pd.concat([df1, df2])       stacks vertically or horizontally- df1.join(df2)               inner/outer/left/right join on index (or key column)- pd.merge([df1,df2])         many joints on multiple columns  Merge - Possible to use merge() any time to do database-like join operations. - It’s the most flexible of the three operations.- When you want to combine data objects based on one or more keys i- More specifically, merge() is most useful when you want to combine rows that share data.- Possible to achieve many-to-one and many-to-many joins with merge(). - In a many-to-one join,     - one of the df has many rows in the merge column that repeat the same values (ex: 1, 1, 3, 5, 5)    - while the merge column in the other dataset will not have repeat values (such as 1, 3, 5).- In a many-to-many join,     - both of your merge columns will have repeat values.     - These merges are more complex and result in the Cartesian product of the joined rows.    - Meaning: after the merge, there are every combination of rows sharing the same value in the key column.- When you using merge(), two required arguments:    - The left DataFrame    - The right DataFrame<jupyter_code># Example of aplication: mergin on a common column
result = pd.merge(df1, df2[[]'col1', 'col2', 'col3']], on='col1')<jupyter_output><empty_output><jupyter_text>Syntax<jupyter_code>merge(self, right, how='inner', on=None, left_on=None, right_on=None, 
      left_index=False, right_index=False, sort=False, suffixes=('_x', '_y'), 
      copy=True, indicator=False, validate=None) 

The join is done on columns or indexes. 
- If joining columns on columns, the DataFrame indexes will be ignored. 
- Otherwise the index will be passed on.

right:      DataFrame or named Series
            Object to merge with.

how:        {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘inner’
            - left: use only keys from left frame, preserve key order
            - right: use only keys from right frame, preserve key order.
            - outer: use union of keys from both frames, sort keys lexicographically.
            - inner: use intersection of keys from both frames, preserve the order of the left keys.

on:         label or list
            Column or index level names to join on.
            These MUST be found in both DataFrames. 
            If on is None and not merging on indexes: intersection of the columns in both DataFrames.

left_on:    label or list, or array-like
            Columns from the left DataFrame to use as keys. 
            Can either be column names or arrays with length equal to the length of the DataFrame.

left_index: bool, default False
            If True, use the index (row labels) from the left DataFrame as its join key(s). 
            If used, MUST have right_index as well
            In case of a DataFrame with a MultiIndex (hierarchical):
                - the number of levels must match the number of join keys from the right DataFrame           

************            


sort        bool, default False
            Sort the join keys lexicographically in the result DataFrame. 
            If False, the order of the join keys depends on the join type (how keyword).

suffixes    tuple of (str, str), default (‘_x’, ‘_y’)
            Suffix to apply to overlapping column names in the left and right side, respectively. 
            To raise an exception on overlapping columns use (False, False).

copy        bool, default True
            If False, avoid copy if possible.

indicator   bool or str, default False
            If True, new col called “_merge” with information on the source of each row added
            If string, new col called str with information on source of each row will be added 
            Information column is Categorical-type and takes on a value of 
            - “left_only” for observations whose merge key only appears in ‘left’ DataFrame, 
            - “right_only” for observations whose merge key only appears in ‘right’ DataFrame,
            - “both” if the observation’s merge key is found in both.

validate:   str, optional
            If specified, checks if merge is of specified type.
            - “one_to_one” or “1:1”: check if merge keys are unique in both left and right
            - “one_to_many” or “1:m”: check if merge keys are unique in left 
            - “many_to_one” or “m:1”: check if merge keys are unique in right 
            - “many_to_many” or “m:m”: allowed, but does not result in checks.<jupyter_output><empty_output><jupyter_text>Example 1 - merging principles<jupyter_code>df1 = pd.DataFrame({'lkey': ['foo', 'bar', 'baz', 'foo', 'bar'],
                    'value': [1, 2, 3, 5, np.NaN]})
df2 = pd.DataFrame({'rkey': ['foo', 'bar', 'baz','baz' ,'foo'],
                    'value': [5, 6, np.NaN, 4, 8]})

df1
df2
# Merge df1 and df2 on the lkey and rkey columns. 
# The value columns have the default suffixes, _x and _y, appended.
df1.merge(df2, left_on='lkey', right_on='rkey', how='inner')

df1.merge(df2, left_on='lkey', right_on='rkey', how='outer')<jupyter_output><empty_output><jupyter_text>Example 2<jupyter_code>df1 = pd.DataFrame({'ID': ['0011','0013','0014','0016','0017'], 
                           'First Name': ['Joseph', 'Mike', 'Jordan', 'Steven', 'Susan']})
df2 = pd.DataFrame({'ID': ['0010','0011','0013','0014','0017'], 
                           'Last Name': ['Gordan', 'Johnson', 'Might' , 'Jackson', 'Shack']})
df3 = pd.DataFrame({'ID': ['0020','0022','0025'],
                           'First Name': ['Adam', 'Jackie', 'Sue']})
df4 = pd.DataFrame({'Key': ['0020','0022','0025'],
                           'Scores': [95, 90, 80]})
print(df1)
print(df2)
# how=inner will keep observations that have a match on the merge variable in both data frames.
new_merged_dataframe = pd.merge(df1, df2, on= "ID", how= "inner")
new_merged_dataframe
# Passing the “how = ‘outer'” argument will keep all observations from both data frames.
new_OUTER_merged_dataframe = pd.merge(df1, df2, on= "ID", how= "outer")
new_OUTER_merged_dataframe
# Merging on different columns with unique values
# - If the two data frames each contain the unique identifier, but are stored under different columns
# - It is possible to merge using the left_on and right_on arguments. 
# If going this route, you have to pass both arguments. 
# The general structure is 
   # - left_on = column_name_with_unique_identifier
   # - right_on = column_name_with_unique_identifier.

# Note: Merging data frames this way keep both columns stated in the left_on and right_on arguments.

merged_dataframe = pd.merge(df3, df4, left_on= "ID", right_on= "Key", how= "inner")
merged_dataframe<jupyter_output><empty_output><jupyter_text>Example 3 - merging using 2 keys<jupyter_code>left = pd.DataFrame({'key1': ['K0', 'K0', 'K1', 'K2'],
                     'key2': ['K0', 'K1', 'K0', 'K1'],
                        'A': ['A0', 'A1', 'A2', 'A3'],
                        'B': ['B0', 'B1', 'B2', 'B3']})
    
right = pd.DataFrame({'key1': ['K0', 'K1', 'K1', 'K2'],
                               'key2': ['K0', 'K0', 'K0', 'K0'],
                                  'C': ['C0', 'C1', 'C2', 'C3'],
                                  'D': ['D0', 'D1', 'D2', 'D3']})
left
right
pd.merge(left, right, on=['key1'])

# here the merge is only on key1 - note the repetiiotn of A2 since it is allocated to C1 and C2
pd.merge(left, right, on=['key1', 'key2'])

# what is happening here is important: the merge is done when key1 and key2 are the same<jupyter_output><empty_output><jupyter_text>Example 4 - index renaming, no index label<jupyter_code>import pandas as pd
left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']})

right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']})

left.index.set_names('aaa', inplace=True)
right.index.set_names('aaa', inplace=True)
# if the df have same name for index, it is possible to merge using the label of the index
pd.merge(left, right, on='aaa')
# To note, if the index do not have name, then using this will work (left and right both necessary)
pd.merge(left, right, left_index=True, right_index=True)<jupyter_output><empty_output><jupyter_text>Join - While merge() is a module function, .join() is an object function that lives on the DataFrame.- This enables to specify only one DataFrame, which will join the called DataFrame  .join() on.- join() uses merge(), but provides a more efficient way to join than a fully specified merge() call. - example        df1.join(df2, lsuffix="_left", rsuffix="_right")    - With the indices visible, this is a left join happening here, with df1 being the left df    - This example provides the parameters lsuffix and rsuffix.     - Because .join() joins on indices and doesn’t directly merge df, all columns, even those with matching names, are retained in the resulting DataFrame.  Syntax<jupyter_code>.join(self, other, on=None, how='left', lsuffix='', rsuffix='', sort=False) 

other:  DataFrame, Series, or list of DataFrame
        Index should be similar to one of the columns in this one. 
        
on:     str, list of str, or array-like, optional
        Column or index level name(s) in the caller to join, otherwise joins index-on-index. 
        If multiple values given, the other DataFrame must have a MultiIndex. 
        Can pass an array as the join key if it is not already contained in the calling DataFrame. 
        Like an Excel VLOOKUP operation.

how:    {‘left’, ‘right’, ‘outer’, ‘inner’}, default ‘left’
        How to handle the operation of the two objects.
        - left: use calling frame’s index (or column if on is specified)
        - right: use other’s index.
        - outer: form union of calling frame’s index (or column if on is specified) with other’s index, and sort it. lexicographically.
        - inner: form intersection of calling frame’s index (or column if on is specified) with other’s index, preserving the order of the calling’s one.

lsuffix str, default ‘’
        - Suffix to use from left frame’s overlapping columns.

rsuffix str, default ‘’
        - Suffix to use from right frame’s overlapping columns.

sort    bool, default False
        - Order result DataFrame lexicographically by the join key. 
        - If False, the order of the join key depends on the join type (how keyword).<jupyter_output><empty_output><jupyter_text>Example<jupyter_code>left = pd.DataFrame({'A': ['A0', 'A1', 'A2'],
                     'B': ['B0', 'B1', 'B2']},
                      index=['K0', 'K1', 'K2']) 

right = pd.DataFrame({'C': ['C0', 'C2', 'C3'],
                    'D': ['D0', 'D2', 'D3']},
                      index=['K0', 'K2', 'K3'])
left
right
left.join(right)
# to note, inner join (default) will drop the NaN from the left side
left.join(right, how='outer')
# to note, outer join will NOT drop NaN<jupyter_output><empty_output><jupyter_text>Concat - Concatenation is a bit different from the merging techniques above- With merging, we expect the resulting dataset     - to have rows from the parent datasets mixed in together based on commonality    - to lose rows that don’t have matches in the other dataset.    - With Concatenation    - datasets are just stitched together along an axis — either the row axis or column axis.<jupyter_code>#Example:
concatenated = pd.concat([df1, df2], axis=1)<jupyter_output><empty_output><jupyter_text>Syntax<jupyter_code>pd.concat( objs, axis=0, join='outer', ignore_index=False, keys=None,
           levels=None, names=None, verify_integrity=False, sort=None, copy=True)


objs:     a sequence or mapping of Series or DataFrame objects
          
axis:     {0/’index’, 1/’columns’}, default 0

join:     {‘inner’, ‘outer’}, default ‘outer’

ignore_index bool, default False
          If True, do not use the index values along the concatenation axis. 
          The resulting axis will be labeled 0, …, n - 1. 
          Useful if where the concatenation axis does not have meaningful indexing information. 


keys:     sequence, default None
          If multiple levels passed, should contain tuples.
          Construct hierarchical index using the passed keys as the outermost level.

levels:   list of sequences, default None
          Specific levels (unique values) to use for constructing a MultiIndex. 
          Otherwise they will be inferred from the keys.

names:    list, default None
          Names for the levels in the resulting hierarchical index.

verify_integrity bool, default False
         Check whether the new concatenated axis contains duplicates. 
         This can be very expensive relative to the actual data concatenation.

sort:    bool, default False
         Sort non-concatenation axis if it is not already aligned when join is ‘outer’. 
         No effect if join='inner' (already preserves the order of the non-concatenation axis)<jupyter_output><empty_output><jupyter_text>Simple Examples<jupyter_code>s1 = pd.Series(['a', 'b'])
s2 = pd.Series(['c', 'd'])
pd.concat([s1, s2])
# reset the index with ignore_index
pd.concat([s1, s2], ignore_index=True)
# Add a hierarchical index at the outermost level of the data with the keys option.
pd.concat([s1, s2], keys=['s1', 's2'])
# Label the index keys you create with the names option.
pd.concat([s1, s2], keys=['s1', 's2'],names=['Series name', 'Row ID'])
# Combine two DataFrame objects with identical columns.
df1 = pd.DataFrame([['a', 1], ['b', 2]],columns=['letter', 'number'])
df2 = pd.DataFrame([['c', 3], ['d', 4]],columns=['letter', 'number'])
pd.concat([df1, df2])
# Combine DataFrame objects with overlapping columns and return everything. 
# Columns outside the intersection will be filled with NaN values.
df3 = pd.DataFrame([['c', 3, 'cat'], ['d', 4, 'dog']],columns=['letter', 'number', 'animal'])

pd.concat([df1, df3], sort=False)
# Combine DataFrame objects with overlapping columns and return only those that are shared 
# by passing inner to the join keyword argument.
pd.concat([df1, df3], join="inner")<jupyter_output><empty_output><jupyter_text>Notes  Check Installed versions<jupyter_code>Show installed versions

pd.__version__             # displays panda verions
pd.show_verions()          # displays version of everything (machine, windows, python, etc)<jupyter_output><empty_output><jupyter_text>Fast way to create a DataFrame<jupyter_code>import numpy as np
import pandas as pd

pd.DataFrame(np.random.rand(4,8), columns=list('abcdefgh'))<jupyter_output><empty_output><jupyter_text>Create df from clipboard<jupyter_code>After having copied a dataset from excel, do this:
    
    df= pd.read_clipboard()<jupyter_output><empty_output><jupyter_text>Args & Kwargs<jupyter_code>'*args' and '**kwargs' are magic variables
   - it is not necessary to write *args or **kwargs. 
   - Only the * (aesteric) is necessary. 
   - You could have also written *var and **vars. 
   - Writing *args and **kwargs is just a convention
*args and **kwargs are mostly used in function definitions. 
*args and **kwargs allow you to pass a variable number of arguments to a function. 

When you do not know before hand how many arguments can be passed
 - *args is used to send a non-keyworded variable length argument list to the function. 
 - **kwargs allows you to pass keyworded variable length of arguments to a function (named arguments)

- arg creates tuples, kwarg creates dictionary
- by using kwarg, it is possible to name the arguments (which is not possible in arg)<jupyter_output><empty_output><jupyter_text>https://www.listendata.com/2019/07/args-and-kwargs-in-python.html  Example: *arg<jupyter_code>def add(*args):
     print(sum(args))
add(5,5,6)
# list comprehension - makes code more readable and fast than a for loop and lambda function
def even(*args):
    print([n for n in args if n%2 == 0])
even(1,2,3,4)<jupyter_output>[2, 4]<jupyter_text>Example: **kwarg<jupyter_code>def test2(**kwargs):
    print(kwargs)
    print(kwargs.keys())
    print(kwargs.values())
test2(a=1, b=3)<jupyter_output>{'a': 1, 'b': 3}
dict_keys(['a', 'b'])
dict_values([1, 3])<jupyter_text>Display Settings and Style  Display Settings<jupyter_code># To disply 2 decimals

pd.set_options('display.float_format',{:.2f}.format)
pd.reset_options('display.float_format')

display can be:
    - float_format
    - colheader_justify, column_space, date_dayfirst, date_yearfirst
    - max_categories, max_columns, max_colwidth, max_rows, min_rows, precision

    https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.set_option.html<jupyter_output><empty_output><jupyter_text>Style to next level<jupyter_code># This is cool - an example

my_format = {'Date': '{%d / % m / %y}',      
             'Col1': '${:.2f}',                      # for $ signs and 2 decinals
             'Col2': '{:,}' }                        # for thousands separator

# Then pass the formting to the df

df.style.format(my_format)
hide_index())                                        # remove index
hightlight_min('Col1', color = 'red')                # highlight min value in Col1 in red
hightlight_max('Col2', color = 'lightgreen')         # highlight max value in Col2 in green

background_gradient(subset='Col3', cmap='Blues')     # conditional formating with blue gradient
bar('Col4', color='lightblue', align='zero')         # conditional formating with bars<jupyter_output><empty_output><jupyter_text>Profile a DataFrame<jupyter_code># - pip install pandas-profiling[notebook]           # pip install
# - conda install -c conda-forge pandas-profiling    # in anaconda prompt
# - pip install https://github.com/pandas-profiling/pandas-profiling/archive/master.zip    # github

from pandas_profiling import ProfileReport
pandas_profiling.ProfileReport(df)<jupyter_output><empty_output><jupyter_text>Syntax<jupyter_code>Those are exaclty ok and produce the same

df1 = df[df['col1'] == df['col1'].min()]
df1 = df[df.col1 == df.col1.min()]<jupyter_output><empty_output><jupyter_text>Inplace<jupyter_code>By default, inplace=False, so that we can experiment, try and play with the data
When toggling inplace=True, we will affect 'permanently' the underlying data

inplace is used in formula such as (and many more)
- dropna()
- set_index()
- drop()

as an example, those two are similar 
- inplace        df.set_index('name',inplace=True)
- assignment:    df = df.set_index('name')<jupyter_output><empty_output><jupyter_text>Reduce df file size<jupyter_code>When importing, use this:
    df = pd.read_csv("file_name.csv", usecols=cols, dtype=dtypes)
    
To check for memory usage:
    df.info(memory_usage='deep')
    
File size can be divided by 10x, depending on data characteristics<jupyter_output><empty_output><jupyter_text>Create a pivot table<jupyter_code>df.pivot_table(index=['AAA','BBB'],
              columns=['XYZ'],
              Values='Col1',
              aggfunc=['np.mean','count']    # 'np.sum'
              margins=True,                  # this is to provide total columns and rows, if needed
              fill_value=0)                  # replaces missing values / NaN with 0 
               
https://pbpython.com/pandas-pivot-table-explained.html<jupyter_output><empty_output><jupyter_text>Reshape a Multiindexed Series<jupyter_code>This is exaclty the same - provides the mean of values in Col1 grouped by AAA
    df.groupby('AAA').Col1.mean()
    df.groupby('AAA')['Col1'].mean()
    
It is possible to extend that to a multi-index - the output will be vertical
     df.groupby(['AAA','BBB'])['Col1'].mean()
    
In order to have a nicer looking output (like a pivot table)
    df.groupby(['AAA','BBB'])['Col1'].mean().unstack()<jupyter_output><empty_output><jupyter_text>Convert a Continous into Discrete - with Bins<jupyter_code>Use this (need to have more bins than labels)
    pd.cut(df['col1'], bins =[0,2,5,10], labels=['low','med','high'])<jupyter_output><empty_output><jupyter_text>Split String in Multiple Columns<jupyter_code>Will create new df with those:
    df['Col1'].str.split(' ', expand=True)    # expand on space (if no space, expands each letter)
    df['Col1'].str.split(',', expand=True)    # expand on coma<jupyter_output><empty_output><jupyter_text>Aggregate by Multiple functions<jupyter_code>In the example, we had 'Orders_ID' and the detail of 'Item_Price' of those 'Orders_ID'

    # those are the same, they return the sum of Item_Price for Order_ID 1
    df.[df['Order_ID']==1]['Item_Price'].sum()
    df.[df.Order_ID ==1].Item_Price'.sum()

    # this creates a df with Order_IDs and sum of Item_Price
    df.groupby('Order_ID')['Item_Price'].sum() 
    
    # this creates a df with 2 columns with sum and count 
    df.groupby('Order_ID')['Item_Price'].agg(['sum','count'])    
    
To take this to the next level and integrate this information back to the original df
We want to Combine the outpout of an aggregation with a Dataframe

Solution is to use the TRANSFORM method 
Transform performs same calculation but returns output data with same shape as original
    
    # This creates the array 'df2'
    df2 = df.groupby('Order_ID')['Item_Price'].transform('sum') 
    
    # This adds a new column 'Total Price' in the original df, using df2 data
    df['Total_Price'] = df2

    # This creates a new columns 'Percent_Order' 
    df['Percent_Order'] = df.Item_Price / df.Total_Price<jupyter_output><empty_output><jupyter_text>Build DF from multiple files (Rows and Column wise)<jupyter_code>Need to install the Glob module and have in the same place the files we need

    from glob import glob
    # for vertical concatenation (adding rows)
    pd.concat((pd.read_csv(file) for file in AAA_folder, ignore_index=True))
                                                            # this to avoid duplicated indexes at 0
    # for horizontal concatenation (adding columns)
    pd.concat((pd.read_csv(file) for file in AAA_folder, axis=1))
import glob
df = pd.concat((pd.read_csv(file, header=None) for file in glob.glob("*.csv")),axis=0, ignore_index=True)
# header = None is very importnat here
# To understand the above, this will print th elist of files ending in 'csv' located in the same place as the ipynb file
for file in glob.glob("*.csv"):    # this could be the file path
    print(file)<jupyter_output><empty_output><jupyter_text>Get the length of str characters from a column<jupyter_code>We call this custom methods:

.apply(lambda x: len(x))'
.apply(len)
stack, unstack, melt, pivot        https://pythonhealthcare.org/2018/04/08/32-reshaping-pandas-data-with-stack-unstack-pivot-and-melt/
cheatsheet to do:        https://www.dataquest.io/blog/pandas-cheat-sheet/
good blog:               https://www.novixys.com/blog/author/admin/<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11620926243567753, 'fraction_numerical': 0.02206755508642301, 'mean_word_length': 3.6527010435850213, 'pattern_counts': {'":': 0, '<': 151, '<?xml version=': 0, '>': 153, 'https://': 10, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 8, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '38', 'dir': 'jupyter-structured-clean-dedup', 'id': '547860', 'license': 'BSD-2-Clause', 'n_tokens_mistral': 9841, 'n_tokens_neox': 9211, 'n_words': 3319, 'path': 'Learning Notes/Learning Notes 5 - Pandas 3.ipynb', 'repo_name': 'k21k/Python-Notes', 'size': '30316'}