I have two DataFrame Consists of time and price columns.
df1: df2: Time1 Price1 Time2 price2 0 900 10 900 10 1 910 10 910 10 2 910 15 920 10 3 910 20 930 10 4 920 10 5 930 10
I want to create a new DataFrame df3 as the length of df2, and I also want to put df1[‘price’] in it like below
df3: Time2 price2 price1 900 10 10 910 10 15 920 10 10 930 10 10
Where price1 shows the mean of price1 values for the corresponding time2 values like below
import statistics time910 = (df1.price1[1],df1.price1[2],df1.price1[3]) if df3.price1[1] = statistics.mean(time910): print('same')
same
I’m sorry if it’s unclear, but could you advise me on how to do the above cleanly?
Thank you very much.
Advertisement
Answer
you can try data frame merge with inner join. following would be logic you can work with! comment section gives more insight on code.
import pandas as pd df_d={'Time1':[900,910,910,910,920,930],"Price1":[10,10,15,20,10,10]} # raw data as dictinary df1=pd.DataFrame(df_d) # build a dataframe df1_mean=df1.groupby('Time1')['Price1'].mean().to_frame() # group by Time1 and mean by price1 df2_d={'Time2':[900,910,920,930],"Price2":[10,10,10,10]} # raw data as dictinary df2=pd.DataFrame(df2_d) # build a dataframe
now merge will do the job:
result=df2.merge(df1_mean,left_on='Time2',right_index=True,how='inner') # merge,right df's as key so right_index=True