0

I am facing a problem that I am uncapable of finding a way around it.

I find very difficult too to explain what I am trying to do so hopefully a small example would help

I have df1 as such:

Id   product_1   product_2  
Date
1    0.1855672   0.8855672
2    0.1356667   0.0356667 
3    1.1336686   1.7336686  
4    0.9566671   0.6566671

and I have df2 as such:

                            product_1    Month
Date                                          
2018-03-30                         11.0      3
2018-04-30                         18.0      4
2019-01-29                         14.0      1
2019-02-28                         22.0      2

and what I am trying to achieve is this in df2:

                            product_1    Month   seasonal_index  
Date                                          
2018-03-30                         11.0       3        1.1336686 
2018-04-30                         18.0       4        0.9566671
2019-01-29                         14.0       1        0.1855672
2019-02-28                         22.0       2        0.1356667

So what I try is to match the product name in df2 with the corresponding column in d1 and then get the value of for each index value that matches the month number in df2 I have tried doing things like:

for i in df1:
    df2['seasonal_index'] = df1.loc[df1.iloc[:,i] == df2['Month']]

but with no success. Hopefully someone could have a clue on how to unblock the situation

Improve this question
3
  • 3
    This is basic merging, see this question and answer. Commented Dec 19, 2020 at 16:38
  • it might @Erfan you are right but I dont find a way to do it, because wouldn't be mergin on month, but there is no month column in df1. I'll attempt anyways, thanks. Commented Dec 19, 2020 at 16:44
  • Try something like this df2['season_index'] = df2['Month'].map(df1.set_index('Date')['product_1']) Commented Dec 19, 2020 at 16:48

1 Answer 1

Reset to default
1

Here you are my friend, this produces exactly the output you specified.

import pandas as pd

# replicate df1
data1 = [[0.1855672, 0.8855672],
         [0.1356667, 0.0356667],
         [1.1336686, 1.7336686],
         [0.9566671, 0.6566671]]
index1 = [1, 2, 3, 4]
df = pd.DataFrame(data=data1,
                  index= index1,
                  columns=['product_1', 'product_2'])
df.columns.name = 'Id'
df.index.name = 'Date'

# replicate df2
data2 = [[11.0, 3],
         [18.0, 4],
         [14.0, 1],
         [22.0, 2]]
index2 = [pd.Timestamp('2018-03-30'),
          pd.Timestamp('2018-04-30'),
          pd.Timestamp('2019-01-29'),
          pd.Timestamp('2019-02-28')]
df2 = pd.DataFrame(data=data2, index=index2,
                   columns=['product_1', 'Month'])
df2.index.name = 'Date'

# Merge your data
df3 = pd.merge(left=df2, right=df[['product_1']],
               left_on='Month',
               right_index=True,
               how='outer',
               suffixes=('', '_df2'))
df3 = df3.rename(columns={'product_1_df2': 'seasonal_index'})
print(df3)

If you are interested in learning why this works, take a look at this link explaining the pandas.merge function. Notice specifically that for your dataframes, the key for df2 is one of its columns (so we use the left_on parameter in pd.merge) and the key for df is its index (so we use the right_index parameter in pd.merge).

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.merge.html

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.