I have 2 files: a) I want to find values in one dataframe (A) from data in another dataframe (B), after that b) I need to get column (r1) of B when "a)" is True. Besides, I need to gen a new variable like price2 = (price1/r1)*100.
And also, I have a Column 'date1' which has values in the form YYYYMM, The Column date1 is of type object.
df0["date1"] = pd.to_datetime(df0["date1"], format='%Y%m').dt.strftime('%Y-%m')
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
I wish to convert it in to date type. These files are prepared to show as an example. But this I need to do with files 9 million records and monthly data of 30 years, it is an unbalanced data panel. And my idea is to migrate to python, pandas and I don't know much about both.
View of the dataframes (A) and (B). and the desired finished product data frame:
Files: https://drive.google.com/file/d/1h4oqGykgkxC7UFnzyeixpxDTnh8Dpakd/view?usp=sharing
https://drive.google.com/file/d/1zaQ4nA8QSl40SMrkbdUKMp74pMIvZbp4/view?usp=sharing
file (link) with the desired finished product: https://drive.google.com/file/d/1aXbSQ3JhLieLGRa7tHbuv9C52aNgG998/view?usp=sharing
I hope that somebudy will be able to help me please.
CodePudding user response：
Can you try the following:
merged_df = df0.merge(df1, how='left', left_on='date1', right_on='date2')
merged_df['price2'] = merged_df['price1'] / merged_df['r1'] * 100
CodePudding user response：
The code format it was:
dfr_1['r1'] = (dfr_1['r1'].replace('\.','', regex=True)
.replace(',','.', regex=True)
.astype(float))
merged_df = df0.merge(dfr_1, how='left', left_on='date1', right_on='date2')
merged_df["price1"] = merged_df["price1"].astype(str).astype(float)
merged_df['price2'] = merged_df['price1'] / merged_df['r1'] * 100
