plot more graphs from excel file and Pandas ( python)

Question

I have a .xlsx file structured as follows

Now, I know how to manipulate the data in my table by pandas function. However, I am (unsuccessfully, I should say) looking for a way to plot in the same graph area the three curves labelled by the flags A, B, C in column 2 using col 3 as x-axis and col 9 as y-axis.

I copy here the table in a "copy-&-paste" suitable form

Col1    Col2    Col3    Col4    Col5    Col6        Col7    Col8    Col9
1       FlagA   5743    bla bla 0.02%   13-Apr-18   DS      Y       17229
2       FlagA   654     bla bla -0.89%  13-Apr-18   DS      Y       1962
3       FlagA   543     bla bla -0.74%  13-Apr-18   DS      Y       1629
4       FlagA   321     bla bla -0.56%  13-Apr-18   DS      Y       963
5       FlagA   8765    bla bla -0.37%  13-Apr-18   DS      Y       26295
6       FlagA   98769   bla bla 3.75%   13-Apr-18   DS      Y       296307
7       FlagA   943     bla bla 4.01%   13-Apr-18   DS      Y       2829
8       FlagA   7986    bla bla 4.04%   13-Apr-18   DS      Y       23958
9       FlagA   54343   bla bla 3.27%   13-Apr-18   DS      Y       163029
10      FlagB   876856  bla bla 4.16%   13-Apr-18   DS      Y       2630568
11      FlagB   36854   bla bla 3.36%   13-Apr-18   DS      Y       110562
12      FlagB   3644    bla bla 4.21%   13-Apr-18   DS      Y       10932
13      FlagC   68      bla bla 3.51%   13-Apr-18   DS      Y       204
14      FlagC   354     bla bla 3.70%   13-Apr-18   DS      Y       1062
15      FlagC   568748  bla bla 1.66%   13-Apr-18   DS      Y       1706244
16      FlagC   64      bla bla 2.00%   13-Apr-18   DS      Y       192

To summarise: You are looking for a scatter plot with Col3 value as x-axis, Col9 value as y-axis and color or marker shape according to Col2? Something like this: https://stackoverflow.com/a/21655256/8881141 ? — Mr. T, Jun 07 '18 at 15:34

sacuL · Accepted Answer · 2018-06-08T12:36:42.240

1

You can try like this:

for flag, data in df.groupby('Col2'):
    plt.plot(data['Col3'], data['Col9'], label=flag)

plt.legend()
plt.show()

To add a condition, just use an if statement:

for flag, data in df.groupby('Col2'):
    if flag != 'FlagC':
        plt.plot(data['Col3'], data['Col9'], label=flag)

edited Jun 08 '18 at 12:36

answered Jun 07 '18 at 15:36

sacuL

49,704
8
81
106

Thanks. What if I wanna then insert a conditional expression and show only plots coresponding to labels "a" and "b" but not "c"? – Oscar Jun 08 '18 at 08:19
I had thought that, however no plot is produced since I got the error message 'No handles with labels found to put in legend.' – Oscar Jun 08 '18 at 13:10
It works on your example data, I'm not sure what the problem is – sacuL Jun 08 '18 at 13:17
1

I think I found the issue, which is twofold: 1. my condition is if flag == 'FlagC'. 2. I group by 2 columns: df.groupby(['Col2', 'Col6']) (in my code I have different dates), thus the flag is never equal to Col2. – Oscar Jun 08 '18 at 14:05

score 1 · Answer 2 · answered Jun 07 '18 at 15:37

1

You can use pd.DataFrame.plot() directly after grouping your DataFrame and setting the proper index.

This works for me:

 plotted_df = df[['Col2', 'Col3', 'Col9']].set_index('Col3')
 plotted_df.groupby('Col2')['Col9'].plot()

answered Jun 07 '18 at 15:37

pdn

81
5

plot more graphs from excel file and Pandas ( python)

2 Answers2