Plotting some data in a weird format in excel/python

I have some data that is in this format:

Is there a way to plot this data? The way it is set up is that the table is sorta like a line chart, I know its weird but that’s the way the data came out as.

If not excel, then maybe in python?

I don’t know how to plot this data. It is already in a kind of format as if it is a graph though, the cells are like a point of data on a line graph.

>Solution :

You can use pandas and matplotlib to pivot your dataframe and plot your figure:

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.ticker as ticker

# Read your data
# df = pd.read_excel('data.xlsx')
data = {'Position': [1, 2, 3],
        '2012': ['A', 'C', 'B'],
        '2013': ['B', 'A', 'D'],
        '2014': ['A', 'C', 'E']}
df = pd.DataFrame(data)

# Reshape your table
piv = (df.melt('Position', var_name='Year', value_name='ID')
         .pivot(index='Year', columns='ID', values='Position'))

# Plot data
ax = piv.plot(xlabel='Year', ylabel='Position', title='Rank by year')
ax.yaxis.set_major_locator(ticker.MultipleLocator())
ax.invert_yaxis()
plt.show()

Output:

Pivoted dataframe:

>>> piv
ID      A    B    C    D    E
Year                         
2012  1.0  3.0  2.0  NaN  NaN
2013  2.0  1.0  NaN  3.0  NaN
2014  1.0  NaN  2.0  NaN  3.0

Note: you probably do the same with Excel.