[Python] statistics with Pandas DataFrame

import pandas as pd 

ng_list = [{"name": "Jake", "math": 61, "chemistry": 80}, {"name": "Annie", "math": 78, "chemistry": 90}, {"name": "Jane", "math": 71, "chemistry": 54}, {"name": "Sam", "math": 75, "chemistry": 74}, {"name": "Ben", "math": 46, "chemistry": 64}, {"name": "Sky", "math": 38, "chemistry": 77}]  

# create data frame 
df = pd.DataFrame(ng_list) 
print(df, "\n") 

# sum for each row
df["total"] = df["math"] + df["chemistry"]
print(df, "\n") 

# sum for each column
print(df["math"].sum(), "\n") 

# mean with condition
mean_math_bet60_80 = df.loc[(df["math"] >= 60) & (df["math"] <= 80), "math"].mean()
print(mean_math_bet60_80, "\n")

# common statistics for numerical values
print(df.describe(), "\n")

# common statistics for non-numerical values
print(df.describe(include='object'), "\n")


# functions for other staticstics:
# max, min: maximum, mimimum values 
# count: count
# sem: standard error
# mode: most frequently appeared value
# quantile: quantile, e.g., 10% quantile --> df['math'].quantile(0.1)
# corr: corelation between two columes, e.g., df['math'].corr(df['chemistry'])

[Python] concatenate two data frame, and deal with NaN

import pandas as pd 

ng_list1 = [{"name": "Jake", "grade": 80}, {"name": "Annie", "grade": 90}, {"name": "Jane"}] 
ng_list2 = [{"name": "Sam", "grade": 74}, {"name": "Ben", "grade": 64}, {"name": "Sky", "grade": 77}, {"name": "Annie", "grade": 90}] 
 
# create data frame 
df1 = pd.DataFrame(ng_list1)
df2 = pd.DataFrame(ng_list2)
print(df1, "\n")
print(df2, "\n")

# concatenate df1 and df2
df = pd.concat([df1, df2], ignore_index=True)
print(df, "\n") 

# remove duplicates
df.drop_duplicates(inplace=True, ignore_index=True) 

# dealing with NaN
# if you wanna drop any row including NaN, use df.dropna(inplace=True)
# if you wanna drop a row with NaN for all columns, use df.dropna(how="all", inplace=True)
# if you wanna fill all NaN with 0, use df.fillna(0, inplace=True)
df.fillna(0, inplace=True)
print(df, "\n") 

[Python] sort data frame and reset index

import pandas as pd
import numpy as np


n = ["Jake", "Annie", "Charles", "Sam", "David"]
g = [80, 90, 75, 64, 78]

# create a data frame 
df = pd.DataFrame()
df["name"], df["grade"] = n, g 
print(df, "\n")

# sort by name
df.sort_values(by=["name"], ascending=[True], inplace=True)  
print(df, "\n")

# reset index
df.reset_index(inplace=True, drop=True)
print(df, "\n")

[Python] create a new Pandas DataFrame, and add a new column to the data frame

import pandas as pd
import numpy as np


n = ["Annie", "Charles", "David", "Sam"]
g = [80, 90, 75, 78]

# create a data frame using one of lists
df = pd.DataFrame(n, columns=["name"])
print(df, "\n")

# add another list to the data frame
df.insert(len(df.columns), "grade", g)
print(df, "\n")

# dertermine whether pass or not and create a new column for it
df["pass"] = np.where(df["grade"] >=80, "yes", "no")
print(df, "\n")

[Python] to reset index for a sorted list

import pandas as pd
import numpy as np


data1 = [1, 3, 2, 4]
data2 = [1015, 1014, 1014, 1019]
data3 = [33, 39, 91, 14]

data = [data1, data2, data3]
print('data: ', data, "\n")

data_transposed = np.transpose(data)
print('transposed data: \n', data_transposed, "\n")

df = pd.DataFrame(data_transposed, columns=['quantity', 'date', 'price'])
print('DataFrame: \n', df, "\n")

size = len(df['date'])
evaluation = [df['quantity']*df['price']] 
temp = np.array(evaluation).reshape(size,1) 
df = np.hstack([df, temp]) 
df = pd.DataFrame(df, columns=['quantity', 'date', 'price', 'evaluation'])
print("DataFrame with added column: \n", df, "\n")

df = df.sort_values(['date', 'evaluation'], ascending=[True, True])
df = df[["date", "evaluation", "price", "quantity"]]
print('sorted DataFrame: \n', df, "\n")

df = df.reset_index(drop=True)
print('re-indexed data: \n', df)

[Python] to append/insert data into a list/array

import pandas as pd

data = [[-4, -3, -2, -1], [1, 2, 3, 4]]
data2 = [5, 6, 7, 8]
data3 = [9, 10, 11, 12]

data_list = []
data_list.append(data)
print(data_list)

data_list.append(data2)
print(data_list)

data_list.insert(0, data3)
print(data_list)
print()

# add header (title row) using pandas
df = pd.DataFrame(data, columns=['A', 'B', 'C', 'D'])
print(df, "\n")

temp_df = pd.DataFrame([data2], columns=['A', 'B', 'C', 'D'])
df = pd.concat([df, temp_df]).reset_index(drop=True)
print(df, "\n")

temp_df = pd.DataFrame({"A": data3[0], "B": data3[1], 'C': data3[2], 'D': data3[3]}, index=[0])
df = pd.concat([temp_df, df]).reset_index(drop=True)
print(df, "\n")

# add a new column using pandas
size = len(df)
data2 = [i * 2 for i in range(size)]
temp = pd.Series(data2, name='XYZ')
df = df.join(temp)
print(df)