Python-and-Tableau/valueinc_sales.py at main · Mandar-1007/Python-and-Tableau · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
# -*- coding: utf-8 -*-
"""
Created on Wed Aug 31 09:34:52 2022

@author: Mandar
"""

import pandas as pd

# file_name = pd.read_csv('file.csv') ----> format to read_csv

data = pd.read_csv('transaction.csv')

data = pd.read_csv('transaction.csv',sep=';')

# Summary of the data

data.info()

#working with calculations
#Defining variables

CostPerItem = 11.73
SellingPricePerItem = 21.11
NumberOfItemsPurchased = 6

#Mathematical operations on Tableau

ProfitsPerItem = 21.11 - 11.73
ProfitsPerItem = SellingPricePerItem - CostPerItem

ProfitPerTransaction = ProfitsPerItem * NumberOfItemsPurchased

CostPerTransaction = CostPerItem * NumberOfItemsPurchased

SellingPricePerTransaction = SellingPricePerItem * NumberOfItemsPurchased

#CostPerTransaction Column calculation
#CostPerTransaction = CostPerItem * NumberOfItemsPurchased
# variable = dataframe['column_name']

CostPerItem = data['CostPerItem']

NumberOfItemsPurchased = data['NumberOfItemsPurchased']

CostPerTransaction = CostPerItem * NumberOfItemsPurchased

#Adding New column to data frame

data['CostPerTransaction'] = CostPerTransaction

# data['CostPerTransaction'] = data['CostPerItem'] * data['NumberOfItemsPurchased']

data['SalesPerTransaction'] = data['SellingPricePerItem'] * data['NumberOfItemsPurchased']

#Profit claculation

data['ProfitPerTransaction'] = data['SalesPerTransaction'] - data['CostPerTransaction']

data['Markup'] = data['ProfitPerTransaction']/(data['CostPerTransaction'])

#Rounding Markup

#roundMarkup = round(data['Markup'],2)

data['Markup'] = round(data['Markup'],2)

#Combining Data fields

my_name = 'Mandar'+' Nadkarni'

my_date = 'Day'+'-'+'Month'+'-'+'Year'

#data['Date'] = data['Day']+'-'

#checking column data type
print(data['Day'].dtype)

#Change columns type

day = data['Day'].astype(str)

year = data['Year'].astype(str)

print(day.dtype)

my_date = day+'-'+data['Month']+'-'+year

data['date'] = my_date

#using iloc to view specific columns/rows

data.iloc[0]    #views the row with index as 0

data.iloc[0:3]  #views first 3 rows

data.iloc[-5:]  #last 5 rows

data.head(5)    #first 5 rows

data.iloc[:,2]  #all rows and 2nd column

data.iloc[4,2]  #4th row and 2nd column

#Using split to split the client keywords field
#new_var = column.str.split('sep', expand = True)

split_col = data['ClientKeywords'].str.split(',',expand = True)

#Creating new columns for the split column in client keywords

data['ClientAge'] = split_col[0]

data['ClientType'] = split_col[1]

data['LengthofContract'] = split_col[2]

#Using the replace function

data['ClientAge'] = data['ClientAge'].str.replace('[','')

data['LengthofContract'] = data['LengthofContract'].str.replace(']','')

#Change to lower case

data['ItemDescription'] = data['ItemDescription'].str.lower()

#how to merge files

#bringing in a new data set

seasons = pd.read_csv('value_inc_seasons.csv',sep=';')

#Merge files: merge_df = pd.merge(df_old, df_new, on = 'key')

data = pd.merge(data, seasons, on = 'Month')

#Dropping columns

#df = df.drop['columnname', axis = 1]

data = data.drop('ClientKeywords', axis = 1)

data = data.drop('Day', axis = 1)

data = data.drop(['Month','Year'], axis = 1)

#export into csv

data.to_csv('ValueInc_Cleaned.csv',index = False)