How to create a scatter plot where x and y values are the column and row names

Cyne 0
Viewed 29

I have a question of plotting a scatter plot from a dataframe.

The data I would like to plot seems like this:
data frame to plot

I would like to have a scatter plot where the x axis are the years and the y axis are named as cities. The sizes of the scatters on the scatterplot should be based on the data value.

the wished visualization of the data:
the wished visualization of the data

I searched examples of documents from different libraries and also stack overflow, but unfortunately I didn't find a suitable answer to this.

I would appreciate if anyone can help, either excel or python solution would be fine.

Thanks

scatter-plot matplotlib excel python
1 Answers

Something like this should work:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# assuming your example data is in a dataframe called df 


# rename columns so that we can apply 'wide_to_long'
df.rename(columns={1990: 'A1990', 1991: 'A1991', 2019: 'A2019', 2020: 'A2020'}, inplace=True)

# reshape data using 'wide_to_long' to get it into the right format for scatter plot
df = pd.wide_to_long(df, "A", i="City", j="year")
df.reset_index(inplace=True)
df["A"] = df["A"].astype(int)

# OPTIONAL: scale the "bubble size" variable in column A to make graph easier to interpret
df["A"] = (df["A"] + 0.5) * 100

# map years onto integers so we can only plot the years that we want
year_dict = {1990: 1, 1991: 2, 2019: 3, 2020: 4}
df['year_num'] = df['year'].map(year_dict)

# plot the data
fig, ax = plt.subplots()
plt.scatter(df['year_num'], df['City'], s=df['A'], alpha=0.5)

# label the years corresponding to 'year_num' values on the x-axis
plt.xticks(np.arange(1, 5, 1.0))
labels = [1990, 1991, 2019, 2020]
ax.set_xticklabels(labels)

plt.show()

You can play around with the colors/formatting options in matplotlib to get the look you want, but the above should accomplish the basic idea.

user4NY510
Related