Pythonic way to plot data frames AND average of y values with different x values

Question

I have the following 4 data frames which are all diferent and are in 4 files:

0 P 1 E 1 1
1 P 1 E 1 2
2 P 1 E 2 3
3 P 1 E 3 4
4 P 1 E 4 5
5 P 1 B 0 6
6 P 1 B 1 7
7 P 1 B 2 8
8 P 1 B 3 9
9 P 1 B 4 10

1 P 1 E 1 3
2 P 1 E 2 4
3 P 1 E 3 5
4 P 1 E 4 6
5 P 1 B 0 7
6 P 1 B 1 8
7 P 1 B 2 9
8 P 1 B 3 10
9 P 1 B 4 11 
10 P 1 B 1 12 

2 P 1 E 1 5
3 P 1 E 2 6
4 P 1 E 3 7
5 P 1 E 4 8
6 P 1 B 0 9
7 P 1 B 1 10
8 P 1 B 2 11
9 P 1 B 3 12
10 P 1 B 4 13 
11 P 1 B 1 14 

2 P 1 E 1 5
3 P 1 E 2 6
4 P 1 E 3 7
5 P 1 E 4 8
6 P 1 B 0 9
7 P 1 B 1 10
8 P 1 B 2 11
9 P 1 B 3 12
10 P 1 B 4 13 
11 P 1 B 1 14 

I want to plot the first and last column of each data frame (which I can do) and then plot the average of the last columns (which I can't do). Note (c.f. data above) that, I need the average of y values with different x values. The second solution here, looks promising but doesn't really solve my problem because I don't want to create a (x1,y1) pair for each of my data frames ( I have more than 50)

I tried to concatenate using pd.concat, but the name of the columns is printed in the concatenated data frame.

Using the solution given from the answer mentioned above

x1 = np.arange(10)
x2 = np.arange(10)+1  
x3 = np.arange(10)+2
x4 = np.arange(10)+3
y1 = x1+1
y2 = x2+2
y3 = x3+3
y4 = x4 +4
df=pd.concat([pd.Series(y1,index=x1),
            pd.Series(y2,index=x2),
            pd.Series(y3,index=x3),
            pd.Series(y4,index=x4)], axis=1).mean(axis=1) 
 ax.plot(x1, y1)
 ax.plot(x2, y2)
 ax.plot(x3, y3) 
 ax.plot(x4, y4) 
 df.plot(color='red')

I'm looking for a graph that looks like this:

Felipe Lanza's solution below was given before I edited this question with the information that I need the average of y values with different x values.

Answer 1

From what I understood your concern was dealing with different columns with the same name...

I am making quite a few assumptions, but something along these lines should work:

(Edit) FWIW, here's a simpler version of your proposed solution:

path =  'path/to/dataFrame'
all_files = glob.glob(path + "/*.csv")
cols_list = []
fig, ax = plt.subplots()

for filename in all_files:
    # You can directly load only the two columns being used...
    df = pd.read_csv(filename, sep=" ", usecols=[0, 5], index_col=0, names=["A", "ID"], header=None)
    # ... and skip the conversion to arrays and concatenating a series
    cols_list.append(df)
    df.plot(ax=ax, style='o-')

cols_list_df = pd.concat(cols_list, axis=1)
cols_list_df.mean(axis=1).plot(ax=ax, style='o-')

Answer 2

Using Felipe Lanza's answer above and DYZ's answer here I got a solution to my problem:

 path =  'path/to/dataFrame'
 all_files = glob.glob(path + "/*.csv")
 cols_list = []
 fig, axes = plt.subplots()

  for i, filename in enumerate(all_files):
    df = pd.read_csv(filename, sep=" ", names = ["A", "E", "C","O","M","ID"],header=None )
    x=df["A"]        
    y=df["ID"]  
    xarray=np.array(x)
    yarray=np.array(y)
    df2=pd.concat([pd.Series(yarray,index=xarray)],axis=1).mean(axis=1)
    cols_list.append(df2)
    axes.plot(x, y,'o-')
  cols_list_df=pd.concat(cols_list,axis=1)
  cols_list_df.mean(axis=1).plot(style='o-')

With the dataframes posted above, the plot looks like this:

I'm sure there should be a smarter solution but this is good enough for me.