0

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

Stevi G
  • 257
  • 1
  • 4
  • 13
  • 3
    `pd.merge` has the `left_on` and `right_on` parameters where you can define differently named columns. Start with documentation when you have questions: http://pandas.pydata.org/pandas-docs/version/0.22/generated/pandas.DataFrame.merge.html – A.Kot Jun 11 '18 at 18:01
  • You can also rename the column before merging, so that you don't wind up with both key columns after the merge. – ALollz Jun 11 '18 at 18:19
  • @A.Kot whenever i use the left_on and right_on i get **ValueError: len(right_on) must equal len(left_on)** – Stevi G Jun 11 '18 at 18:33
  • Pretty self-explanatory: you can't select 2 columns on the left df and merge on 3 columns on the right df. What is your right_on and left_on equal to? – A.Kot Jun 12 '18 at 14:52
  • @A.Kot it worked. Dunno what happened but on the 8th execution it just started working – Stevi G Jun 12 '18 at 17:41

0 Answers0