Pandas从小白到大师

import pandas as pd
import numpy as np
import os
data_path = 'path/to/folder/' # 注意修改路径
df = (pd.read_csv(filepath_or_buffer=os.path.join(data_path, 'master.csv'))
      .rename(columns={'suicides/100k pop' : 'suicides_per_100k',
                       ' gdp_for_year ($) ' : 'gdp_year', 
                       'gdp_per_capita ($)' : 'gdp_capita',
                       'country-year' : 'country_year'})
      .assign(gdp_year=lambda _df: _df['gdp_year'].str.replace(',','').astype(np.int64))
     )

df.describe(include='all')

df.dtypes

country               object
year                   int64
sex                   object
age                   object
suicides_no            int64
population             int64
suicides_per_100k    float64
country_year          object
HDI for year         float64
gdp_year               int64
gdp_capita             int64
generation            object
dtype: object

import pandas as pd
def mem_usage(df: pd.DataFrame) -> str:
    """This method styles the memory usage of a DataFrame to be readable as MB.
    Parameters
    ----------
    df: pd.DataFrame
        Data frame to measure.
    Returns
    -------
    str
        Complete memory usage as a string formatted for MB.
    """
    return f'{df.memory_usage(deep=True).sum() / 1024 ** 2 : 3.2f} MB'
def convert_df(df: pd.DataFrame, deep_copy: bool = True) -> pd.DataFrame:
    """Automatically converts columns that are worth stored as
    ``categorical`` dtype.
    Parameters
    ----------
    df: pd.DataFrame
        Data frame to convert.
    deep_copy: bool
        Whether or not to perform a deep copy of the original data frame.
    Returns
    -------
    pd.DataFrame
        Optimized copy of the input data frame.
    """
    return df.copy(deep=deep_copy).astype({
        col: 'category' for col in df.columns
        if df[col].nunique() / df[col].shape[0] < 0.5})

>>> mem_usage(df)
10.28 MB
>>> mem_usage(df.set_index(['country', 'year', 'sex', 'age']))
5.00 MB
>>> mem_usage(convert_df(df))
1.40 MB
>>> mem_usage(convert_df(df.set_index(['country', 'year', 'sex', 'age'])))
1.40 MB

>>> %%time
>>> df.query('country == "Albania" and year == 1987 and sex == "male" and age == "25-34 years"')  # 查询
CPU times: user 7.27 ms, sys: 751 µs, total: 8.02 ms
# =========下面使用多重索引=========
%%time
mi_df = df.set_index(['country', 'year', 'sex', 'age']) # 创建多重索引的时间
CPU times: user 10.8 ms, sys: 2.2 ms, total: 13 ms
>>> %%time  
>>> mi_df.loc['Albania', 1987, 'male', '25-34 years'] #
CPU times: user 459 µs, sys: 1 µs, total: 460 µs
#===================

import numpy as np
import pandas as pd
df = pd.DataFrame({'a_column': [1, -999, -999],
                    'powerless_column': [2, 3, 4],
                    'int_column': [1, 1, -1]})
df['a_column'] = df['a_column'].replace(-999, np.nan)
df['power_column'] = df['powerless_column'] ** 2
df['real_column'] = df['int_column'].astype(np.float64)
df = df.apply(lambda _df: _df.replace(4, np.nan))
df = df.dropna(how='all')

df = (pd.DataFrame({'a_column': [1, -999, -999],
                    'powerless_column': [2, 3, 4],
                    'int_column': [1, 1, -1]})
        .assign(a_column=lambda _df: _df['a_column'].replace(-999, np.nan),
                power_column=lambda _df: _df['powerless_column'] ** 2,
                real_column=lambda _df: _df['int_column'].astype(np.float64))
        .apply(lambda _df: _df.replace(4, np.nan))
        .dropna(how='all')
      )

(df
 .groupby('age')
 .agg({'generation':'unique'})
 .rename(columns={'generation':'unique_generation'})
#  Recommended from v0.25
#  .agg(unique_generation=('generation', 'unique'))
)

#使用sort_values函数和head 函数 排序并得到前10名
(df
 .groupby(['country', 'year'])
 .agg({'suicides_per_100k': 'sum'})
 .rename(columns={'suicides_per_100k':'suicides_sum'})
#  Recommended from v0.25
#  .agg(suicides_sum=('suicides_per_100k', 'sum'))
 .sort_values('suicides_sum', ascending=False)
 .head(10)

#直接使用nlargest 函数得到新列suicides_sum的前10名
(df
 .groupby(['country', 'year'])
 .agg({'suicides_per_100k': 'sum'})
 .rename(columns={'suicides_per_100k':'suicides_sum'})
#  Recommended from v0.25
#  .agg(suicides_sum=('suicides_per_100k', 'sum'))
 .nlargest(10, columns='suicides_sum')
)

mi_df.loc[('Switzerland', 2000)]

(mi_df
 .loc[('Switzerland', 2000)]
 .unstack('sex')
 [['suicides_no', 'population']]
)

def log_head(df, head_count=10):
    print(df.head(head_count))
    return df

def log_columns(df):
    print(df.columns)
    return df

def log_shape(df):
    print(f'shape = {df.shape}')
    return df

(df
 .pipe(log_shape)
 .query('sex == "female"')
 .groupby(['year', 'country'])
 .agg({'suicides_per_100k':'sum'})
 .pipe(log_shape)
 .rename(columns={'suicides_per_100k':'sum_suicides_per_100k_female'})
#  Recommended from v0.25
#  .agg(sum_suicides_per_100k_female=('suicides_per_100k', 'sum'))
 .nlargest(n=10, columns=['sum_suicides_per_100k_female'])
)

shape = (27820, 12)
shape = (2321, 1)