MicrosoftExcel-壓縮行數

我有一個包含超過 400,000 個 eircodes 的 CSV，并使用 ArcGIS ProGenerate Near Table功能計算最近的 3 個空氣質量監測器。

反過來，這生成了一個超過 100 萬行的新 csv，因為每個 eircode 都被復制了三次，并為每個監視器分配了一行。（見下面的例子）

有沒有辦法在 Microsoft Excel 中實作以下輸出？因為我只想要單行的 eircode 并包含所有相關資訊。

uj5u.com熱心網友回復：

這是一個使用 Python 及其DictReader/Writer類的解決方案，它使處理您需要的標題/列變得非常簡單。

#!/usr/bin/env python3

import csv
import sys

COMBINED_ROW_TMPL = dict(
    ObjectID=0,
    Eircode='',
    Easting='',
    Northing='',
    M1_Name='',
    M1_Location='',
    M1_Easting='',
    M1_Northing='',
    M2_Name='',
    M2_Location='',
    M2_Easting='',
    M2_Northing='',
    M3_Name='',
    M3_Location='',
    M3_Easting='',
    M3_Northing=''
)

combined_rows = []
with open('input.csv', newline='') as f:
    reader = csv.DictReader(f)

    object_id = 0
    last_eircode = ''
    combined_row = {}
    m_i = 0
    for row in reader:
        this_eircode = row['Eircode']

        if this_eircode != last_eircode:
            # A new Eircode, set up combined_row with data that doesn't change
            object_id  = 1
            combined_row = dict(COMBINED_ROW_TMPL)  # a clean copy of the template

            combined_row['ObjectID'] = object_id
            combined_row['Eircode']  = this_eircode
            combined_row['Easting']  = row['Easting']
            combined_row['Northing'] = row['Northing']

            m_i = 1  # reset monitor group to first instance (i.e., "M1")
            last_eircode = this_eircode

        # Monitor group
        pfx = f'M{m_i}'
        combined_row[pfx   '_Name']     = row['Monitor Name']
        combined_row[pfx   '_Location'] = row['Monitor Location']
        combined_row[pfx   '_Easting']  = row['Monitor Easting']
        combined_row[pfx   '_Northing'] = row['Monitor Northing']

        if m_i == 3:
            combined_rows.append(combined_row)  # only append once per monitor group

        m_i  = 1


with open('output.csv', 'w', newline='') as f:
    writer = csv.DictWriter(f, COMBINED_ROW_TMPL.keys())
    writer.writeheader()
    writer.writerows(combined_rows)

當我將您的示例作為input.csv 運行時

ObjectID,Eircode,Easting,Northing,Monitor Name,Monitor Location,Monitor Easting,Monitor Northing
1,K67AH57,717387.8,748192.3,Swords,Dublin,718046.6,747362
2,K67AH57,717387.8,748192.3,Dublin Airport,Dublin,.....,....
3,K67AH57,717387.8,748192.3,Finglass,Dublin,.....,....
4,H54W283,544468,752503,Claremorris,Mayo,.....,.....
5,H54W283,544468,752503,Rahoon,Galway,.....,.....
6,H54W283,544468,752503,Roscommon,Roscommon,.....,.....

我得到output.csv的以下內容：

ObjectID,Eircode,Easting,Northing,M1_Name,M1_Location,M1_Easting,M1_Northing,M2_Name,M2_Location,M2_Easting,M2_Northing,M3_Name,M3_Location,M3_Easting,M3_Northing
1,K67AH57,717387.8,748192.3,Swords,Dublin,718046.6,747362,Dublin Airport,Dublin,.....,....,Finglass,Dublin,.....,....
2,H54W283,544468,752503,Claremorris,Mayo,.....,.....,Rahoon,Galway,.....,.....,Roscommon,Roscommon,.....,.....

uj5u.com熱心網友回復：

BigBen 關于 Excel 的說法是正確的，但這激起了我的興趣，如果你只是偶爾這樣做......所以我只是在午餐時間敲了這個非常簡單（并且可能錯誤百出）的代碼。將以下內容粘貼到 Excel 中 VBA 中的代碼模塊中（使用 F8 執行步驟）。將資料復制到 Sheet1 中，它將處理到 Sheet2。從幾百行開始，看看需要多長時間（觀察 Excel 視窗左下角的狀態欄進度）......我猜對于 100 萬行來說太長了。（可以加速 10 倍以上通過完全在陣列/記憶體中作業，如果可行）。所以，對于它的價值：

Option Explicit

Sub ToOneLine()

Dim CurrEir As String
Dim PreviousEir As String
Dim EirIndex As Integer
Dim RowIndex As Long
Dim WriteRow As Long
Dim LastRow As Long

Worksheets("Sheet1").Select

'Debug.Print Worksheets("Sheet1").Rows.Count 'result of this put in as Maximum Array size in the line below
Const MaxArraySize = 1048576
Dim ArrayEir(1 To MaxArraySize, 1 To 16)

LastRow = Worksheets("Sheet1").UsedRange.Rows.Count

        WriteRow = 0
        PreviousEir = ""
        RowIndex = 1
        
    Do While EirIndex <= 3

        CurrEir = Range(Cells(RowIndex, 2), Cells(RowIndex, 2))
        
        If PreviousEir <> CurrEir Then
            EirIndex = 1
            WriteRow = WriteRow   1
        ElseIf PreviousEir = CurrEir Then
            EirIndex = EirIndex   1
        End If
        PreviousEir = CurrEir
        
        ArrayEir(WriteRow, 1) = WriteRow                                        '"Line Number"
        ArrayEir(WriteRow, 2) = Range(Cells(RowIndex, 2), Cells(RowIndex, 2))  'Eircode
        ArrayEir(WriteRow, 3) = Range(Cells(RowIndex, 3), Cells(RowIndex, 3))  'Easting
        ArrayEir(WriteRow, 4) = Range(Cells(RowIndex, 4), Cells(RowIndex, 4))  'Northing
        If EirIndex = 1 Then
            ArrayEir(WriteRow, 5) = Range(Cells(RowIndex, 5), Cells(RowIndex, 5))  'M1_Name
            ArrayEir(WriteRow, 6) = Range(Cells(RowIndex, 6), Cells(RowIndex, 6))  'M1_Location
            ArrayEir(WriteRow, 7) = Range(Cells(RowIndex, 7), Cells(RowIndex, 7))  'M1_Easting
            ArrayEir(WriteRow, 8) = Range(Cells(RowIndex, 8), Cells(RowIndex, 8))  'M1_Northing
        ElseIf EirIndex = 2 Then
            ArrayEir(WriteRow, 9) = Range(Cells(RowIndex, 5), Cells(RowIndex, 5))  'M2_Name
            ArrayEir(WriteRow, 10) = Range(Cells(RowIndex, 6), Cells(RowIndex, 6))  'M2_Location
            ArrayEir(WriteRow, 11) = Range(Cells(RowIndex, 7), Cells(RowIndex, 7))  'M2_Easting
            ArrayEir(WriteRow, 12) = Range(Cells(RowIndex, 8), Cells(RowIndex, 8))  'M2_Northing
        ElseIf EirIndex = 3 Then
            ArrayEir(WriteRow, 13) = Range(Cells(RowIndex, 5), Cells(RowIndex, 5))  'M3_Name
            ArrayEir(WriteRow, 14) = Range(Cells(RowIndex, 6), Cells(RowIndex, 6))  'M3_Location
            ArrayEir(WriteRow, 15) = Range(Cells(RowIndex, 7), Cells(RowIndex, 7))  'M3_Easting
            ArrayEir(WriteRow, 16) = Range(Cells(RowIndex, 8), Cells(RowIndex, 8))  'M3_Northing
        End If

        Application.StatusBar = "Progress: " & RowIndex & " in " & LastRow & " of possibly " & MaxArraySize
        
        
        RowIndex = RowIndex   1
    Loop
    
        Application.StatusBar = False

Worksheets("Sheet2").Activate
Worksheets("Sheet2").Range(Cells(1, 1), Cells(UBound(ArrayEir, 1), UBound(ArrayEir, 2))).Value = ArrayEir

End Sub

標籤：擅长 文件

上一篇：僅在VBA中抓取網頁的特定部分

下一篇：如何在VBA中創建n個陣列