adjust column lengths when using Pandas ExcelWriter

 To dynamically adjust all the column lengths,

        writer = pd.ExcelWriter('temp.xlsx') 

        df.to_excel(writer, sheet_name='sheetName', index=False)

        for column in df:

                column_length = max(df[column].astype(str).map(len).max(), len(column))

                col_idx = df.columns.get_loc(column)

                writer.sheets['sheetName'].set_column(col_idx, col_idx, column_length)

        writer.close()

To manually adjust a column using column name,

        col_idx = df.columns.get_loc('columnName')

        writer.sheets['sheetName'].set_column(col_idx, col_idx, 20)

To manually adjust a column using column index,

        writer.sheets['sheetName'].set_column(col_idx, col_idx, 20)

Potential error messages:

        AttributeError: 'Worksheet' object has no attribute 'set_column'

-->  install "xlsxwriter" module

-->  use the installed as the engine,  writer = pd.ExcelWriter('temp.xlsx', engine='xlsxwriter') 

[Python] join two dataframe according to specific column

 To join two dataframe (df1, df2) according to specific column (e.g., X,Y,Z),

# merge two dataframe comparing X column data of df1 and Y column data of df2

# merge rows where X value on df1 is same to Y value of df2  (other rows will be abandoned)

# in the merged dataframe, "_x" will be added to column label of df1 and "_y" will be added to column label of df2

          pd.merge(df1, df2, left_on="X", right_on="Y")     

# comparing multiple columns

# rows with same X, Y, Z values on df1 and df2 will merge  (other rows will be abandoned)

# in the merged dataframe, "_x" will be added to column label of df1 and "_y" will be added to column label of df2 

          pd.merge(df1, df2, left_on=['X ','Y','Z'], right_on=['X ','Y','Z'])

[Python] to compare/calculate Pandas columns

to compare Pandas columns, 

    print(df['Column1'].equals(df['Column2'])) 

if df['Column1'] is equal to df['Column2'], it will return "True" otherwise "False" 

to calculate/assign values,

    print(df['Column1'] + df['Column2']) 

import numpy as np
import pandas as pd

a = [['10', '1.2', '4.2'], ['15', '70', '0.03'], ['8', '5', '0']]
df = pd.DataFrame(a, columns=['one', 'two', 'three'])

df['que'] = np.where((df['one'] >= df['two']) & (df['one'] <= df['three']), df['one'], np.nan)

result will look like:

          one  two three  que

   0     10    1.2   4.2    10

   1      5     70    0.03  NaN

   2      8     5      0       NaN

[Python] how to find the first N largest/smallest values

import pandas as pd

temp= pd.read_csv("temp.txt", delimiter="\t", skipinitialspace=True, encoding= "utf-8")
print(temp["score"].nlargest(5))    # print top 5 values
print(temp["score"].smallest(5))    # print bottom 5 values

print(temp.loc[((temp['score'] >80) & (temp['score'] <=90))  |  ((temp['math'] >=80) & (temp['math'] <90))].nlargest(5))    # print conditioned top 5 values  

[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)