I wrote the basic operation of Pandas with Jupyter Lab (Part 2)

14th

About NaN of DataFrame

import numpy as np
import pandas as pd

df = pd.read_csv('train.csv')
df.head()

All records containing NaN have been dropped. (index remains the same. Basically, index is not reassigned unless .reset_index ()

df.dropna().head()

You can drop a column containing NaN by taking axis = 1 as an argument (default is line with axis = 0).

do not use much. When building a model, it is used in the strategy of reducing the variables (explanatory variables) that explain the data without reducing the number of data, but it is unlikely that "reduce the explanatory variables because there is even one NaN". .. Which explanatory variables are important for model building is a very important and careful issue.

df.dropna(axis=1) .head()

By passing a list of column names to the subset argument, only the rows containing NaN in that column will be dropped.

You only need to drop the NaN row in a particular column. Remember, it's very convenient.

Of course, .dropna () does not overwrite the original df. If you want to update the original df, reassign it with the familiar inplace = True or df = df.dropna ().

df.dropna(subset=['Age']).head() ###index=888 is dropped.

.fillna(value)

Assign a specific Value to NaN.

df.fillna('THIS IS IT').head()

Substitute the column mean for the NaN contained in a particular column

df['Age'].mean()

29.69911764705882

df['Age'].fillna(df['Age'].mean()).head()

0    22.0
1    38.0
2    26.0
3    35.0
4    35.0
Name: Age, dtype: float64

df['Age'] = df['Age'].fillna(df['Age'].mean())

df.head()

pd.isna(df).head()

Substitute the NaN judgment result of Cabin using the Cabin_nan column

df['Cabin_nan'] = pd.isna(df['Cabin']) df

15th

groupby with the .groupby () function ()

df = pd.read_csv('train.csv')
df.head()

Group by in Pclass

Since df.groupby ('Pclass') alone does not know what to do after groupby is grouped, call functions such as .mean () and .count () after that.

df.groupby('Pclass').mean()

df = df[df['Pclass']==1]
df.head()

Take the statistic for each column with'Pclass'== 1

df[df['Pclass']==1].describe()

Extract only mean

It's hard to take them out one by one, so it's a good idea to use .groupby () to see them all together.

df[df['Pclass']==1].describe().loc['mean']

PassengerId    461.597222
Survived         0.629630
Pclass           1.000000
Age             38.233441
SibSp            0.416667
Parch            0.356481
Fare            84.154687
Name: mean, dtype: float64

After groupby, take the value specified in by, which is the first argument of groupby, in index. In the above example, it is the value of Pclass (1, 2, 3). Of course, the result of grouby is also a DataFrame, so you can fetch a specific group of Series with .loc [].

df.groupby('Pclass').mean().loc[1]

PassengerId    461.597222
Survived         0.629630
Age             38.233441
SibSp            0.416667
Parch            0.356481
Fare            84.154687
Name: 1, dtype: float64

df.groupby('Pclass').count().loc[1] #count()And sum()Etc. are also possible

PassengerId    216
Survived       216
Name           216
Sex            216
Age            186
SibSp          216
Parch          216
Ticket         216
Fare           216
Cabin          176
Embarked       214
Name: 1, dtype: int64

df.groupby('Pclass').describe()

df.groupby('Pclass').describe()['Age'] #I took out only Age

In Jupyter, if the columns and rows of DataFrame cannot be displayed, they will be omitted.

If you want to display all columns (or all rows) without omitting them, you can prevent them from being omitted by executing the following.

#Display without omitting columns
pd.set_option('display.max_columns', None)
#Display without omitting lines
pd.set_option('display.max_rows', None)

Turn the result of groupby with a for statement

for i, group_df in df.groupby('Pclass'):
    print("{}: group_df's type is {} and has {}".format(i, type(group_df), len(group_df)))

1: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 216
2: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 184
3: group_df's type is <class 'pandas.core.frame.DataFrame'> and has 491

Number the highest Fare for each record in each Pclass group

df = pd.read_csv('train.csv')
results = []
for i, group_df in df.groupby('Pclass'):
    sorted_group_df = group_df.sort_values('Fare')
    sorted_group_df['RankInClass'] = np.arange(len(sorted_group_df))
    results.append(sorted_group_df)

results_df = pd.concat(results)
results_df.head()

Join table

There are two main types of table joins.

Join by using a specific column or index as a Key

Simply combine (or vertically) DataFrames horizontally (or vertically)

import pandas as pd

df1 = pd.DataFrame({'Key':['k0','k','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key':['k0','k1','k2'],
                   'C':['c0','c2','c3'],
                   'D':['d0','d1','d2']})

df1

df2

Both have a column called'Key'and their values are the same.

The other columns have different values. This column called ‘Key’ is used as a Key to join two DataFrames horizontally. Use .merge () for merging.

df1.merge(df2)

Simply combine (or vertically) DataFrames horizontally (or vertically)

Use pd.concat () concat = concatenate

#Vertical (often used)
pd.concat([df1,df2], axis=0) #The default is axis=0

/opt/anaconda3/lib/python3.7/site-packages/ipykernel_launcher.py:2: FutureWarning: Sorting because non-concatenation axis is not aligned. A future version
of pandas will change to not sort by default.

To accept the future behavior, pass 'sort=False'.

To retain the current behavior and silence the warning, pass 'sort=True'.

#side
pd.concat([df1,df2], axis=1)

16th

How to use .merge ()

how: How to combine → {‘left’, ‘right’, ‘outer’, ‘inner’}, default is ‘inner’

Specify the column to be on: key (column that exists in both DataFrames). If not specified, they will be joined in a common column.

left_on: Column to be the key of the left DataFrame

right_on: Column to be the key of right DataFrame

left_index: Specify True when setting the left Key to index

right_index: Specify True when setting the Key of right to index

how

df1

df2

df1.merge(df2, how='left')

df1.merge(df2, how='outer')

df1.merge(df2, how='inner')

on

The argument on specifies which column should be used as the Key when joining. You can only specify columns that are in both the left table and the right table.

If there is a common column, that column will be the Key and will be combined without specifying anything, but let's specify the basics. It's easy to understand and safe. It is often said that "they were joined in an unexpected column". If there are multiple common columns, you do not know which column will be joined, and even if there is only one common column, it is better to specify it. (Since the examples so far were before the explanation of on, I intentionally wrote it without specifying the on argument.)

To set index as Key, set right_index and left_index described later to True. If the column names of each table (DataFrame) are different, specify left_on and right_on described later.

df1 = pd.DataFrame({'Key':['k0','k1','k2'],
                    'ID':['aa','bb','cc'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key':['k0','k1','k3'],
                    'ID':['aa','bb','cc'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})

df1.merge(df2, on='Key')

df1.merge(df2, on='ID')

Change suffix

df1.merge(df2, on='ID', suffixes=('_left', '_right'))

left_on, right_on

Specify this argument when the column name you want to use as Key is different between left and right.

df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})

df1.merge(df2, left_on='Key1', right_on='Key2')

left_index, right_index

If you want to specify Index as Key instead of column, specify True for left_index and right_index.

df1.merge(df2, left_index=True, right_index=True)

join

If you use the join function, it will join by index, but you do not need to remember because you can do almost the same with merge.

df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})

df1.join(df2)

df1.merge(df2, left_index=True, right_index=True)

df1 = pd.DataFrame({'Key1':['k0','k1','k2'],
                   'A':['a0','a1','a2'],
                   'B':['b0','b1','b2']})
df2 = pd.DataFrame({'Key2':['k0','k1','k3'],
                   'C':['c0','c1','c3'],
                   'D':['d0','d1','d3']})
df3 = pd.DataFrame({'Key3':['k0','k1','k4'],
                   'E':['c0','c1','c3'],
                   'F':['d0','d1','d3']})
df1.join([df2, df3])

17th

.unique() .nunique()

import pandas as pd
df = pd.read_csv('train.csv')
df.head()

df['Pclass'].unique()

array([3, 1, 2])

df['Pclass'].nunique()

3

.value_counts()

df['Pclass'].value_counts()

3    491
1    216
2    184
Name: Pclass, dtype: int64

(Super important) .apply ()

You can use the apply () function to process all records in a DataFrame and store the result in a separate column. It is an image that applies processing to each line

def get_age_group(age):
    return str(age)[0] + '0s'

get_age_group(45)

'40s'

df = pd.DataFrame({'name':['John','Mike','Emily'],
                  'age':['23','36','42']})
df

df['age'].apply(get_age_group)

0    20s
1    30s
2    40s
Name: age, dtype: object

How to use .apply () with a lambda function

#Assign to the variable f in the lambda function
f = lambda x: str(x)[0] + '0s'
#Put 43 as a trial
f(43)

'40s'

df['age_group'] = df['age'].apply(lambda x: str(x)[0] + '0s')

df

How to use .apply () for the whole record

df = pd.DataFrame({'name':['John','Mike','Emily'],
                  'age':['23','36','42']})
df['description'] = df.apply(lambda row:'{} is {} years old'.format(row['name'], row['age']), axis=1)

df

18th

Save DataFrame in csv format with .to_csv ()

df = pd.read_csv('train.csv')
df.head()

df['Adult'] = df['Age'].apply(lambda x: x>20)
df.tail()

df.to_csv('train_w_adult.csv')

df = pd.read_csv('train_w_adult.csv')
df.head(3)

Looking at the result of df.head (3), the index information when it was saved last time is saved in the mysterious column ‘Unnamed: 0’.

If you specify index = False in .to_csv (), you do not need to save the index. Basically, always specify index = False and save it in csv format.

Please note that if the same file already exists in the save destination, it will be overwritten.

df = pd.read_csv('train.csv')
df['Adult'] = df['Age'].apply(lambda x: x>20)
df.to_csv('train_w_adult.csv', index=False)

df = pd.read_csv('train_w_adult.csv')
df.head(3)

df = pd.DataFrame({'A':[['a', 'b'], 2, 3], 'B':[['c', 'd'], 5, 6]})
df

#Make sure that the stored values are a list.
type(df['A'].iloc[0])

list

#Save as csv
df.to_csv('temp.csv', index=False)
#Load the saved csv
df = pd.read_csv('temp.csv')
df

type(df['A'].iloc[0])

str

Iterate a DataFrame with .iterrows ()

It is used when iterating a DataFrame with a for statement. It's hard to remember, but since "rows" is "iterated", let's remember iter + row + s. Since it can be turned with for, let's think that there are multiple s.

"Iteration" means to repeat the process. It's a loop. For example, if it is a list, it can be iterated with for i in list: (see Part 4).

In DataFrame, you cannot directly say for i in df: like a list. Write as follows using a function called .iterrows ().

df = pd.read_csv('train.csv')
for idx, row in df.iterrows():
    if row['Age'] > 40 and row['Pclass'] == 3 and row['Sex'] == 'male' and row['Survived'] == 1:
        print('{} is very lucky guy...!'.format(row['Name']))

Dahl, Mr. Karl Edwart is very lucky guy...!
Sundman, Mr. Johan Julian is very lucky guy...!

.apply () is often used to save the result of processing each record in another column, and this time .iterows () is often used when you want to process only instead of returning a value.

For example, the file path is stored in DataFrame, and .iterrows () is used to move or read the file.

Sort by specific column with .sort_values ()

#Sort by age
df.sort_values('Age')
df.head()

Create a pivot table with .pivot_table ()

data = {'Data':['Jan-1','Jan-1','Jan-1','Jan-2','Jan-2','Jan-2'],
       'User':['Emily', 'John','Nick','Kevin','Emily','John'],
       'Method':['Card','Card','Cash','Card','Cash','Cash'],
       'Price':[100,250,200,460,200,130]}
df = pd.DataFrame(data)
df

In values, enter the column you want to aggregate. This time it is Price.

First, the value specified by values (Price in this case) is entered in each cell. This is the column you want to aggregate.

On the other hand, just pass the columns for which you want to specify index and columns in a list.

df.pivot_table(values='Price', index=['Data', 'User'], columns=['Method'])

df.pivot_table(values='Price', index=['Data', 'Method'], columns=['User'])

First, clarify "which column you want to aggregate" and put that column in values, then just put the information you want in index and columns.

Cross-section operation with .xs ()

.xs () is an abbreviation for cross section. I don't use this much either, but it comes in handy when working with DataFrames that have multiple indexes, such as pivots. It's a good idea to remember it along with the pivot.

What you need to do with this .xs () is to use it in the pivot table above, for example, when you want to extract only the "Card" row. (Exactly cross-section)

#pivot.xs('Card', level = 'Method')