Csv file split comma separated values into separate rows and dividing the corresponding dollar amount by the number of comma separated values in panda

Question

beginner here! I have a csv file with comma separated values. I want to split each comma separated value in different rows in pandas. However, the corresponding dollar amounts should be divided by the number of comma separated values in each cell and export the result in a different csv file. the csv table and the desired output table

I have used df.explode(IDs) but couldn’t figure out how to divide the Dollar_Amount by the number of IDs in the corresponding cells.

import pandas as pd
in_csv = pd.read_csv(‘inputCSV.csv’)
new_csv = df.explode(‘IDs’)
new_csv.to_csv(‘outputCSV.csv’)

Answer 1

You can divide the dollar amount by the number of ids in each row before using explode. This can be done as follows:

# Preprocessing
df['Dollar_Amount'] = df['Dollar_Amount'].str[1:].str.replace(',', '').astype(float)
df['IDs'] = df['IDs'].str.split(",")

# Compute the new dollar amount and explode
df['Dollar_Amount'] = df['Dollar_Amount'] / df['IDs'].str.len()
df = df.explode('IDs')

# Postprocessing
df['Dollar_Amount'] = df['Dollar_Amount'].round(2).apply(lambda x: '${0:,.2f}'.format(x))

With an example input:

        IDs  Dollar_Amount  A
0   1,2,3,4    $100,000.00  4
1     5,6,7     $50,000.00  3
2         9     $20,000.00  1
3     10,11     $20,000.00  2

The result is as follows:

   IDs  Dollar_Amount   A
0    1     $25,000.00   4
0    2     $25,000.00   4
0    3     $25,000.00   4
0    4     $25,000.00   4
1    5     $16,666.67   3
1    6     $16,666.67   3
1    7     $16,666.67   3
2    9     $20,000.00   1
3   10     $10,000.00   2
3   11     $10,000.00   2