Line chart in matplotlib with a double axis(strings on the axis)

Question

I am trying to create a chart using python from a data in an Excel sheet. The data looks like this

       Location Values
Trial 1 Edge    12
         M-2    13
       Center   14
         M-4    15
         M-5    12
         Top    13
Trial 2 Edge    10
        N-2     11
      Center    11
        N-4     12
        N-5     13
        Top     14
Trial 3 Edge    15
        R-2     13
     Center     12
       R-4      11
       R-5      10
       Top       3

I want my graph to look like this: Chart-1 .The chart should have the Location column values as X-axis, i.e, string object. This can be done easily(by using/creating Location as an array),

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
datalink=('/Users/Maxwell/Desktop/W1.xlsx')
df=pd.read_excel(datalink,skiprows=2)
x1=df.loc[:,['Location']]
x2=df.loc[:,['Values']]
x3=np.linspace(1,len(x2),num=len(x2),endpoint=True)
vals=['Location','Edge','M-2','Center','M-4','M-5','Top','Edge','N-2','Center','N-4','N-5','Top','Edge','R-2']
plt.figure(figsize=(12,8),dpi=300)
plt.subplot(1,1,1)
plt.xticks(x3,vals)
plt.plot(x3,x2)
plt.show()

But, I also want to show Trial-1, Trial-2 .. on X-axis. Upto now I had been using Excel to generate chart but, I have a lot of similar data and want to use python to automate the task.

If you can share a [mcve] of the code that produced the plot that you call "this can be easily done", it would help people to actually provide you with a solution for the problem. — ImportanceOfBeingErnest, May 04 '17 at 16:47
I think you misunderstood me.The plot was created in Excel. I am looking for a code to generate a similar chart in python. — AP85', May 04 '17 at 16:53
So what do you mean by "this can be done easily" then? My comment was meant to help you formulate a question, which can be answered within the limits of this Q&A site. You may of course ask if someone can write the complete code for you - I doubt however, that someone would actually do it; but you may still try. You might also want to look in how far [this question](http://stackoverflow.com/questions/43545879/bar-chart-with-multiple-labels) and its answers can help you. — ImportanceOfBeingErnest, May 04 '17 at 17:01
@ImportanceOfBeingErnest : Sorry, for all the confusion/nuisance. I have edited my question and I have included, what I have done so far. I hope it will make much sense now to anyone reading the question/post. I tried to go through the [this question](http://stackoverflow.com/questions/43545879/bar-chart-with-multiple-labels). I didn't see a single comment on the code, I am very new to python and it is very difficult to understand which code is doing what. — AP85', May 05 '17 at 14:49

plasmon360 · Accepted Answer · 2017-05-07T02:23:41.437

With your excel sheet that has data as follows,

,

you can use matplotlib to create the plot you wanted. It is not straightforward but can be done. See below:

EDIT: earlier I suggested factorplot, but it is not applicable because your location values for each trial are not constant.

df = pd.read_excel(r'test_data.xlsx', header = 1, parse_cols = "D:F",
                  names = ['Trial', 'Location', 'Values'])
'''
  Trial Location  Values
0   Trial 1     Edge      12
1       NaN      M-2      13
2       NaN   Center      14
3       NaN      M-4      15
4       NaN      M-5      12
5       NaN      Top      13
6   Trial 2     Edge      10
7       NaN      N-2      11
8       NaN   Center      11
9       NaN      N-4      12
10      NaN      N-5      13
11      NaN      Top      14
12  Trial 3     Edge      15
13      NaN      R-2      13
14      NaN   Center      12
15      NaN      R-4      11
16      NaN      R-5      10
17      NaN      Top       3
'''


# this will replace the nan with corresponding trial number for each set of trials
df = df.fillna(method = 'ffill')
'''
 Trial Location  Values
0   Trial 1     Edge      12
1   Trial 1      M-2      13
2   Trial 1   Center      14
3   Trial 1      M-4      15
4   Trial 1      M-5      12
5   Trial 1      Top      13
6   Trial 2     Edge      10
7   Trial 2      N-2      11
8   Trial 2   Center      11
9   Trial 2      N-4      12
10  Trial 2      N-5      13
11  Trial 2      Top      14
12  Trial 3     Edge      15
13  Trial 3      R-2      13
14  Trial 3   Center      12
15  Trial 3      R-4      11
16  Trial 3      R-5      10
17  Trial 3      Top       3
'''

from matplotlib import rcParams
from matplotlib import pyplot as plt
import matplotlib.ticker as ticker

rcParams.update({'font.size': 10})
fig1 = plt.figure()
f, ax1 = plt.subplots(1, figsize = (10,3))

ax1.plot(list(df.Location.index), df['Values'],'o-')
ax1.set_xticks(list(df.Location.index))
ax1.set_xticklabels(df.Location, rotation=90 )
ax1.yaxis.set_label_text("Values")

# create a secondary axis
ax2 = ax1.twiny()
# hide all the spines that we dont need
ax2.spines['top'].set_visible(False)
ax2.spines['bottom'].set_visible(False)
ax2.spines['right'].set_visible(False)
ax2.spines['left'].set_visible(False)

pos1 = ax2.get_position() # get the original position 
pos2 = [pos1.x0 + 0, pos1.y0 -0.2,  pos1.width , pos1.height ] # create a new position by offseting it
ax2.xaxis.set_ticks_position('bottom')
ax2.set_position(pos2) # set a new position


trials_ticks = 1.0 * df.Trial.value_counts().cumsum()/ (len(df.Trial)) # create a series object for ticks for each trial group
trials_ticks_positions = [0]+list(trials_ticks) # add a additional zero. this will make tick at zero.
trials_labels_offset = 0.5 * df.Trial.value_counts()/ (len(df.Trial)) # create an offset for the tick label, we want the tick label to between ticks
trials_label_positions = trials_ticks - trials_labels_offset # create the position of tick labels

# set the ticks and ticks labels
ax2.set_xticks(trials_ticks_positions)
ax2.xaxis.set_major_formatter(ticker.NullFormatter())
ax2.xaxis.set_minor_locator(ticker.FixedLocator(trials))
ax2.xaxis.set_minor_formatter(ticker.FixedFormatter(list(trials_label_positions.index)))
ax2.tick_params(axis='x', length = 10,width = 1)

plt.show()

results in

Thank you so very much for your help. I had a problem executing the code you provided. 'df['Trial'] = [u for u in df['Trial'].dropna().unique() for l in range(len(df['Location'].unique()))]' gives _Length of values does not match length of index_ error. I am trying to figure out what went wrong. Also, I am trying to make the whole plot as one, not divide into 3 parts. Your code helped me learn some more python. — AP85', May 05 '17 at 14:43
Is ur data in excel file like the way i post in my answer. It will help if u can upload ur excelfile with part of data. — plasmon360, May 05 '17 at 15:15
I created a github directory. The filename is W1.xlsx.[link](https://github.com/PyAnil/Graphing) — AP85', May 06 '17 at 06:56
i have updated the code. This code uses matplotlib and not seaborn. If the code was helpful, please upvote or/and accept the answer. — plasmon360, May 07 '17 at 02:24
Thank you so very much for updating the code. I was able to(almost) replicate the same results as you did. (The order on the chart I got was Trial2, Trial1 and Trial3). Forgive me, if I am bothering you, but I couldn't understand the way you assigned ticks and tick labels. Also, I think the code `ax2.xaxis.set_minor_locator` has something missing! — AP85', May 07 '17 at 04:40

Line chart in matplotlib with a double axis(strings on the axis)

1 Answers1