I have a dataframe as below:
ORDER TYPE CURRENT_PRICE MAX_PRICE MIN_PRICE
1500 AA 6255 7257 4356
1500 BB 6822 7109 4102
1510 AA 6853 7439 4650
1510 BB 6986 7177 4412
1520 AA 6676 7064 4754
1520 BB 6239 7404 4217
1530 AA 6620 7886 4511
1530 BB 6609 7587 4248
1540 AA 6854 7540 4387
1540 BB 6040 7292 4246
1550 AA 6547 7339 4850
1550 BB 6581 7925 4238
I want to plot both TYPE (AA, BB) with Group-by on ORDER column. For each TYPE the CURRENT_PRICE should be a bar with corresponding MAX_PRICE & MIN_PRICE to marked in each bar.
I want something like below: (Black Bar representing MAX_PRICE & MIN_PRICE)
Here is a way to do it.
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
df = pd.read_excel("path_to_data")
labels = df['ORDER'].unique()
aa_values = df.loc[df['TYPE'] == 'AA']['CURRENT_PRICE']
bb_values = df.loc[df['TYPE'] == 'BB']['CURRENT_PRICE']
x = np.arange(len(labels)) # the label locations
width = 0.35 # the width of the bars
fig, ax = plt.subplots()
rects1 = ax.bar(x - width/2, aa_values, width, label='AA')
rects2 = ax.bar(x + width/2, bb_values, width, label='BB')
# Add min_max lines
for label, x_ in zip(labels, x):
# Add for AA
aa_min, aa_max = df.loc[(df.ORDER == label) & (df.TYPE == 'AA')][['MIN_PRICE', 'MAX_PRICE']].values[0]
bb_min, bb_max = df.loc[(df.ORDER == label) & (df.TYPE == 'BB')][['MIN_PRICE', 'MAX_PRICE']].values[0]
ax.vlines(x_ - width / 2, aa_min, aa_max, color="black", lw=3)
ax.vlines(x_ + width / 2, bb_min, bb_max, color="black", lw=3)
# Add some text for labels, title and custom x-axis tick labels, etc.
ax.set_ylabel('Price')
ax.set_xticks(x)
ax.set_xticklabels(labels)
ax.legend()
ax.bar_label(rects1, padding=3)
ax.bar_label(rects2, padding=3)
fig.tight_layout()