Pandas Basics

Get column names and find the column name ends with certain words.

cname = food_info.columns.tolist()
gram_columns = []
for i in cname:
    if i.endswith("(g)"):
        gram_columns.append(i)
    else:
        pass
gram_df = food_info[gram_columns]
gram_df.head(3)

Pandas Normalizing Columns

max_protein = food_info["Protein_(g)"].max()
normalized_protein = food_info["Protein_(g)"]/max_protein

Pandas Column Sorting_values

food_info.sort_values("Norm_Nutr_Index", inplace = True, ascending = False)

check if null & count num

age = titanic_survival["age"]  # list age column
age_is_null = pd.isnull(age)  # get a series of True and False 
age_null_true = age[age_is_null]  # return to False value
age_null_count = len(age_null_true)  # count num of False
print (age_null_count)

calculate mean age where age is not null

age_is_null = pd.isnull(titanic_survival["age"])
age_is_true = titanic_survival["age"][age_is_null == False]
correct_mean_age = sum(age_is_true) / len(age_is_true)

However, missing data is so common that Series has a built-in function for it.

correct_mean_age = titanic_survival["age"].mean()

Get mean of a column group by other column value

def c_means(cname):
    passenger_classes = [1, 2, 3]
    fares_by_class = {}
    for i in passenger_classes:
        indexnum = (titanic_survival["pclass"] == i)
        m = titanic_survival[indexnum][cname].mean()
        fares_by_class[i] = m
    return fares_by_class

fares_by_class = c_means("fare")
print (fares_by_class)

Pandas_Pivot_Table

• doc pivot table
• to produce the same result, could just do:

passenger_class_fares = titanic_survival.pivot_table(index="pclass", values="fare", aggfunc=np.mean)

• to calculate several columns at the same time
  values = ["cname1", "cname2"]

import numpy as np
port_stats = titanic_survival.pivot_table(index="embarked", values=["fare", "survived"], aggfunc=np.sum)
print (port_stats)

dropna for certain columns

• e.x. certain columns are ["age", "sex"] below

rop_na_rows = titanic_survival.dropna(axis=0)
drop_na_columns = titanic_survival.dropna(axis = 1)
new_titanic_survival = titanic_survival.dropna(axis = 0, subset = ["age", "sex"])

To access rows via loc or iloc

• iloc using position index
• loc using index num

first_five_rows = new_titanic_survival.iloc[0:5]
first_ten_rows = new_titanic_survival.iloc[0:10]
row_position_fifth = new_titanic_survival.iloc[4]
row_index_25 = new_titanic_survival.loc[25]

Check certain row values and column values

• loc using label names, like "1100", "age"

row_index_1100_age = new_titanic_survival.loc[1100,"age"]
row_index_25_survived = new_titanic_survival.loc[25, "survived"]
five_rows_three_cols = new_titanic_survival.iloc[0:5, 0:3]

• reset_index(drop = True)

titanic_reindexed = new_titanic_survival.reset_index(drop= True)
print (titanic_reindexed.iloc[0:5, 0:3])

df.apply(), apply function for the dataframe

def count_null(df):
    is_null = pd.isnull(df)  #return boolean matrix
    is_null_true = df[is_null]  # return is_null matrix
    is_null_count = len(is_null_true)  # count null numbers
    return is_null_count  # return null numbers
a = count_null(titanic_survival)
print (a)
column_null_count = titanic_survival.apply(count_null)
print(column_null_count)

• check df["age"] <18, >=18, unknown

def is_minor(row):
    if row["age"] < 18:
        return "minor"
    elif row["age"] >= 18:
        return "adult"
    else:
        return "unknown"
age_labels = titanic_survival.apply(is_minor, axis=1)
print (age_labels)

pivot_table

age_group_survival = titanic_survival.pivot_table(index="age_labels", values=["survived"], aggfunc=np.mean)
print (age_group_survival)
print (type(age_group_survival))

Another way to do groupby:

import numpy as np
# Unique values in Major_category column.
mc = all_ages['Major_category'].unique()
mclist = mc.tolist()
for item in mclist:
    temp = all_ages.loc[all_ages["Major_category"] == item]
    grads = temp["Total"].sum()
    aa_cat_counts[item] = grads
print (aa_cat_counts)

Compare values from different df
Convert string value to float

import numpy as np
import pandas as pd
majors = recent_grads['Major'].unique()
rg_lower_count = 0
malist = majors.tolist()
#print (malist)
for i in malist:
    rtemp = recent_grads.loc[recent_grads["Major"]== i]
    rrate = rtemp["Unemployment_rate"]
    print (rrate)
    atemp = all_ages.loc[all_ages["Major"]== i]
    arate = atemp["Unemployment_rate"]
    if float(rrate) < float(arate): #may not be good practice
        rg_lower_count += 1
print (rg_lower_count)