TungNT (Blue)

You've loaded an old revision of the document! If you save it, you will create a new version with this data.

====== 1. Pandas ======

https://pandas.pydata.org/docs

**Import thư viện:**

<file python>
import pandas as pd
import numpy as np

pd.options.mode.copy_on_write = True # https://pandas.pydata.org/docs/user_guide/copy_on_write.html
</file>

====== 2. Create DataFrame ======

===== 2.1 Create from a CSV =====

<file python>
df = pd.read_csv('data/pandas/file.csv')
df = pd.read_csv('data/pandas/file.csv', engine="pyarrow")
df = pd.read_csv('data/pandas/file.csv', low_memory=False)
</file>

===== 2.2 Create from a Dictionary =====

<file python>
tempdict = {'col1':[1,2,3], 'col2':[4,5,6], 'col3':[7,8,9]}

df = pd.DataFrame.from_dict(tempdict)
</file>

===== 2.3 Create from a DataFrame =====

<file python>
df_new = df[["eid", "type", "request_amount", "user_name", "status", "partner_status", "approved_time", "created_at", "updated_at", "date"]]
</file>

===== 2.4 Append from DataFrames =====

<file python>
df_ut_01 = pd.read_csv('../data/ns/ut_2021_01.csv', engine="pyarrow")
df_ut_02 = pd.read_csv('../data/ns/ut_2021_12.csv', engine="pyarrow")

df_ut = df_ut_01._append(df_ut_02)
</file>

====== 3. Read DataFrame ======

===== 3.1 Show Top n and Bottom n Rows =====

<file python>
df.head(10)

df.head()

df.tail(15)
</file>

===== 3.2 Show Columns and Data Type =====

<file python>
df.columns

df.dtypes
</file>

===== 3.3 Summary Statistics =====

<file python>
df.describe()

df.describe(include='object')
</file>

===== 3.4 Filtering Columns =====

<file python>
df.COLUMN_NAME

df['COLUMN_NAME']

df[['COLUMN_NAME_1', 'COLUMN_NAME_2']]
</file>

===== 3.5 Filtering on Rows =====

<file python>
df[df['COLUMN_NAME'] == 'Column Value']

df[(df['COLUMN_NAME_1']=='Column Value') & (df['COLUMN_NAME_2']==True)]
</file>

**Ví dụ 1:**

<file python>
approve_from_time = np.datetime64('2021-01-01 00:00:00')
approve_to_time = np.datetime64('2021-01-30 23:59:59')

df_ut_data = df_ut[(df_ut['approved_time'] >= approve_from_time) & (df_ut['approved_time'] <= approve_to_time)]

df_ut_data_success = df_ut[(df_ut['partner_status'] == 'SUCCESS') & (df_ut['approved_time'] >= approve_from_time) & (df_payout['approved_time'] <= approve_to_time)]
</file>

**Ví dụ 2:**

<file python>
remit_accounts = ['TerraPay', 'tranglo', 'Nium', 'GME', 'OnePIP', 'Ipay', 'SingX', 'brdgx', 'hanshan', 'HanshanAPI', 'tranglo_CITAD', 'Nium_CITAD', 'SingX_CITAD']

df_payout_remit = df_ut_2024_new[
        (df_ut_2024_new['type'] == 'TRANSFER_BANK') 
        & (df_ut_2024_new["user_name"].isin(remit_accounts))
]
</file>

====== 4. Update DataFrame ======

===== 4.1 Creating Column =====

<file python>
df['NEW_COLUMN'] = df['NEW_COLUMN_1'] + df['NEW_COLUMN_2']
</file>

<code python>
df_ut['date'] = pd.to_datetime(df_ut['date'], format='%d/%m/%Y')
df_ut['approved_time'] = pd.to_datetime(df_ut['approved_time'], format='%d/%m/%Y %H:%M:%S')
df_ut['created_at'] = pd.to_datetime(df_ut['created_at'], format='%d/%m/%Y %H:%M:%S')
df_ut['updated_at'] = pd.to_datetime(df_ut['updated_at'], format='%d/%m/%Y %H:%M:%S')
df_ut['processed_time'] = (df_ut['approved_time'] - df_ut['created_at']).dt.seconds
df_ut['weekday'] = df_ut['created_at'].dt.day_name()
df_ut['fee_postpaid'] = np.where(((df_ut['fee_type'] == 'POSTPAID' )) , df_ut['fee'], 0)
</code>
===== 4.2. Update a DataFrame =====

<file python>
df['COLUMN_NAME'] = 'Column Value'
</file>

**Ví dụ 1:**

<file python>
def get_fee(entity):
    year = int(entity['created_at'].year)
    
    if year == 2024:
        return get_fee_2024(entity)
    
    return 0
    
def get_fee_2024(entity):
    month = int(entity['created_at'].month)
    day = entity['created_at'].day
    provider = entity['provider']
    request_amount = entity['request_amount']    
    bank_no = entity['bank_no']     
    
    if provider == 'EPAY':
        return 2200
    
    if provider == 'TECHCOMBANK' and bank_no != provider:
        return 275 if month >= 3 else 550  
    
    if provider == 'MBBANK':
        return 880 if bank_no != provider else 1980
    
    if provider == 'VIETINBANK' and bank_no != provider:
        return 1650
    
    if provider == 'VPBANK' and bank_no != provider:
        return 275 if month <= 2 else 605

if provider == 'BIDV':
        if bank_no != provider:
            return 1100
        
        return 1705 if request_amount <= 300000000 else 1925  
    
    return 0
        
df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y')
df['created_at'] = pd.to_datetime(df['created_at'], format='%d/%m/%Y %H:%M:%S')
df['fee'] = df.apply(lambda x: get_fee(x), axis=1)
df['processed_time'] = (df['approved_time'] - df['created_at']).dt.seconds
df.loc[:,'day'] = df.created_at.dt.strftime('%d')
</file>

**Ví dụ 2:**

<file python>
df_industries = pd.read_csv('../data/ns/user_industries.csv', low_memory=False)

df_industries.rename(columns={
    'Tài khoản': 'name', 
    'Tên tài khoản': 'fullname', 
    'Trạng thái': 'status',
    'Thời gian tạo': 'created_at',
    'Ngành nghề': 'industry_name'    
}, inplace=True)

df_industries = df_industries.drop(columns=['STT'])

df_industries.insert(3, "Note", "")

df_industries.set_index('name')

df_industries
</file>

**Ví dụ 3:**

<file python>
import math

def get_fee_total(d):
    fee_total = 0
    
    if math.isnan(d['2021_payin_fee_total']) == False:
        fee_total = fee_total + float(d['2021_payin_fee_total'])
    if math.isnan(d['2022_payin_fee_total']) == False:
        fee_total = fee_total + float(d['2022_payin_fee_total'])
    if math.isnan(d['2023_payin_fee_total']) == False:
        fee_total = fee_total + float(d['2023_payin_fee_total'])
    if math.isnan(d['2024_payin_fee_total']) == False:
        fee_total = fee_total + float(d['2024_payin_fee_total'])
        
    if math.isnan(d['2021_payout_fee_total']) == False:
        fee_total = fee_total + float(d['2021_payout_fee_total'])
    if math.isnan(d['2022_payout_fee_total']) == False:
        fee_total = fee_total + float(d['2022_payout_fee_total'])
    if math.isnan(d['2023_payout_fee_total']) == False:
        fee_total = fee_total + float(d['2023_payout_fee_total'])
    if math.isnan(d['2024_payout_fee_total']) == False:
        fee_total = fee_total + float(d['2024_payout_fee_total'])
        
    return fee_total

df_fee['fee_total'] = df_fee.apply(lambda x: get_fee_total(x), axis=1)
</file>
===== 4.3. Delete a DataFrame =====

===== 4.4. Convert nan to 0 =====

<file python>
df_statistic['count'] = df_statistic['count'].fillna(0)
</file>

===== 4.5. Convert column type =====

<file python>
df_statistic['count'] = df_statistic['count'].astype('Int64')
</file>
====== 5. Group/Sort Data ======

<file python>
df.groupby(['user_name'])['request_amount'].sum()

df_groups = df.groupby(['transaction_id']).agg({'transaction_id':'size', 'fee': 'sum', 'amount': 'mean'}).rename(columns={'transaction_id':'count', 'fee': 'fee_total', 'amount': 'amount_avg'}).reset_index()

df_groups.sort_values(by='count', ascending=False)
df_groups.sort_values(by='avg', ascending=False)

# Tính trung bình 1 cột
df["internal_time"].mean()
</file>

====== 6. Merge 2 Dataframe ======

<file python>
df_statistic = pd.merge(df_users, df_industries, left_on='name', right_on='name', how = 'left')
</file>
====== 7. Output ======

<file python>
df.to_csv('data/pandas/output.csv')

df.to_json()

df.to_html()

with pd.ExcelWriter('../data/ns/dd/file.xlsx') as writer:
    df_1.to_excel(writer, sheet_name='sheet 1')
    df_2_excel(writer, sheet_name='sheet 2') 
</file>

Please fill all the letters into the box to prove you're human. B S G S I Please keep this field empty:

Edit summary