Concatenate/Merge/Join two different Dataframes Pandas

Question

I am looking to join two dataframes using pandas on the 'Date' columns. I usually use df2= pd.concat([df, df1],axis=1), however for some reason this is not working.

In this example, i am pulling the data from a sql file, creating a new column called 'Date' that is merging my year and month columns, and then pivoting. Whne i try and concatenate the two dataframes, the dataframe shows up side by side instead of merged together.

What comes up: Date Count of Cats Date Count of Dogs

What I want to come up:

Date Count of Cats Count of Dogs

Any ideas? My other problem is I am trying to make sure the Date columns writes to excel as a string and not a datetime function. Please keep this is mind when thinking about a solution.

Here is my code:

executeScriptsFromFile('cats.sql')
df = pd.DataFrame(cursor.fetchall())
df.columns = [rec[0] for rec in cursor.description]
monthend = {'Q1':'3/31','Q2':'6/30','Q3':'9/30','Q4':'12/31'}
df['Date']=df['QUARTER'].map(monthend)+'/'+ df['YEAR']
df['Date'] = pd.to_datetime(df['Date'])
df10= df.pivot_table(['Breed'], ['Date'], aggfunc=np.sum,fill_value=0)
df10.reset_index(drop=False, inplace=True)
df10.reindex_axis(['Breed', 'Count of Cats'], axis=1)
df10.columns = ('Breed', 'Count of Cats')


executeScriptsFromFile('dogs.sql')
df = pd.DataFrame(cursor.fetchall())
df.columns = [rec[0] for rec in cursor.description]
monthend = {'Q1':'3/31','Q2':'6/30','Q3':'9/30','Q4':'12/31'}
df['Date']=df['QUARTER'].map(monthend)+'/'+ df['YEAR']
df['Date'] = pd.to_datetime(df['Date'])
df11= df.pivot_table(['Breed'], ['Date'], aggfunc=np.sum,fill_value=0)
df11.reset_index(drop=False, inplace=True)
df11.reindex_axis(['Breed', 'Count of Dogs'], axis=1)
df11.columns = ('Breed', 'Count of Dogs')
df11a= df11.round(0)

df12= pd.concat([df10, df11a],axis=1)

Can you add some samples of your dataframes? Also is some difference of type of df10.index and d11.index ? Because this should work. — jezrael
I am not sure, but it seem you need remove df10.reset_index(drop=False, inplace=True) and df11.reset_index(drop=False, inplace=True), please test it. — jezrael
Or maybe need remove only one level, need date as index in both dataframes before concat — jezrael
Yes, you can convert it to string like df.index = df.index.astype(str) — jezrael
Good luck, and i think is I think it is not stupid, sometimes man works with easy think 2-3 hour and cannot find problem. So good luck and i am not sure if create answer. what do you think? — jezrael

jezrael jezrael · Accepted Answer · 2017-01-17T22:04:39

I think you have to remove code:

df10.reset_index(drop=False, inplace=True)
df11.reset_index(drop=False, inplace=True)

because need level date in index for concat by date.

Also for convert index to string use:

df.inde = df.index.astype(str)

Concatenate/Merge/Join two different Dataframes Pandas

1 Answers