I have two csv files. One is query file, the other is key file.
This is the query file:
Date Copies Client Partner
07/10/2022 8 AAA IM
07/10/2022 3 BBB KZA
08/10/2022 10 AAA KZA
09/10/2022 6 BBB IM
It shows the copies made for each client and the partner working for it.
I need to add two columns Client ID and Project ID. Those details are in another csv file i.e. key.
I want pandas to look for the combination of Client + Partner and add in the right Project ID and Client ID in the next columns.
Note that the Client ID stays the same regardless of which partner is working on it, however the Project ID changes based on the combination of Client and Partner.
This is the key file:
Partner Client Project ID Client ID
IM AAA PRJ-01 CC002
IM BBB PRJ-09 CC008
IM CCC PRJ-11 CC006
KZA AAA PRJ-07 CC002
KZA BBB PRJ-04 CC008
MIA AAA PRJ-17 CC002
MIA BBB PRJ-14 CC008
I was thinking of using the np.select but the combination of clients and partners will take the inputs to several thousands:
cond = [
(df['Client'] == 'AAA'),
(df['client'] == 'BBB'),
]
choices = ['CC002','CC008']
df['Client ID'] = np.select(cond, choices)
cond2 = [
(df['Client'] == 'AAA') & (df['Partner'] == 'IM'),
(df['Client'] == 'AAA') & (df['Partner'] == 'KZA'),
(df['Client'] == 'BBB') & (df['Partner'] == 'IM'),
(df['Client'] == 'BBB') & (df['Partner'] == 'KZA'),
]
choices2 = ['PRJ-01','PRJ-07','PRJ-09','PRJ-04']
df['Project ID'] = np.select(cond2, choices2)
This will take me ages to put in all the date and hence I am looking for a solution that can read the csv files and automatically populate the date based on the combinations.
Little help will be appreciated. Thanks!
