Concatenating 3 df with the same index in pandas

Question

I am having issues concatenating 3 dataframes with the same index (in this case the index are dates). I am trying to plot three differnet dataframes onto one plotly graph and figured combining the dataframes would be the easiest. Currently I am using code such as below.

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

But I am raising the below error.

ValueError: cannot reindex from a 
duplicate axis

My dataframes are structured like below.

           A
7/28/2018  4 
7/29/2018  5

           B
7/28/2018  3
7/29/2018  4

           C
7/28/2018  1
7/29/2018  2

And I want the below result:

           A B C
7/28/2018  4 3 1
7/29/2018  5 4 2

Can someone explain what I am doing incorrectly here?

I think the error show it duplicate axis, you have duplicate index in dataframe A — BENY, Jul 29 '18 at 22:48
If you remove the dup index, then the info in column will be removed as well. https://stackoverflow.com/questions/13035764/remove-rows-with-duplicate-indices-pandas-dataframe-and-timeseries — BENY, Jul 29 '18 at 22:52
why would i use merge if i have nothing to merge with on? My data frames are single column dataframes. — Victor, Jul 29 '18 at 22:55

score 0 · Answer 1 · answered Jul 29 '18 at 23:12

You should ensure your indices are precisely the same. Here's a minimal example showing that you can concatenate with duplicate indices, provided they are identical:

idx = ['7/28/2018', '7/28/2018', '7/29/2018']

df_A = pd.DataFrame({'A': [1, 2, 3]}, index=idx)
df_B = pd.DataFrame({'B': [4, 5, 6]}, index=idx)
df_C = pd.DataFrame({'C': [7, 8, 9]}, index=idx)

res = pd.concat([df_A, df_B, df_C], axis=1)

print(res)

           A  B  C
7/28/2018  1  4  7
7/28/2018  2  5  8
7/29/2018  3  6  9

Even reordering one of the indices will cause this to break. An alternative solution is to drop duplicate indices before concatenation, see Remove rows with duplicate indices.

Concatenating 3 df with the same index in pandas

1 Answers1