user2334921 user2334921 - 19 days ago 8
Python Question

Python pandas: fill a dataframe with data from another

I have an empty pandas dataframe as displayed in the first picture.

What I like first dataframe

So, many, many Pfam IDs as columns and many different gene IDs as indices. Then I have a second dataframe like this.
second dataframe

Now what I would like to do is getting the data from the second into the first, doing this I simply like to write a 0 in each Pfam column that has no entry for a particular gene ID, and a 1 in each case a gene has a Pfam.

Any help would be highly appreciated.

Answer

assume the first dataframe is named d1 and the second is d2

d1.fillna(d2.groupby([d2.index, 'Pfam']).size().mul(0).unstack())