I am new to using Python and Pandas, but have been trying to automate some of the data cleaning/merging for reports of mine. So far I've had success in building up the combined file of all information I need to feed into my reporting summary but have gotten stuck with grouping and merging data with matching prefixes.
I have a data set that is structured similar to this in a pandas dataframe:
Company_Num Company_Name 2019_Amt 2020_Amt Code Flag Manager
1 ABC Company Ltd 2000 400 A Y John
1 ABC Company Ltd 2000 400 A Y John
2 DEFGHIJ Company (London) 480 100 B N James
3 DEFGHIJ Company (Bristol) 600 700 B N James
4 DEFGHIJ Company (York) 1500 1000 B N James
5 KLM Services 9000 7000 A Y Jane
6 NOPQ Industries 300 400 C Y Jen
7 NOPQ Industries - London 7000 8000 C Y Jen
I'm wanting to get a summary set of data where there are no duplicates in my data and instead of having rows for each office I have one summarised value for each company. Ultimately with a dataframe like:
Company_Name 2019_Amt 2020_Amt Code Flag
ABC Company Ltd 2000 400 A Y
DEFGHIJ Company 2580 1800 B N
KLM Services 9000 7000 A Y
NOPQ Industries 7300 8400 C Y
So far I have managed to drop the duplicates using:
df.drop_duplicates(subset=['Company_Num', 'Company_Name', 'Code', '2019_Amt', '2020_Amt'])
With the resulting table:
Company_Num Company_Name 2019_Amt 2020_Amt Code Flag Manager
1 ABC Company Ltd 2000 400 A Y John
2 DEFGHIJ Company (London) 480 100 B N James
3 DEFGHIJ Company (Bristol) 600 700 B N James
4 DEFGHIJ Company (York) 1500 1000 B N James
5 KLM Services 9000 7000 A Y Jane
6 NOPQ Industries 300 400 C Y Jen
7 NOPQ Industries - London 7000 8000 C Y Jen
The solution that I have tried is to substring the first 9 characters of each company name and use a groupby and sum on those, but that leaves me with the column being saved as the substring. This has also dropped the columns Code and Flag from my dataframe, leaving me with table like this:
df['SubString_Company_Name'] = df['Company_Name'].str.slice(0,9)
df.groupby([df.SubString_Company_Name]).sum().reset_index()
SubString_Company_Name 2019_Amt 2020_Amt
ABC Compa 2000 400
DEFGHIJ C 2580 1800
KLM Servi 9000 7000
NOPQ Indu 7300 8400
I have tried to use the os.path.commonprefix function to get the company names, but can't find a way to use it in a dataframe, and for multiple values. My understanding is it will look at the list as a whole and return the longest common prefix of the whole list which wouldn't work. I have also considered extracting all duplicate substrings into new dataframes and summing and renaming there before merging back into one data set, but I'm not sure if that would work. The solutions I've found online have been centred around uniform data where lambda can be used with a delimiter or the prefix is always the same size, whereas my data is not uniform and the prefixes are varying sizes.
My data is changed every month and so I want to design a dynamic solution that isn't relying on substrings since I could run into issues with only taking 9 characters. My final consideration is to extract the SubString_Company_Name into a list, convert that to the os.path.commonprefix of the Company_Name and then save the unique commonprefix value of each Company_Name into a new list and for each item in that list create a new summary table. But I don't know if this would work and I want to know if there's a better or more efficient way of doing this before trying.