1##############################################################################
2#
3# An example of converting a Pandas dataframe to an xlsx file with a line
4# chart using Pandas and XlsxWriter.
5#
6# Copyright 2013-2021, John McNamara, jmcnamara@cpan.org
7#
8
9import pandas as pd
10import random
11
12# Create some sample data to plot.
13max_row     = 21
14categories  = ['Node 1', 'Node 2', 'Node 3', 'Node 4']
15index_1     = range(0, max_row, 1)
16multi_iter1 = {'index': index_1}
17
18for category in categories:
19    multi_iter1[category] = [random.randint(10, 100) for x in index_1]
20
21# Create a Pandas dataframe from the data.
22index_2 = multi_iter1.pop('index')
23df      = pd.DataFrame(multi_iter1, index=index_2)
24df      = df.reindex(columns=sorted(df.columns))
25
26# Create a Pandas Excel writer using XlsxWriter as the engine.
27sheet_name = 'Sheet1'
28writer     = pd.ExcelWriter('pandas_chart_line.xlsx', engine='xlsxwriter')
29df.to_excel(writer, sheet_name=sheet_name)
30
31# Access the XlsxWriter workbook and worksheet objects from the dataframe.
32workbook  = writer.book
33worksheet = writer.sheets[sheet_name]
34
35# Create a chart object.
36chart = workbook.add_chart({'type': 'line'})
37
38# Configure the series of the chart from the dataframe data.
39for i in range(len(categories)):
40    col = i + 1
41    chart.add_series({
42        'name':       ['Sheet1', 0, col],
43        'categories': ['Sheet1', 1, 0,   max_row, 0],
44        'values':     ['Sheet1', 1, col, max_row, col],
45    })
46
47# Configure the chart axes.
48chart.set_x_axis({'name': 'Index'})
49chart.set_y_axis({'name': 'Value', 'major_gridlines': {'visible': False}})
50
51# Insert the chart into the worksheet.
52worksheet.insert_chart('G2', chart)
53
54# Close the Pandas Excel writer and output the Excel file.
55writer.save()
56