如果條件為假，則添加空行

鑒于以下資料框：

 Row|Address_StoreHours_Phone_RemoveHrTag|
 --- ------------------------------------ 
|  1|                Ph 148-01 Metro P...|
|  2|                Store Hours: 7:00...|
|  3|                <hr class="dashed"/>|
|  4|                Ground Floor Vict...|
|  5|                        632-82833778|
|  6|                        Store Hours:|
|  7|                <hr class="dashed"/>|
|  8|                Phase 1 Package 1...|
|  9|                        632-83722847|
| 10|                Store Hours: 7:00...|
| 11|                <hr class="dashed"/>|

如果 hr / 的行不能被 4 整除，我如何在 hr / 上方添加空行 1 行，例如

 Row|Address_StoreHours_Phone_RemoveHrTag|
 --- ------------------------------------ 
|  1|                Ph 148-01 Metro P...|
|  2|                                    |
|  3|                Store Hours: 7:00...|
|  4|                <hr class="dashed"/>|
|  5|                Ground Floor Vict...|
|  6|                        632-82833778|
|  7|                        Store Hours:|
|  8|                <hr class="dashed"/>|
|  9|                Phase 1 Package 1...|
| 10|                        632-83722847|
| 11|                Store Hours: 7:00...|
| 12|                <hr class="dashed"/>|

這是我到目前為止所做的，如果 hr / 在可被 4 整除的行中，hr_tag 列回傳 true

spark.sql("""
    WITH create_row AS(
        SELECT CAST(ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS INTEGER) AS Row, *
        FROM ssd_others
    ), filter_hr_tag AS (
        SELECT 
            *,
            CASE
                WHEN `Address_StoreHours_Phone_RemoveHrTag` = '<hr />' AND `Row` % 4 = 0
                THEN True
                ELSE False
            END AS hr_tag
        FROM create_row
    )
    SELECT * 
    FROM filter_hr_tag
""")

這是代碼的輸出

Row|Address_StoreHours_Phone_RemoveHrTag|hr_tag|
 --- ------------------------------------ ------ 
|  1|                Ph 148-01 Metro P...| false|
|  2|                Store Hours: 7:00...| false|
|  3|                <hr class="dashed"/>| false|
|  4|                Ground Floor Vict...| false|
|  5|                        632-82833778| false|
|  6|                        Store Hours:| false|
|  7|                <hr class="dashed"/>| false|
|  8|                Phase 1 Package 1...| false|
|  9|                        632-83722847| false|
| 10|                Store Hours: 7:00...| false|
| 11|                <hr class="dashed"/>| false|

uj5u.com熱心網友回復：

我不能用 spark 來做，所以我的解決方法就是使用 python 的串列

ssd_row = ssd_cleaning.select("Row").rdd.flatMap(lambda x: x).collect()
ssd_other_info = ssd_cleaning.select("Address_StoreHours_Phone_RemoveHrTag").rdd.flatMap(lambda x: x).collect()

ssd_list = [list(x) for x in zip(ssd_row, ssd_other_info)]

for row in ssd_list:
    if row[1] == '<hr />' and row[0] % 4 != 0:
        new_empty_row = [row[0] - 1.5,""]
        ssd_list.append(new_empty_row) 
        ssd_list.sort(key = lambda x: x[0])
        for index,x in enumerate(ssd_list):
            ssd_list[index][0] = index 1

reordered_data_distribution_cleaning_ssd = spark.createDataFrame(ssd_list)

uj5u.com熱心網友回復：

這可以通過

1. 確定何時必須添加空行。
2. 如果對于每一行，后續行都包含add_empty_row，則轉換Address_StoreHours_Phone_RemoveHrTag為空字串陣列，然后轉換為Address_StoreHours_Phone_RemoveHrTag.
3. 最后分解步驟 2 中的列并重新計算行號。

視窗功能無需partitionBy將所有資料移動到單個磁區中，從而降低性能。我假設鑒于您在相關代碼片段中的使用，這是可以接受的。

data = [(1, 'Ph 148-01 Metro P...'),
        (2, 'Store Hours: 7:00...'),
        (3, '<hr />',),
        (4, 'Ground Floor Vict...'),
        (5, ' 632-82833778",',),
        (6, 'Store Hours:'),
        (7, '<hr />',),
        (8, 'Phase 1 Package 1...'),
        (9, ' 632-83722847",',),
        (10, 'Store Hours: 7:00...'),
        (11, '<hr />',), ]

df = spark.createDataFrame(data, ("Row", "Address_StoreHours_Phone_RemoveHrTag", ))

df.createOrReplaceTempView("create_row")

query = """
SELECT ROW_NUMBER() OVER(ORDER BY `row`, length(`address_storehours_phone_removehrtag`)) as `row`,
       EXPLODE(CASE
                   WHEN add_empty_row THEN array("", `address_storehours_phone_removehrtag`)
                   ELSE array(`address_storehours_phone_removehrtag`)
               END) AS `address_storehours_phone_removehrtag`
FROM
  (SELECT `row`,
          `address_storehours_phone_removehrtag`,
          LEAD(add_empty_row, 1, FALSE) OVER (
                                              ORDER BY `row`) AS add_empty_row
   FROM
     (SELECT *,
             CASE
                 WHEN `address_storehours_phone_removehrtag` = '<hr />'
                      AND `row` % 4 != 0 AND `row` % 4 = `row` THEN TRUE
                 ELSE FALSE
             END AS add_empty_row
      FROM create_row) t) w
"""


spark.sql(query).show()


"""
 --- ------------------------------------ 
|row|address_storehours_phone_removehrtag|
 --- ------------------------------------ 
|  1|                Ph 148-01 Metro P...|
|  2|                                    |
|  3|                Store Hours: 7:00...|
|  4|                <hr />|
|  5|                Ground Floor Vict...|
|  6|                      632-82833778",|
|  7|                        Store Hours:|
|  8|                <hr />|
|  9|                Phase 1 Package 1...|
| 10|                      632-83722847",|
| 11|                Store Hours: 7:00...|
| 12|                <hr />|
 --- ------------------------------------ 
"""

標籤：sql 阿帕奇火花 火花

上一篇：如何獲得合計占總數的百分比

下一篇：M2M關系PK查詢