0

I am automating an Excel process that is nearly finished but I am running up against a problem that I have not been able to solve. I have these rows (placeholder numbers) that are only different in the program code. I need them summed under the 1450 program code. So, I changed the program code for all those rows to 1450 and put them into a pivot table to sum them up. I then deleted the original rows in the dataframe and used the concat function to add in the new one. The problem is that, as far as I can tell, the concat function is doing absolutely nothing. I verified that the pivot table is the row that I want so I am not exactly sure why it is not working.

Here is my code for this:

merged_df.loc[(merged_df['Category'] == 'Workers Comp') & (merged_df['Direct/Assumed'] == 'THC QS'), 'Program'] = 1450
merged_df['Workaround'] = merged_df['Direct/Assumed']
wcF = merged_df[(merged_df.Workaround == 'THC QS') & (merged_df.Category == 'Workers Comp')]
wcP = pd.pivot_table(wcF, index = ['Category', 'AY', Program', 'Office_Adj', 'ASLOB_Adjusted', 'State'], values = ['LossP', 'ALAEP', 'ULAEP', 'AAO-ULP'], aggfunc=np.sum)
merged_df.drop(merged_df[(merged_df['Category'] == 'Workers Comp') & (merged_df['Direct/Assumed'] == 'THC QS')].index, inplace = True)
merged_df = pd.concat([merged_df, wcP], ignore_index = True)

The workaround is there because it did not like the / in the column name. Can anyone help me with this problem or give me a better way to go about this? Sorry for the image of the rows rather than code.

edit: After printing the df correctly, I noticed the result is coming in like this:

{'AAO-ULP': {('Workers Comp', 2023, 1450.0, 10.0, 16.0, 'MI'): #}, 'ALAEP': {('Workers Comp', 2023, 1450.0, 10.0, 16.0, 'MI'): #}, 'LossP': {('Workers Comp', 2023, 1450.0, 10.0, 16.0, 'MI'): #}, 'ULAEP': {('Workers Comp', 2023, 1450.0, 10.0, 16.0, 'MI'): #}}

It looks correct in the Excel file despite the df looking like that. I want it to be all in one row where LossP, ALAEP, ULAEP, and AAO-ULP are columns. I am not sure how to show that output not in Excel.

SaicheS
  • 11
  • 3
  • 1
    please don't include images of data or code. It will be easier to help you if your example data is included in your example code. Also include the output you're looking for – oh_my_lawdy Jul 27 '23 at 18:26
  • Hmm, I tried printing it in the file to paste in here but it looked different from the excel file in the image. Could that be an indication of an issue or could that be a formatting issue? I do not have any options changed besides max columns. – SaicheS Jul 27 '23 at 18:50
  • Please provide a sample of your dataframe using `df.head().to_dict()` and also include your expected output. – not_speshal Jul 27 '23 at 19:14
  • I edited it but I am not sure how useful what I put is. – SaicheS Jul 27 '23 at 19:45
  • Refrain from showing your dataframe as an image. Your question needs a minimal reproducible example consisting of sample input, expected output, actual output, and only the relevant code necessary to reproduce the problem. See [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) for best practices related to Pandas questions. – itprorh66 Jul 27 '23 at 19:47

0 Answers0