<jupyter_start><jupyter_text>Data structuring, part 3 The Pandas way*<NAME>*  Conda- Seaborn issue - update package: `conda update seaborn` - In general we can install packages     - `conda install xxx`  Recap*Which datatypes beyond numeric does pandas handle natively?*- Strings, categorical, temporal data, missing data*What can we do to missing values and duplicates?*- Fill out, drop, ignore, get new data- Drop them, calculate relevant measures over duplicates  Agenda1. [the split apply combine framework](Split-apply-combine)1. [joining datasets](Joining-data)1. [reshaping data](Reshaping-data) -   Loading the software<jupyter_code>import numpy as np
import pandas as pd
import seaborn as sns<jupyter_output><empty_output><jupyter_text>Split-apply-combine   Split-apply-combine (1)*What is the split-apply-combine framework?* A procedure to 1. **split** a DataFrame 2. **apply** certain functions (sorting, mean, other custom stuff)3. **combine** it back into a DataFrame  Split-apply-combine (2) How do we *split* observations by x and *apply* the calculation mean of y?*   groupby (1)A powerful tool in DataFrames are the `groupby` method. Example:<jupyter_code>tips = sns.load_dataset('tips')
split_var = 'sex'
apply_var = 'total_bill'

tips\
    .groupby(split_var)\
    [apply_var]\
    .mean()<jupyter_output><empty_output><jupyter_text>groupby (2)*What does the groupby by method do?* - It implements *split-apply-combine* Can other functions be applied? - Yes: `mean`, `std`, `min`, `max` all work. - Using `.apply()` method and inserting your homemade function works too.  groupby (3)*Does it work for multiple variables, functions?*<jupyter_code>split_vars = ['sex', 'time'] 
apply_vars = ['total_bill', 'tip']
apply_fcts = ['mean', 'std', 'median']
combined = tips\
    .groupby(split_vars)\
    [apply_vars]\
    .agg(apply_fcts)

print(combined.reset_index() )<jupyter_output>sex    time total_bill                        tip                 
                        mean       std median      mean       std median
0    Male   Lunch  18.048485  7.953435  16.58  2.882121  1.329017   2.31
1    Male  Dinner  21.461452  9.460974  19.63  3.144839  1.529116   3.00
2  Female   Lunch  16.339143  7.500803  13.42  2.582857  1.075108   2.01
3  Female  Dinner  19.213077  8.202085  17.19  3.002115  1.193483   3.00<jupyter_text>Note grouping with multiple variables uses a [MultiIndex](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.MultiIndex.html) which we do not cover.  groupby (4)*Can we use groupby in a loop?* Yes, we can iterate over a groupby object. Example:<jupyter_code>results = {}
for group, group_df in tips.groupby('sex'):
    group_mean = group_df.total_bill.mean()
    results[group] = group_mean
    
    
print(group)
results<jupyter_output>Female<jupyter_text>ProTip: `groupby` is an iterable we can also use with `multiprocessing` for parallel computing.  groupby (5)*How do we get our `groupby` output into the original dataframe?*Option 1: you merge it (not recommended)Option 2: you use `transform`.<jupyter_code>mu_time = tips.groupby(split_var)[apply_var].transform('mean')

(tips.total_bill - mu_time).head()<jupyter_output><empty_output><jupyter_text>*Why is this useful?*- Useful for fixed effects computation  Joining data Until now we've worked with one DataFrame at a time.We will now learn to put them together.  Concatenating DataFrames (1)Let's make some data to play with<jupyter_code>df1 = pd.DataFrame([[1, 2], [3, 4]], columns=['A', 'B'])
df2 = pd.DataFrame([[2, 3], [5, 6], [2, 5]], columns=['B', 'C'])
print(df1,'\n')
print(df2)<jupyter_output>A  B
0  1  2
1  3  4 

   B  C
0  2  3
1  5  6
2  2  5<jupyter_text>Concatenating DataFrames (2)Let's try to vertically put two DataFrames together:<jupyter_code>print(df1,'\n')
print(df2,'\n')
dfs = [df1, df2]
print(pd.concat(dfs)) # vertically stacking dataframes<jupyter_output>A  B
0  1  2
1  3  4 

   B  C
0  2  3
1  5  6 

     A  B    C
0  1.0  2  NaN
1  3.0  4  NaN
0  NaN  2  3.0
1  NaN  5  6.0<jupyter_text>The `concat` function creates one big DataFrame from two or more dataframes. Requires overlapping columns.  Concatenating DataFrames (3)*How might we do this horizontally?*<jupyter_code>df3 = pd.DataFrame([[7, 8], [9, 10]], columns=['C', 'D'], index = [1,2])
print(df1)
print(df3)

print(pd.concat([df1, df3], axis=1)) # put together horizontally - axis=1<jupyter_output>A  B
0  1  2
1  3  4
   C   D
1  7   8
2  9  10
     A    B    C     D
0  1.0  2.0  NaN   NaN
1  3.0  4.0  7.0   8.0
2  NaN  NaN  9.0  10.0<jupyter_text>Merging DataFrames (1) We can merge DataFrames which share common identifiers, row by row. Example:<jupyter_code>print(pd.merge(df1, df2, how='outer'))
print(pd.concat([df1, df2]))<jupyter_output>A  B    C
0  1.0  2  3.0
1  3.0  4  NaN
2  NaN  5  6.0
     A  B    C
0  1.0  2  NaN
1  3.0  4  NaN
0  NaN  2  3.0
1  NaN  5  6.0<jupyter_text>`merge` is useful for when you have two or more datasets about the same entities, e.g. data about individual where you merge by social security number.  Merging DataFrames (2)Merging can be either of four types.- inner merge [default]: observations exist in both dataframes - left (right) merge: observations exist in left (right) dataframe- outer merge: observations exist either in left or in right dataframe   Merging DataFrames (3)*What happens if we do a left merge `df1` and `df2`?*<jupyter_code>print(pd.merge(df1, df2, how='inner'))<jupyter_output>A  B  C
0  1  2  3
1  1  2  5<jupyter_text>Merging DataFrames (4)With `join` we can also merge on indices.  Reshaping data  Stacking data A DataFrame can be collapsed into a Series with the **stack** command:<jupyter_code>df = pd.DataFrame([[1,2],[3,4]],columns=['EU','US'],index=[2000,2010])
print(df, '\n')
stacked = df.stack() # going from wide to long format
print(stacked.reset_index())<jupyter_output>EU  US
2000   1   2
2010   3   4 

   level_0 level_1  0
0     2000      EU  1
1     2000      US  2
2     2010      EU  3
3     2010      US  4<jupyter_text>Note: The stacked DataFrame is in long/tidy format, the original is wide.  To wide format Likewise we can transform a long DataFrame with the unstack<jupyter_code>print(stacked.unstack(level=1))<jupyter_output>EU  US
2000   1   2
2010   3   4
{'fraction_non_alphanumeric': 0.1084664536741214, 'fraction_numerical': 0.0621405750798722, 'mean_word_length': 3.6828721017202692, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '265466', 'license': 'MIT', 'n_tokens_mistral': 2448, 'n_tokens_neox': 2253, 'n_words': 754, 'path': 'material/session_7/lecture_7.ipynb', 'repo_name': 'karlbindslev/sds_group29', 'size': '6276'}