How to get values in a dataframe from data from another dataframe and generate new variable? And searches with dates and conditions in Pandas?

user19990680 0
Viewed 36

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:

Dataframe (A)

DataFrame (B)

finished product

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.

dataframe pandas python
2 Answers

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
Jeril0

The code format it was:

dfr_1['r1'] = (dfr_1['r1'].replace('\.','', regex=True)
                    .replace(',','.', regex=True)
                    .astype(float))

enter image description here

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

enter image description here

user199906800
Related