'Comparing data between two CSV files to move data to a third CSV file
I have this csv file, file1.csv:
OU
CORP:Jenny Smith:
"CORP:John Smith:,John Smith:"
STORE:Mary Poppins:
STORE:Tony Stark:
STORE:Carmen Sandiego:
NEWS:Peter Parker:
NEWS:Clark Kent:
STORES:store123:
NEWS:CORPmanager:
NEWS:STOREmanager:
ICECREAM:Will Ferrall:
SECURITY:James Bond:
SECURITY:Sherlock Holmes:
DELI:Brian Johnson:
""
Then file2.csv:
OU
CORP
STORE
NEWS
ICECREAM
SECURITY
DELI
""
For every line in file2.csv that has 'CORP', 'STORE', or 'NEWS', I already searched through file1.csv and created a file, such as STOREall.csv, CORPall.csv, and NEWSall.csv.
I want OUs, such as ICECREAM, SECURITY, and DELI to be in the CORPall.csv file too.
So NEWSall.csv has:
OU
NEWS:Peter Parker:
NEWS:Clark Kent:
NEWS:CORPmanager
NEWS:STOREmanager
In CORPall.csv
OU
CORP:Jenny Smith:
CORP:John Smith:,John Smith:
ICECREAM:Will Ferrall:
SECURITY:James Bond:
SECURITY:Sherlock Holmes:
DELI:Brian Johnson:
In STOREall.csv
OU
STORE:Mary Poppins:
STORE:Tony Stark:
STORE:Carmen Sandiego:
STORES:store123:
I am using Pandas and CSV in my program.
I used this snippet of code to move STORE: or CORP:, etc., to their corresponding files.
allOUs is all the OUs that go to file2.csv.
for dept in allOUs['OU']:
df_dept = df[df['OU'].str.startswith(f'{dept}:')]
df_dept['OU'].to_csv(f'{dept}all.csv', index=False, header=False)
This is the logic in my head of what I want to happen:
df_dept = df[df['OU'].str.startswith(f'{dept}:')]
if df_dept == "ICECREAM:" or df_dept == "SECURITY:" or df_dept == "DELI:":
df_dept['OU'].to_csv('CORPall.csv', index=False, header=False)
else: #if everything else is not icecream, security or deli
#move them normally to their self named files
df_dept['OU'].to_csv(f'{dept}all.csv', index=False, header=False)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
