Объедините 2 таблицы и при необходимости создайте новые строки
У меня есть две таблицы с общим столбцом. Я хочу объединить обе таблицы, но общий столбец не имеет одинаковых точных значений в обеих таблицах.
Таблица 1:
Rate | Age
0-4 | <6.0
5-6 | 6.0
7-8 | 6.3
9-10 | <6.6
11-12 | 6.9
13-14 | 7.0
15-16 | 7.3
17-18 | 7.6
19-20 | 7.9
21 | 8.0
22-23 | 8.3
24-25 | 8.6
26 | 8.9
27 | 9.0
28 | 9.3
29 | 9.6
30 | 9.9
31 | 10.0
32 | 10.3
33 | 10.6
34 | 10.9
35 | 11.0
36 | 11.3
37 | 11.6
38 | 11.9
39 | 12.0
40 | 12.3
41 | 12.6
42 | 12.9
43 | 13.0
44 | 13.3
45 | 13.6
46 | 13.9
47 | 14.6
48 | 15.0
49 | 15.3
50 | 15.6
51 | 16.0
52 | 16.6
53 | 17.0
54 | 17.3
55 | 17.6
56 | 18.0
57-80 | >18.0
Таблица 2:
Rate | Grade
0-7 | <1.0
7-8 | 1
9-11 | 1.2
12-13 | 1.4
14-16 | 1.7
17-18 | 2
19-20 | 2.2
21 | 2.4
22-23 | 2.7
24-25 | 3
26 | 3.2
27 | 3.4
28 | 3.7
29 | 4
30 | 4.2
31 | 4.4
32 | 4.7
33-34 | 5
35 | 5.2
36 | 5.4
37 | 5.7
38 | 6
39 | 6.2
40 | 6.4
41 | 6.7
42 | 7
43 | 7.4
44 | 7.7
45 | 8
46 | 8.2
47 | 8.4
48 | 9
49 | 9.2
50 | 9.4
51 | 10.2
52 | 10.4
53 | 11
54 | 11.4
55 | 12
56 | 12.4
57-80 | 13
Вновь созданная таблица будет иметь 3 столбца (Ставка, Возраст, Оценка).
Я попробовал следующий скрипт:
import pandas as pd
# Read tables into DataFrames
table1 = pd.read_excel('table1.xlsx')
table2 = pd.read_excel('table2.xlsx')
# Merge tables on 'Rate' column with outer join
merged_table = pd.merge(table1, table2, on='Rate', how='outer')
# Print or save merged table
merged_table.to_excel('merged.xlsx')
Вывод этого скрипта не соответствует ожиданиям:
Rate Age Grade
0 21 8 2.4
1 26 8.9 3.2
2 27 9 3.4
3 28 9.3 3.7
4 29 9.6 4
5 30 9.9 4.2
6 31 10 4.4
7 32 10.3 4.7
8 33 10.6
9 34 10.9
10 35 11 5.2
11 36 11.3 5.4
12 37 11.6 5.7
13 38 11.9 6
14 39 12 6.2
15 40 12.3 6.4
16 41 12.6 6.7
17 42 12.9 7
18 43 13 7.4
19 44 13.3 7.7
20 45 13.6 8
21 46 13.9 8.2
22 47 14.6 8.4
23 48 15 9
24 49 15.3 9.2
25 50 15.6 9.4
26 51 16 10.2
27 52 16.6 10.4
28 53 17 11
29 54 17.3 11.4
30 55 17.6 12
31 56 18 12.4
32 0-4 <6.0
33 0-7 <1.0
34 11-12 6.9
35 12-13 1.4
36 13-14 7
37 14-16 1.7
38 15-16 7.3
39 17-18 7.6 2
40 19-20 7.9 2.2
41 22-23 8.3 2.7
42 24-25 8.6 3
43 33-34 5
44 5-6 6
45 56-80 >18.0 13
46 7-8 6.3 1
47 9-10 6.6
48 9-11 1.2
49 14 7.2
50 14 8.7
51 14 9.7
52 14 10
53 14 10.7
54 14 11.2
55 14 11.7
56 14 12.2
57 14 12.7
58 14.3 7.2
59 14.3 8.7
60 14.3 9.7
61 14.3 10
62 14.3 10.7
63 14.3 11.2
64 14.3 11.7
65 14.3 12.2
66 14.3 12.7
67 14.9 7.2
68 14.9 8.7
69 14.9 9.7
70 14.9 10
71 14.9 10.7
72 14.9 11.2
73 14.9 11.7
74 14.9 12.2
75 14.9 12.7
76 15.9 7.2
77 15.9 8.7
78 15.9 9.7
79 15.9 10
80 15.9 10.7
81 15.9 11.2
82 15.9 11.7
83 15.9 12.2
84 15.9 12.7
85 16.3 7.2
86 16.3 8.7
87 16.3 9.7
88 16.3 10
89 16.3 10.7
90 16.3 11.2
91 16.3 11.7
92 16.3 12.2
93 16.3 12.7
94 16.9 7.2
95 16.9 8.7
96 16.9 9.7
97 16.9 10
98 16.9 10.7
99 16.9 11.2
100 16.9 11.7
101 16.9 12.2
102 16.9 12.7
103 17.9 7.2
104 17.9 8.7
105 17.9 9.7
106 17.9 10
107 17.9 10.7
108 17.9 11.2
109 17.9 11.7
110 17.9 12.2
111 17.9 12.7
Желаемый результат:
Rate | Age | Grade
0-4 | <6.0 | <1.0
5-6 | 6.0 | <1.0
7-8 | 6.3 | 1
.. | .. | ..
Пример для тестирования:
import pandas as pd
table1 = pd.DataFrame({'Rate': ['0-4', '5-6', '7-8', '9-10', '11-12', '13-14'],
'Age': ['<6.0', '6.0', '6.3', '6.6', '6.9', '7.0']})
table2 = pd.DataFrame({'Rate': ['0-7', '7-8', '9-11', '12-13', '14-16', '17-18'],
'Grade': ['<1.0', '1', '1.2', '1.4', '1.7', '2']})
merged_table = pd.merge(table1, table2, on='Rate', how='outer')
merged_table.sort_values('Rate').to_excel('merged1.xlsx')
Вывод из этого примера:
Rate Age Grade
0 0-4 <6.0
1 0-7 <1.0
2 11-12 6.9
3 12-13 1.4
4 13-14 7.0
5 14-16 1.7
6 17-18 2
7 5-6 6.0
8 7-8 6.3 1
9 9-10 6.6
10 9-11 1.2
Желаемый результат этого примера:
Rate Age Grade
0 0-4 <6.0 <1.0
1 5-6 6.0 <1.0
2 7-8 6.3 1
9 9-10 6.6 1.2
2 11-12 6.9 1.4
4 13-14 7.0 1.7
5 15-16 7.3 1.7
6 17-18 7.6 2
Таким образом, каждая строка содержит значения для трех столбцов.
Возможно ли это с пандами или другими модулями/библиотеками?
Почему вы поставили how='inner' в пример кода, который должен выполнять внешнее соединение, вместо how='outer'? Вы понимаете, что на самом деле означает этот параметр?
8-7 и 17-18 здесь отсутствуют во входных данных, поэтому у нас нет возможности узнать, что не так. Пожалуйста, сделайте минимально воспроизводимый пример . Подробности см. в разделе Как сделать хорошие воспроизводимые примеры панд. В процессе вы можете обнаружить проблему сами :)
Чтобы внести ясность, внешнее соединение Rate кажется именно тем, что вам нужно, поэтому неясно, в чем проблема. В этой связи: если вам все еще нужна помощь после создания MRE, напишите более конкретное название; советы по этому поводу см. в разделе Как спрашивать.
@wjandrea Я уже упоминал, что пробовал и how='outer', и how='inner'. Оба не сработали так, как ожидалось. При необходимости могу загрузить 2 файла для тестирования. Я добавил несколько строк из обоих файлов, которых должно быть достаточно для тестирования.
@ d3v Как я уже сказал, одного теста недостаточно. Пожалуйста, сделайте MRE.
@ d3v Я знаю, вы сказали, что пробовали оба, но how='inner' определенно не то, что вам нужно, и противоречит комментарию кода «Объединить таблицы в столбце «Rate» с внешним соединением».
Я обновил пост и добавил полное содержимое обоих файлов, а также выходные данные скрипта, а не просто минимальный воспроизводимый пример. Надеюсь, ты теперь счастлив.
@d3v Нам не нужен полный контент, это слишком много. Пожалуйста, прочитайте Как сделать хорошие воспроизводимые примеры панд.
Я добавил минимальный пример с его выводом и желаемым результатом.
@ d3v Хорошо, отлично, теперь, пожалуйста, избавься от более крупного примера.
@ d3v Также избавьтесь от to_excel в конце (при условии, что это не имеет отношения к проблеме).
Ответы 1
Пример
Ваш MRE кажется неадекватным. Я несколько изменил пример. (В частности, мне кажется неправильной та часть, где 0-7 и 7-8 сосуществуют в df2).
import pandas as pd
df1 = pd.DataFrame({'Rate': ['0-4', '5-6', '7-8', '9-10', '11-13', '14-16', '17'], 'Age': ['<6.0', '6.0', '6.3', '6.6', '6.9', '7.0', '7.3']})
df2 = pd.DataFrame({'Rate': ['0-6', '7-8', '9-10', '11-12', '13-14', '15-16', '17'], 'Grade': ['<1.0', '1', '1.2', '1.4', '1.7', '2.0', '2.3']})
Код
используйте IntervalIndex, чтобы установить интервал, а затем индексируйте максимальное значение интервала, чтобы получить результат. (поскольку оценка соответствует максимальному значению в диапазоне).
tmp = df2['Rate'].str.split('-', expand=True).ffill(axis=1).astype('int')
idx = pd.IntervalIndex.from_arrays(tmp[0], tmp[1], closed='both')
m = df2['Grade'].set_axis(idx)
tmp1 = df1['Rate'].str.extract('(\d+)$')[0].astype('int')
out = df1.assign(Grade=m[tmp1].values)
Спасибо за попытку помочь, а не просто давать инструкции и говорить. Когда я пытаюсь реализовать код с помощью двух файлов Excel, я получаю следующее: ` SyntaxWarning: недопустимая escape-последовательность '\d'` и ValueError: cannot convert float NaN to integer
@ d3v Вы пробовали запустить его с помощью приведенного мной примера? Если в приведенном вами примере это работает, то в примере и в файле Excel все по-другому. Кроме того, размещение сообщения об ошибке и обращение к кому-то другому не решит проблему, поскольку отвечающим придется догадываться. (Я тоже предполагаю, но не отвечаю догадками, а спрашивающие не должны заставлять отвечающих гадать.) Если вы не можете предоставить достаточно информации, лучше задать вопрос в ChatGPT, чем задавать его человеку.
Я скопировал данные из Excel в код, и все заработало. Если есть третий DataFrame с Rate, Percentile и Scaled, какую часть мне следует дублировать, чтобы работать с тремя DataFrames? Извините, я новичок в Python и Pandas.
Я бы использовал
.sort_values("Rate")в вашем примере с объединенной таблицей, чтобы убедиться, что это не работает. Кроме того, предоставьте минимально воспроизводимый пример. А именно, например, извлеките первые 5 строк и покажите результат. Если это работает, извлеките другие строки.