Hi I am new and try to understand how I can use nested loops in python. I tried to understand summing up same values and learned to use the group_by function (based on another question in stackoverflow which I saw today). I would like to learn the pytonic-dataframe way.
Now I wanted to have sum up the working days in the following way. I am summing up based on the scenario the units, e.g.: Scenario = 1, Company = A, Country = USA, Unit = HR+Corporate Client, sum up the working hours = 65+63 = 128 etc. After the raw data I included how the output should look like. I am not sure whether this would also work with group_by which is more like a pivot way doing it.
I started with a nested loop but having problems with indexing the dates. Therefore my codes filters only on a date by date basis which is not efficient but it works. I learned that nested loops are not sufficient for dataframes but not sure which way I can go. The code looks like the following:
import pandas as pd
working_date_start = '2017-07-14'
working_date_end = '2017-07-15'
flag_scenario = 0
Scenario = 0
df = pd.read_csv('C:/Comapny_WorkingHours.csv', encoding='cp1252', sep=';', index_col=None).dropna()
df = df[(df['working_date'] >= working_date_start) & (df['working_date'] < working_date_end) & (df['flag'] == flag_scenario) & (df['Scenario'] >= Scenario)]
pd_date = pd.DatetimeIndex(df['working_date'].values)
df['working_date'] = pd_date
index_data = df.set_index('working_date')
for current_date in index_data.index.unique():
print('calculating date: ' +str(current_date))
for i in range(0, len(df)):
for j in range(i+1, len(df)):
if df.iloc[i]['Scenario'] == df.iloc[j]['Scenario'] and df.iloc[i]['Unit'] != df.iloc[j]['Unit'] and df.iloc[i]['Company'] == 'Company A' and df.iloc[j]['Company'] == 'Company A' and df.iloc[i]['Country'] == 'USA' and df.iloc[j]['Country'] == 'USA':
print(df.iloc[i]['Scenario'], df.iloc[j]['Scenario'])
print(df.iloc[i]['Unit'], df.iloc[j]['Unit'])
The original data looks like the following:
working_date flag Scenario Working Hours Country Company Unit
2017-07-14 0 1 65 USA Company A HR
2017-07-14 0 2 75 USA Company A HR
2017-07-14 0 3 73 USA Company A HR
2017-07-14 0 4 66 USA Company A HR
2017-07-14 0 1 63 USA Company A Corporate Client
2017-07-14 0 2 51 USA Company A Corporate Client
2017-07-14 0 3 60 USA Company A Corporate Client
2017-07-14 0 4 55 USA Company A Corporate Client
2017-07-14 0 1 71 USA Company A Controlling
2017-07-14 0 2 45 USA Company A Controlling
2017-07-14 0 3 76 USA Company A Controlling
2017-07-14 0 4 62 USA Company A Controlling
2017-07-14 0 1 57 USA Company A Corporate Center
2017-07-14 0 2 64 USA Company A Corporate Center
2017-07-14 0 3 68 USA Company A Corporate Center
2017-07-14 0 4 69 USA Company A Corporate Center
2017-07-14 0 1 54 USA Company B Private and Business Customers
2017-07-14 0 2 62 USA Company B private and business customers
2017-07-14 0 3 47 USA Company B private and business customers
2017-07-14 0 4 62 USA Company B private and business customers
2017-07-14 0 1 45 USA Company B Marketing
2017-07-14 0 2 78 USA Company B Marketing
2017-07-14 0 3 59 USA Company B Marketing
2017-07-14 0 4 78 USA Company B Marketing
2017-07-14 0 1 49 USA Company B IT
2017-07-14 0 2 74 USA Company B IT
2017-07-14 0 3 78 USA Company B IT
2017-07-14 0 4 55 USA Company B IT
2017-07-14 0 1 66 USA Company B Project Management
2017-07-14 0 2 76 USA Company B Project Management
2017-07-14 0 3 53 USA Company B Project Management
2017-07-14 0 4 58 USA Company B Project Management
2017-07-15 0 1 56 USA Company A HR
2017-07-15 0 2 54 USA Company A HR
2017-07-15 0 3 77 USA Company A HR
2017-07-15 0 4 58 USA Company A HR
2017-07-15 0 1 78 USA Company A Corporate Client
2017-07-15 0 2 76 USA Company A Corporate Client
2017-07-15 0 3 59 USA Company A Corporate Client
2017-07-15 0 4 56 USA Company A Corporate Client
2017-07-15 0 1 57 USA Company A Controlling
2017-07-15 0 2 54 USA Company A Controlling
2017-07-15 0 3 56 USA Company A Controlling
2017-07-15 0 4 74 USA Company A Controlling
2017-07-15 0 1 71 USA Company A Corporate Center
2017-07-15 0 2 75 USA Company A Corporate Center
2017-07-15 0 3 79 USA Company A Corporate Center
2017-07-15 0 4 78 USA Company A Corporate Center
2017-07-15 0 1 74 USA Company B Private and Business Customers
2017-07-15 0 2 72 USA Company B private and business customers
2017-07-15 0 3 66 USA Company B private and business customers
2017-07-15 0 4 66 USA Company B private and business customers
2017-07-15 0 1 69 USA Company B Marketing
2017-07-15 0 2 69 USA Company B Marketing
2017-07-15 0 3 63 USA Company B Marketing
2017-07-15 0 4 59 USA Company B Marketing
2017-07-15 0 1 57 USA Company B IT
2017-07-15 0 2 67 USA Company B IT
2017-07-15 0 3 77 USA Company B IT
2017-07-15 0 4 60 USA Company B IT
2017-07-15 0 1 55 USA Company B Project Management
2017-07-15 0 2 57 USA Company B Project Management
2017-07-15 0 3 80 USA Company B Project Management
2017-07-15 0 4 59 USA Company B Project Management
The output I want looks like the following:
working_date Scenario Units Working Hours Summed Up
2017-07-14 1 HR_Corporate Client 128
2017-07-14 1 HR_Controlling 136
2017-07-14 1 HR_Corporate Center 122
2017-07-14 2 HR_Corporate Client 126
2017-07-14 2 HR_Controlling 120
2017-07-14 2 HR_Corporate Center 139
2017-07-14 3 HR_Corporate Client 133
2017-07-14 3 HR_Controlling 149
2017-07-14 3 HR_Corporate Center 141
2017-07-14 4 HR_Corporate Client 121
2017-07-14 4 HR_Controlling 128
2017-07-14 4 HR_Corporate Center 135
2017-07-14 1 Corporate Client_Controlling 134
2017-07-14 1 Corporate Client_Corporate Center 120
2017-07-14 2 Corporate Client_Controlling 96
2017-07-14 2 Corporate Client_Corporate Center 115
2017-07-14 3 Corporate Client_Controlling 136
2017-07-14 3 Corporate Client_Corporate Center 128
2017-07-14 4 Corporate Client_Controlling 117
2017-07-14 4 Corporate Client_Corporate Center 124
2017-07-14 1 Controlling_Corporate Center 128
2017-07-14 2 Controlling_Corporate Center 109
2017-07-14 3 Controlling_Corporate Center 144
2017-07-14 4 Controlling_Corporate Center 131
import pandas as pd
df = pd.read_csv('C:/Comapny_WorkingHours.csv', encoding='cp1252', sep=';', index_col=None).dropna()
df = df.reset_index(drop=False)
# this will give you the unique combinations of two units
from itertools import combinations
scenario_list = df['Scenario'].unique().tolist()
# this creates a dict containing the scene and corresponidng units combos
combos_dict = {}
for scene in scenario_list:
units_list = df[df['Scenario'] == scene]['Unit'].unique().tolist()
combos_dict[scene] = list(combinations(units_list, 2))
new_df = pd.DataFrame()
for key in combos_dict.keys():
# filters the dataframe by the scenario matched in the combo_dict
filter_df = df[df['Scenario'] == key]
for combo in combos_dict[key]:
# itterates through the combo_dict values to create a sub_filter
# that is used to create a new final dataframe
sub_filter = filter_df[(filter_df['Unit'] == combo[0]) |
(filter_df['Unit'] == combo[1])]
sub_df = pd.DataFrame(data=[[sub_filter['working_date'].iloc[0],
key,
'{}_{}'.format(combo[0], combo[1]),
sum(sub_filter['Working Hours'])]],
columns=['working_date',
'Scenario',
'Units',
'Working Hours Summed Up'])
# creates a new dataframe with the desired output
new_df = new_df.append(sub_df)