At work, I use software for analyzing administrative data and daily I generate a CSV that looks like:ReceiptDate,Agent,Project,ControlNum 6/13/2017 18:02,Markieff,T180222559.P,492 6/13/2017 18:02,Markieff,T180222559.P,493 6/13/2017 18:02,Markieff,T180222559.P,494 6/12/2017 18:01,Markieff,C181224579.N,7240 6/11/2017 17:43,Marcus,T180046232.P,635 Exported count: 5,Total items count: 5,but with several hundred entries instead of just four. Sometimes the same Project can have dozens of ControlNums.I have to report this stuff by e-mail and via updating a couple of spreadsheets and it's a lot more presentable if it's in tables that look like:6/13/2017 18:02 | Markieff | T180222559.P | 494, 493, 4926/12/2017 18:01 | Markieff | C181224579.N | 72406/11/2017 17:43 | Marcus | T180046232.P | 635To that end and to fulfill my New Year's resolution to finally learn how to code a little bit, I've slapped together a script in Python that does that for me.The script is more than fast enough for my purposes but I always want to get better. The code is below, and my questions follow (though feedback of any kind is welcome):import pandas as pdpd.set_option('mode.chained_assignment', None)df = pd.read_csv('test.csv', skipfooter = 1, engine='python')# I convert ControlNum to strings for the sake of joining themdf['ControlNum'] = df['ControlNum'].apply(lambda x: str(x))df = df.sort_values(by=['ReceiptDate', 'Agent', 'Project', 'ControlNum'], ascending = False)# Marks the entries with a Project that's used more than oncedf['HasMultipleControlNums'] = df.duplicated(subset=['Project'], keep = False)# We don't need to mess with the entries with unique Projectsdf_complexprojects = df[df['HasMultipleControlNums'] == True]# I know C is a bad variable name but I barely understand groupby so I'm not sure# what a better, more descriptive name would be. Anyway, these two lines join# together ControlNums that belong to the same projectC = df_complexprojects.groupby(['Project'])['ControlNum'].apply(lambda x: ', '.join(x))# This line triggers pandas's SettingWithCopyWarning, which I've muted.df_complexprojects['ControlNum'] = df_complexprojects['Project'].apply(lambda x: C.get_value(x))# After joining together ControlNums, we have some identical entries. Bye-bye.df_complexprojects = df_complexprojects.drop_duplicates(subset='Project', keep='first')# This step is why df['HasMultipleControlNums'] exists. Is there a better way to# bring the entries I worked on and the entries I didn't together?df_final = df_complexprojects.append(df[df['HasMultipleControlNums'] == False], ignore_index = True)del df_final['HasMultipleControlNums']# In practice, this script is in a function. The returned dataframe# is manipulated using Beautiful Soup and win32api (to create Outlook# e-mails) and Flask (so I can do all of this via a web app with a GUI)# and archived as an Excel spreadsheetreturn df_finalHere it is again without the gratuitous comments:import pandas as pdpd.set_option('mode.chained_assignment', None)df = pd.read_csv('test.csv', skipfooter = 1, engine='python')df['ControlNum'] = df['ControlNum'].apply(lambda x: str(x))df = df.sort_values(by=['ReceiptDate', 'Agent', 'Project', 'ControlNum'], ascending = False)df['HasMultipleControlNums'] = df.duplicated(subset=['Project'], keep = False)df_complexprojects = df[df['HasMultipleControlNums'] == True]C = df_complexprojects.groupby(['Project'])['ControlNum'].apply(lambda x: ', '.join(x))df_complexprojects['ControlNum'] = df_complexprojects['Project'].apply(lambda x: C.get_value(x))df_complexprojects = df_complexprojects.drop_duplicates(subset='Project', keep='first')df_final = df_complexprojects.append(df[df['HasMultipleControlNums'] == False], ignore_index = True)del df_final['HasMultipleControlNums']return def_finalIs this readable/good style/Pythonic? Without the comments, would anyone understand what I'm doing here? Does it reek of amateurism?Do I need to create df_complexprojects and df_final? Is df['MultipleControlNums'] necessary? Is that SettingWithCopyWarning something I should take more seriously?Is pandas overkill for this? As a novice, would I be better-served learning how to do this with simple data structures? (An earlier version of this used openpyxl and not pandas and it was sooooooooooooo slow)What else should I be asking? I don't know what I don't know.I apologize if this is too broad/wordy for codereview. I'm new at all of this and am dying for feedback from more experienced folks. I really appreciate it.
Aggregating text and deleting duplicates with pandas to consolidate a table for reporting
python;python 2.7;pandas