#python #sql #pandas #merge
#python #sql #pandas #слияние
Вопрос:
У меня есть два фрейма данных:
-
Во-первых, у меня есть информация о студенте. Я буду называть это df1
user_id | plan | subplan | matrix_code | student_semester 102532 | GADMSSP | GSP10 | 1501 | 8 106040 | GRINTSP | | 1901 | 4 106114 | GCSOSSULA | | 1901 | 4 106504 | GCSOSSP | | 1902 | 3 106664 | GCINESP | | 1901 | 4 -
Во-вторых, у меня есть требования к факультативам для учебного заведения. Я буду называть это df2.
plan | subplan | matrix_code | semester | credits| cumulative_credits GADMSSP | | 1501 | 5 | 4 | 4 GADMSSP | | 1501 | 6 | 4 | 8 GADMSSP | | 1501 | 7 | 4 | 12 GADMSSP | | 1501 | 8 | 0 | 12 GRINTSP | | 1901 | 7 | 2 | 2 GRINTSP | | 1901 | 8 | 0 | 2 GCSOSSULA | | 1901 | 3 | 4 | 4 GCSOSSULA | | 1901 | 4 | 0 | 4 GCSOSSULA | | 1901 | 5 | 0 | 4 GCSOSSULA | GSUL5 | 1901 | 5 | 4 | 8 GCSOSSULA | | 1901 | 6 | 0 | 4 GCSOSSULA | GSUL5 | 1901 | 6 | 0 | 8 GCSOSSULA | | 1901 | 7 | 0 | 4 GCSOSSULA | GSUL5 | 1901 | 7 | 0 | 8 GCSOSSULA | | 1901 | 8 | 0 | 4 GCSOSSULA | GSUL5 | 1901 | 8 | 0 | 8 GCSOSSP | | 1902 | 5 | 4 | 4 GCSOSSP | | 1902 | 6 | 4 | 8 GCSOSSP | | 1902 | 7 | 4 | 12 GCSOSSP | | 1902 | 8 | 0 | 12 GCINESP | | 1901 | 2 | 4 | 4 GCINESP | | 1901 | 3 | 4 | 8 GCINESP | | 1901 | 4 | 4 | 12 GCINESP | | 1901 | 5 | 4 | 16 GCINESP | | 1901 | 6 | 4 | 24 GCINESP | | 1901 | 7 | 4 | 32 GCINESP | | 1901 | 8 | 4 | 40
Итак, я должен объединить df с учетом некоторых условий:
-
planиmatrix_codeдолжны быть одинаковыми для df1 и df2. -
df1.subplanлибо совпадает сdf2.subplan, либо может быть нулевым. Таким образом, идентификатор пользователя 102532 в строке 1 df1 получит значениеdf2.subplannull, поскольку нет указания на конкретныеsubplanтребования для этого плана иmatrix_code. -
Получаем
student_semester 1, но рассматриваем maxdf2.semesterкак пределstudent_semester. Таким образом, идентификатор пользователя 102532 в строке 1 должен оставаться в семестре 8. Этому я не могу добавить 1 семестр, но я хотел бы указать, что это пользователь, который не достиг требований в прошлом семестре. -
Меня интересуют только cumulative_credits.
Для этих двух dfs результат должен быть примерно таким:
user_id | plan | subplan | matrix_code | semester | student_semester | cumulative_credits
102532 | GADMSSP | GSP10 | 1501 | 8 | 9 | 12
106040 | GRINTSP | | 1901 | 5 | 4 | 0
106114 | GCSOSSULA | | 1901 | 5 | 4 | 4
106504 | GCSOSSP | | 1902 | 4 | 3 | 0
106664 | GCINESP | | 1901 | 5 | 4 | 16
Но если нет возможного способа получить студентов с 0 cumulative_credits, результат должен быть:
user_id | plan | subplan | matrix_code | semester | student_semester | cumulative_credits
102532 | GADMSSP | GSP10 | 1501 | 8 | 9 | 12
106114 | GCSOSSULA | | 1901 | 5 | 4 | 4
106664 | GCINESP | | 1901 | 5 | 4 | 16
Что я делал до сих пор, так это следующее:
pip install -U pandasql
import pandas as pd
pysqldf = lambda q: sqldf(q, globals())
df2 = df2.groupby(['plan', 'subplan', 'matrix_code', 'semester']).cumulative_credits.max()
df2 = df2.to_frame()
df2 = df2.reset_index()
electives = """
SELECT user_id
,a.plan
,a.subplan as "student_subplan"
,a.matrix_code
,a.student_semester
,b.subplan as "matrix_subplan"
,b.semester
,cumulative_credits
FROM df1 a
LEFT JOIN df2 b
ON a.plan = b.plan
AND a.matrix_code = b.matrix_code
WHERE (b.subplan = '' OR a.subplan = b.subplan)
"""
electives = pysqldf(electives)
Затем я пытался получить 3-е условие, но я понятия не имею, как правильно это сделать. Я думаю, что мог бы использовать лямбда, но я не уверен, как.
df_s['semester_x'] = df_s['student_semester'] 1 | df_s['student_semester'] == df_s['semester'].max()
Кроме того, если есть лучший способ выполнить предыдущие шаги условий, используя слияние с условием, это могло бы быть неплохо.
РЕДАКТИРОВАТЬ — РЕШЕНИЕ:
Я использовал часть решения Parfait. Я только что создал условную логику, чтобы получить совокупные кредиты студента в следующем семестре вместо максимальных совокупных кредитов матричного кода.
Вот что я сделал:
Первая часть — решение Parfait:
agg = (pd.merge(df1, df2, on=['plano', 'matriz'], suffixes=["", "_"])
.fillna('')
.query("(subplano == '') | (subplano_aluno == subplano)")
.rename({'subplano':'subplano_matriz', 'semestre_': 'semestre_matriz', 'semestre': 'semestre_aluno'}, axis='columns')
Вторая часть:
y = """
with a as
(
SELECT DISTINCT plan
,CASE
WHEN plan LIKE '%SULB%' OR plano LIKE '%SULC%' THEN 10
WHEN plan LIKE '%SULD%' OR plano LIKE '%SULE%' THEN 12
ELSE 8
END as "semester_max"
FROM agg
)
SELECT DISTINCT
user_id
,student_semester
,plan
,student_subplan
,matrix_code
,matrix_subplan
,cumulative_credits
,matrix_semester
,semester_max
,CASE
WHEN student_semester < semester_max THEN (student_semester) 1
WHEN student_semester = semester_max THEN student_semester
END as "next_semester"
FROM
(
SELECT DISTINCT
user_id
,student_semester
,b.plan
,student_subplan
,matrix_code
,matrix_subplan
,cumulative_credits
,matrix_semester
,semester_max
FROM agg b
INNER JOIN a ON b.plano = a.plano
) x
WHERE matrix_semester = next_semester
"""
z = pysqldf(x)
Ответ №1:
Рассмотрите возможность добавления CASE инструкции в SQL-запрос:
SELECT d1.user_id
, d1.plan
, d1.subplan AS student_subplan
, d1.matrix_code
, d1.student_semester
, d2.subplan AS matrix_subplan
, CASE
WHEN d1.student_semester = MAX(d2.semester)
THEN d1.student_semester
ELSE d1.student_semester 1
END AS semester
, MAX(d2.cumulative_credits) AS cumulative_credits
FROM df1 d1
LEFT JOIN df2 d2
ON d1.plan = d2.plan
AND d1.matrix_code = d2.matrix_code
WHERE (d2.subplan IS NULL OR d1.subplan = d2.subplan)
GROUP BY d1.user_id
, d1.plan
, d1.subplan
, d1.matrix_code
, d1.student_semester
, d2.subplan;
В Pandas перевод будет использовать merge groupby Series.where для условной логики регистра:
# MERGE
agg = (pd.merge(df1, df2, on=['plan', 'matrix_code'], suffixes=["", "_"])
.fillna('')
.query("(subplan_ == '') | (subplan == subplan_)")
.rename({'subplan':'student_subplan', 'subplan_':'matrix_subplan'}, axis='columns')
)
# AGGRGEATION
agg = (agg.groupby(['user_id', 'plan', 'student_subplan', 'matrix_code',
'student_semester', 'matrix_subplan'], as_index=False)
.agg({'semester':'max', 'cumulative_credits':'max'})
)
# CONDITIONAL LOGIC
agg['semester'] = agg['student_semester'].where(agg['semester'] == agg['student_semester'],
agg['student_semester'].add(1))
agg
# user_id plan student_subplan matrix_code student_semester matrix_subplan semester cumulative_credits
# 0 102532 GADMSSP GSP10 1501 8 8 12
# 1 106040 GRINTSP 1901 4 5 2
# 2 106114 GCSOSSULA 1901 4 5 4
# 3 106504 GCSOSSP 1902 3 4 12
# 4 106664 GCINESP 1901 4 5 40
Комментарии:
1. Единственное, что не сработало, это cumulative_credits. Мне нужно получить cumulative_credits до student_semester 1, чтобы узнать лимит кредитов, которые у них есть на этот семестр
2. Будет ли этот запрос работать там, где вы присоединитесь снова? Однако один пользователь появляется дважды.
3. (Давайте удалим предыдущие комментарии выше) Для этой дублирующей записи оба имеют одинаковый план / matrix_code / semestr 1, но разные совокупные кредиты. Ваши желаемые результаты требуют меньшего совокупного значения кредитов. Не уверен, является ли это проблемой курса pandas / sql или реальной рабочей проблемой, но нужно подумать о ваших общих потребностях. Только вы определяете правильный вывод. Попробуйте поиграть с запросом. Может быть, очистить исходные данные от таких дублирующих совпадений?
4. Что мне нужно, так это совокупные кредиты студента в следующем семестре (так что, если он сейчас в 6-м классе, я хочу получить требования к накопительным кредитам в 7-м семестре. Но если он сейчас на последнем семестре, я должен отследить, выполнил ли он все необходимые зачеты (поэтому, если он сейчас на 8-м, я должен перечислить требования к 8-му семестру, чтобы проверить, выполнил ли он это или нет)
5. Рассмотрим второе объединение и агрегацию с обновленным запросом .