第二十四個夏天後: csv

顯示具有 csv 標籤的文章。顯示所有文章

2024年8月23日星期五

Python 開發筆記 - 不透過 Google API Key 下載公開 Google Sheets 資料，將每個 Sheet 匯出 csv 格式

有個工作任務要做 Google Sheets 資料比對，最簡單的方式就把他們匯出後用 git diff 來比對即可，想試著用 AI 產生一隻 python 小工具，只要輸入 Google Sheets URL 或是 Google Sheets URL 內關鍵的辨識 ID （在此稱作 spreadsheet id），就能夠下載該 Google Spreadsheet 內所有 sheet 資料

然後，要下載指定 sheet 必須得知每個 sheet gid ，這個問 ChatGPT-4o 或 Claude.ai 老半天還是沒法解，包過上傳 html static code，最後自己還是跳下來收尾人工刻一下，原理：

先設法下載到 HTML Code
透過 docs-sheet-tab-caption 抓出 Sheet Name
透過 var bootstrapData = {...}; 得知內有 Sheet Name 與 Gid 的資料
再用 [0,0,\"gid\",[ 格式，找到 gid

連續動作：

% python3 main.py

usage: main.py [-h] (--google-spreadsheet-url GOOGLE_SPREADSHEET_URL | --google-spreadsheet-id GOOGLE_SPREADSHEET_ID) [--output OUTPUT]

main.py: error: one of the arguments --google-spreadsheet-url --google-spreadsheet-id is required

% python3 main.py --google-spreadsheet-id 'XXXXXXXXXXXXXXXXXXXXXXX'

[INFO] Downloaded sheet: sheet01 to sheets_csv/sheet01.csv

[INFO] Downloaded sheet: sheet02 to sheets_csv/sheet02.csv

[INFO] Downloaded sheet: sheet03 to sheets_csv/sheet03.csv

[INFO] Downloaded sheet: sheet04 to sheets_csv/sheet04.csv

[INFO] Downloaded sheet: sheet05 to sheets_csv/sheet05.csv

程式碼：

```
% cat main.py
import argparse
import re
import requests
import json
import os

def extract_spreadsheet_id(url):
match = re.search(r'/d/([a-zA-Z0-9-_]+)', url)
return match.group(1) if match else None

def get_spreadsheet_info(spreadsheet_id):
url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/edit"
headers = {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

try:
response = requests.get(url, headers=headers)
response.raise_for_status()
html_content = response.text

# Extract sheet names
sheet_names = re.findall(r'docs-sheet-tab-caption[^>]+>([^<]+)</div>', html_content)
#print(f"Found sheet names: {sheet_names}")

# Extract mergedConfig
config_match = re.search(r'var bootstrapData\s*=\s*({.*?});', html_content, re.DOTALL)
if config_match:
config_str = config_match.group(1)
sheet_info = {}
try:
for index, sheet_name in enumerate(sheet_names):
#print(f"Processing sheet: {sheet_name}, index: {index}")
beginPattern = f'[{index},0,\\"'
endPattern = f'\\",['
beginIndex = config_str.find(beginPattern)
endIndex = config_str.find(endPattern, beginIndex)
gidValue = config_str[beginIndex + len(beginPattern):endIndex]
sheet_info[sheet_name] = gidValue
return sheet_info
except Exception as e:
print(f"[INFO] Error extracting sheet information: {e}")
return None
else:
print("[INFO] Could not find bootstrapData in the HTML content")
return None
except requests.RequestException as e:
print(f"[INFO] Error fetching the spreadsheet: {e}")
return None

def download_sheet_as_csv(spreadsheet_id, sheet_name, gid, output_folder):
csv_url = f"https://docs.google.com/spreadsheets/d/{spreadsheet_id}/export?format=csv&gid={gid}"
csv_response = requests.get(csv_url)

if csv_response.status_code == 200:
output_path = os.path.join(output_folder, f"{sheet_name}.csv")
with open(output_path, 'wb') as f:
f.write(csv_response.content)
print(f"[INFO] Downloaded sheet: {sheet_name} to {output_path}")
else:
print(f"[INFO] Failed to download sheet: {sheet_name}. Status code: {csv_response.status_code}")

def main():
parser = argparse.ArgumentParser(description="Extract Google Spreadsheet information")
group = parser.add_mutually_exclusive_group(required=True)
group.add_argument("--google-spreadsheet-url", help="Google Spreadsheet URL")
group.add_argument("--google-spreadsheet-id", help="Google Spreadsheet ID")
parser.add_argument('--output', type=str, default='sheets_csv', help='The directory to save the CSV files')
args = parser.parse_args()

if args.google_spreadsheet_url:
spreadsheet_id = extract_spreadsheet_id(args.google_spreadsheet_url)
else:
spreadsheet_id = args.google_spreadsheet_id

if not spreadsheet_id:
print("[INFO] Invalid Google Spreadsheet URL or ID")
return

sheet_info = get_spreadsheet_info(spreadsheet_id)
if sheet_info:
for name, gid in sheet_info.items():
download_sheet_as_csv(spreadsheet_id, name, gid, args.output)
else:
print("[INFO] Failed to extract sheet information")

if __name__ == "__main__":
main()
```

2021年4月9日星期五

[PHP] 透過 Maxmind GeoIP DB 統計用戶資訊 @ macOS 11.2, PHP 7.3.24

真是超級久沒用 maxmind.com 的 GeoIP DB 了，一時之間還以為沒有免費使用的方式，追了一下是要註冊帳號才能下載，而 maxmind 也有提供自動化每天更新 GeoIP DB 的機制。

在此對一份類似 Access Logs 做分析，將其轉成 CSV 格式來分析，其中 CSV 裡頭有 id 跟 remote_ip 兩個欄位，將 remote_ip 分析完後直接歸類屬於哪個 country_code，程式碼很簡單：

% php -v

WARNING: PHP is not recommended
PHP is included in macOS for compatibility with legacy software.
Future versions of macOS will not include PHP.
PHP 7.3.24-(to be removed in future macOS) (cli) (built: Dec 21 2020 21:33:25) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.24, Copyright (c) 1998-2018 Zend Technologies

% cat composer.json

{
"require": {
"geoip2/geoip2": "~2.0"
}
}

% php composer.phar install

No lock file found. Updating dependencies instead of installing from lock file. Use composer update over composer install if you do not have a lock file.
Loading composer repositories with package information
Updating dependencies
Lock file operations: 4 installs, 0 updates, 0 removals
- Locking composer/ca-bundle (1.2.9)
- Locking geoip2/geoip2 (v2.11.0)
- Locking maxmind-db/reader (v1.10.0)
- Locking maxmind/web-service-common (v0.8.1)
Writing lock file
Installing dependencies from lock file (including require-dev)
Package operations: 4 installs, 0 updates, 0 removals
- Installing composer/ca-bundle (1.2.9): Extracting archive
- Installing maxmind/web-service-common (v0.8.1): Extracting archive
- Installing maxmind-db/reader (v1.10.0): Extracting archive
- Installing geoip2/geoip2 (v2.11.0): Extracting archive
2 package suggestions were added by new dependencies, use `composer suggest` to see details.

Generating autoload files
1 package you are using is looking for funding.
Use the `composer fund` command to find out more!

% cat job.php

<?php

require 'vendor/autoload.php';

// https://github.com/maxmind/GeoIP2-php
use GeoIp2\Database\Reader;
$reader = new Reader('GeoLite2-City.mmdb');

// https://www.php.net/manual/en/function.fgetcsv.php
$row = 1;
$header_row = NULL;
$header = array();
$country_group = array();
$lookup = array();

$tracking_time_cost_per_run = microtime(true);
echo "[".date("Y-m-d H:i:s")."]\tinit\n";
if (($handle = fopen("access_log.csv", "r")) !== FALSE) {
while (($data = fgetcsv($handle, 10240, ",")) !== FALSE) {
if (is_null($header_row)) {
$header_row = $data;
foreach($header_row as $index => $name) {
$header[$name] = $index;
}
continue;
}
$remote_ip = $data[ $header['remote_ip'] ];
$data_id = $data[ $header['id'] ];

try {
$record = $reader->city($remote_ip);
} catch (Exception $e) {
continue;
}
if (!is_null($record) && property_exists($record, 'country') && !is_null($record->country) && isset($record->country->isoCode)) {
if (!isset($country_group[$record->country->isoCode]))
$country_group[$record->country->isoCode] = array();
$hash_key = $remote_ip.'-'.$record->country->isoCode;
if (!isset($lookup[$hash_key])) {
$lookup[$hash_key] = 1;
array_push($country_group[$record->country->isoCode], $data_id);
}
}
++$row;
if ($row % 100000 == 0) {
echo "[".date("Y-m-d H:i:s")."]\t".number_format($row).", time cost: ".(microtime(true) - $tracking_time_cost_per_run)."\n";
$tracking_time_cost_per_run = microtime(true);
file_put_contents('/tmp/lookup-result.json', json_encode($country_group));
}
}
fclose($handle);

echo "[".date("Y-m-d H:i:s")."]\t".number_format($row)."\n";
echo "Total #: $row\n";
print_r($header);
file_put_contents('lookup-result.json', json_encode($country_group));
}

產出：

% time php job.php
[2021-04-09 21:03:51] init
[2021-04-09 21:05:18] 100,000, time cost: 86.867056131363
[2021-04-09 21:06:41] 200,000, time cost: 83.395349025726

...

% jq '' /tmp/lookup-result.json | head -n10
{
"TW": [
"1",
"68",
"101",
"121",
"147",
"193",
"236",
"259",

...

就把一堆 record id 擺在某個 country_code 下面，每 85秒處理完 10萬筆資料。

2017年9月19日星期二

[Python] 機器學習筆記 - 使用 Pandas 處理 CSV 格式、過濾資料與自身 Index 更新問題

使用 Pandas 套件進行分析資料時，它提供的功能包括便利的資料過濾：


import seaborn as sns

import pandas as pd



dataset = sns.load_dataset("tips")

print(dataset)

print(dataset.shape)

print(dataset.columns)



print("list total_bill > 30:")

print(dataset[ dataset['total_bill'] > 30 ] )

然而，預設 Pandas 會記錄原先的 raw index ，這也有不錯的功用，但有時希望照新的架構顯示，需要再多用 reset_index():


print("list total_bill > 30 and tip < 4:")

print(dataset[ (dataset['total_bill'] > 30) & (dataset['tip'] < 4) ] )



print("rebuild index:")

dataset = dataset[ (dataset['total_bill'] > 30) & (dataset['tip'] < 4) ]

dataset = dataset.reset_index()

print(dataset)

連續動作：


$ python pandas_study.py

     total_bill   tip     sex smoker   day    time  size

0         16.99  1.01  Female     No   Sun  Dinner     2

1         10.34  1.66    Male     No   Sun  Dinner     3

2         21.01  3.50    Male     No   Sun  Dinner     3

3         23.68  3.31    Male     No   Sun  Dinner     2

4         24.59  3.61  Female     No   Sun  Dinner     4

5         25.29  4.71    Male     No   Sun  Dinner     4

6          8.77  2.00    Male     No   Sun  Dinner     2

7         26.88  3.12    Male     No   Sun  Dinner     4

8         15.04  1.96    Male     No   Sun  Dinner     2

9         14.78  3.23    Male     No   Sun  Dinner     2

10        10.27  1.71    Male     No   Sun  Dinner     2

11        35.26  5.00  Female     No   Sun  Dinner     4

12        15.42  1.57    Male     No   Sun  Dinner     2

13        18.43  3.00    Male     No   Sun  Dinner     4

14        14.83  3.02  Female     No   Sun  Dinner     2

15        21.58  3.92    Male     No   Sun  Dinner     2

16        10.33  1.67  Female     No   Sun  Dinner     3

17        16.29  3.71    Male     No   Sun  Dinner     3

18        16.97  3.50  Female     No   Sun  Dinner     3

19        20.65  3.35    Male     No   Sat  Dinner     3

20        17.92  4.08    Male     No   Sat  Dinner     2

21        20.29  2.75  Female     No   Sat  Dinner     2

22        15.77  2.23  Female     No   Sat  Dinner     2

23        39.42  7.58    Male     No   Sat  Dinner     4

24        19.82  3.18    Male     No   Sat  Dinner     2

25        17.81  2.34    Male     No   Sat  Dinner     4

26        13.37  2.00    Male     No   Sat  Dinner     2

27        12.69  2.00    Male     No   Sat  Dinner     2

28        21.70  4.30    Male     No   Sat  Dinner     2

29        19.65  3.00  Female     No   Sat  Dinner     2

..          ...   ...     ...    ...   ...     ...   ...

214       28.17  6.50  Female    Yes   Sat  Dinner     3

215       12.90  1.10  Female    Yes   Sat  Dinner     2

216       28.15  3.00    Male    Yes   Sat  Dinner     5

217       11.59  1.50    Male    Yes   Sat  Dinner     2

218        7.74  1.44    Male    Yes   Sat  Dinner     2

219       30.14  3.09  Female    Yes   Sat  Dinner     4

220       12.16  2.20    Male    Yes   Fri   Lunch     2

221       13.42  3.48  Female    Yes   Fri   Lunch     2

222        8.58  1.92    Male    Yes   Fri   Lunch     1

223       15.98  3.00  Female     No   Fri   Lunch     3

224       13.42  1.58    Male    Yes   Fri   Lunch     2

225       16.27  2.50  Female    Yes   Fri   Lunch     2

226       10.09  2.00  Female    Yes   Fri   Lunch     2

227       20.45  3.00    Male     No   Sat  Dinner     4

228       13.28  2.72    Male     No   Sat  Dinner     2

229       22.12  2.88  Female    Yes   Sat  Dinner     2

230       24.01  2.00    Male    Yes   Sat  Dinner     4

231       15.69  3.00    Male    Yes   Sat  Dinner     3

232       11.61  3.39    Male     No   Sat  Dinner     2

233       10.77  1.47    Male     No   Sat  Dinner     2

234       15.53  3.00    Male    Yes   Sat  Dinner     2

235       10.07  1.25    Male     No   Sat  Dinner     2

236       12.60  1.00    Male    Yes   Sat  Dinner     2

237       32.83  1.17    Male    Yes   Sat  Dinner     2

238       35.83  4.67  Female     No   Sat  Dinner     3

239       29.03  5.92    Male     No   Sat  Dinner     3

240       27.18  2.00  Female    Yes   Sat  Dinner     2

241       22.67  2.00    Male    Yes   Sat  Dinner     2

242       17.82  1.75    Male     No   Sat  Dinner     2

243       18.78  3.00  Female     No  Thur  Dinner     2



[244 rows x 7 columns]

(244, 7)

Index(['total_bill', 'tip', 'sex', 'smoker', 'day', 'time', 'size'], dtype='object')

list total_bill > 30:

     total_bill    tip     sex smoker   day    time  size

11        35.26   5.00  Female     No   Sun  Dinner     4

23        39.42   7.58    Male     No   Sat  Dinner     4

39        31.27   5.00    Male     No   Sat  Dinner     3

44        30.40   5.60    Male     No   Sun  Dinner     4

47        32.40   6.00    Male     No   Sun  Dinner     4

52        34.81   5.20  Female     No   Sun  Dinner     4

56        38.01   3.00    Male    Yes   Sat  Dinner     4

59        48.27   6.73    Male     No   Sat  Dinner     4

83        32.68   5.00    Male    Yes  Thur   Lunch     2

85        34.83   5.17  Female     No  Thur   Lunch     4

95        40.17   4.73    Male    Yes   Fri  Dinner     4

102       44.30   2.50  Female    Yes   Sat  Dinner     3

112       38.07   4.00    Male     No   Sun  Dinner     3

141       34.30   6.70    Male     No  Thur   Lunch     6

142       41.19   5.00    Male     No  Thur   Lunch     5

156       48.17   5.00    Male     No   Sun  Dinner     6

167       31.71   4.50    Male     No   Sun  Dinner     4

170       50.81  10.00    Male    Yes   Sat  Dinner     3

173       31.85   3.18    Male    Yes   Sun  Dinner     2

175       32.90   3.11    Male    Yes   Sun  Dinner     2

179       34.63   3.55    Male    Yes   Sun  Dinner     2

180       34.65   3.68    Male    Yes   Sun  Dinner     4

182       45.35   3.50    Male    Yes   Sun  Dinner     3

184       40.55   3.00    Male    Yes   Sun  Dinner     2

187       30.46   2.00    Male    Yes   Sun  Dinner     5

197       43.11   5.00  Female    Yes  Thur   Lunch     4

207       38.73   3.00    Male    Yes   Sat  Dinner     4

210       30.06   2.00    Male    Yes   Sat  Dinner     3

212       48.33   9.00    Male     No   Sat  Dinner     4

219       30.14   3.09  Female    Yes   Sat  Dinner     4

237       32.83   1.17    Male    Yes   Sat  Dinner     2

238       35.83   4.67  Female     No   Sat  Dinner     3

list total_bill > 30 and tip < 4:

print index:

 index: 56

 index: 102

 index: 173

 index: 175

 index: 179

 index: 180

 index: 182

 index: 184

 index: 187

 index: 207

 index: 210

 index: 219

 index: 237

rebuild index:

    index  total_bill   tip     sex smoker  day    time  size

0      56       38.01  3.00    Male    Yes  Sat  Dinner     4

1     102       44.30  2.50  Female    Yes  Sat  Dinner     3

2     173       31.85  3.18    Male    Yes  Sun  Dinner     2

3     175       32.90  3.11    Male    Yes  Sun  Dinner     2

4     179       34.63  3.55    Male    Yes  Sun  Dinner     2

5     180       34.65  3.68    Male    Yes  Sun  Dinner     4

6     182       45.35  3.50    Male    Yes  Sun  Dinner     3

7     184       40.55  3.00    Male    Yes  Sun  Dinner     2

8     187       30.46  2.00    Male    Yes  Sun  Dinner     5

9     207       38.73  3.00    Male    Yes  Sat  Dinner     4

10    210       30.06  2.00    Male    Yes  Sat  Dinner     3

11    219       30.14  3.09  Female    Yes  Sat  Dinner     4

12    237       32.83  1.17    Male    Yes  Sat  Dinner     2

print index:

 index: 0

 index: 1

 index: 2

 index: 3

 index: 4

 index: 5

 index: 6

 index: 7

 index: 8

 index: 9

 index: 10

 index: 11

 index: 12

2016年12月29日星期四

[PHP] 使用 str_getcsv 處理 CSV 格式 (Google Docs export CSV format)

這議題已經很久，之前一直沒踩到什麼，直到處理 Google doc export the CSV format ...

大部分的分析都用：


foreach ( @explode("\n", file_get_content('https://docs.google.com/spreadsheets/d/.../export?format=csv...')) as $line ) {

 $field = str_getcsv($line);

}

但是，若有 enclosure 的方式（例如 "...\n....\n") 就會出包啦，這在 http://php.net/manual/en/function.str-getcsv.php 網站上也有人提到，解法就是一樣用 str_getcsv 去切 $row：


$row = str_getcsv( file_get_content('https://docs.google.com/spreadsheets/d/.../export?format=csv...'), "\n");

foreach( $row as $line ) {

 $field = str_getcsv($line);

}

但是，在處理 Google Docs 又發現切不對，最後發現改用 "\r\n" 即可 :P


$row = str_getcsv( file_get_content('https://docs.google.com/spreadsheets/d/.../export?format=csv...'), "\r\n");

foreach( $row as $line ) {

 $field = str_getcsv($line);

}

Updated @ 2017/05/04
可以改用 tsv 輸出：


$csv = explode("\n", @file_get_contents('https://docs.google.com/spreadsheets/d/.../export?format=tsv...'));

array_shift($csv);  // remove header  





foreach($csv as $line) { 

     $fields = str_getcsv($line, "\t"); 

     print_r($fields); 

}

2015年3月18日星期三

[Linux] 透過 sed 將 MySQL Table 資料轉成 csv 格式 @ Ubuntu 14.04

印象中之前找資料時，有發現 MySQL Server 可以輸出 CSV 格式到本地端。但是，對於無法存取到 DB Server 的檔案系統的環境時(AWS RDS)，只好手動處理一下 :P

用法：


$ mysql -h rds-server -u root -p -e "SELECT field1, field2 FROM db_name.table_name" | grep "^Keyword" | sed 's/\t/","/g' | sed 's/^/"/g' | sed 's/$/"/g' > db-table-export.csv

其中 grep 那段只是為了mysql 輸出時，略過第一行 XD

訂閱：文章 (Atom)

2024年8月23日 星期五

Python 開發筆記 - 不透過 Google API Key 下載公開 Google Sheets 資料，將每個 Sheet 匯出 csv 格式

2021年4月9日 星期五

[PHP] 透過 Maxmind GeoIP DB 統計用戶資訊 @ macOS 11.2, PHP 7.3.24

2017年9月19日 星期二