Skip to content

Instantly share code, notes, and snippets.

@gabraganca

gabraganca/excel_arq.xlsx

Last active January 10, 2018 16:21
Show Gist options
  • Save gabraganca/9549fff165af146c5df9b53c0e73c4f2 to your computer and use it in GitHub Desktop.
Save gabraganca/9549fff165af146c5df9b53c0e73c4f2 to your computer and use it in GitHub Desktop.
Download ZIP
Ajudando o Xavier
Display the source blob
Display the rendered blob
Raw
xavier_ajuda.ipynb
{
"cells": [
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Problema\n",
"\n",
"Temos um arquivo Excel com duas planilhas:\n",
"\n",
"1. A `Planilha_1` contém duas colunas: `DEVICE` e `ADDRESSID`. \n",
"2. A `Planilha_2` contém três colunas: `LOCATION`, `NAME` e `NOVA`\n",
"\n",
"Nosso objetivo é identificar as entradas em comum em `Planilha_1:DEVICE` e `Planilha_2:LOCATION`, e retornar os valores de `Planilha_1:ADRESSID` destes valores em comum."
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"## Solução"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Carregamento da biblioteca Pandas."
]
},
{
"cell_type": "code",
"execution_count": 1,
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Carregamento das planilhas"
]
},
{
"cell_type": "code",
"execution_count": 2,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>DEVICE</th>\n",
" <th>ADRESSID</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>Overhold</td>\n",
" <td>31.77.57.23</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>Cardguard</td>\n",
" <td>64.167.22.255</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>Alpha</td>\n",
" <td>138.52.179.239</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>Konklab</td>\n",
" <td>36.54.4.13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>Y-Solowarm</td>\n",
" <td>210.198.154.120</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" DEVICE ADRESSID\n",
"0 Overhold 31.77.57.23\n",
"1 Cardguard 64.167.22.255\n",
"2 Alpha 138.52.179.239\n",
"3 Konklab 36.54.4.13\n",
"4 Y-Solowarm 210.198.154.120"
]
},
"execution_count": 2,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"planilha_1 = pd.read_excel('excel_arq.xlsx', sheet_name='Planilha_1')\n",
"# Mostra os primieros 5 valores\n",
"planilha_1.head()"
]
},
{
"cell_type": "code",
"execution_count": 3,
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>LOCATION</th>\n",
" <th>NAME</th>\n",
" <th>NOVA</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>Wuluo</td>\n",
" <td>Home Ing</td>\n",
" <td>Loris tardigratus</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>El Cuy</td>\n",
" <td>Toughjoyfax</td>\n",
" <td>Chloephaga melanoptera</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>Seydi</td>\n",
" <td>Pannier</td>\n",
" <td>Heloderma horridum</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>Przewóz</td>\n",
" <td>Sonsing</td>\n",
" <td>Sarkidornis melanotos</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>Caherconlish</td>\n",
" <td>Fixflex</td>\n",
" <td>Eurocephalus anguitimens</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" LOCATION NAME NOVA\n",
"0 Wuluo Home Ing Loris tardigratus\n",
"1 El Cuy Toughjoyfax Chloephaga melanoptera\n",
"2 Seydi Pannier Heloderma horridum\n",
"3 Przewóz Sonsing Sarkidornis melanotos\n",
"4 Caherconlish Fixflex Eurocephalus anguitimens"
]
},
"execution_count": 3,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"planilha_2 = pd.read_excel('excel_arq.xlsx', sheet_name='Planilha_2')\n",
"# Mostra os primieros 5 valores\n",
"planilha_2.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Identificamos os itens `DEVICE` que estão na lista `NAME`."
]
},
{
"cell_type": "code",
"execution_count": 4,
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"0 True\n",
"1 True\n",
"2 False\n",
"3 True\n",
"4 True\n",
"Name: DEVICE, dtype: bool"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cond = planilha_1['DEVICE'].isin(planilha_2['NAME'])\n",
"# Mostra os primieros 5 valores\n",
"cond.head()"
]
},
{
"cell_type": "markdown",
"metadata": {},
"source": [
"Mostramos as entradas da `Planilha_1` usando a listagem encontrada anteriormente."
]
},
{
"cell_type": "code",
"execution_count": 5,
"metadata": {
"scrolled": false
},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>DEVICE</th>\n",
" <th>ADRESSID</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>Overhold</td>\n",
" <td>31.77.57.23</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>Cardguard</td>\n",
" <td>64.167.22.255</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>Konklab</td>\n",
" <td>36.54.4.13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>Y-Solowarm</td>\n",
" <td>210.198.154.120</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>Zoolab</td>\n",
" <td>48.61.22.191</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>Y-Solowarm</td>\n",
" <td>164.0.176.23</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>It</td>\n",
" <td>67.141.1.164</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>Kanlam</td>\n",
" <td>118.47.120.129</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>Subin</td>\n",
" <td>186.175.84.143</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>Solarbreeze</td>\n",
" <td>60.149.36.235</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>Treeflex</td>\n",
" <td>126.176.97.110</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>Viva</td>\n",
" <td>145.254.77.152</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>Bitchip</td>\n",
" <td>167.249.167.225</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>Opela</td>\n",
" <td>210.48.49.48</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>Bytecard</td>\n",
" <td>145.167.2.27</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>Redhold</td>\n",
" <td>239.135.39.170</td>\n",
" </tr>\n",
" <tr>\n",
" <th>24</th>\n",
" <td>Home Ing</td>\n",
" <td>215.40.29.99</td>\n",
" </tr>\n",
" <tr>\n",
" <th>25</th>\n",
" <td>Stringtough</td>\n",
" <td>51.122.73.33</td>\n",
" </tr>\n",
" <tr>\n",
" <th>26</th>\n",
" <td>Zathin</td>\n",
" <td>126.148.67.41</td>\n",
" </tr>\n",
" <tr>\n",
" <th>27</th>\n",
" <td>Cookley</td>\n",
" <td>4.44.114.39</td>\n",
" </tr>\n",
" <tr>\n",
" <th>28</th>\n",
" <td>Lotstring</td>\n",
" <td>132.122.66.5</td>\n",
" </tr>\n",
" <tr>\n",
" <th>29</th>\n",
" <td>Konklux</td>\n",
" <td>165.229.52.67</td>\n",
" </tr>\n",
" <tr>\n",
" <th>31</th>\n",
" <td>Bytecard</td>\n",
" <td>227.20.57.161</td>\n",
" </tr>\n",
" <tr>\n",
" <th>32</th>\n",
" <td>Kanlam</td>\n",
" <td>79.95.166.141</td>\n",
" </tr>\n",
" <tr>\n",
" <th>37</th>\n",
" <td>Sonsing</td>\n",
" <td>35.158.73.183</td>\n",
" </tr>\n",
" <tr>\n",
" <th>38</th>\n",
" <td>Y-Solowarm</td>\n",
" <td>194.137.174.193</td>\n",
" </tr>\n",
" <tr>\n",
" <th>39</th>\n",
" <td>Zathin</td>\n",
" <td>38.29.255.56</td>\n",
" </tr>\n",
" <tr>\n",
" <th>40</th>\n",
" <td>Veribet</td>\n",
" <td>78.67.34.4</td>\n",
" </tr>\n",
" <tr>\n",
" <th>42</th>\n",
" <td>Duobam</td>\n",
" <td>88.66.251.21</td>\n",
" </tr>\n",
" <tr>\n",
" <th>43</th>\n",
" <td>Gembucket</td>\n",
" <td>213.0.180.184</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>960</th>\n",
" <td>Cardguard</td>\n",
" <td>24.115.30.137</td>\n",
" </tr>\n",
" <tr>\n",
" <th>961</th>\n",
" <td>Mat Lam Tam</td>\n",
" <td>42.85.216.70</td>\n",
" </tr>\n",
" <tr>\n",
" <th>962</th>\n",
" <td>Cookley</td>\n",
" <td>158.225.24.222</td>\n",
" </tr>\n",
" <tr>\n",
" <th>963</th>\n",
" <td>Toughjoyfax</td>\n",
" <td>29.135.145.71</td>\n",
" </tr>\n",
" <tr>\n",
" <th>965</th>\n",
" <td>Treeflex</td>\n",
" <td>243.43.239.48</td>\n",
" </tr>\n",
" <tr>\n",
" <th>966</th>\n",
" <td>Alphazap</td>\n",
" <td>181.64.49.73</td>\n",
" </tr>\n",
" <tr>\n",
" <th>969</th>\n",
" <td>Viva</td>\n",
" <td>135.182.127.91</td>\n",
" </tr>\n",
" <tr>\n",
" <th>970</th>\n",
" <td>Redhold</td>\n",
" <td>92.244.94.176</td>\n",
" </tr>\n",
" <tr>\n",
" <th>971</th>\n",
" <td>Ventosanzap</td>\n",
" <td>69.117.222.16</td>\n",
" </tr>\n",
" <tr>\n",
" <th>972</th>\n",
" <td>Sonair</td>\n",
" <td>234.245.67.53</td>\n",
" </tr>\n",
" <tr>\n",
" <th>973</th>\n",
" <td>Pannier</td>\n",
" <td>19.212.92.56</td>\n",
" </tr>\n",
" <tr>\n",
" <th>974</th>\n",
" <td>Wrapsafe</td>\n",
" <td>229.193.29.45</td>\n",
" </tr>\n",
" <tr>\n",
" <th>975</th>\n",
" <td>Viva</td>\n",
" <td>119.236.177.18</td>\n",
" </tr>\n",
" <tr>\n",
" <th>977</th>\n",
" <td>Subin</td>\n",
" <td>37.177.197.52</td>\n",
" </tr>\n",
" <tr>\n",
" <th>978</th>\n",
" <td>Greenlam</td>\n",
" <td>153.255.196.213</td>\n",
" </tr>\n",
" <tr>\n",
" <th>979</th>\n",
" <td>Ronstring</td>\n",
" <td>149.5.27.107</td>\n",
" </tr>\n",
" <tr>\n",
" <th>980</th>\n",
" <td>Solarbreeze</td>\n",
" <td>159.12.110.188</td>\n",
" </tr>\n",
" <tr>\n",
" <th>981</th>\n",
" <td>Kanlam</td>\n",
" <td>134.135.10.56</td>\n",
" </tr>\n",
" <tr>\n",
" <th>982</th>\n",
" <td>Domainer</td>\n",
" <td>94.65.143.216</td>\n",
" </tr>\n",
" <tr>\n",
" <th>983</th>\n",
" <td>Treeflex</td>\n",
" <td>210.25.222.227</td>\n",
" </tr>\n",
" <tr>\n",
" <th>984</th>\n",
" <td>Fintone</td>\n",
" <td>67.155.43.120</td>\n",
" </tr>\n",
" <tr>\n",
" <th>986</th>\n",
" <td>Hatity</td>\n",
" <td>100.126.230.220</td>\n",
" </tr>\n",
" <tr>\n",
" <th>987</th>\n",
" <td>Greenlam</td>\n",
" <td>94.125.191.33</td>\n",
" </tr>\n",
" <tr>\n",
" <th>988</th>\n",
" <td>Konklux</td>\n",
" <td>17.52.91.238</td>\n",
" </tr>\n",
" <tr>\n",
" <th>991</th>\n",
" <td>Toughjoyfax</td>\n",
" <td>38.7.57.13</td>\n",
" </tr>\n",
" <tr>\n",
" <th>994</th>\n",
" <td>Stringtough</td>\n",
" <td>221.13.25.173</td>\n",
" </tr>\n",
" <tr>\n",
" <th>995</th>\n",
" <td>Bitwolf</td>\n",
" <td>220.195.52.173</td>\n",
" </tr>\n",
" <tr>\n",
" <th>996</th>\n",
" <td>Opela</td>\n",
" <td>161.96.44.167</td>\n",
" </tr>\n",
" <tr>\n",
" <th>998</th>\n",
" <td>Bitchip</td>\n",
" <td>66.16.239.170</td>\n",
" </tr>\n",
" <tr>\n",
" <th>999</th>\n",
" <td>Stringtough</td>\n",
" <td>248.92.93.186</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>714 rows × 2 columns</p>\n",
"</div>"
],
"text/plain": [
" DEVICE ADRESSID\n",
"0 Overhold 31.77.57.23\n",
"1 Cardguard 64.167.22.255\n",
"3 Konklab 36.54.4.13\n",
"4 Y-Solowarm 210.198.154.120\n",
"5 Zoolab 48.61.22.191\n",
"6 Y-Solowarm 164.0.176.23\n",
"7 It 67.141.1.164\n",
"8 Kanlam 118.47.120.129\n",
"10 Subin 186.175.84.143\n",
"12 Solarbreeze 60.149.36.235\n",
"13 Treeflex 126.176.97.110\n",
"15 Viva 145.254.77.152\n",
"16 Bitchip 167.249.167.225\n",
"18 Opela 210.48.49.48\n",
"21 Bytecard 145.167.2.27\n",
"23 Redhold 239.135.39.170\n",
"24 Home Ing 215.40.29.99\n",
"25 Stringtough 51.122.73.33\n",
"26 Zathin 126.148.67.41\n",
"27 Cookley 4.44.114.39\n",
"28 Lotstring 132.122.66.5\n",
"29 Konklux 165.229.52.67\n",
"31 Bytecard 227.20.57.161\n",
"32 Kanlam 79.95.166.141\n",
"37 Sonsing 35.158.73.183\n",
"38 Y-Solowarm 194.137.174.193\n",
"39 Zathin 38.29.255.56\n",
"40 Veribet 78.67.34.4\n",
"42 Duobam 88.66.251.21\n",
"43 Gembucket 213.0.180.184\n",
".. ... ...\n",
"960 Cardguard 24.115.30.137\n",
"961 Mat Lam Tam 42.85.216.70\n",
"962 Cookley 158.225.24.222\n",
"963 Toughjoyfax 29.135.145.71\n",
"965 Treeflex 243.43.239.48\n",
"966 Alphazap 181.64.49.73\n",
"969 Viva 135.182.127.91\n",
"970 Redhold 92.244.94.176\n",
"971 Ventosanzap 69.117.222.16\n",
"972 Sonair 234.245.67.53\n",
"973 Pannier 19.212.92.56\n",
"974 Wrapsafe 229.193.29.45\n",
"975 Viva 119.236.177.18\n",
"977 Subin 37.177.197.52\n",
"978 Greenlam 153.255.196.213\n",
"979 Ronstring 149.5.27.107\n",
"980 Solarbreeze 159.12.110.188\n",
"981 Kanlam 134.135.10.56\n",
"982 Domainer 94.65.143.216\n",
"983 Treeflex 210.25.222.227\n",
"984 Fintone 67.155.43.120\n",
"986 Hatity 100.126.230.220\n",
"987 Greenlam 94.125.191.33\n",
"988 Konklux 17.52.91.238\n",
"991 Toughjoyfax 38.7.57.13\n",
"994 Stringtough 221.13.25.173\n",
"995 Bitwolf 220.195.52.173\n",
"996 Opela 161.96.44.167\n",
"998 Bitchip 66.16.239.170\n",
"999 Stringtough 248.92.93.186\n",
"\n",
"[714 rows x 2 columns]"
]
},
"execution_count": 5,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"planilha_1[cond]"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python [conda root]",
"language": "python",
"name": "conda-root-py"
},
"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.5.4"
}
},
"nbformat": 4,
"nbformat_minor": 2
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment