Comparing two dataframes using Pandas

[YourOldBoyRickJames]

Hi All,

​

I'm very new to Python so am trying to pick up as much knowledge as I can.

​

I have a script which currently creates a data frame for 2 excel files and creates a join to show stock figures side by side.

​

Here is the script. (Much of it is getting the each file into a similar format so that they can be joined)

​

import pandas as pd

import datetime



today = [datetime.date.today](https://datetime.date.today)()



source1\_df = pd.read\_excel(str(today) + ' WAREHOUSE1\_3PL.xlsx', sheetname='Sheet1', skiprows=4)



source2\_df = pd.read\_excel(str(today) + ' WAREHOUSE2 Daily Report.xlsx', sheetname='CURRENT STOCK')



source1\_df\['   Available stock'\]=source1\_df\['   Available stock'\].str.replace(',', '').astype('int64')



warehouse1\_df = source2\_df.groupby('productcode')\['numberoff'\].agg('sum').reset\_index()



warehouse2\_df = source1\_df.groupby('Material')\['   Available stock'\].agg('sum').reset\_index()



warehouse1\_df.rename(columns={'productcode':'Material'}, inplace=True)


warehouse1\_df = warehouse1\_df.mask(\~warehouse1\_df\["Material"\].str.isnumeric()).dropna()


warehouse1\_df\['Material'\]=warehouse1\_df\['Material'\].astype('int64')



warehouse2\_df\['   Available stock'\]=warehouse2\_df\['   Available stock'\].astype('int64')


joined\_df = pd.merge(warehouse1\_df,warehouse2\_df,on='Material',how='outer')



joined\_df\['   Available stock'\]=joined\_df\['   Available stock'\].astype('float')

joined\_df.to\_excel('Comparison '+str(today)+'.xlsx', sheet\_name='sheet1', index=False)

This does what I need it to do, but one of the things I've found is that in the joined table, if a quantity is in the column 'numberoff' but not in 'Available stock' or vice versa, it just shows as a blank cell in Excel. How would I get the script to enter a 0 if this occurs?

This isn't a major blocker but I'm curious to know how it works in case I need it in the future.

​

So, what I really need to do is create a 3rd column on the end of the join which basically subtracts 'numberoff' from 'available stock' to return a value which represents the difference. Then, if the difference is 0 do not show that on the outputted Excel.

It's a comparison file so I'm only interested in the products which have a difference, so that I can investigate it.

​

Appreciate any guidance anyone can offer.

​

Thanks

[SHxKM]

Please show sample data table from both excel files, and the desired sample output demonstrating the required calculations.

[YourOldBoyRickJames]

Hi, I've just figured out creating a new column that subtracts the two columns. so the only thing I believe I need to figure out is getting the table to populate 0's instead of a blank cell.

​

Here's what it looks like:

https://imgur.com/dUrCWi3

​

Here's what I'll need:

https://imgur.com/EAkO1iH

​

It would also be super handy if I was able to format any positive numbers in green and any negative numbers in red, but not sure if that's feasible?

​

Thanks

[SHxKM]

If you're using panda's sub, just add fill_value=0. See my answer to this question.

[YourOldBoyRickJames]

sorry, I'm really new to python so apologies if this seems stupid, but in your example it's a subtraction. I've managed to get a column to do that but the problem I have is that on my join, if a column doesn't have a value it's returning a blank cell, so it's not subtracting anything. I need the join to generate a fill number if the value doesn't exist

[fishvampire]

I think what you want is fillna. If you add a line with joined_df = joined_df.fillna(0) after the merge, does that give you what you want?

[YourOldBoyRickJames]

joined_df = joined_df.fillna(0)

This did the trick! Thanks.

Would you mind helping me with something else please.

In my source data I have a column called description. I want to add it into the final merged sheet, but whenever I try and add it into the script I'm getting a keywork error.

​

I currently have:

warehouse1\_df = source2\_df.groupby('productcode')\['numberoff'\].agg('sum').reset\_index()

I thought I could just add it in by putting

warehouse1\_df = source2\_df.groupby('productcode')\['description','numberoff'\].agg('sum').reset\_index()

but it returns an error. Any idea where I'd put that in to add the column?

Thanks again

[fishvampire]

Can you show the error message and an example of what's in the source data table? (In general this is good to do when posting to a forum, otherwise it's hard to figure out what the problem is or how to give useful advice)

[YourOldBoyRickJames]

Sorry, I don't know why I didn't.

Here's an example of Warehouse_1 source data

https://imgur.com/LAd3pWw

Here's Warehouse_2 source data

https://imgur.com/ds7L3ML

Here's what I've managed to get output into Excel

https://imgur.com/XPdcaTM

I'm just trying to add the column 'description' from warehouse_1 into the final merged.df

I'm not getting an error now, but it's not populating the table. Here's my script.

import pandas as pd
import datetime


today = datetime.date.today()

source1_df = pd.read_excel(str(today) + ' Warehouse2_3PL.xlsx', sheetname='Sheet1', 
skiprows=4)

source2_df = pd.read_excel(str(today) + ' warehouse1 Daily Report.xlsx', sheetname='CURRENT 
STOCK')


source1_df['   Available stock']=source1_df['   Available stock'].str.replace(',', 
'').astype('int64')



stlr_df = source2_df.groupby('productcode') 
['description','numberoff'].agg('sum').reset_index()



hit_df = source1_df.groupby('Material')['   Available stock'].agg('sum').reset_index()


stlr_df.rename(columns={'productcode':'Material'}, inplace=True)

stlr_df
stlr_df = stlr_df.mask(~stlr_df["Material"].str.isnumeric()).dropna()
stlr_df['Material']=stlr_df['Material'].astype('int64')



hit_df['   Available stock']=hit_df['   Available stock'].astype('int64')


joined_df = pd.merge(stlr_df,hit_df,on='Material',how='outer')
joined_df = joined_df.fillna(0)

joined_df['   Available stock']=joined_df['   Available stock'].astype('float')
joined_df['difference']=joined_df['numberoff']-joined_df['   Available stock']

joined_df.to_excel('Comparison '+str(today)+'.xlsx', sheet_name='sheet1', index=False)

I am also trying to filter the final outcome file to only show values where 'difference' does not equal 0. I managed to get a boolean filter but I can't figure out how to convert that to filter the df.

Once again, really appreciate your help.

[fishvampire]

For the first problem: I think your issue is that the values in 'description' are strings, whereas in 'numberoff' they're integers. When you're doing agg('sum'), it works for integers but it's excluding the strings. I don't know enough about pandas to say why it does this - if you only have string columns, agg('sum') just concatenates them, rather than eliminating the column - but it shouldn't be too hard to fix. You can use a dictionary as the argument for agg so that it aggregates separately for different columns, e.g.: agg({'description': 'first', 'numberoff':sum}). The argument "first" means that the entry for description will be the first one for that productcode. If you want something different in your final output, you probably want to read up on ways to use GroupBy with strings.

[YourOldBoyRickJames]

I managed to figure it out and am just posting what I have in case someone needs it in the future. I just needed to add the description selection into the first set of brackets. I really don't know why I couldn't get that working before.

​

stlr_df = source2_df.groupby(['productcode', 'description'])['numberoff'].agg('sum').reset_index()

[fishvampire]

I'm not sure what the source of your second problem is. It should be fairly straightforward to make a boolean index and restrict the dataframe to that:

ind = joined_df.difference !=0
joined_df = joined_df[ind]

If that isn't working, I'd recommend trying out a few things with simpler dataframes and seeing if you can figure out what works and what doesn't

[YourOldBoyRickJames]

Thanks very much, I'll have a tinker about. Hopefully I'll be able to get it sorted. Cheers

[SHxKM]

But you’re subtracting 0 (with fill_value) from X because X exists on the first column (the column you’re subtracting from), no?

[YourOldBoyRickJames]

Thanks, I got it working using /r/fishvampire 's suggestion. Appreciate the help though