How do you annotate a chart from a pivot-table dataframe column?

Question

I have a dataset

a   b   c   d
10-Apr-86   Jimmy   1   this is
11-Apr-86   Minnie  2   the way
12-Apr-86   Jimmy   3   the world
13-Apr-86   Minnie  4   ends
14-Apr-86   Jimmy   5   this is the
15-Apr-86   Eliot   6   way
16-Apr-86   Jimmy   7   the world ends
17-Apr-86   Eliot   8   not with a bang
18-Apr-86   Minnie  9   but a whimper

I want to make a chart in matplotlib that looks like this

I've figure out how to get just the dots (no annotations) using the following code:

df = (pd.read_csv('python.csv'))
df_wanted = pd.pivot_table(
    df,
    index='a',
    columns='b',
    values='c')

df_wanted.index = pd.to_datetime(df_wanted.index)

plt.scatter(df_wanted.index, df_wanted['Jimmy'])
plt.scatter(df_wanted.index,df_wanted['Minnie'])
plt.scatter(df_wanted.index,df_wanted['Eliot'])

I think that to annotate, I need a list of values (as demonstrated here ) on the final column of my pivot table

My problem is: how do I get that final column 'd' of the original dataset to become the final column of my pivot table?

I tried dat1 = pd.concat([df_wanted, df['d']], axis = 1) - but this created a new set of rows underneath the rows of my dataframe. I realized the axis wasn't the same, so I tried to make a new pivot table with the d column as values - but got the error message No numeric types to aggregate.

I tried df_wanted2.append(df['d']) - but this made a new column for every element in column d.

Any advice? Ultimately, I want to make it so the data labels appear when one rolls over the point with the mouse

score 1 · Accepted Answer · answered Jul 04 '17 at 01:56

In this specific case, it doesn't seem you need to set column d as the final column of your pivot table.

plt.scatter(df_wanted.index, df_wanted['Jimmy'])
plt.scatter(df_wanted.index,df_wanted['Minnie'])
plt.scatter(df_wanted.index,df_wanted['Eliot'])
plt.legend(loc=0)

for k, v in df.set_index('a').iterrows():
    plt.text(k, v['c'], v['d']) # or: plt.annotate(xy=(k, v['c']), s=v['d'])

How do you annotate a chart from a pivot-table dataframe column?

1 Answers1