import requests, pandas as pd, numpy as np
from requests import session
from bs4 import BeautifulSoup
dfsi=pd.read_csv('dfsi.csv',sep=';')
dfsi2=pd.read_csv('dfsi2.csv',sep=';')
dfsi['nr_alkalmazottak']=dfsi['nr']
dfsi2['nr_arbevetel']=dfsi2['nr']
data=dfsi.set_index('Cégnév').join(dfsi2.set_index('Cégnév'),how='outer',lsuffix='_left', rsuffix='_right')
data['Cím']=data[['cms_right','cms_left']].T.ffill().bfill().T['cms_right']
data['Koord']=data[['coords_right','coords_left']].T.ffill().bfill().T['coords_right']
data['Kw']=data[['kws_right','kws_left']].T.ffill().bfill().T['kws_right']
data['Régió']=data[['region_right','region_left']].T.ffill().bfill().T['region_right']
data=data.drop(['0_left', 'region_left','nr_left', 'coords_left', 'kws_left', 'cms_left',
'0_right', 'region_right', 'nr_right', 'coords_right', 'kws_right', 'cms_right',
'Unnamed: 0_left','Unnamed: 0_right'],axis=1)
lat=[]
lon=[]
for i in range(len(data.index)):
k=data.loc[data.index[i]]['Koord'].replace("'",'').replace('[','')\
.replace(']','').replace(' ','').split(',')
lon.append(k[0])
lat.append(k[1])
data['Latitude']=lat
data['Longitude']=lon
data=data.drop('Koord',axis=1)
data
Alkalmazottak száma 2017 | Alkalmazottak száma 2016 | Alkalmazottak száma 2015 | Alkalmazottak száma 2014 | nr_alkalmazottak | Árbevétel 2017 (RON) | Árbevétel 2016 (RON) | Árbevétel 2015 (RON) | Árbevétel 2014 (RON) | nr_arbevetel | Cím | Kw | Régió | Latitude | Longitude | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Cégnév | |||||||||||||||
A M C SRL | NaN | NaN | NaN | NaN | NaN | 27 881 270 | 24 390 005 | 22 173 039 | 18 131 027 | 20.0 | 527100, H\xeddv\xe9g, Rom\xe1n u., 203\t | ['nagykereskedelem', '', '"'] | also-haromszek | 25.5892160 | 45.8391350 |
ABC IMPEX SRL | 145 | 132 | 132 | 120 | 20.0 | 36 539 530 | 33 071 733 | 31 956 498 | 30 363 973 | 20.0 | 535600, Sz\xe9kelyudvarhely, R\xe1k\xf3czi Fer... | ['csomagoloanyagok', 'nyomdak', '', '"'] | udvarhelyszek | 25.290034 | 46.289768 |
ABRAZIV SRL | NaN | NaN | NaN | NaN | NaN | 5 483 275 | 0 | 0 | 0 | 49.0 | 535500, Gyergy\xf3szentmikl\xf3s, \xc1llom\xe1... | ['szerszamgepek', '', '"'] | gyergyoszek | 25.5751650 | 46.7174250 |
ADIMAG COM IMPEX SRL | NaN | NaN | NaN | NaN | NaN | 57 106 224 | 48 827 471 | 47 437 389 | 44 031 925 | 36.0 | 540190, Marosv\xe1s\xe1rhely, Szabads\xe1g u.,... | ['belsoepiteszeti-anyagok', 'epitoanyagok', 'f... | marosszek | 24.548819 | 46.537905 |
AFEROM TRANS SRL | NaN | NaN | NaN | NaN | NaN | 13 341 348 | 13 592 056 | 12 531 454 | 12 096 492 | 30.0 | 530153, Cs\xedkszereda, Ak\xe1cfa u., 3A\t | ['belfoldi-teherfuvarozas', 'kulfoldi-teherfuv... | csikszek | 25.786071 | 46.361412 |
AGER SRL | NaN | NaN | NaN | NaN | NaN | 14 549 472 | 10 629 185 | 10 511 810 | 10 322 534 | 45.0 | 525300, Bodzafordul\xf3, Kemping u., 29\t | ['muanyag-feldolgozas', '', '"'] | also-haromszek | 26.0291420 | 45.6671960 |
AGM ECO CORPORATE SRL | 68 | 36 | 2 | 6 | 50.0 | NaN | NaN | NaN | NaN | NaN | 535600, Sz\xe9kelyudvarhely, Cip\xe9szek u., 15\t | ['pekaru', 'pekaru-es-peksutemenyek', 'pekseg-... | udvarhelyszek | 25.3117290 | 46.3160230 |
AGRICO M SRL | NaN | NaN | NaN | NaN | NaN | 9 400 301 | 7 643 351 | 6 376 915 | 5 701 521 | 30.0 | 525400, K\xe9zdiv\xe1s\xe1rhely, Ady Endre u.,... | ['burgonya-vetomag', 'b\\xfazavetomag', 'etkez... | felso-haromszek | 26.1326700 | 46.0066320 |
AGRO PAN STAR SRL | NaN | NaN | NaN | NaN | NaN | 19 033 775 | 20 905 425 | 13 372 911 | 2 845 996 | 34.0 | 520019, Sepsiszentgy\xf6rgy, Dacz\xf3 u., 6\t | ['kenyer', 'kenyer-es-pekaru', 'peksegek', 'pe... | also-haromszek | 25.7932580 | 45.8700010 |
AGRO ROM IMPEX SRL | NaN | NaN | NaN | NaN | NaN | 52 824 141 | 43 731 414 | 29 093 421 | 21 671 970 | 43.0 | 540198, Marosv\xe1s\xe1rhely, Rakt\xe1r u., 8/... | ['kereskedelem', '', '"'] | marosszek | 24.531839 | 46.526013 |
AGROPROD CARTOF SRL | NaN | NaN | NaN | NaN | NaN | 6 144 223 | 9 738 941 | 6 257 500 | 695 186 | 43.0 | 525400, K\xe9zdiv\xe1s\xe1rhely, \xc1llom\xe1s... | ['zoldseg\\x96gyumolcs-nagybani-eladas', '', '"'] | felso-haromszek | 26.1377110 | 45.9939110 |
AGROWEST BMB SRL | NaN | NaN | NaN | NaN | NaN | 10 271 662 | 10 107 975 | 9 992 347 | 11 025 678 | 29.0 | 527070, Csern\xe1ton, F\xf5 u., 654\t | ['mezogazdasagi-gepek-es-szerszamok', 'szersza... | felso-haromszek | 26.034927 | 45.959291 |
AIRQUEE SRL | 194 | 192 | 161 | 123 | 13.0 | 20 263 081 | 19 580 909 | 17 489 284 | 12 506 711 | 31.0 | 520073, Sepsiszentgy\xf6rgy, Term\xe9s u., 3\t | ['felf\\xfajhato-szerkezetek', '', '"'] | also-haromszek | 25.8139480 | 45.8632520 |
ALEX & CO SA | 143 | 144 | 174 | 173 | 17.0 | 16 942 918 | 15 610 215 | 15 773 075 | 13 850 999 | 39.0 | 527075, K\xf6k\xf6s, F\xf5 u., 34\t | ['koporsok', 'koporsokeszites', '', '"'] | also-haromszek | 25.804319 | 45.773468 |
ALIAT AUTO SRL | NaN | NaN | NaN | NaN | NaN | 104 993 558 | 91 134 374 | 69 603 591 | 60 851 171 | 23.0 | 540237, Marosv\xe1s\xe1rhely, D\xf3zsa Gy\xf6r... | ['autokereskedelem', '', '"'] | marosszek | 24.540352 | 46.526409 |
ALLCOLORS SERV SRL | 228 | 172 | 154 | 126 | 39.0 | NaN | NaN | NaN | NaN | NaN | 547612, Vidr\xe1tszeg, , 1/G/5\t | ['elektrosztatikus-porfestes', '', '"'] | marosszek | 24.4163430 | 46.4717940 |
ALMI ROM SRL | NaN | NaN | NaN | NaN | NaN | 18 494 022 | 14 487 619 | 12 418 359 | 11 362 558 | 9.0 | 535500, Gyergy\xf3szentmikl\xf3s, Kossuth Lajo... | ['elelmiszer-adalekanyagok', '', '"'] | gyergyoszek | 25.5755780 | 46.7144880 |
ALT TECHNOLOGIES TRANSYLVANIA SRL | 198 | 186 | 176 | 156 | 12.0 | 54 588 122 | 54 277 063 | 53 072 027 | 37 934 554 | 14.0 | 537165, Farkaslaka, , DN 13A 648\t | ['legzsakgyartas', '', '"'] | udvarhelyszek | 25.216672 | 46.385158 |
ALUTUS SA | 47 | 51 | 41 | 51 | 44.0 | NaN | NaN | NaN | NaN | NaN | 530154, Cs\xedkszereda, Hargita u., 108/A\t | ['nyomdak', '', '"'] | csikszek | 25.7723090 | 46.3694580 |
ALZOCOM-TRANSPORT SRL | NaN | NaN | NaN | NaN | NaN | 16 841 974 | 13 720 358 | 12 785 106 | 10 426 539 | 27.0 | 530112, Cs\xedkszereda, M\xfcller L\xe1szl\xf3... | ['belfoldi-aruszallitas', 'szallitas', '', '"'] | csikszek | 25.8072970 | 46.3569650 |
AMECO RENEWABLE ENERGY SRL | 34 | 30 | 59 | 83 | 44.0 | 11 285 020 | 7 623 894 | 19 744 193 | 21 180 409 | 19.0 | 537130, Gyergy\xf3alfalu, , 1\t | ['fafeldolgozas', '', '"'] | gyergyoszek | 25.574541 | 46.718254 |
AMIGO & INTERCOST SRL | 786 | 724 | 620 | 631 | 3.0 | 381 852 182 | 338 871 404 | 290 602 580 | 276 276 037 | 1.0 | 535600, Sz\xe9kelyudvarhely, F\xe1sok u., 1A\t | ['ital-nagykereskedes', 'szeszes-ital-nagykere... | udvarhelyszek | 25.304518 | 46.314643 |
AMIGO SRL | NaN | NaN | NaN | NaN | NaN | 28 169 310 | 22 490 359 | 19 643 902 | 17 290 545 | 15.0 | 530144, Cs\xedkszereda, Vad\xe1sz u., 9\t | ['ital-nagykereskedes', 'szeszes-italok', '', ... | csikszek | 25.7903730 | 46.3597460 |
APC UNIVERSAL PARTNER SRL | 139 | 1 | - | - | 22.0 | 17 535 928 | 0 | - | - | 49.0 | 535600, Sz\xe9kelyudvarhely, Lakatosok u., 8C/... | ['barkacs--es-vastermekek', '', '"'] | udvarhelyszek | 25.3133555 | 46.3169344 |
APEMIN TUSNAD SA | 143 | 136 | 137 | 145 | 12.0 | 51 210 314 | 45 394 719 | 41 361 947 | 37 154 570 | 5.0 | 537336, \xdajtusn\xe1d, \xc1llom\xe1s u., 599\t | ['asvanyviz-palackozas', '', '"'] | csikszek | 25.9119480 | 46.2068660 |
AQUA CALIMANI SRL | 34 | 35 | 37 | 40 | 45.0 | NaN | NaN | NaN | NaN | NaN | 535700, Marosh\xe9v\xedz, Bălcescu, Nicol... | ['asvanyviz-forgalmazas', '', '"'] | gyergyoszek | 25.350802 | 46.922981 |
AQUA NOVA HARGITA SRL | 108 | 109 | 110 | 110 | 27.0 | NaN | NaN | NaN | NaN | NaN | 535600, Sz\xe9kelyudvarhely, Ha\xe1z Rezs\xf5 ... | ['viz--es-csatornahalozat', 'vizszolgaltatas',... | udvarhelyszek | 25.309561 | 46.314822 |
ARAMIS RO SRL | 152 | 141 | 143 | 151 | 18.0 | NaN | NaN | NaN | NaN | NaN | 535600, Sz\xe9kelyudvarhely, Ifj\xfas\xe1g bej... | ['biztonsagtechnika', 'orzo-vedo-szolgaltataso... | udvarhelyszek | 25.306127 | 46.311619 |
ARCON SRL | 101 | 151 | 159 | 161 | 25.0 | 83 022 166 | 110 394 138 | 124 968 490 | 120 246 976 | 9.0 | 520027, Sepsiszentgy\xf6rgy, K\xf5r\xf6si Csom... | ['polisztiren-gyartas', '', '"'] | also-haromszek | 25.789279 | 45.868490 |
ARTEMOB INTERNATIONAL SRL | 579 | 511 | 508 | 512 | 6.0 | 134 053 580 | 96 337 149 | 64 999 036 | 62 058 764 | 16.0 | 545500, Szov\xe1ta, Parajdi u., 137\t | ['b\\xfatorasztalosok', 'b\\xfatorforgalmazas'... | marosszek | 25.0750100 | 46.5756230 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
TRICOMSERV SA | 75 | 87 | 87 | 87 | 32.0 | 11 980 197 | 15 453 556 | 17 599 455 | 16 389 103 | 49.0 | 520033, Sepsiszentgy\xf6rgy, Cigaretta u., 46\t | ['hidroforok-gyartasa', 'viz--es-csatornahaloz... | also-haromszek | 25.786644 | 45.855076 |
TRIMEX SERVICII SRL | NaN | NaN | NaN | NaN | NaN | 8 928 863 | 13 535 129 | 10 185 155 | 8 239 848 | 31.0 | 527160, Torja, F\xf5 u., 7\t | ['epitoanyagok', 'mutragya', '', '"'] | felso-haromszek | 26.063746 | 46.040239 |
TRIO IMPEX SRL | 72 | 81 | 74 | 66 | 38.0 | NaN | NaN | NaN | NaN | NaN | 527035, Sepsibodok, F\xf5 u., 291/A\t | ['nemzetkozi-aruszallitas', '', '"'] | also-haromszek | 25.850000 | 45.950000 |
TROPICAL SRL | NaN | NaN | NaN | NaN | NaN | 7 698 693 | 6 656 268 | 7 113 164 | 8 511 397 | 34.0 | 537130, Gyergy\xf3alfalu, Bakta u., 150/A\t | ['bel--es-kulfoldi-arufuvarozas', 'kulfoldi-sz... | gyergyoszek | 25.500000 | 46.700000 |
TURISM COVASNA SA | 105 | 99 | 83 | 84 | 13.0 | 12 120 757 | 11 254 632 | 9 665 694 | 10 079 313 | 23.0 | 525200, Kov\xe1szna, 1918. December 1. u., 1-2\t | ['gyogykezelesek', 'hotelek', '', '"'] | felso-haromszek | 26.166816 | 45.844993 |
TUSNAD SA | 94 | 94 | 93 | 91 | 20.0 | NaN | NaN | NaN | NaN | NaN | 535100, Tusn\xe1df\xfcrd\xf5, Olt u., 87\t | ['barok', 'ettermek', 'hotelek', 'szallodak', ... | csikszek | 25.860563 | 46.146941 |
UDVARHELYI HIRADO SRL | 195 | 299 | 281 | 271 | 13.0 | NaN | NaN | NaN | NaN | NaN | 535600, Sz\xe9kelyudvarhely, Szentimre u., 17/5\t | ['filmkidolgozas', 'fotokonyv', 'hirportalok',... | udvarhelyszek | 25.302565 | 46.300650 |
UNIC TRIO SRL | 54 | 56 | 55 | 54 | 37.0 | NaN | NaN | NaN | NaN | NaN | 535200, Bal\xe1nb\xe1nya, 1918. December 1. u.... | ['kenyer-es-pekaru', 'pektermekek', '', '"'] | csikszek | 25.8063930 | 46.6556100 |
UNIO LUNCA SRL | 74 | 70 | 72 | 68 | 27.0 | NaN | NaN | NaN | NaN | NaN | 537152, S\xf6t\xe9tpatak, F\xf5 u., 138\t | ['peksegek', '', '"'] | csikszek | 25.9541230 | 46.5592880 |
UNIPREST INSTAL SRL | NaN | NaN | NaN | NaN | NaN | 111 860 492 | 92 938 901 | 81 287 908 | 65 084 212 | 21.0 | 540240, Marosv\xe1s\xe1rhely, Rakt\xe1r u., 20\t | ['futestechnika', 'viz-gaz--es-futestechnika',... | marosszek | 24.531839 | 46.526013 |
UPS DISTRIBUTION SRL | NaN | NaN | NaN | NaN | NaN | 9 939 657 | 8 901 784 | 8 323 927 | 8 549 844 | 22.0 | 535500, Gyergy\xf3szentmikl\xf3s, G\xe1bor \xc... | ['szamitogepek', 'szoftverek', '', '"'] | gyergyoszek | 25.602353 | 46.715585 |
VALDEK IMPEX SRL | 121 | 130 | 155 | 178 | 20.0 | 22 250 435 | 38 466 262 | 98 072 334 | 64 206 026 | 27.0 | 520080, Sepsiszentgy\xf6rgy, 1918. December 1.... | ['epitkezes', 'utak-es-hidak', '', '"'] | also-haromszek | 25.7893400 | 45.8659360 |
VALKES SRL | 788 | 886 | 1 007 | 1 416 | 1.0 | 30 626 442 | 32 843 165 | 31 885 928 | 36 661 502 | 18.0 | 520077, Sepsiszentgy\xf6rgy, Păiş Da... | ['kabelek', '', '"'] | also-haromszek | 25.806651 | 45.855129 |
VBH ROMCOM SRL | NaN | NaN | NaN | NaN | NaN | 54 520 221 | 49 332 868 | 46 612 739 | 39 811 744 | 40.0 | 540210, Marosv\xe1s\xe1rhely, D\xf3zsa Gy\xf6r... | ['ajto-ablakvasalat', 'femalkatresz-gyartas', ... | marosszek | 24.540352 | 46.526409 |
VEL FUNGO SRL | NaN | NaN | NaN | NaN | NaN | 26 121 250 | 14 907 258 | 8 315 513 | 7 089 148 | 17.0 | 537295, Mad\xe9falva, , 92/B\t | ['gombatermesztes', '', '"'] | csikszek | 25.7531520 | 46.4374820 |
VIADUCT SRL | 151 | 157 | 135 | 109 | 19.0 | 23 616 230 | 26 571 799 | 30 606 802 | 27 305 770 | 35.0 | 535600, Sz\xe9kelyudvarhely, Bethlenfalvi \xfa... | ['epitkezes', 'ipari-csarnokok-epitese', 'kana... | udvarhelyszek | 25.3207769 | 46.3228211 |
VIASTEIN SRL | NaN | NaN | NaN | NaN | NaN | 17 168 209 | 15 295 661 | 15 236 830 | 12 024 620 | 38.0 | 527075, K\xf6k\xf6s, Illyefalvi u., 499\t | ['beton-csatornaelemek', 'beton-epitoelemek', ... | also-haromszek | 25.7997160 | 45.7707810 |
VIASTRADA SRL | 34 | 27 | 23 | 19 | 46.0 | 12 107 718 | 9 049 705 | 7 945 073 | 8 452 400 | 18.0 | 537309, Teker\xf5patak, , 143/B\t | ['bel--es-kulfoldi-arufuvarozas', 'foldmunkala... | gyergyoszek | 25.5981230 | 46.6960280 |
VIKING SRL | 94 | 94 | 94 | 72 | 31.0 | NaN | NaN | NaN | NaN | NaN | 535600, Sz\xe9kelyudvarhely, Tompa L\xe1szl\xf... | ['biztonsagi-rendszerek', 'epitkezes', 'foldel... | udvarhelyszek | 25.2939758 | 46.3073295 |
VILLEX SRL | 28 | 23 | 21 | 19 | 47.0 | NaN | NaN | NaN | NaN | NaN | 525400, K\xe9zdiv\xe1s\xe1rhely, Tur\xf3czi M\... | ['villamossagi-berendezesek', '', '"'] | felso-haromszek | 26.135058 | 45.999479 |
WABERER S ROMANIA SA | 70 | 70 | 73 | 159 | 30.0 | 30 033 637 | 39 478 024 | 45 080 527 | 70 231 898 | 13.0 | 530152, Cs\xedkszereda, Hargita u., 101\t | ['szallitas', '', '"'] | csikszek | 25.7484570 | 46.3708030 |
WALOR RO SRL | 215 | 176 | 163 | 157 | 12.0 | 103 680 748 | 79 335 550 | 57 782 546 | 48 459 276 | 6.0 | 520077, Sepsiszentgy\xf6rgy, \xc9p\xedt\xf5k u... | ['cnc-megmunkalas', '', '"'] | also-haromszek | 25.818515 | 45.861235 |
WEEKEND SRL | NaN | NaN | NaN | NaN | NaN | 19 455 456 | 20 504 368 | 15 555 277 | 14 800 679 | 32.0 | 520027, Sepsiszentgy\xf6rgy, Olt u., 7\t | ['hoallo-uvegek', 'kandallok', '', '"'] | also-haromszek | 25.795791 | 45.865823 |
WERNETTO SRL | 51 | 62 | 45 | 45 | 28.0 | 12 704 346 | 6 602 913 | 1 813 259 | 1 813 259 | 17.0 | 535500, Gyergy\xf3szentmikl\xf3s, Dr. Jakab An... | ['epitkezes', '', '"'] | gyergyoszek | 25.5855270 | 46.7212110 |
WIKEND FOREST IMPEX SRL | NaN | NaN | NaN | NaN | NaN | 6 952 665 | 7 195 045 | 7 843 382 | 5 227 975 | 38.0 | 537250, Gyergy\xf3remete, Bal\xe1s G\xe1bor u.... | ['\\xfat--es-hidepites', '', '"'] | gyergyoszek | 25.4426580 | 46.7963040 |
WONDERLAND SRL | 29 | 28 | 29 | 27 | 44.0 | NaN | NaN | NaN | NaN | NaN | 527160, Torja, F\xf5 u., 225\t | ['epitkezes', '', '"'] | felso-haromszek | 26.063746 | 46.040239 |
ZABOLA ESTATE SRL | 31 | 25 | 23 | 20 | 39.0 | NaN | NaN | NaN | NaN | NaN | 527190, Zabola, , 437\t | ['hotelek', 'kastelyszallo', 'vendeglatas', ''... | felso-haromszek | 26.1980287 | 45.8915563 |
ZAMBELLI METAL SRL | 179 | 162 | 163 | 136 | 15.0 | 42 016 920 | 34 591 562 | 27 891 895 | 28 480 483 | 15.0 | 520077, Sepsiszentgy\xf6rgy, \xc9p\xedt\xf5k u... | ['badogosmunkak', '', '"'] | also-haromszek | 25.818515 | 45.861235 |
ZARAH MODEN SRL | 785 | 778 | 839 | 826 | 2.0 | 148 531 005 | 141 121 629 | 139 752 712 | 144 887 360 | 2.0 | 525400, K\xe9zdiv\xe1s\xe1rhely, B\xe9ke u., 27\t | ['nadraggyartas', 'textilipar', '', 'INDUSTRIA... | felso-haromszek | 26.1359670 | 45.9969390 |
ZENCO TRANS SRL | 37 | 33 | 33 | 30 | 40.0 | NaN | NaN | NaN | NaN | NaN | 535700, Marosh\xe9v\xedz, , 2\t | ['aruszallitas', '', '"'] | gyergyoszek | 25.3534040 | 46.9260300 |
424 rows × 15 columns
manual=data[data['Longitude']=='']
data=data[data['Longitude']!='']
data.to_excel('data.xlsx')
manual.to_excel('manual.xlsx')
sectors=pd.read_excel('sectors.xlsx')
sectormap={}
repl={'á':'a','é':'e','í':'i','ó':'o','ú':'u','ü':'u','ű':'u','ő':'o','ö':'o'}
for s in sectors.columns:
for k in sectors[s].values:
for j in str(k).replace(' ','').split(','):
sectormap[j]=s
for c in repl:
j=j.replace(c,repl[c])
sectormap[j]=s
valid=[]
kws=[]
cont=True
ki=-1
for i in range(len(data.index)):
ks=data.loc[data.index[i]]['Kw'].replace("'",'').replace('[','')\
.replace(']','').replace(' ','').lower().split(',')[:-2]
for k in ks:
if k in sectormap:
kws.append(sectormap[k])
break
k=k.replace('-','')
if k in sectormap:
kws.append(sectormap[k])
break
if len(kws)<i-1:
print('ERROR',ks,i)
break
else:
#print('SUCCES',kws[-1],ks)
valid.append([data.index[i],kws[-1],ks])
pd.DataFrame(valid).to_excel('valid.xlsx')
Manual overwrite