CODEWITHSUNDEEP
pythonpandasnumpytime-series
Kben59
Kben59

Reputation: 388

How to merge values from one serie to another

I have two series, the first one is 

Date     Item
2012  Cheeseburger
      Pasta
      Rice
2013  Tomatoes
      Salad
      Cheese

And the second one is : 

Name  Cheese  Milk  Pasta  Juice  Rice  Cake  Tomatoes  Fries  Beef  Salad   Cheeseburger
2012    2      1     1.5     1    0.5    3      2.2      1.4    5      2       4.5
2013    1.7   0.9    1.7     1    0.75   3      2.2      1.3   5.3    1.9       4

I want to create a third column in the first serie to put the values present in the second one which fit with the same date and same name. I don't really knwo how to do it. I tried with the stack method but without success.

The final result should look like something like that : 

Date     Item
2012  Cheeseburger  4.5
      Pasta         1.5
      Rice          0.5
2013  Tomatoes      2.2
      Salad         1.9
      Cheese        1.7

Does anyone know a way to do it ? Thanks

Here is the code that generates the second data frame

df_two = pd.DataFrame({
     "2012": [2, 1, 1.5, 1, 0.5, 3, 2.2, 1.4, 5, 2, 4.5],
     "2013": [1.7, 0.9, 1.7, 1, 0.75, 3, 2.2, 1.3, 5.3, 1.9, 4]
})

df_two.columns = [
     "Cheese", "Milk", "Pasta", "Juice", "Rice",
     "Cake", "Tomatoes", "Fries", "Beef", "Salad", "Cheeseburger"]

Upvotes: 2

Views: 45

Answers (1)

wwnde
wwnde

Reputation: 26676

Take the first dataframe as df.

df.reset_index(inplace=True)

Take the second datframe as df1 and pd.melt to collapse the columns to rows and come up with df2

df2=pd.melt(df1,id_vars=['Name'], value_vars=['Cheese', 'Milk', 'Pasta', 'Juice', 'Rice', 'Cake', 'Tomatoes',
       'Fries', 'Beef', 'Salad', 'Cheeseburger'],var_name='Item')
df2.columns=['Date','Item','value']#Rename columns

Left merge df1 on df2

pd.merge(df,df2, how='left', on=['Date', 'Item']).set_index(['Date','Item'])

enter image description here

Upvotes: 2

Related Questions