{ "cells": [ { "cell_type": "code", "execution_count": 6, "id": "bd78c2e2", "metadata": {}, "outputs": [ { "ename": "ValueError", "evalue": "No objects to concatenate", "output_type": "error", "traceback": [ "\u001b[31m---------------------------------------------------------------------------\u001b[39m", "\u001b[31mValueError\u001b[39m Traceback (most recent call last)", "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[6]\u001b[39m\u001b[32m, line 41\u001b[39m\n\u001b[32m 38\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[33mf\u001b[39m\u001b[33m\"\u001b[39m\u001b[33mArquivo ignorado (colunas ausentes ou erro na leitura): \u001b[39m\u001b[38;5;132;01m{\u001b[39;00mfile\u001b[38;5;132;01m}\u001b[39;00m\u001b[33m\"\u001b[39m)\n\u001b[32m 40\u001b[39m \u001b[38;5;66;03m# Concatena os DataFrames válidos\u001b[39;00m\n\u001b[32m---> \u001b[39m\u001b[32m41\u001b[39m retorno_analista = \u001b[43mpd\u001b[49m\u001b[43m.\u001b[49m\u001b[43mconcat\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdataframes\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mignore_index\u001b[49m\u001b[43m=\u001b[49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m)\u001b[49m\n\u001b[32m 43\u001b[39m \u001b[38;5;66;03m# Exibe a forma do DataFrame resultante\u001b[39;00m\n\u001b[32m 44\u001b[39m \u001b[38;5;28mprint\u001b[39m(retorno_analista.shape)\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\joao.herculano\\AppData\\Local\\Programs\\Python\\Python313\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:382\u001b[39m, in \u001b[36mconcat\u001b[39m\u001b[34m(objs, axis, join, ignore_index, keys, levels, names, verify_integrity, sort, copy)\u001b[39m\n\u001b[32m 379\u001b[39m \u001b[38;5;28;01melif\u001b[39;00m copy \u001b[38;5;129;01mand\u001b[39;00m using_copy_on_write():\n\u001b[32m 380\u001b[39m copy = \u001b[38;5;28;01mFalse\u001b[39;00m\n\u001b[32m--> \u001b[39m\u001b[32m382\u001b[39m op = \u001b[43m_Concatenator\u001b[49m\u001b[43m(\u001b[49m\n\u001b[32m 383\u001b[39m \u001b[43m \u001b[49m\u001b[43mobjs\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 384\u001b[39m \u001b[43m \u001b[49m\u001b[43maxis\u001b[49m\u001b[43m=\u001b[49m\u001b[43maxis\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 385\u001b[39m \u001b[43m \u001b[49m\u001b[43mignore_index\u001b[49m\u001b[43m=\u001b[49m\u001b[43mignore_index\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 386\u001b[39m \u001b[43m \u001b[49m\u001b[43mjoin\u001b[49m\u001b[43m=\u001b[49m\u001b[43mjoin\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 387\u001b[39m \u001b[43m \u001b[49m\u001b[43mkeys\u001b[49m\u001b[43m=\u001b[49m\u001b[43mkeys\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 388\u001b[39m \u001b[43m \u001b[49m\u001b[43mlevels\u001b[49m\u001b[43m=\u001b[49m\u001b[43mlevels\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 389\u001b[39m \u001b[43m \u001b[49m\u001b[43mnames\u001b[49m\u001b[43m=\u001b[49m\u001b[43mnames\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 390\u001b[39m \u001b[43m \u001b[49m\u001b[43mverify_integrity\u001b[49m\u001b[43m=\u001b[49m\u001b[43mverify_integrity\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 391\u001b[39m \u001b[43m \u001b[49m\u001b[43mcopy\u001b[49m\u001b[43m=\u001b[49m\u001b[43mcopy\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 392\u001b[39m \u001b[43m \u001b[49m\u001b[43msort\u001b[49m\u001b[43m=\u001b[49m\u001b[43msort\u001b[49m\u001b[43m,\u001b[49m\n\u001b[32m 393\u001b[39m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 395\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m op.get_result()\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\joao.herculano\\AppData\\Local\\Programs\\Python\\Python313\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:445\u001b[39m, in \u001b[36m_Concatenator.__init__\u001b[39m\u001b[34m(self, objs, axis, join, keys, levels, names, ignore_index, verify_integrity, copy, sort)\u001b[39m\n\u001b[32m 442\u001b[39m \u001b[38;5;28mself\u001b[39m.verify_integrity = verify_integrity\n\u001b[32m 443\u001b[39m \u001b[38;5;28mself\u001b[39m.copy = copy\n\u001b[32m--> \u001b[39m\u001b[32m445\u001b[39m objs, keys = \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43m_clean_keys_and_objs\u001b[49m\u001b[43m(\u001b[49m\u001b[43mobjs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mkeys\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m 447\u001b[39m \u001b[38;5;66;03m# figure out what our result ndim is going to be\u001b[39;00m\n\u001b[32m 448\u001b[39m ndims = \u001b[38;5;28mself\u001b[39m._get_ndims(objs)\n", "\u001b[36mFile \u001b[39m\u001b[32mc:\\Users\\joao.herculano\\AppData\\Local\\Programs\\Python\\Python313\\Lib\\site-packages\\pandas\\core\\reshape\\concat.py:507\u001b[39m, in \u001b[36m_Concatenator._clean_keys_and_objs\u001b[39m\u001b[34m(self, objs, keys)\u001b[39m\n\u001b[32m 504\u001b[39m objs_list = \u001b[38;5;28mlist\u001b[39m(objs)\n\u001b[32m 506\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mlen\u001b[39m(objs_list) == \u001b[32m0\u001b[39m:\n\u001b[32m--> \u001b[39m\u001b[32m507\u001b[39m \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m(\u001b[33m\"\u001b[39m\u001b[33mNo objects to concatenate\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m 509\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m keys \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[32m 510\u001b[39m objs_list = \u001b[38;5;28mlist\u001b[39m(com.not_none(*objs_list))\n", "\u001b[31mValueError\u001b[39m: No objects to concatenate" ] } ], "source": [ "import os\n", "import glob\n", "import pandas as pd\n", "import numpy as np\n", "\n", "# Caminho da pasta com os arquivos Excel\n", "folder_path = \"C:/Users/joao.herculano/Downloads/RE_ [BOT] LANÇAMENTOS C25.16 efigenia\"\n", "\n", "# Padrão para localizar todos os arquivos .xlsx\n", "excel_files = glob.glob(os.path.join(folder_path, '*.xlsx'))\n", "\n", "# Lista para armazenar os DataFrames válidos\n", "dataframes = []\n", "\n", "# Colunas obrigatórias\n", "required_columns = ['PDV',\n", "'PRODUTO LANÇAMENTO',\n", "'SUGESTÃO COMERCIAL']\n", "\n", "# Para cada arquivo Excel\n", "for file in excel_files:\n", " success = False\n", " for header_row in range(10):\n", " print(file)\n", " try:\n", " df = pd.read_excel(file, header=header_row)\n", " if all(col in df.columns for col in required_columns):\n", " if 'Sugestão Pedro' in df.columns or 'Sugestão Laura' in df.columns:\n", " print(file)\n", " df = df[required_columns]\n", " df['arquivo fonte'] = file\n", " dataframes.append(df)\n", " success = True\n", " break\n", " except Exception as e:\n", " continue\n", " if not success:\n", " print(f\"Arquivo ignorado (colunas ausentes ou erro na leitura): {file}\")\n", "\n", "# Concatena os DataFrames válidos\n", "retorno_analista = pd.concat(dataframes, ignore_index=True)\n", "\n", "# Exibe a forma do DataFrame resultante\n", "print(retorno_analista.shape)" ] }, { "cell_type": "code", "execution_count": 2, "id": "8f30f0f6", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(44802, 4)" ] }, "execution_count": 2, "metadata": {}, "output_type": "execute_result" } ], "source": [ "retorno_analista = retorno_analista.drop_duplicates()\n", "retorno_analista.shape" ] }, { "cell_type": "code", "execution_count": 3, "id": "3288bda7", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "Index(['PDV', 'PRODUTO LANÇAMENTO', 'SUGESTÃO COMERCIAL', 'arquivo fonte'], dtype='object')" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "retorno_analista.columns" ] }, { "cell_type": "code", "execution_count": 4, "id": "d3e20e1d", "metadata": {}, "outputs": [], "source": [ "retorno_analista = retorno_analista.fillna(0)\n", "#retorno_analista = retorno_analista[retorno_analista['SUGESTÃO COMERCIAL']!=0]\n", "retorno_analista['SUGESTÃO COMERCIAL'] = retorno_analista['SUGESTÃO COMERCIAL'].astype('Int64')\n", "\n", "retorno_analista2 = retorno_analista.groupby(['PDV','PRODUTO LANÇAMENTO'])['SUGESTÃO COMERCIAL'].max().reset_index()" ] }, { "cell_type": "code", "execution_count": 5, "id": "96504ca6", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
PDVPRODUTO LANÇAMENTOSUGESTÃO COMERCIAL
133124255491541000
7232099849154877
7242099849193550
13322425549193500
7042099749154450
\n", "
" ], "text/plain": [ " PDV PRODUTO LANÇAMENTO SUGESTÃO COMERCIAL\n", "1331 24255 49154 1000\n", "723 20998 49154 877\n", "724 20998 49193 550\n", "1332 24255 49193 500\n", "704 20997 49154 450" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "retorno_analista2.sort_values('SUGESTÃO COMERCIAL',ascending=False).head()" ] }, { "cell_type": "code", "execution_count": 6, "id": "e66c606c", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
chavePDVPRODUTO LANÇAMENTOSUGESTÃO COMERCIAL
035464878035464878020
135464915435464915450
235464919335464919350
335464989335464989320
435464994335464994320
\n", "
" ], "text/plain": [ " chave PDV PRODUTO LANÇAMENTO SUGESTÃO COMERCIAL\n", "0 354648780 3546 48780 20\n", "1 354649154 3546 49154 50\n", "2 354649193 3546 49193 50\n", "3 354649893 3546 49893 20\n", "4 354649943 3546 49943 20" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "retorno_analista2['chave'] = retorno_analista2['PDV'].astype('str')+retorno_analista2['PRODUTO LANÇAMENTO'].astype('str')\n", "\n", "retorno_analista2.head()\n", "retorno_analista2 = retorno_analista2[['chave','PDV',\t'PRODUTO LANÇAMENTO',\t'SUGESTÃO COMERCIAL'\t]]\n", "\n", "retorno_analista2.head()" ] }, { "cell_type": "code", "execution_count": 7, "id": "527c33cf", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "0 354648780\n", "1 354649154\n", "2 354649193\n", "3 354649893\n", "4 354649943\n", "Name: chave, dtype: object" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "retorno_analista2['chave'] = retorno_analista2['chave'].str.replace('.0','')\n", "\n", "retorno_analista2['chave'].head()" ] }, { "cell_type": "code", "execution_count": null, "id": "2819f6d3", "metadata": {}, "outputs": [], "source": [ "retorno_analista2.to_excel(r'C:\\Users\\joao.herculano\\Downloads\\RE_ [BOT] LANÇAMENTOS C25.16 efigenia\\retorno_efigenia.xlsx',index=False)\n" ] }, { "cell_type": "code", "execution_count": null, "id": "1d8b6fc0", "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.13.2" } }, "nbformat": 4, "nbformat_minor": 5 }