1

i have the following excel (xlsx) file with informations like:

    files    val_1   val_2      val_3   val_4
1   file_1   545.0   475.20822  116     43.0
2   file_2   179.0   19.791214  17      39.0
3   file_3   600.0   450.54407  302     92.0
4   file_4   1936.0  397.71826  182     271.0

i want to parse this file and then e.g. draw a boxplot by column "val_1" and then save the boxplot into the same excel file but on e.g. worksheet : sheet 2

is there a way to do this just with pandas without matplotlib ? or is matplotlib mandatory?

friggler
  • 177
  • 2
  • 14

2 Answers2

2

I think your best bet is to use something like XlsxWriter . MatPlotLib would save the chart as an image file, XlsxWriter would save the chart as a functioning chart in the excel file

jmcnamara
  • 38,196
  • 6
  • 90
  • 108
chris dorn
  • 817
  • 8
  • 13
-1

thanks i have a solution now and it is a mix of openpyxl + pandas + matplotlib 

workbook = load_workbook(excel_file,read_only=False)
worksheet = workbook['Sheet2']   # make sure this sheet exist e.g. create it before

df = pd.read_excel(excel_file, "Sheet1") #sheet 1 contains the data see above

# combine these different collections into a list    
data_to_plot = [df._series['val_1']]

# Create a figure instance
fig = plt.figure(1, figsize=(8, 8))

# Create an axes instance
ax = fig.add_subplot(111)

# Create the boxplot
bp = ax.boxplot(data_to_plot)

for flier in bp['fliers']:
     flier.set(marker='o', color='#e7298a', alpha=0.5)

# Save the figure
fig.savefig('val_1.png', bbox_inches='tight')

# load the created boxplot image and insert in sheet2
img = openpyxl.drawing.image.Image('val_1.png')
worksheet.add_image(img,'D3')

workbook.save(excel_file)

just one last thing i try to add the x labels as diagonal like shown here: https://matplotlib.org/examples/pylab_examples/boxplot_demo2.html

i added these lines before : bp = ax.boxplot(data_to_plot)

xtickNames = plt.setp(ax, xticklabels="Label_1")
plt.setp(xtickNames, rotation=45, fontsize=10)

but with this i only see a single character "L" in the x axis not "Label_1"

instead of these two lines i tried this one:

ax.set_xticklabels( ['Label_1'], rotation=45 )

but it also doesnt work now i see a diagonal '1' ???

friggler
  • 177
  • 2
  • 14
  • You don't have to mark my response as the answer, but your response is definitely not correct. You are saving an image file within Excel, you should save that as an Excel chart or save the png file on your computer. – chris dorn May 31 '18 at 15:51
  • i do save the png file on my computer fig.savefig() and after that i paste the png file in an excel file. no need to mark it as not correct because it works i have tested it – friggler Jun 06 '18 at 11:07