[FIXED] pandas groupby and select value from aother column for ecah group for plotting

Issue

I am using groupby() and for plotting each group. I have a dataframe: which each group have the same 'normal' value

id  date  value  normal
1    5.2  20      200   
1    5.4  100     200
1    6.9   30     200
2    2.4   20     500
2    3.4   100    500

I want to plot each group date and value columns (for x,y axes) and add axhline with the normal values correspond to the group. I've tried:

ax = sns.scatterplot("date", "value", data = data)
grouped = data.groupby('id')
normal_val= grouped['normal']
plt.axhline(normal_val,c='red', ls=':')    #===> THIS LINE
plt.show()

but does not work. Hope someone could help! thanks

Solution

Referring to the highlighted line, the normal_val has more than 1 value, not sure which one are you interested in.

print(data.groupby('id')[['normal']].mean())

     normal
id         
1.0   200.0
2.0   500.0

If you change the line to something like below, you will get an output

normal_val1 = 200
normal_val2 = 500
plt.axhline(normal_val1, c='red', ls=':')
plt.axhline(normal_val2, c='green', ls='-.')

Edit: Depending on how many 'normal' values you have, you can add to the color list c. I've started with 8, so this code should work:

import seaborn as sns
import matplotlib.pyplot as plt

ax = sns.scatterplot("date", "value", data = data)
df = data.groupby('id')[['normal']].mean().reset_index()
c = ['red', 'green', 'yellow', 'blue', 'navy', 'cyan', 'pink', 'brown']
if len(df['normal']) <= len(c):
    for i, normal in enumerate(df['normal']):
        plt.axhline(normal, c=c[i], ls='-.')
plt.show()

Answered By - perpetualstudent