This question already has an answer here:
- Merge two different dataframes on different column names [duplicate] 3 answers
- Pandas join on columns with different names [duplicate] 2 answers
- Pandas: join DataFrames on field with different names? 2 answers
- how to concat two data frames with different column names in pandas? - python 2 answers
- Joining pandas dataframes by column names 2 answers
I have a dataframe called prices contains two columns : Timestamp and closing prices. The contents are as below :
Timestamp Close
1/1/2017 0:00 966.6
1/1/2017 1:00 963.87
1/1/2017 2:00 963.97
1/1/2017 3:00 962.83
I have another dataframe called output the contents of which are as below :
created_at count
6/7/2018 19:00 1
6/7/2018 20:00 2
6/7/2018 21:00 3
6/7/2018 22:00 2
6/7/2018 23:00 1
What i want to do is to append the closing price from the price dataframe to the above output dataframe to get a dataframe that should look like this :
created_at count close
1/1/2017 0:00 5 966.6
1/1/2017 1:00 1 963.87
1/1/2017 2:00 1 963.97
1/1/2017 3:00 1 962.83
I know I can merge the 2 dataframes and then drop the Timestamp column using
output.drop['Timestamp'], axis=1)
and I can remove the NaN values using\
output.dropna()
but I cannot merge the 2 files on different columns. How can I do this? The updated code is as below :
import pandas as pd
path1 = r'C:\Users\Ahmed Ismail Khalid\Desktop\Bullcrap Testing Delete Later\Bitcoin Prices Hourly Based.csv'
path2 = r'C:\Users\Ahmed Ismail Khalid\Desktop\Bullcrap Testing Delete Later\adam3us.csv'
path3 = r'C:\Users\Ahmed Ismail Khalid\Desktop\Bullcrap Testing Delete Later\ascending and final.csv'
df1 = pd.read_csv(path1)
df2 = pd.read_csv(path2)
df3 = pd.read_csv(path3)
output = pd.merge(df1, df2, how="inner", on="created_at") #column_name should be common in both dataframe. how represents type of intersection. In your case it will be inner(INNER JOIN)
df4 = output.created_at.value_counts().rename_axis('created_at').reset_index(name='adam3us_tweets')
df4 = df4.sort_values(by=['created_at'])
# output the dataframe df4
print(df4,'\n\n')
df4.to_csv('results.csv', encoding='utf-8',index=False)
Any and all help would be appreciated.
Thanks