Кестелерді біріктіру: екі дереккөзді кілт бойынша жалғау

Не үшін керек

Дерек бір кестеде жатуы сирек кездеседі. Банк ел кодтарын береді, атаулар анықтамада тұрады. Сатылым бір файлда, баға — басқасында. Тапсырыс бөлек, тұтынушы бөлек.

28-сабақ дәл осы қарызбен аяқталды: атауларымыз болмағандықтан, есеп KAZ, UZB, RUS кодтарымен шықты. Бүгін оларды қасына қоямыз — әрі біріктірудің нені бүлдіре алатынын да көреміз.

Жиырма екінші сабақтағы JOIN есіңізде болса — бұл сол, тек жадтағы кестелердің үстінде. Тұзақтары да сол.

Бірден тұтас

sklejka.py файлы. Сол жақта кодтар бойынша инфляция, оң жақта анықтама — әдейі толық емес әрі артық коды бар.

"""29-сабақ: екі кестені кілт бойынша біріктіру.

Сол жақта — ел кодтары бойынша инфляция, оң жақта — атаулары бар анықтама. Кілт
біреу, ал жұбы табылмаған жолдарға не болатыны қалай біріктіргеніңізге
байланысты.
"""

import pandas as pd

# Инфляция, жылына %. Дүниежүзілік банктің дерегі, ондыққа дейін дөңгелектелген.
data = pd.DataFrame(
    [("KAZ", 2023, 14.5), ("KAZ", 2024, 8.7),
     ("UZB", 2023, 10.0), ("UZB", 2024, 9.6),
     ("RUS", 2023, 5.9), ("RUS", 2024, 8.4)],
    columns=["code", "year", "value"],
)
# Анықтама: атауы бар кодтар аз, оның есесіне артық код бар.
names = pd.DataFrame(
    [("KAZ", "Қазақстан", "Орталық Азия"),
     ("UZB", "Өзбекстан", "Орталық Азия"),
     ("KGZ", "Қырғызстан", "Орталық Азия")],
    columns=["code", "name", "region"],
)
print("сол жақта жол:", len(data), "| оң жақта жол:", len(names))

print()
print("== inner: екі жақтан да табылғаны ғана қалады")
inner = data.merge(names, on="code")
print(inner)
print("жол саны:", len(inner), "— жоғалтқанымыз:", len(data) - len(inner))

print()
print("== left: сол жақ тұтас қалады, оң жақта NaN пайда болады")
left = data.merge(names, on="code", how="left")
print(left.tail(3))
print("атауы жоқ жол:", int(left["name"].isna().sum()))

print()
print("== indicator әр жолдың қайдан келгенін көрсетеді")
both = data.merge(names, on="code", how="outer", indicator=True)
print(both["_merge"].value_counts().to_dict())

print()
print("== аттары әртүрлі кілттер")
other = names.rename(columns={"code": "iso"})
print(data.merge(other, left_on="code", right_on="iso").columns.tolist())

print()
print("== белгі бойынша: join")
a = data.set_index("code")
b = names.set_index("code")
print(a.join(b, how="left").head(2))

print()
print("== кілттегі қайталау жолдарды көбейтеді")
twice = pd.concat([names, names.iloc[[0]]], ignore_index=True)
print("анықтамада жол:", len(twice), "| біріктіргеннен кейін:", len(data.merge(twice, on="code")))
try:
    data.merge(twice, on="code", validate="many_to_one")
except pd.errors.MergeError as err:
    print("validate ұстады:", str(err).split("\n")[0])

Шығысы:

сол жақта жол: 6 | оң жақта жол: 3

== inner: екі жақтан да табылғаны ғана қалады
  code  year  value       name        region
0  KAZ  2023   14.5  Қазақстан  Орталық Азия
1  KAZ  2024    8.7  Қазақстан  Орталық Азия
2  UZB  2023   10.0  Өзбекстан  Орталық Азия
3  UZB  2024    9.6  Өзбекстан  Орталық Азия
жол саны: 4 — жоғалтқанымыз: 2

== left: сол жақ тұтас қалады, оң жақта NaN пайда болады
  code  year  value       name        region
3  UZB  2024    9.6  Өзбекстан  Орталық Азия
4  RUS  2023    5.9        NaN           NaN
5  RUS  2024    8.4        NaN           NaN
атауы жоқ жол: 2

== indicator әр жолдың қайдан келгенін көрсетеді
{'both': 4, 'left_only': 2, 'right_only': 1}

== аттары әртүрлі кілттер
['code', 'year', 'value', 'iso', 'name', 'region']

== белгі бойынша: join
      year  value       name        region
code                                      
KAZ   2023   14.5  Қазақстан  Орталық Азия
KAZ   2024    8.7  Қазақстан  Орталық Азия

== кілттегі қайталау жолдарды көбейтеді
анықтамада жол: 4 | біріктіргеннен кейін: 6
validate ұстады: Merge keys are not unique in right dataset; not a many-to-one merge

Талдау

Әдепкісі — inner, әрі бұл сабақтың ең қымбат жолы

data.merge(names, on="code") кілті екі жақтан да табылған жолдарды ғана қалдырады. Мысалда алты жолдан төртеуі қалды: анықтамада Ресей жоқ, әрі оның екі жолы да жоғалды. Қате де, ескерту де жоқ.

Кестелермен жұмыстағы ең жиі кездесетін үнсіз дерек жоғалуы — осы. Есеп сенімді көрінеді, сомалар шындағыдан аз, ал мұны әдейі тексермесеңіз көрмейсіз.

Бір ғана ұзындық бұған жетпейді. Бір біріктіруде бір жолдарды жоғалтып, екіншілерін көбейтуге болады, әрі ұзындық сол күйінде қалады. Сол жақтағы кілттер A мен B, анықтамада A, A және C: inner B-ны тастайды, A-ны екі еселейді — әрі сол екі жолды қалдырады. Ұзындық сәйкес келді, ал кесте басқа болып шықты.

Сондықтан тексеру төрт нәрседен құралады, әрі біріншісі бәрінен арзан:

• validate= — қандай байланыс күтетініңізді айту: «дерек пен анықтама» үшін "many_to_one", сондай-ақ "one_to_one", "one_to_many". Сәйкес келмесе — қате болған жерде MergeError;
• how="outer" бар indicator=True — шын біріктіруге дейінгі үш сан: екі жақтан да табылғаны қанша, сол жақта және оң жақта жұпсыз қалғаны қанша;
• кілттің қайталанбауы — анықтама болуға тиіс жақ үшін names["code"].duplicated().sum();
• ұзындық пен бақылау сомасы — len бұрын және кейін, қасында есепте көрсететін бағанның сомасы: ол өссе, жолдар көбейген.

left: дерек шешеді, анықтама толықтырады

how="left" сол жақтағы кестенің барлық жолын сақтайды; жұбы табылмаған жерде оң жақтағы бағандарда NaN тұрады. Сол жақта дерек, оң жақта анықтама тұрғанда бұл — қалыпты таңдау: қандай жолдардың бар екенін дерек шешеді, анықтама оларға сөз ғана қосады.

Тағы how="right" (айнадағыдай) және how="outer" — екі жақтағының бәрін қалдыру — бар. outer есеп үшін емес, тексеру үшін пайдалы: indicator=True-мен бірге ол екі жақтан да табылған жолдың, сол жақта жұпсыз қалғанның және оң жақта жұпсыз қалғанның санын көрсетеді.

{'both': 4, 'left_only': 2, 'right_only': 1}

Шын мәнінде біріктірер алдында қарауға тұрарлық үш сан. left_only — анықтамада жоғы; right_only — деректе жоғы.

Кілт әртүрлі аталғанда

left_on="code", right_on="iso" — аттары әртүрлі бағандар бойынша біріктіреді. Жауапта екі баған да қалады, қайталанғанын өзіңіз алып тастайсыз.

Кілт баған емес, жол белгісі болса, join істейді: a.join(b) индекс бойынша біріктіреді. Бұл сол merge(left_index=True, right_index=True), тек қысқасы; жиырма бесінші сабақта қатарлар арифметикасы да белгі бойынша сәйкестендірілетінін көрдік — join сол туралы, бірақ кестелер үшін.

Екі кестедегі аттас бағандар қақтығыспайды: оларға _x пен _y жалғанады. Жалғауды өзіңіз бергеніңіз жақсы — suffixes=("_дерек", "_анықтама"), — әйтпесе бір айдан кейін қайсысы қайсы екенін ешкім есіне түсірмейді.

Кілттегі қайталау жолдарды көбейтеді

Екінші тұзақ, біріншісінің айнадағы көрінісі. Анықтамада код екі рет кездессе, сол кодты деректің әр жолы екі еселенеді. Жол саны бұрынғыдан көп болады, сомалар өседі, әрі тағы да бірде-бір ескерту жоқ.

Жаттығуда бұл сандармен көрінеді: 300 сомасы 400-ге айналады — бірдеңе қосылғандықтан емес, бір жол екі рет саналғандықтан.

Емі — validate:

• validate="one_to_one" — кілт екі жақта да қайталанбайды;
• validate="many_to_one" — сол жақта қайталануы мүмкін, оң жақта жоқ (ең жиі жағдай: дерек пен анықтама);
• validate="one_to_many" — керісінше.

Сәйкес келмесе — үнсіз көбейтудің орнына MergeError. Бұл — әрқашан жазуға тұрарлық тексерулердің бірі: ол ештеңеге тұрмайды әрі қатені болған жерінде ұстайды.

merge мен concat — әртүрлі нәрсе

merge кілт бойынша біріктіріп, баған қосады. concat кестелерді бірінің астына бірін (немесе қатар) қояды, ешқандай кілт сұрамай: он екі айлық файлды бір қатарға осылай жинайды. «Кестелерді біріктіру» дегенді іздесеңіз, алдымен нақты не керегін шешіп алыңыз: көбірек баған ба, әлде көбірек жол ба.

Сабақ картасы

Өз сөзіңізбен айтыңыз

Қарамай, дауыстап немесе қағазға жауап беріңіз. Жауаптар — сабақтың соңында.

1. inner-де және left-те жұбы табылмаған жолдарға не болады?
2. Әр жақта қанша жол жұпсыз қалғанын бір жолмен қалай білуге болады?
3. Біріктіруден кейін жол саны неге бұрынғыдан көп болып кетуі мүмкін?

Жаттығу

Тапсырмаға дейінгі үш қысқа қадам: болжау, толықтыру, түзету. Жауаптар — сабақтың соңында, бірақ алдымен өзіңіз жауап беріңіз.

1. Болжаңыз. Бұл бағдарлама не басып шығарады?

import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Қазақстан", "Өзбекстан"]})
joined = left.merge(right, on="code")
print(len(left), "→", len(joined))
print(joined.to_string(index=False))

2. Бос орынды толтырыңыз. ... орнына жол саны үшеу болып, Ресейде атаудың орнына жетіспейтін мән тұратындай етіңіз.

# сол жақта — дерек, ол жоғалмауға тиіс
import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Қазақстан", "Өзбекстан"]})
joined = left.merge(right, on="code", ...)
print(len(joined), "| атауы жоқ:", int(joined["name"].isna().sum()))

3. Түзетіңіз. Жол екеу еді, үшеу болды, ал сома үш жүзден төрт жүзге өсті. Ештеңе қосқан жоқпыз.

# анықтамада код екі рет кездеседі
import pandas as pd

data = pd.DataFrame({"code": ["KAZ", "UZB"], "value": [100, 200]})
names = pd.DataFrame({"code": ["KAZ", "KAZ", "UZB"], "name": ["Қазақстан", "Қазақстан", "Өзбекстан"]})
joined = data.merge(names, on="code")
print("жол:", len(joined), "| сома:", int(joined["value"].sum()))

Тапсырма

Міндетті. Төрт код бойынша сегіз жол инфляция және төрт елге арналған анықтама берілген — бірақ олар дәл сол елдер емес. Деректің бірде-бір жолы жоғалмайтындай біріктіріп, мыналарды басып шығарыңыз:

1. біріктіруге дейін жол саны қанша еді және кейін қанша болды, әрі атауы табылмаған кодтар;
2. елдер бойынша орташа инфляция — кодтың қасында атауымен;
3. өңірлер бойынша орташа, тек өңірі белгілі жолдарды ғана есептеп.

Күтілетін шығыс:

жол болатын: 8 | болды: 8
атауы жоқ: ['RUS']

елдер бойынша орташа инфляция:
code       name  value
 KAZ  Қазақстан  11.60
 KGZ Қырғызстан   8.55
 RUS        NaN   7.15
 UZB  Өзбекстан   9.80

өңірлер бойынша:
              ел  орташа
region                  
Орталық Азия   3    9.98

Дайын болғаны: шығыс жол-жолымен сәйкес келеді; біріктіру тек ұзындықты салыстырумен емес, validate арқылы тексерілген; біріктіруге дейінгі және кейінгі жол саны бірдей; өңірі жоқ жолдар өңірлер бойынша топтауға кірмеген әрі басқа өңірге жазылмаған.

Өз деректеріңізде. Ортақ кілті бар екі кестеңізді алыңыз — коды не идентификаторы бар кез келгені. Алдымен merge(..., how="outer", indicator=True) арқылы қарап, үш санды есептеңіз. Содан кейін тапсырмаңызға керегінше біріктіріп, ұзындықты бұрынғы және кейінгі күйінде салыстырыңыз.

Қалауыңыз бойынша.

• Кілті қайталанатын деректе validate="one_to_one" қойып біріктіріп, хабарламаны тұтас оқып шығыңыз.
• Сол кестелерді индекс бойынша join арқылы біріктіріп, не шыққанын салыстырыңыз.
• Екі кестені concat арқылы қосып, мұның merge-тен несімен өзгеше екенін өзіңізге түсіндіріңіз.

Жобада бұл қайда тұрады

Оныншы қадам: есеп кодтармен сөйлеуді қояды. sholu/anyqtama.py — үш бағаннан тұратын кесте (код, атауы, өңір), әрі есеп сонымен код бойынша біріктіріледі.

Ондағы екі шешімді кодтан қарап шыққан жөн. Біріктіру — сол жақты: қандай жолдардың бар екенін дерек шешеді, анықтама сөз ғана қосады, ал анықтамада жоқ ел есепте өз кодымен қалады. Әрі біріктіру тексерілген: validate="one_to_one", өйткені анықтамадағы қайталанған код жолдарды көбейтер еді, ал қорытындылар өздігінен өсіп кетер еді.

Қарыздар. Анықтамамыз әдейі толық емес: Ресей онда жоқ, әрі есепте ол өңірі бос код болып қалды. Бұл — дұрыс мінез, бірақ есептегі бос ұяшық — біреу жауап беруге тиіс сұрақ; ондай бостықты немен толтыруды лас дерек туралы келесі сабақ талдайды.

Жауаптар

3 → 2
code  value      name
 KAZ    8.7 Қазақстан
 UZB    9.6 Өзбекстан

import pandas as pd

left = pd.DataFrame({"code": ["KAZ", "UZB", "RUS"], "value": [8.7, 9.6, 8.4]})
right = pd.DataFrame({"code": ["KAZ", "UZB"], "name": ["Қазақстан", "Өзбекстан"]})
joined = left.merge(right, on="code", how="left")
print(len(joined), "| атауы жоқ:", int(joined["name"].isna().sum()))

3 | атауы жоқ: 1

import pandas as pd

data = pd.DataFrame({"code": ["KAZ", "UZB"], "value": [100, 200]})
names = pd.DataFrame({"code": ["KAZ", "KAZ", "UZB"], "name": ["Қазақстан", "Қазақстан", "Өзбекстан"]})
joined = data.merge(names.drop_duplicates("code"), on="code", validate="one_to_one")
print("жол:", len(joined), "| сома:", int(joined["value"].sum()))

жол: 2 | сома: 300

Дереккөздер

• Кестелерді біріктіру және жалғау — merge, join, concat және олардың бүкіл параметрі.
• validate параметрі — жолдардың көбейіп кетуін ұстайтын тексеру.
• Тұтыну бағасының инфляциясы, Дүниежүзілік банк — осы сабақтағы сандардың дереккөзі.