[pandas Tips] มาระเบิดคอลัมน์ที่ Value เป็นลิสต์ a,b,c ไปขึ้นบรรทัดใหม่กัน ! Let’s Explode a list to new row on pandas/BigQuery

by GleeGM · May 16, 2024

unnest explode pandas sql

มาระเบิดคอลัมน์กันด้วย Explode ใน Python! …แต่ในรูปมันปลาระเบิดนี่…
ภาพ: Crochet lesson – Federico Zandomeneghi

ในหลายครั้ง ข้อมูลที่เขาใส่เข้ามาให้ในเทเบิลก็มารูปแบบลิสต์ เช่นข้อมูลเกี่ยวกับแอปฟังเพลง คุณสมาชิก Mary
เค้าเขียนมาใน Profile เขาว่า เค้าชอบฟังเพลงสามประเภทได้แก่ Indie,Pop,House ซึ่งข้อมูลใน Database ก็เก็บมาเป็นลิสต์บรรทัดเดียวให้

ทีนี้ บ่อยครั้งที่เราต้องเอาไปใช้ต่อแล้วต้องการแตกลิสต์เพื่อให้ไปขึ้นบรรทัดใหม่แทนเพื่อการใช้งานที่สะดวกกว่า (เช่นเอาไป Join กับข้อมูลชุดอื่นๆ) จึงทำให้เราต้องการแตกลิสต์ไส้ในเหล่านี้ไปขึ้นบรรทัดใหม่โดยปริยาย

before after explode — หน้าตาผลลัพธ์ที่เราอยากได้

ว่าแต่ Function ที่จะมาช่วยให้ชีวิตเราดีขึ้นมันคืออะไรล่ะ ? โพสนี้จะพาไปดูทั้งบน pandas (Python) และ BigQuery กัน (ใจก็อยากเขียนแค่ pandas แต่บังเอิญว่าบน BigQuery ก็พอมีวิธีเหมือนกันเลยใส่แถมๆ มาด้วย ฮี่ฮี่ 55555) ป่ะ ลุย !!!!

สารบัญ

1. Python — pandas

เพื่อไม่ให้อธิบายเยอะ ดูรูปกับโค้ดไปแล้วแล้วกัน… (เอ๊า !)
เราจะใช้ .str.split() เพื่อแตกค่า String จาก “Indie,Pop,House” เป็น [Indie,Pop,House] บรรทัดนี้คอมพิวเตอร์มัน Object มองว่าเป็น List จริงๆละ ไม่ใช่เป็น String เหมือนก่อนหน้านี้ และค่อยจับใส่ .explode() ให้มันไปขึ้นคนละบรรทัดดั่งภาพด้านล่าง

Input

Example Code

import pandas as pd

# Create a sample dataframe
data = {
    'Name': ['Mary'],
    'Member_tier': ['Gold'],
    'Music_preference': ['Indie,Pop,House']
}

df = pd.DataFrame(data)

# Split the "Music_preference" column by comma and create a new row for each value

df['Music_preference'] = df['Music_preference'].str.split(',')
df = df.explode('Music_preference')

print(df)

Output

   Name  Member_tier Music_preference
0  mary   Gold        Indie
1  mary   Gold        Pop
2  mary   Gold        House

Ready-to-use Function for Excel/CSV file

กรณีมีไฟล์เป็น excel หรือ CSV แล้วอยากโยนเข้าไปเลย เรารวบมาเป็น Function ไว้ให้แล้ว !

import pandas as pd

working_df=pd.read_excel('file_to_use.xlsx') #ถ้าไฟล์เป็น CSV เปลี่ยนชื่อฟังก์ชั่นเป็น read_csv
def explode_column_with_export(df_tmp,column_to_explode:str,delim:str,export_file_name:str):
    df_res=df_tmp.copy()
    df_res[column_to_explode]=df_res[column_to_explode].str.split(delim)
    df_res= df_res.explode(column_to_explode)
    df_res.to_csv(export_file_name,index=False)
    return df_res
    
#Execute ! เปลี่ยนตัวแปรดังต่อไปนี้   
# 'column_to_explode' คือชื่อคอลัมน์ที่ต้องการระเบิด
# ',' คือตัวคั่นข้างในที่อยากให้ระเบิดออกคืออะไร (กรณีของเราคือ comma ,)
# 'output_file_name.csv' ชื่อไฟล์ที่จะเซฟออกมา
explode_column_with_export(working_df,'column name to explode',',','output_file_name.csv')

2. SQL — BigQuery

กรณีทำบน SQL เช่น BigQuery จะมีฟังก์ชั่น UNNEST เขียนกำชับไว้หลังสุด ทำให้ชีวิตง่ายขึ้นบ้าง ส่วน SQL ยี่ห้ออื่นจะมีวิธีเขียนที่ต่างออกไป (แถมเขียนยาววุ่นวายกว่าที่คิดซะด้วย ถ้าใครโหลดมาทำบน pandas ได้ก็อาจจะสบายใจกว่า ฮา 😅)

ตัวอย่าง

Example Code

with data as (select 'Mary' as Name, 'Gold' as Member_tier, 'Indie,Pop' as Music_preference)

select Name,Member_tier
, TRIM(val) AS Music_preference_explode from data, UNNEST(SPLIT(Music_preference,',')) as val

คำอธิบายโค้ด

วิธีอ่าน อ่านจากข้อ 1 → 2 → 3 ตามรูป

สมมุติว่า music_preference คือคอลัมน์ใน database ที่เราอยากระเบิด
โดยใช้ฟังก์ชั่น Unnest แล้วตั้งชื่อใหม่ว่า val: UNNEST(SPLIT(Music_preference,’,’)) as val
ทำการลบ space เผื่อเกินมาด้วย TRIM จากนั้นตั้งชื่อใหม่ว่า music_preference_explode