Skip to content
Advertisement

Merging 2 different DataFrame with different length

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

enter image description here

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement