Panda - Compare Two Dataframes - Verify INT difference and print-CodePudding

i tried a lot to find a solution to this before get help on this issue that have so many answers here. But even trying some solutions I couldn't solve.

I have a code that check Keywords rank on google and return a CSV. Every day i get data but now i want to compare dataframe from today/yesterday and see if the keyword has been up or down.

How can I compare Rank int and verify how many changes the keywork had?

print('Today alargador 6mm na orelha: 1 position')
print('Today alargador 8mm na orelha: -3 position')

Thank you!

                        Rank   Device       Time
Keyword                                         
alargador 18mm            42  desktop 2021-06-10
alargador 6mm na orelha   29  desktop 2021-06-10
alargador 8mm na orelha   28  desktop 2021-06-10
alargador caracol         18  desktop 2021-06-10
alargador de madeira      11  desktop 2021-06-10
                        Rank   Device       Time
Keyword                                         
alargador 18mm            42  desktop 2021-07-10
alargador 6mm na orelha   28  desktop 2021-07-10
alargador 8mm na orelha   31  desktop 2021-07-10
alargador caracol         18  desktop 2021-07-10
alargador de madeira      11  desktop 2021-07-10

My code:

import glob
import pandas as pd
from datetime import date
from datetime import datetime, timedelta

path = r"C:\Users\...\CSV\*.csv"

def print_full(x):
   pd.set_option('display.max_rows', None)
   pd.set_option('display.max_columns', None)
   pd.set_option('display.width', 2000)
   pd.set_option('display.float_format', '{:20,.2f}'.format)
   pd.set_option('display.max_colwidth', None)
   print(x)
   pd.reset_option('display.max_rows')
   pd.reset_option('display.max_columns')
   pd.reset_option('display.width')
   pd.reset_option('display.float_format')
   pd.reset_option('display.max_colwidth')


today = date.today()
yesterday = (datetime.now()-timedelta(days=1)).strftime("%Y-%d-%m")
d1 = today.strftime("%Y-%d-%m")
d2 = yesterday
print("Today's date:", d2)


df = pd.concat(map(pd.read_csv, glob.glob(path)))
df1 = df.drop('URL', axis=1)
df1['Time'] = pd.to_datetime(df['Date'], utc=False)

df2 = df1.drop('Date', axis=1)
df2.set_index('Keyword', inplace=True)

df3 = df2[df2.Time == d2]
df4 = df2[df2.Time == d1]

print_full((df3).head(5))
print_full((df4).head(5))

CodePudding user response：

If you have 2 dataframes one for yesterday and one for today:

>>> df1
                         Rank   Device        Time
Keyword                                           
alargador 18mm             42  desktop  2021-06-10
alargador 6mm na orelha    29  desktop  2021-06-10
alargador 8mm na orelha    28  desktop  2021-06-10
alargador caracol          18  desktop  2021-06-10
alargador de madeira       11  desktop  2021-06-10

>>> df2
                         Rank   Device        Time
Keyword                                           
alargador 18mm             42  desktop  2021-07-10
alargador 6mm na orelha    28  desktop  2021-07-10
alargador 8mm na orelha    31  desktop  2021-07-10
alargador caracol          18  desktop  2021-07-10
alargador de madeira       11  desktop  2021-07-10

out = df1['Rank'].sub(df2['Rank']).loc[lambda x: x != 0].to_frame()
print(out)

# Output:
                         Rank
Keyword                      
alargador 6mm na orelha     1
alargador 8mm na orelha    -3

Update

If the rank is the same show same value

Just remove the .loc part:

out = df1['Rank'].sub(df2['Rank']).to_frame()
print(out)

# Output:
                         Rank
Keyword                      
alargador 18mm              0
alargador 6mm na orelha     1
alargador 8mm na orelha    -3
alargador caracol           0
alargador de madeira        0