I'm learning Pandas. I have a project where I need to munge some CSV files and resave them as CSV. I can use dictionaries and CSV module, but decided to use DataFrames to get more exposure and practice with Pandas.
The task is to sum()
values for some keys while others are not summed. My solution was to add a column called "Line" where items with the same line number would be summed.
sales = [{"Line": 1, "KEY": "Apples", "AMOUNT": 3.99},
{"Line": 1, "KEY": "Apples", "AMOUNT": 3.99},
{"Line": 2, "KEY": "Oranges", "AMOUNT": 5.99},
{"Line": 3, "KEY": "Pears", "AMOUNT": 2.99},
{"Line": 3, "KEY": "Pears", "AMOUNT": 2.99},
{"Line": 4, "KEY": "Carrots", "AMOUNT": .99},
{"Line": 5, "KEY": "Carrots", "AMOUNT": .99},
]
df = pd.DataFrame(sales)
Now, Pandas groupby
might seem like a perfect solution, but since I need to export the munged file as CSV, this seemed like something I could do only to have to undo.
# Find duplicate Line entries
# Subset df into just duplicate `Line` values
df_tmp = df[df.duplicated(subset="Line", keep=False)]
# Save a list of Line numbers to sum
line_dups = df_tmp['Line'].unique()
for x in line_dups:
# Sum every line in the DF; one value sum is unchanged
# asum = df.loc[df['Line'] == x, 'AMOUNT'].sum()
# or
# Subset the subset
df_tmp2 = df_tmp[df_tmp["Line"] == x]
# sum the sub-subset
asum = df_tmp2['AMOUNT'].sum()
# set the value of all keys with the same Line value
df.loc[df['Line'] == x, 'AMOUNT'] = asum
# take the inverse of the duplicate subset on the original df
# Keep only the first duplicate line
df2 = df[~df.duplicated(subset="Line", keep='first')]
The solution is effective, df2
:
AMOUNT KEY Line
0 7.98 Apples 1
2 5.99 Oranges 2
3 5.98 Pears 3
5 0.99 Carrots 4
6 0.99 Carrots 5
And yet, this solution is sort of hairy with all of the temp dataframes. Maybe a more seasoned Pandas user will have something to add which might help me better understand Pandas?