时间戳

首页 > 解决方案 > 数据帧交替数组

python - 数据帧交替数组

问题描述

我目前有一个包含以下信息的 excel 文件：

Company Initial     Purchase Number
ABCD            A123456789
ABCD            B123456789
BCDE            C123456789
BCDE            D123456789
BCDE            E123456789
CDEF            F123456789
DEFG            G123456789
DEFG            H123456789
DEFG            I123456789
DEFG            J123456789
DEFG            K123456789

我想把它变成一个表格，这样同一个公司的首字母就不会连续重复。

Company Initial     Purchase Number
DEFG            K123456789
ABCD            A123456789
DEFG            G123456789
ABCD            B123456789
DEFG            J123456789
BCDE            C123456789
DEFG            I123456789
BCDE            D123456789
DEFG            H123456789
BCDE            E123456789
CDEF            F123456789

我目前正在 Pandas 和 VBA 中尝试这个，但似乎无法围绕一个可行的解决方案。我也对其他 Python 库持开放态度。

谢谢。

标签： pythonexcelvbapandas

解决方案

借助内存中的 ListBoxes 的另一种可能性：

Sub Reshuffle()
Dim Arr As Variant, FreqArr As Variant, Place As Long, Comp1 As Variant, Comp2 As Variant
Dim rngArr As Range, i As Long, j As Long, k As Long, ListB1 As MSForms.ListBox, ListB2 As MSForms.ListBox

Set ListB1 = CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
Set ListB2 = CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")

 Set rngArr = Range("A2:B12")

    With ListB1
        .Column = Application.Transpose(rngArr)
         ListB2.List = .List
                For i = LBound(.List) To UBound(.List)
                    Arr = Application.Match(Application.Transpose(Application.Index(.List, 0, 1)), Application.Index(.List, 0, 1), 0)
                    FreqArr = Application.Frequency(Arr, Arr)
                        If Application.Max(FreqArr) > (UBound(.List) + 2) / 2 Then MsgBox "not possible"
                            For j = 1 To UBound(.List) + 1
                                Place = Application.Match(Application.Large(FreqArr, j), FreqArr, 0)
                                Comp2 = .List(Place - 1, 0)
                                    If Comp2 <> Comp1 Then Exit For
                            Next j
                                Comp1 = Comp2
                                    With ListB2
                                        For k = LBound(.List, 2) To UBound(.List, 2)
                                            .List(i, k) = ListB1.List(Place - 1, k)
                                        Next k
                                        ListB1.RemoveItem Place - 1
                                    End With
                Next i
      End With
rngArr.Value = ListB2.List 'replaces in same range

Set ListB1 = Nothing
Set ListB2 = Nothing

End Sub

推荐阅读