I have a dataframe like this:
date sales company country
16/03/2012 3000 H&M US
13/04/2012 2300 H&M US
26/03/2012 1230 H&M FR
13/04/2012 1300 H&M FR
23/03/2012 2230 H&M IT
19/04/2012 1100 H&M IT
16/03/2012 3000 ABC US
13/04/2012 2300 ABC US
26/03/2012 100 ABC FR
13/04/2012 60 ABC FR
23/03/2012 435 ABC IT
19/04/2012 300 ABC IT
I want to define a criteria: A company is considered to be an internatinal if less than 50% of its (avarage)sales coming from the single country. I want to create a new column that takes the value of 1 if it is international and 0 otherwise. Final output should look something like this:
date sales company country international
16/03/2012 3000 H&M US 1
13/04/2012 2300 H&M US 1
26/03/2012 1230 H&M FR 1
13/04/2012 1300 H&M FR 1
23/03/2012 2230 H&M IT 1
19/04/2012 1100 H&M IT 1
16/03/2012 3000 ABC US 0
13/04/2012 2300 ABC US 0
26/03/2012 100 ABC FR 0
13/04/2012 60 ABC FR 0
23/03/2012 435 ABC IT 0
19/04/2012 300 ABC IT 0
How I could do that ? P.S: There might be missing sales values in the dataset, how could I say ignore those values ?
Use transform for means, comapre them and groupby with aggregate all for check if all values return Trues:
s2 = df.groupby('company')['sales'].transform('mean') / 2
print (s2)
0 930.00
1 930.00
2 930.00
3 930.00
4 930.00
5 930.00
6 516.25
7 516.25
8 516.25
9 516.25
10 516.25
11 516.25
Name: sales, dtype: float64
s1 = df.groupby(['company', 'country'])['sales'].transform('mean')
print (s1)
0 2650.0
1 2650.0
2 1265.0
3 1265.0
4 1665.0
5 1665.0
6 2650.0
7 2650.0
8 80.0
9 80.0
10 367.5
11 367.5
Name: sales, dtype: float64
df['international'] = (s1 > s2).groupby(df['company']).transform('all').astype(int)
print (df)
date sales company country international
0 16/03/2012 3000 H&M US 1
1 13/04/2012 2300 H&M US 1
2 26/03/2012 1230 H&M FR 1
3 13/04/2012 1300 H&M FR 1
4 23/03/2012 2230 H&M IT 1
5 19/04/2012 1100 H&M IT 1
6 16/03/2012 3000 ABC US 0
7 13/04/2012 2300 ABC US 0
8 26/03/2012 100 ABC FR 0
9 13/04/2012 60 ABC FR 0
10 23/03/2012 435 ABC IT 0
11 19/04/2012 300 ABC IT 0