8000 Létrehozva a Colaboratory használatával · klajosw/python@41b4074 · GitHub
[go: up one dir, main page]
Skip to content

Commit 41b4074

Browse files
klajoswklajosw
committed
Létrehozva a Colaboratory használatával
1 parent ebd08c0 commit 41b4074

File tree

1 file changed

+279
-13
lines changed

1 file changed

+279
-13
lines changed

‎kl_py_pandas_xls_chart.ipynb

Lines changed: 279 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
{
1414
"cell_type": "markdown",
1515
"metadata": {
16-
"id": "R5ZZP4ewA8mP"
16+
"id": "_wobQks6g85J"
1717
},
1818
"source": [
1919
"<p align=\"left\"> \n",
@@ -33,24 +33,24 @@
3333
"\n",
3434
"https://github.com/klajosw/\n",
3535
"\n",
36+
"Forrás:\n",
37+
"https://xlsxwriter.readthedocs.io\n",
38+
"\n",
3639
"---"
3740
]
3841
},
3942
{
4043
"cell_type": "code",
4144
"execution_count": null,
4245
"metadata": {
43-
"id": "Ekvj4iSxA8mV"
46+
"id": "cDyz0uPZg85O"
4447
},
4548
"outputs": [],
4649
"source": [
4750
"import pandas as pd\n",
48-
"from pandas import ExcelWriter\n",
49-
"from pandas import ExcelFile\n",
5051
"import datetime\n",
5152
"import matplotlib.pyplot as plt \n",
5253
"\n",
53-
"\n",
5454
"dt = datetime.datetime.now() - datetime.timedelta(days=1) ## tegnapi nap\n",
5555
"datum = '{:%Y-%m-%d}'.format(dt)\n",
5656
"\n",
@@ -68,22 +68,26 @@
6868
"df = pd.DataFrame(data, index=index)\n",
6969
"\n",
7070
"#Excel objektum létrehozása\n",
71-
"writer = pd.ExcelWriter(r'kl_dojo_'+datum+'_kesz.xlsx')\n",
72-
"df.to_excel(writer, sheet_name='Munka1', engine='xlsxwriter')\n",
71+
"writer = pd.ExcelWriter(r'c:\\Users\\User\\Documents\\data\\xls\\kl_dojo_'+datum+'_kesz.xlsx')\n",
72+
"#writer = pd.ExcelWriter(r'c:\\Users\\User\\Documents\\data\\xls\\kl_farms2.xlsx', engine='xlsxwriter')\n",
73+
"df.to_excel(writer, sheet_name='Munka1')\n",
7374
"\n",
7475
"#munka fizet és munkalap létrehozása\n",
7576
"workbook = writer.book\n",
7677
"worksheet = writer.sheets['Munka1']\n",
7778
"\n",
7879
"#diagram készítése\n",
79-
"chart = workbook.add_chart({'type': 'column'})\n",
80+
"chart = workbook.add_chart({'type': 'column'}) \n",
81+
"## diagram tipusok : area, bar, column, line, pie, doughnut, scatter, stock, radar\n",
82+
"\n",
8083
"\n",
8184
"# adat betöltés ciklus\n",
8285
"for col_num in range(1, len(dojo_1) + 1):\n",
8386
" chart.add_series({\n",
8487
" 'name': ['Munka1', 0, col_num],\n",
8588
" 'categories': ['Munka1', 1, 0, 4, 0],\n",
8689
" 'values': ['Munka1', 1, col_num, 4, col_num],\n",
90+
"# 'fill': {'color': brews['Set1'][col_num - 1]},\n",
8791
" 'overlap':-10,\n",
8892
" })\n",
8993
" \n",
@@ -101,12 +105,274 @@
101105
"# writer.close()\n",
102106
"\n",
103107
"#plt.plot(df)\n",
104-
"df.plot(kind='bar', color=['red','blue','green','orange','yellow'])\n",
105-
"df.plot(kind='line', color=['red','blue','green','orange','yellow'])\n",
106-
"df.plot(kind='bar', color=['red','blue','green','orange','yellow'])\n",
107-
"df.plot(kind='barh',color=['red','blue','green','orange','yellow'])\n",
108+
"df.plot(kind='line', color=['red','blue','green','orange','yellow'], label = \"line 1\")\n",
109+
"df.plot(kind='bar', color=['red','blue','green','orange','yellow'], label = \"line 2\")\n",
110+
"df.plot(kind='barh', color=['red','blue','green','orange','yellow'], label = \"line 3\")\n",
108111
"df.plot(kind='hist', color=['red','blue','green','orange','yellow'])\n",
109-
"df.plot(kind='area', color=['red','blue','green','orange','yellow'])"
112+
"df.plot(kind='area', color=['red','blue','green','orange','yellow'])\n",
113+
"df.plot(kind='density', color=['red','blue','green','orange','yellow'])\n",
114+
"df.plot(kind='hist', color=['red','blue','green','orange','yellow'])\n",
115+
"\n",
116+
"plt.legend()\n",
117+
"plt.show()"
118+
]
119+
},
120+
{
121+
"cell_type": "code",
122+
"execution_count": null,
123+
"metadata": {
124+
"id": "KI6-CUcdg85R"
125+
},
126+
"outputs": [],
127+
"source": [
128+
"import xlsxwriter\n",
129+
"\n",
130+
"#Excel objektum létrehozása, munkafüzet, munkalap\n",
131+
"workbook = xlsxwriter.Workbook(r'c:\\Users\\User\\Documents\\data\\xls\\xls_demo.xlsx')\n",
132+
"worksheet = workbook.add_worksheet()\n",
133+
"\n",
134+
"# cellára állás\n",
135+
"worksheet.set_column('A:A', 20)\n",
136+
"\n",
137+
"# formázások : bold format / vastagított betűs írás\n",
138+
"boldf = workbook.add_format({'bold': True})\n",
139+
"bif = workbook.add_format({'bold': True, 'italic': True})\n",
140+
"moneyf = workbook.add_format({'num_format': 'FT#,##0'})\n",
141+
"datef = workbook.add_format({'num_format': 'yyyy.m.d'})\n",
142+
"\n",
143+
"# egyszerű szöveg írása\n",
144+
"worksheet.write('A1', 'Hello világ')\n",
145+
"worksheet.write(0, 0, 'Hello világ') ## ugyan az mint az előző sor (csak más cella címzési mód)\n",
146+
"\n",
147+
"\n",
148+
"# formázott szöveg\n",
149+
"worksheet.write('A2', 'Hello világ vastaggal', boldf) ## formázott írás : szövegkiemelés /vastagitás\n",
150+
"\n",
151+
"# szaámok írása cellákba\n",
152+
"worksheet.write(2, 0, 123)\n",
153+
"worksheet.write(3, 0, 123.456, moneyf) ## formázott írás : pénz formátum\n",
154+
"worksheet.write(4, 0, '1962.03.14', datef ) ## formázott írás : dátum\n",
155+
"worksheet.write(5, 0, '=SIN(PI()/4)') ## képlet\n",
156+
"worksheet.write(6, 0, '=DATEVALUE(\"1962.03.14\")')\n",
157+
"worksheet.write('A7', '{=SUM(A1:B1*A2:B2)}')\n",
158+
"\n",
159+
"# tabla formázás 1\n",
160+
"worksheet.write('B8', 'Oszlop1')\n",
161+
"worksheet.write('B9', 42)\n",
162+
"worksheet.write('B10', 5)\n",
163+
"# --\n",
164+
"worksheet.write('C8', 'Oszlop2')\n",
165+
"worksheet.write('C9', 142)\n",
166+
"worksheet.write('C10', 15)\n",
167+
"# --\n",
168+
"worksheet.add_table('B8:C10')\n",
169+
"#----\n",
170+
"\n",
171+
"\n",
172+
"# tabla formázás 2.\n",
173+
"data = [\n",
174+
" ['Python', 10000, 5000, 8000, 6000],\n",
175+
" ['PHP', 2000, 3000, 4000, 5000],\n",
176+
" ['JavaScript', 6000, 6000, 6500, 6000],\n",
177+
" ['HTML', 500, 300, 200, 700],\n",
178+
"]\n",
179+
"formula = '=SUM(Adatok[@[Point 1]:[Point 4]])'\n",
180+
"worksheet.add_table('J3:O7', {'data': data, # forrás adat\n",
181+
" 'first_column': True, # első oszlop kiemelése \n",
182+
" 'last_column': True, # utolsó oszlop kiemelése\n",
183+
" 'total_row': 1, # öszegzés oszlop utolsó sorba\n",
184+
" 'name': 'Adatok', # tabla adat elnevezése\n",
185+
" 'style': 'Table Style Light 11', # stilus beállítása\n",
186+
" 'columns': [{'header': 'Pr nev'},\n",
187+
" {'header': 'Point 1', 'total_function': 'sum'},\n",
188+
" {'header': 'Point 2', 'total_function': 'sum'},\n",
189+
" {'header': 'Point 3', 'total_function': 'sum'},\n",
190+
" {'header': 'Point 4', 'total_function': 'sum'},\n",
191+
" {'header': 'Össz', 'formula': formula,'total_function': 'sum'},\n",
192+
" ]})\n",
193+
"\n",
194+
"\n",
195+
"\n",
196+
"\n",
197+
"# kép beszúrása cellába\n",
198+
"worksheet.insert_image('C2', r'c:\\Users\\User\\Downloads\\pok2.jpg')\n",
199+
"\n",
200+
"workbook.close()"
201+
]
202+
},
203+
{
204+
"cell_type": "code",
205+
"execution_count": null,
206+
"metadata": {
207+
"id": "6NUC02XZg85S"
208+
},
209+
"outputs": [],
210+
"source": [
211+
"import pandas as pd\n",
212+
"\n",
213+
"data = [ ## adatok 1.\n",
214+
" ['Python', 10000, 5000, 8000, 6000],\n",
215+
" ['PHP', 2000, 3000, 4000, 5000],\n",
216+
" ['JavaScript', 6000, 6000, 6500, 6000],\n",
217+
" ['HTML', 500, 300, 200, 700],\n",
218+
"]\n",
219+
"\n",
220+
"df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]}) ## adatok 2.\n",
221+
"\n",
222+
"# excel írás előkészítése\n",
223+
"writer = pd.ExcelWriter(r'c:\\Users\\User\\Documents\\data\\xls\\xls_demo2.xlsx', engine='xlsxwriter')\n",
224+
"\n",
225+
"\n",
226+
"# dataFrame munkalapra írás \n",
227+
"df.to_excel(writer, sheet_name='Munka1') \n",
228+
"#df.to_excel(writer, sheet_name='Munka1', startrow=1, header=True, index=True)\n",
229+
"\n",
230+
"formatsz = workbook.add_format({'num_format': '0%'})\n",
231+
"\n",
232+
"# Excel o bjektum létrehozása \n",
233+
"workbook = writer.book\n",
234+
"worksheet = writer.sheets['Munka1']\n",
235+
"\n",
236+
"# adatrács formázása\n",
237+
"worksheet.conditional_format(1, max_col, max_row, max_col, {'type': '3_color_scale'})\n",
238+
"#worksheet.set_column(1, 1, None, formatsz)\n",
239+
"\n",
240+
"chart = workbook.add_chart({'type': 'column'}) # diagram objektum példányosítás (oszlop diagram ként)\n",
241+
"\n",
242+
"(max_row, max_col) = df.shape # max sor és oszlop kikérése\n",
243+
"chart.add_series({'values': ['Munka1', 1, 1, max_row, 1]}) # diagram rajzolás eréktartomány megadása\n",
244+
"worksheet.insert_chart(1, 3, chart) # diagram rajz pozició megadása\n",
245+
"\n",
246+
"writer.save()\n",
247+
"# writer.close()"
248+
]
249+
},
250+
{
251+
"cell_type": "code",
252+
"execution_count": null,
253+
"metadata": {
254+
"id": "8g1iOI-og85T"
255+
},
256+
"outputs": [],
257+
"source": [
258+
"import pandas as pd\n",
259+
"import matplotlib.pyplot as plt \n",
260+
"\n",
261+
"# minta adat generálás\n",
262+
"dojo_1 = {'Pyton': 20, 'Java': 19, 'C#': 21, 'JS': 13, 'HTML': 18}\n",
263+
"dojo_2 = {'Pyton': 25, 'Java': 22, 'C#': 20, 'JS': 17, 'HTML': 10}\n",
264+
"dojo_3 = {'Pyton': 27, 'Java': 17, 'C#': 12, 'JS': 14, 'HTML': 23}\n",
265+
"dojo_4 = {'Pyton': 24, 'Java': 20, 'C#': 15, 'JS': 15, 'HTML': 11}\n",
266+
"\n",
267+
"# adat és index összeállítás\n",
268+
"data = [dojo_1, dojo_2, dojo_3, dojo_4]\n",
269+
"index = ['Dojo 1', 'Dojo 2', 'Dojo 3', 'Dojo 4']\n",
270+
"\n",
271+
"# Pandas dataframe létrehozása minta adatokból\n",
272+
"df = pd.DataFrame(data, index=index)\n",
273+
"\n",
274+
"\n",
275+
"writer = pd.ExcelWriter(r'c:\\Users\\User\\Documents\\data\\xls\\xls_demo3.xlsx', engine='xlsxwriter')\n",
276+
"df.to_excel(writer, sheet_name='Munka1', startrow=1, header=False, index=False)\n",
277+
"workbook = writer.book\n",
278+
"worksheet = writer.sheets['Munka1']\n",
279+
"(max_row, max_col) = df.shape\n",
280+
"column_settings = [{'header': column} for column in df.columns]\n",
281+
"worksheet.add_table(0, 0, max_row, max_col - 1, {'columns': column_settings})\n",
282+
"worksheet.set_column(0, max_col - 1, 12)\n",
283+
"\n",
284+
"\n",
285+
"\n",
286+
"writer.save()\n",
287+
"# writer.close()\n",
288+
"\n",
289+
"plt.plot(df)"
290+
]
291+
},
292+
{
293+
"cell_type": "code",
294+
"execution_count": null,
295+
"metadata": {
296+
"id": "Qcb1haeOg85T"
297+
},
298+
"outputs": [],
299+
"source": [
300+
"import pandas as pd\n",
301+
"\n",
302+
"# minta adat generálás\n",
303+
"dojo_1 = {'Pyton': 20, 'Java': 19, 'C#': 21, 'JS': 13, 'HTML': 18}\n",
304+
"dojo_2 = {'Pyton': 25, 'Java': 22, 'C#': 20, 'JS': 17, 'HTML': 10}\n",
305+
"dojo_3 = {'Pyton': 27, 'Java': 17, 'C#': 12, 'JS': 14, 'HTML': 23}\n",
306+
"dojo_4 = {'Pyton': 24, 'Java': 20, 'C#': 15, 'JS': 15, 'HTML': 11}\n",
307+
"\n",
308+
"# adat és index összeállítás\n",
309+
"data = [dojo_1, dojo_2, dojo_3, dojo_4]\n",
310+
"index = ['Dojo 1', 'Dojo 2', 'Dojo 3', 'Dojo 4']\n",
311+
"\n",
312+
"# Pandas dataframe létrehozása minta adatokból\n",
313+
"df = pd.DataFrame(data, index=index)\n",
314+
"\n",
315+
"\n",
316+
"writer = pd.ExcelWriter(r'c:\\Users\\User\\Documents\\data\\xls\\xls_demo4.xlsx', engine='xlsxwriter')\n",
317+
"df.to_excel(writer, sheet_name='Munka1', index=False)\n",
318+
"\n",
319+
"workbook = writer.book\n",
320+
"worksheet = writer.sheets['Munka1']\n",
321+
"\n",
322+
"(max_row, max_col) = df.shape\n",
323+
"worksheet.set_column(0, max_col - 1, 12)\n",
324+
"\n",
325+
"\n",
326+
"worksheet A2D2 .autofilter(0, 0, max_row, max_col - 1)\n",
327+
"worksheet.filter_column(0, 'Pyton == 27')\n",
328+
"\n",
329+
"#df = df.reset_index(drop=True)\n",
330+
"for row_num in (df.reset_index(drop=True).index[(df['Pyton'] != 27)].tolist()):\n",
331+
" worksheet.set_row(row_num + 1, options={'hidden': True}) \n",
332+
" \n",
333+
"# worksheet.set_row(2, options={'hidden': True})\n",
334+
"\n",
335+
"\n",
336+
"writer.save()\n",
337+
"\n",
338+
"plt.rcParams[\"figure.figsize\"] = [7.50, 3.50]\n",
339+
"plt.rcParams[\"figure.autolayout\"] = True\n",
340+
"\n",
341+
"# két féle diagram egybe\n",
342+
"fig = plt.figure(figsize=(8.5,11))\n",
343+
"ax2 = plt.subplot2grid((4, 2), (0, 1))\n",
344+
"\n",
345+
"df.plot(kind='bar', ax=ax2, color=['red','blue','green','orange','yellow'])\n",
346+
"df.plot(kind='line',ax=ax2, marker='*', color=['red','blue','green','orange','yellow'], ms=10)\n",
347+
"\n",
348+
"# a diagram két subplot-on (egymás mellet)\n",
349+
"fig, (ax11, ax12) = plt.subplots(1,2, sharex=True)\n",
350+
"df.plot(kind='bar', ax=ax11, color=['red','blue','green','orange','yellow'])\n",
351+
"df.plot(kind='line',ax=ax12, marker='*', color=['red','blue','green','orange','yellow'], ms=10)\n",
352+
"\n",
353+
"# a diagram két subplot-on (egymás alatt)\n",
354+
"fig, (ax11, ax12) = plt.subplots(2, sharex=True)\n",
355+
"df.plot(kind='bar', ax=ax11, color=['red','blue','green','orange','yellow'])\n",
356+
"df.plot(kind='line',ax=ax12, marker='*', color=['red','blue','green','orange','yellow'], ms=10)\n",
357+
"\n",
358+
"#df.plot(kind='line', color=['red','blue','green','orange','yellow'], label = \"line 1\")\n",
359+
"\n",
360+
"#\n",
361+
"#plt.plot(df)\n",
362+
"#df.plot(kind='bar')\n",
363+
"\n",
364+
"plt.show()"
365+
]
366+
},
367+
{
368+
"cell_type": "code",
369+
"execution_count": null,
370+
"metadata": {
371+
"id": "n2gLPUfog85U"
372+
},
373+
"outputs": [],
374+
"source": [
375+
""
110376
]
111377
}
112378
],

0 commit comments

Comments
 (0)
0