Calculate % Difference in Pandas and apply special conditions to results

user_9488179 0
Viewed 25

I'm new to python and trying to get % differences in users between each week starting from the oldest. So trying to calculate like this (this calculation is from Tableau, but I want python to do similar):

3to2WeeksAgo difference:

IF [Users3weeksAgo] != 0 THEN ([Users2weeksAgo]-[Users3weeksAgo])/[Users3weeksAgo]
END

input.csv:

id    ThisWeekUsers LastWeekUsers   Users2weeksAgo  Users3weeksAgo
 1           91              20          8               15
 2            4              1           0               0
 3            4              1           3               14
 ...

What I couldn't get was strictly formatted output. I need my results to show as below: Output.csv 

id    ThisWeekUsers LastWeekUsers   Users2weeksAgo  Users3weeksAgo 3to2WeeksAgo 2toLastWeek LastToThisWeek
 1           91              20          8               15            -46.7%      150.0%         355.0%
 2            4              1           0               0                                        300.0%
 3            4              1           3               14            -78.6%      -66.7%         300.0%
 ...

My code so far:

 import pandas as pd

 df = pd.read_csv('path/to/my/input.csv')

 df['3to2WeeksAgo'] = df['Users2weeksAgo']-df['Users3weeksAgo']/df['Users3weeksAgo']


 df.to_csv('path/to/my/output.csv')

Also, I want to make sure there are no NaN or inf values in my output and keep format strictly as 00.0

These are the results when comparing 3 weeks ago to 2 weeks ago with the code above:

enter image description here

The above results should show:

3to2WeeksAgo
   -46.7% 

   -78.6%

Thanks for your help in advance!

EDIT:

Thanks for your help Kranthi! I've run this code and it seems to be working. However I have additional question. How can I prevent 0.0 from showing when comparing 0 from one week with 0 in another week. Same for 'inf' value. Could you help me getting no values for those cases?see below:

enter image description here

pandas math csv python
1 Answers
import pandas as pd

df = pd.read_csv('path/to/my/input.csv')

df['3to2WeeksAgo'] = np.round(100 * (df['Users2weeksAgo'] - df['Users3weeksAgo']) / df['Users3weeksAgo'], 1)

Same for 2toLastWeek, LastToThisWeek

df = df.fillna(0)
df.to_csv('path/to/my/output.csv')
user_76706180
Related