How to combine different DataFrames into one grouped bar plot

Question

I'm trying to copy the answer from this question, I can't see what I'm doing that's different.

My output, similar to that question or here is a grouped bar plot, where this data frame:

    Area  Number   Cat
0  item1       4  Cat1
1  item2       6  Cat1
2  item3       7  Cat1
3  item4       8  Cat1
4  item5      10  Cat1
0  item1       4  Cat2
1  item2       6  Cat2
2  item3       7  Cat2
3  item7       8  Cat2
4  item6      10  Cat2

Is grouped by area, and then each area has two conjoined bars showing the number for Cat1 and Cat2. So the output should have seven groups of bars (i.e. item 1,2,3,4,5,6,7) and each group has two bars in it, one for category 1 and one for category 2.

This code:

import sys
import pandas as pd
import seaborn as sns
import matplotlib.pyplot as plt

test_dict1 = {'item1':4, 'item2':6, 'item3':7,'item4':8,'item5':10}
test_dict2 = {'item1':4, 'item2':6, 'item3':7,'item7':8,'item6':10}

#df = pd.DataFrame({'test_dict1':pd.Series(test_dict1),'test_dict2':pd.Series(test_dict2)}).reset_index()
#df = df.set_axis(['Item', 'DictOne', 'DictTwo'], axis=1)

test1_df = pd.DataFrame(test_dict1.items(), columns=['Area','Number'])
test2_df = pd.DataFrame(test_dict2.items(), columns=['Area','Number'])


test1_df['Cat'] = 'Cat1'
test2_df['Cat'] = 'Cat2'

dfs = [test1_df,test2_df]
result = pd.concat(dfs)

print(result)

from matplotlib import rcParams
sns.set(style="whitegrid")
rcParams.update({'figure.autolayout': True})
fig, ax1 = plt.subplots(figsize=(12,6))

result.plot.bar(x='Cat', ax=ax1)
plt.tight_layout()
plt.xticks(rotation=45, ha="right")
plt.show()

produces this output:

Can someone show me where I'm going wrong?

Answer 1

It's difficult to give a great answer because I know this is just demo data so dealing with your real data is going to be different. I think the way you are creating the dataframe is giving you the problem since you are plotting directly with pandas.

Currently, your dataframe looks like:

...but you need your X axis to be the index, and a separate column for Cat1 and Cat2 with their respective values. Something closer to:

The way you accomplish this will depend on your actual dataset, but using the example provided (I slightly changed your input values for better visualization), this was my approach:

import pandas as pd

cat1 = {'item1':4, 'item2':6, 'item3':7,'item4':8,'item5':10}
cat2 = {'item1':5, 'item2':4, 'item3':12,'item4':6,'item5':3}

df1 = pd.DataFrame.from_dict(cat1, orient='index', columns=['Cat1'])
df2 = pd.DataFrame.from_dict(cat2, orient='index', columns=['Cat2'])

df = pd.concat([df1, df2], axis=1)

ax = df.plot.bar(rot=0)

Output:

Answer 2

• You can plot your long DataFrame with seaborn

>>> import seaborn as sns
>>> sns.barplot(data=result, x='Area', y='Number', hue='Cat')

• If you have long DataFrame, like in the OP, use pivot_table to create a wide DataFrame, and then plot with pandas.DataFrame.plot.bar or pandas.DataFrame.plot.
• Tested in python 3.8.12, pandas 1.3.3, matplotlib 3.4.3, seaborn 0.11.2

>>> result = result.pivot_table(index='Area', columns="Cat", values="Number")

Cat    Cat1  Cat2
Area             
item1   4.0   4.0
item2   6.0   6.0
item3   7.0   7.0
item4   8.0   NaN
item5  10.0   NaN
item6   NaN  10.0
item7   NaN   8.0

>>> ax1 = result.plot(kind='bar', rot=0, figsize=(12,6))