ทีละขั้นตอนการสอนการจัดรูปแบบสเปรดชีต Excel โดยใช้ Python

เป้าหมาย

คุณต้องอัพเดทรายงานเป็นประจำทุกวันหรือไม่? คุณเคยคิดหาวิธีทำให้งานหุ่นยนต์ที่น่าเบื่อ น่าเบื่อ และเป็นหุ่นยนต์เหล่านี้เป็นไปโดยอัตโนมัติหรือไม่? คุณอาจพูดว่า 'ใช่ แต่ฉันทำไม่ได้ เนื่องจากมีการจัดรูปแบบหลายอย่างที่ฉันต้องเพิ่มใน Excel ด้วยตนเอง' วันนี้ ฉันจะแสดงให้คุณเห็นว่า Python สามารถทำงานบนการจัดรูปแบบสเปรดชีต Excel และสร้างรายงานโดยอัตโนมัติได้อย่างไร

Cryptocurrency เป็นตัวอย่าง

ฉันเป็นคนที่คลั่งไคล้สกุลเงินดิจิทัลและฉันจะตรวจสอบราคาเหรียญต่างๆ ในแต่ละวัน อย่างไรก็ตาม การตรวจสอบเหรียญทั้งหมดทีละเหรียญใช้เวลานานมาก และนั่นเป็นเหตุผลที่ฉันต้องการใช้โปรแกรมเพื่อสร้างรายงานสรุปโดยอัตโนมัติทุกเช้า หากคุณสนใจที่จะทราบการวิเคราะห์ข้อมูลทางการเงินเพิ่มเติม อย่าลังเลที่จะตรวจสอบสิ่งพิมพ์ของฉัน

รหัส LL และข้อมูลจะถูกอัปโหลดไปยัง GitHub ของฉัน คุณสามารถตรวจสอบและทำซ้ำrepo นี้เพื่อศึกษาเพิ่มเติม =)

การขูดข้อมูล

ก่อนอื่นเราต้องรวบรวมข้อมูลเพื่อแสดงในตาราง ตัวอย่างเช่น คุณอาจสอบถามข้อมูลจากฐานข้อมูลของบริษัท คุณอาจได้รับข้อมูลจากคู่ค้าทางธุรกิจของคุณ หรือเช่นเดียวกับกรณีของฉัน ฉันขูดข้อมูลจากอินเทอร์เน็ตทันที แหล่งข้อมูลที่ฉันใช้คือ CoinDesk API สำหรับรายละเอียดเพิ่มเติมเกี่ยวกับวิธีการทำงานของ API นี้และการตั้งค่าพารามิเตอร์ คุณสามารถตรวจสอบบทความของฉันด้านล่าง

Web Scraping Cryptocurrency ข้อมูลราคา 1 นาที (Python)

import json
from urllib import request
url = "https://production.api.coindesk.com/v2/tb/price/ticker?assets=all"
response = request.urlopen(url)
data = json.loads(response.read().decode())

{'statusCode': 200,
 'message': 'OK',
 'data': {'BTC': {'iso': 'BTC',
   'name': 'Bitcoin',
   'slug': 'bitcoin',
   'change': {'percent': 0.6642429710971107, 'value': 298.538427},
   'ohlc': {'o': 44944.160494,
    'h': 46843.67,
    'l': 43480.540171,
    'c': 45242.698921},
   'circulatingSupply': 18814943.79249204,
   'marketCap': 851238837219.2552,
   'ts': 1631590859000,
   'src': 'tb'},
  'ETH': {...}}}

อย่างไรก็ตาม ข้อมูลอยู่ในรูปแบบ JSON ซึ่งอ่านไม่ง่าย ดังนั้นเราจึงต้องประมวลผลข้อมูลล่วงหน้าในรูปแบบตาราง

def flatten_json(coin_dict):
    flatten_dict = {}
    def flatten(x, name=''):
        if type(x) is dict:
            for key in x:
                flatten(x[key], name + key + '_')
        else:
            flatten_dict[name[:-1]] = x
    flatten(coin_dict)
    return flatten_dict
import pandas as pd
master_df = pd.DataFrame()
for coin in data['data'].keys():
    temp_df = pd.json_normalize(flatten_json(data['data'][coin]))
    master_df = master_df.append(temp_df)
master_df = master_df[['iso', 'name', 'ohlc_o', 'ohlc_h', 'ohlc_l', 'ohlc_c', 'change_percent']].reset_index(drop=True)
master_df.columns = ['Symbol', 'Name', 'Open', 'High', 'Low', 'Close', 'Pct_Change']
master_df.iloc[:, 2:] = master_df.iloc[:, 2:].apply(lambda x: round(x, 2))
master_df['Pct_Change'] = master_df['Pct_Change'] / 100
master_df = master_df.sort_values('Pct_Change', ascending=False).reset_index(drop=True)
master_df.to_csv('master_df.csv', index=False)
master_df.head()

1. ทำให้พจนานุกรมใน JSON เรียบขึ้นสำหรับข้อมูลทุกเหรียญ
2. ผนวกกรอบข้อมูลเหรียญทั้งหมดลงในเฟรมข้อมูลหลักเดียว
3. เลือกคอลัมน์ที่เราสนใจ
4. เปลี่ยนชื่อคอลัมน์เพื่อให้ง่ายต่อการอ้างอิง
5. การปัดเศษตัวเลขเพื่อทำให้ตารางง่ายขึ้น
6. เรียงตามเปอร์เซ็นต์การเปลี่ยนแปลง

ในที่สุดก็ถึงจานหลัก จำไว้อย่างหนึ่งก่อนจะเขียนโค้ด คุณต้องมีการออกแบบในใจก่อน ตัวอย่างเช่น ด้านล่างนี้คือเทมเพลตฉบับร่างของฉัน เหตุผลที่ต้องมีแบบร่างเพราะจะช่วยให้คุณกำหนดคอลัมน์หรือดัชนีเพื่อใส่องค์ประกอบได้ดีขึ้น (ชื่อ ตาราง หมายเหตุ ฯลฯ)

1. เพิ่มตารางลงใน excel

file_name = "Cryptocurrency.xlsx"
sheet_name = "Summary"
writer = pd.ExcelWriter(file_name, engine='xlsxwriter')
master_df.to_excel(writer, sheet_name=sheet_name, startrow = 2, index = False)

2. เพิ่มชื่อเรื่องใน excel

from datetime import datetime
workbook  = writer.book
worksheet = writer.sheets[sheet_name]
worksheet.write(0, 0, 'Cryptocurrency Pricing Summary on '+datetime.now().strftime('%d %b %Y'), workbook.add_format({'bold': True, 'color': '#E26B0A', 'size': 14}))

3. เพิ่มหมายเหตุลงใน excel

worksheet.write(len(master_df)+4, 0, 'Remark:', workbook.add_format({'bold': True}))
worksheet.write(len(master_df)+5, 0, 'The last update time is ' + datetime.now().strftime('%H:%M') + '.')

4. เพิ่มสีให้กับส่วนหัวของตาราง

header_format = workbook.add_format({'bold': True, 'text_wrap': True, 'fg_color': '#FDE9D9', 'border': 1})
for col_num, value in enumerate(master_df.columns.values):
    worksheet.write(2, col_num, value, header_format)

5. เพิ่มเส้นขอบให้กับตาราง

row_idx, col_idx = master_df.shape
for r in range(row_idx):
    for c in range(col_idx):
        if c == 6:
            worksheet.write(r + 3, c, master_df.values[r, c], workbook.add_format({'border': 1, 'num_format': '0.00%'}))
        else:
            worksheet.write(r + 3, c, master_df.values[r, c], workbook.add_format({'border': 1, 'num_format': '0.00'}))

6. ตั้งค่าความกว้างของคอลัมน์

worksheet.set_column(0, 6, 12)
worksheet.set_column(1, 1, 20)

บทสรุป

ฉันหวังว่าบทความนี้สามารถช่วยผู้ที่ต้องอัปเดตสเปรดชีต Excel เป็นประจำ หากคุณต้องการทราบเทคนิคการใช้สำนักงานเพิ่มเติมเกี่ยวกับการเขียนโปรแกรม คุณสามารถสมัครรับสื่อของฉัน แล้วฉันจะอัปเดตและแบ่งปันทักษะเพิ่มเติมกับทุกคน =)