Add rows based on column index

Question

I have a dataframe that has the following columns:

 id     index1    index2    value
 DivA     1         4        3
 DivA     3         6        4
 DivB     1         3        5
 DivB     5         7        3

On another dataframe I have the following:

 id      index
DivA       1
DivA       2
      ...
DivA       10
DivB       1
      ...

final result: df2:

 id      index    value
DivA       1        3
DivA       2       nan (because no value in df1)
DivA       3        4
DivA       4        3
      ...
DivA       10       nan (because no value in df1)
DivB       1        5
      ...

I'm wondering if I can add a new column 'value' to the second df in a way that 'index1' and 'index2' are combined and that 'value' in the second df can have 'nan' if there is no values in the first dataframe 'value' ?

Thank you for help !

it puts the three columns into one, what I'm looking for is that I transform the 'index1' and 'index2' into 'index' where the missing values in the two columns have a nan, any thoughts ? — hdatas
– hdatas, Commented Sep 24, 2020 at 20:11

wwnde · Accepted Answer · 2020-09-24 21:09:27Z

2

  #pd.melt first dataframe
    df3=pd.melt(df, id_vars=['id','value'], value_vars=['index1', 'index2'], value_name='index').drop(columns=['variable'])

#pd.concat second dataframe to the pd.met result, sort-values and drop duplicates in index 
(pd.concat([df3, df1], ignore_index=True).sort_values(by=['id','index'])).drop_duplicates(subset=['index'],keep='first')



     id  value  index
0   DivA    3.0      1
9   DivA    NaN      2
1   DivA    4.0      3
4   DivA    3.0      4
5   DivA    4.0      6
10  DivA    NaN     10
3   DivB    3.0      5
7   DivB    3.0      7

answered Sep 24, 2020 at 21:09

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

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

1 Comment

hdatas Over a year ago

It works, I found out that pd.melt can very usefull in a lot of tasks but need a bit of time to understand the concept, thank you !

Collectives™ on Stack Overflow

Add rows based on column index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related