Python Pandas Excel нужна помощь

Сортировка по дате
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Здравствуйте! Нужно сравнить 2 таблицы Excel. В каждой из них есть колонка с фамилиями и соответствующие им объемы топлива. Нужно сравнить их, и там, где отдельно взятой фамилии в разных таблицах соответствует разное кол-во объема топлива подкрасить ячейку с объемом в красный цвет. Фамилии могут быть не по порядку и кол-во фамилий в 2-х таблицах может быть разным. Если в одной таблице количество фамилий больше либо равно, чем в другой, то "красить" ячейки с "Количеством" в бОльшей таблице. При этом нужно подкрасить ячейки с фамилиями, которых нет в меньшей таблице.
Как я понимаю, то код должен начинаться так:
import pandas as pd

df1 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость заправочная", index_col=0)
df2 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость организации", index_col=0)
len(df1)
len(df2)
while len(df1)>=len(df2)
Только начинаю разбираться. Получилось написать короткий код через Pandas, но для этого мне пришлось преобразовывать сами таблицы, удаляя все колонки в каждой из таблиц, кроме 2-х : на листе "Ведомость заправочная" - "VIN" и "Количество", на листе "Ведомость организации" - "Водитель " и "Количество". Собственно сам код:
import pandas as pd

df1 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость заправочная", index_col=0)
df2 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость организации", index_col=0)

diff = df1 - df2
print(diff)
diff.to_excel("Samp.xlsx", sheet_name="Sheet3")

Надеюсь на Вашу помощь!
 

Вложения

  • Сверка топлива пробы.zip
    16 КБ · Просмотры: 6
Последнее редактирование:
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
В таблицах есть фамилии, которые присутствуют в первой таблице и отсутствуют во второй и наоборот: присутствуют во второй и отсутствуют в первой. Я тоже хотел их выделить цветом, кроме красного. Но получилась дичь какая-то))
Python: 
import pandas as pd  # импортируем модуль pandas

# читаем две таблицы в два датафрейма
df1 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость заправочная", index_col=0)
s1 = df1
df2 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость организации", index_col=0)
s2 = df2

# создем два словаря для данных из таблиц (ключ - фамилия, значение - топливо)
dct_df1 = {}
dct_df2 = {}

# проходим по двум столбцам из первой таблицы Unnamed: 1 и Unnamed: 5
# (на самом деле это фамилия и топливо соответственно, просто в первой таблице имена столбцов не указаны)
for name, value in zip(df1["Unnamed: 1"], df1["Unnamed: 5"]):
    if "nan" in str(name) or "VIN" in str(name):  # если имя nan или VIN
        continue  # прерываем текущую итерацию цикла фор
    try:
        dct_df1[name] += -value  # прибавляем к элементу с ключом name (фамилия) значение value (топливо)
                                 # -value, потому что в таблице отрицательные значения
    except KeyError:  # если ошибка KeyError (нет такого ключа)
        dct_df1[name] = -value  # то просто добавялем новый элемент с ключом name (фамилия) и значением value (топливо)

# делаем аналогичную операцию со второй таблицей
for name, value in zip(df2["Водитель"], df2["Количество"]):
    if "nan" in str(name):
        continue
    try:
        dct_df2[name] += value
    except KeyError:
        dct_df2[name] = value


def color(col):
    return f"background-color: {col}"


dct_result = {}  # словарь для результата
dct_result1 = {}
dct_result2 = {}
# далее по сути сравниваем две таблицы
if len(dct_df1)>len(dct_df2):
    for name, value in dct_df2.items():  # пробегаем по словарю из второй таблицы (фамилии и топливо)
        if name in dct_df1:  # если фамилия есть в словаре из первой таблицы
            if value != dct_df1[name]:  # и если топливо не совпадает в двух таблицах
                dct_result[name] = value  # добавляем в словарь результата
                count = 0  # счетчик для закраски строки
                for i in df2["Водитель"]:  # пробегаем датафрейм
                    if i == name:  # если текущий элемент равен имени, которое не совпадает по топливу
                        s2 = df2.reset_index().style.applymap(lambda x: color("red"), subset=count)  # красим в красный
                    count += 1
        else:
            dct_result1[name] = value
            count = 0
            for i in df2["Водитель"]:
                if i != name:
                    s2 = df2.reset_index().style.applymap(lambda x: color("yellow"), subset=count)
                count += 1

else:
    for name, value in dct_df1.items():
        if name in dct_df2:
            if value != dct_df2[name]:
                dct_result[name] = value
                count = 0
                for i in df1["Unnamed: 1"]:
                    if i == name:
                        s1 = df1.reset_index().style.applymap(lambda x: color("red"), subset=count)
                    count += 1
        else:
            dct_result2[name] = value
            count = 0
            for i in df1["Unnamed: 1"]:
                if i != name:
                    s1 = df1.reset_index().style.applymap(lambda x: color("yellow"), subset=count)
                count += 1
print(dct_result)
print(dct_result1)
print(dct_result2)

# Записываем новые датафреймы
with pd.ExcelWriter("Сверка топлива пробы.xlsx") as writer:
    s1.to_excel(writer, sheet_name="Ведомость заправочная")
    s2.to_excel(writer, sheet_name="Ведомость организации")
 
Последнее редактирование:
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Python: 
import pandas as pd  # импортируем модуль pandas

# читаем две таблицы в два датафрейма
df1 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость заправочная", index_col=0)
s1 = df1
df2 = pd.read_excel("Сверка топлива пробы.xlsx", "Ведомость организации", index_col=0)
s2 = df2

# создем два словаря для данных из таблиц (ключ - фамилия, значение - топливо)
dct_df1 = {}
dct_df2 = {}

# проходим по двум столбцам из первой таблицы Unnamed: 1 и Unnamed: 5
# (на самом деле это фамилия и топливо соответственно, просто в первой таблице имена столбцов не указаны)
for name, value in zip(df1["Unnamed: 1"], df1["Unnamed: 5"]):
    if "nan" in str(name) or "VIN" in str(name):  # если имя nan или VIN
        continue  # прерываем текущую итерацию цикла фор
    try:
        dct_df1[name] += -value  # прибавляем к элементу с ключом name (фамилия) значение value (топливо)
                                 # -value, потому что в таблице отрицательные значения
    except KeyError:  # если ошибка KeyError (нет такого ключа)
        dct_df1[name] = -value  # то просто добавялем новый элемент с ключом name (фамилия) и значением value (топливо)

# делаем аналогичную операцию со второй таблицей
for name, value in zip(df2["Водитель"], df2["Количество"]):
    if "nan" in str(name):
        continue
    try:
        dct_df2[name] += value
    except KeyError:
        dct_df2[name] = value


def color(col):
    return f"background-color: {col}"


dct_result = {}  # словарь для результата, если одинаковым именам не соответствует значение
dct_result1 = {} # словарь для результата, если имени нет в первой таблице
dct_result2 = {} # словарь для результата, если имени нет во второй таблице
# далее по сути сравниваем две таблицы
if len(dct_df1) > len(dct_df2):  # если первая таблица больше чем вторая
    for name, value in dct_df2.items():  # пробегаем по словарю из второй таблицы (фамилии и топливо)
        if name in dct_df1:  # если фамилия есть в словаре из первой таблицы
            if value != dct_df1[name]:  # и если топливо не совпадает в двух таблицах
                dct_result[name] = value  # добавляем в словарь результата
                count = 0  # счетчик для закраски строки
                for i in df2["Водитель"]:  # пробегаем датафрейм
                    if i == name:  # если текущий элемент равен имени, которое не совпадает по топливу
                        s2 = df2.reset_index().style.applymap(lambda x: color("red"), subset=count)  # красим в красный
                    count += 1
        else:
            dct_result1[name] = value
            count = 0
            for i in df2["Водитель"]:
                if i == name:
                    s2 = df2.reset_index().style.applymap(lambda x: color("yellow"), subset=count)
                count += 1

else:  # иначе делаем тоже самое, но пробегаем первый словарь и сравниваем со вторым
    for name, value in dct_df1.items():
        if name in dct_df2:
            if value != dct_df2[name]:
                dct_result[name] = value
                count = 0
                for i in df1["Unnamed: 1"]:
                    if i == name:
                        s1 = df1.reset_index().style.applymap(lambda x: color("red"), subset=count)
                    count += 1
        else:
            dct_result2[name] = value
            count = 0
            for i in df1["Unnamed: 1"]:
                if i == name:
                    s1 = df1.reset_index().style.applymap(lambda x: color("yellow"), subset=count)
                count += 1

print("Фамилии совпадают в 2-х таблицах, но несовпадение по топливу:", dct_result)
print("Такие фамилии отсутствуют в первой таблице:", dct_result1)
print("Такие фамилии отсутствуют во второй таблице:", dct_result2)
# Записываем новые датафреймы
with pd.ExcelWriter("Сверка топлива пробы.xlsx") as writer:
    s1.to_excel(writer, sheet_name="Ведомость заправочная")
    s2.to_excel(writer, sheet_name="Ведомость организации")

Снова прошу Вашей помощи... Немного переписал код, добавив в каждый цикл for условие, что если пробегая по словарю dct_df2 или dct_df1, мы не находим фамилию (name), совпадающую в 2-х словарях, строчка при этом красится в желтый цвет.
Я так понимаю, что датафреймы каждый раз перезаписываются, поэтому теряются предыдущие подсчеты (раскраски). Как сделать, чтобы предыдущие раскраски оставались, а к ним добавлялись новые?
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Спасибо большое! Я Вам по-хорошему завидую!
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Подскажите пожалуйста. В словаре существует 2 элемента: собственно ключ и значение. А можно ли к ключу добавить не одно, а несколько значений?
 
0
R

regnor

Модератор
Команда форума
Модератор
Июл 7, 2020
2 661
474
83
Alexxxandr сказал(а):
Подскажите пожалуйста. В словаре существует 2 элемента: собственно ключ и значение. А можно ли к ключу добавить не одно, а несколько значений?
Нажмите, чтобы раскрыть...
можно с помощью списка
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
regnor сказал(а):
можно с помощью списка
Нажмите, чтобы раскрыть...
Это оператор list?
 
0
4olshoy_blen

4olshoy_blen

Популярный
Пользователь
Ноя 13, 2022
423
115
43
Alexxxandr сказал(а):
Это оператор list?
Нажмите, чтобы раскрыть...
Да, например:

Python: 
p = {'a': [1, 2]}
print(p['a'][0], p['a'][1])
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Пытаюсь сам победить задачку, спасибо Regnor за помощь, использую его код, как пример. Спрашивал про словарь, в котором может быть по ключу 2 и более значения, т.к. хочу научиться сравнивать 2 таблицы, в которых ключ- гаражный номер, а значения - это виды топлива (дизтопливо(А), дизтопливо в талонах, бензин, газ) и т.д., а также их количество в литрах. Уже сделал, благодаря ув. Regnor, код, когда сравниваю таблицы с одним ключом и значением, но для этого в данном примере приходится делать большую подготовительную работу. Надеюсь, что осилю с Вашей помощью.
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Я так понимаю, что в первой таблице в колонке "Наименование" гаражные номера (10512..., 512... и т.д.) придется вынести в отдельный столбец, потому что при создании датафрейма и его последующей обработке получится путаница, если, конечно, нет какого-то особого приема у уважаемых питонистов))
 

Вложения

  • Сверка топлива октябрь.zip
    82,3 КБ · Просмотры: 2
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Можно ли сохранять исходный формат таблиц(ширина столбцов, ячеек, цвет)?
 
0
R

regnor

Модератор
Команда форума
Модератор
Июл 7, 2020
2 661
474
83
Alexxxandr сказал(а):
Можно ли сохранять исходный формат таблиц(ширина столбцов, ячеек, цвет)?
Нажмите, чтобы раскрыть...
в новом датафрейме его нужно делать заново, посмотрите документацию пандас
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Здравствуйте, Друзья!
Снова нужна Ваша помощь. Есть эксель файл, в котором есть 2 таблички на 2-х листах. Во втором листе есть колонка "Объем". На первом листе есть колонки 8м3, 10м3, 12м3, имеются строки по дате. На листе2 значение ячейки в колонке "Объем" нужно сделать следующим образом: если значение делится без остатка на 8 (например, в данном случае 16/8=2), то значение 2 вписать в ячейку строки 1декабря и колонки 8м3 на листе1. Затем берем второе значение в колонке "объем" на листе2 и снова пытаемся поделить на 8 (16/8=2). Значение 2 нужно прибавить к предыдущему значению 2 в строке 1дек. в колонке 8м3 (2+2=4) и так далее. Если не делится на 8, пробуем разделить на 10 и вписать результат в ячейки колонки 10м3. Если не делится на 10, то пробуем делить на 12 и вписываем в ячейки колонки 12м3.
Эту операцию, я так понимаю, нужно делать циклом for. Только не пойму, как это привязать по датам? Это делать, используя словарь?
 

Вложения

  • К-во конт-кубов.zip
    32,2 КБ · Просмотры: 3
0
R

regnor

Модератор
Команда форума
Модератор
Июл 7, 2020
2 661
474
83
если у вас так много екселей, проще написать это в ексель...
 
  • Мне нравится
Реакции: Alexxxandr
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
regnor сказал(а):
если у вас так много екселей, проще написать это в ексель...
Нажмите, чтобы раскрыть...
Через макросы VBA? Честно хочется в Python)) Дайте плз какое-нибудь напутствие. Тем более, если я в питоне почти 0, то в вба - минус. Использовать цикл for и в каждой итерации делать if для даты?
 
Последнее редактирование:
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
regnor сказал(а):
суммы со страницы два по дням, математические операции осталось добавить и записать на лист один
Python: 
import pandas as pd

dct = {}

file = "К-во конт-кубов.xlsx"
df = pd.read_excel(file, sheet_name=["Лист1", "Лист2"])
date = df["Лист2"]["Дата"]
capacity = df["Лист2"]["Объем"]

# Делаем словарь, ключ - дата, значение - сумма (индексы элементов в capacity)
for i in date:
    dct[i] = [index for index, value in enumerate(date) if value == i]

# # Суммируем суммы по дням
res = 0
for i in dct:
    for j in dct[i]:
        res += capacity[j]
    dct[i] = res
    res = 0

print(dct)
Нажмите, чтобы раскрыть...
Спасибо большое! Как у Вас так быстро получается обдумать алгоритм?!!
Голову поломал, как у Вас сразу получилось посчитать в первом листе колонку 5, не считая колонки 8м3, 10м3, 12м3(2,3,4 колонки соответственно)? Формула ячейки в 5-й колонке(2 строка, 5 колонка) это: (значение ячейки 2,2 *8м3)+(значение ячейки 2,3 *10м3)+(значение ячейки 2,4 * 12м3)=588м3. Или Вы просто взяли сумму по дням из строки "Всего"?
date - это переменная по дате из созданного датафрейма, capacity - переменная по объему из датафрейма?
С горем пополам понял, как Вы создали словарь (и то, может неправильно понял)
Но дальше....просто вообще ничего не понял((((
Если будет время, помогите разобраться пожалуйста.
 
Последнее редактирование:
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Не могу попасть на вторую страницу((
 
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
Чувствую себя абсолютно тупым((
for i in capacity:
if capacity % 8 == 0
или
for i in dct:
if capacity % 8 == 0
 
0
R

regnor

Модератор
Команда форума
Модератор
Июл 7, 2020
2 661
474
83
второе, и dct - это словарь, значения в нем - список, то есть два for надо, capacity вообще не нужно
 
  • Мне нравится
Реакции: Alexxxandr
0
A

Alexxxandr

Новичок
Пользователь
Сен 11, 2022
36
0
6
regnor сказал(а):
второе, и dct - это словарь, значения в нем - список, то есть два for надо, capacity вообще не нужно
Нажмите, чтобы раскрыть...
Вообще не дается. У нас получается словарь, где ключ - дата, значение: индекс элемента в capacity?
Python: 
for i in dct:

   for j in dct[i]:

     if теперь "житый" элемент мы должны разделить на 8, и если он делится на 8 без остатка, прибавить к существующему нулю результат от деления на 8 на лист1 в соответствующей ячейке

     else делим "житый" элемент на 10, и если он делится на 10 без остатка, прибавить к существующему нулю результат деления на 10 на листе 1 в соответствующей ячейке

     else делим "житый" элемент на 12, проделывая предыдущую операцию

      если не делится без остатка на 8, на 10, на 12

      continue
А можете подсказать пожалуйста, как элементам с индексами j добавить значения?
 
Последнее редактирование:
0
R

regnor

Модератор
Команда форума
Модератор
Июл 7, 2020
2 661
474
83
Alexxxandr сказал(а):
Вообще не дается. У нас получается словарь, где ключ - дата, значение: индекс элемента в capacity?
Python: 
for i in dct:

   for j in dct[i]:

     if теперь "житый" элемент мы должны разделить на 8, и если он делится на 8 без остатка, прибавить к существующему нулю результат от деления на 8 на лист1 в соответствующей ячейке

     else делим "житый" элемент на 10, и если он делится на 10 без остатка, прибавить к существующему нулю результат деления на 10 на листе 1 в соответствующей ячейке

     else делим "житый" элемент на 12, проделывая предыдущую операцию

      если не делится без остатка на 8, на 10, на 12

      continue
А можете подсказать пожалуйста, как элементам с индексами j добавить значения?
Нажмите, чтобы раскрыть...
capacity нужно, ошибся я
Python: 
import pandas as pd

dct = {}

file = "К-во конт-кубов.xlsx"
df = pd.read_excel(file, sheet_name=["Лист1", "Лист2"])
date = df["Лист2"]["Дата"]
capacity = df["Лист2"]["Объем"]

# Делаем словарь, ключ - дата, значение - сумма (индексы элементов в capacity)
for i in date:
    dct[i] = [index for index, value in enumerate(date) if value == i]

# # Делаем дела с делением и добавляем результаты в словарь
res = 0
for i in dct:
    for j in dct[i]:
        if capacity[j] % 8 == 0:
            res += capacity[j] / 8
        elif capacity[j] % 10 == 0:
            res += capacity[j] / 10
        elif capacity[j] % 12 == 0:
            res += capacity[j] / 12
        else:
            continue
    dct[i] = res
    res = 0

print(dct)
 
  • Мне нравится
Реакции: Alexxxandr
Для ответа нужно войти/зарегистрироваться

Форум IT Специалистов

Верх Низ