Python Forum

Hi,

I need some help with formatting the data within my excel files.

I have an excel report that includes multiple lines of data for the same unique id which I wish to transform it into a more readable form.

For example:

Unique ID Timestamp Method Type Transaction Details [-] [+] Gross Amount
6606521 2021-06-01 10:42:00 Online Deposits Deposit 50 50
6606521 2021-06-01 10:42:00 Online Deposits Variable Commission 1.6
6606521 2021-06-01 10:42:00 Online Deposits Fixed Commission 0.29

Instead of the above, I wish to create a dataframe with 3 new columns (Variable Commission, Fixed Commission, Other Commission) that would list all information in one line.

Unique ID Timestamp Method Type Transaction Details Gross Amount Variable Commission Fixed Commission Other Commission Net Amount
6606521 2021-06-01 10:42:00 Online Deposits Deposit 50 1.6 0.29 0 48.11

How can I do this? Should I use the groupby function? The attached image might help in explaining what I am looking for better.

[attachment=1193]

Thanks for your help! I am new to this forum and I am looking forward to learning a lot from here.

Aaron

Hello mate,

If you make a second list/array to save the second table to, you can then add the data from the array/list to the main dataframe.

# Specifying each value in the new column:
df['newColumn'] = [1, 2, 3, 4]
 
# Setting each row in the new column to the same value:
df['newColumn'] = 1
 
# Creating a new column by doing a 
# calculation on an existing column:
df['newColumn'] = df['oldColumn'] * 5

You should save the spreadsheet as a CSV if possible and look at the:

import csv

Documentation here:
https://docs.python.org/3/library/csv.html

Just to give you a starting point, lets say you want to save a list of emails from a CSV to your program:

import csv

#make an empty array
list_of_emails = []

#open or read the csv
with open('users.csv',newline="") as users:
       user_read = csv.DictReader(users)
#the next bit tells it to read through each line and update the array. 
       for row in user_read:
              list_of_emails.append(row['Email'])

I hope this helps. If you have any questions then let me know on here or pm me.

Kind regards,
James