Rearrange csv file

[Ice_face]

Hi,

I have several CSV files which looks like this. I would like to create a new CSV file which is rearranged to the one below.
*The time is irrelevant information in the new file.
*The number of ratings can changes from "questionnaire" to "questionnaire"
*Also I dont know how many qustionnaires will be handed in each day.


Hope someone can help me. I am new to python, and unfortunately the first time I have to use it is for my thesis. In other words, slightly desperate .



Form_date Rating Rating1 Rating2 Rating3 Rating4 Rating5 Rating6 Rating7 Rating8 Rating9 Rating10
07.10.2018 10:09 3
07.10.2018 10:31 2 2 1 1 1
07.10.2018 10:46 2 3
07.10.2018 10:48 1 2
08.10.2018 12:09 6
08.10.2018 12:31 2 2 1 1 1 2 4 6
09.10.2018 17:46 2 3 4 5
09.10.2018 17:48 2 3 4 5 2 3 4 5 2 3 4
09.10.2018 17:51 2 3
09.10.2018 17:52 3 3
09.10.2018 18:53 1 3
09.10.2018 19:46 2 1 1 1 1
ect…

I want to create a new CSV file which is rearranged and looks like the one below

07.10.2018 3 2 2 1 1 1 2 3 1 2
08.10.2018 6 2 2 1 1 1 2 4 6
09.10.2018 2 3 4 5 2 3 4 5 2 3 4 5 2 3 4 2 3 3 3 1 3 2 1 1 1 1
ect…

[perfringo]

One way to approach (any programming) task is to articulate solution (algoritm) in spoken language and then translate it into Python.

I see at least following steps:

1. Open and read csv files (how to open and read csv file?)
2. Get needed data from every row (how to split row in csv file and take only data what is needed?)
3. Write data to csv file (how to write data to new csv file?)

If you articulate steps needed you can start to solve smaller and simple tasks - (1) how to open csv file with Python; how to read data in csv file (2).... etc.

You should try to write code all by yourself. If you run into problems in some part of code then you can expect help. Right now it seems that you expect that somebody will do your whole homework.

[Ice_face]

One way to approach (any programming) task is to articulate solution (algoritm) in spoken language and then translate it into Python.

I see at least following steps:

1. Open and read csv files (how to open and read csv file?)
2. Get needed data from every row (how to split row in csv file and take only data what is needed?)
3. Write data to csv file (how to write data to new csv file?)

If you articulate steps needed you can start to solve smaller and simple tasks - (1) how to open csv file with Python; how to read data in csv file (2).... etc.

You should try to write code all by yourself. If you run into problems in some part of code then you can expect help. Right now it seems that you expect that somebody will do your whole homework.

Hi,


Your feedback is valid. I have done some work on it though.
I haven’t used forums for this purpose before, so apology for not being aware of the unwritten rules?

That said.
1) I know how to open and read CSV files.
2) I am also able to filter out the rows where the data is, so what I need help for is to resort it into the arrangement I have shown on the bottom.
My plan is to use this to run macros in Excel later…
3) I also know how to write to a new CSV file.


Cheers

[perfringo]

In this case I would articulate the task at hand as follows:

- how to read data on rows from different csv files into some data structure

On way of doing it is to use dictionary. Following is not exact solutions to your task but you can modify it to fit your needs:

1. Split row
2. Read data on row to dictionary where date is key and ratings are values
3. Print out dictionary

rows = [
        '07.10.2018 10:09 3', 
        '07.10.2018 10:31 2 2 1 1 1', 
        '07.10.2018 10:46 2 3', 
        '07.10.2018 10:48 1 2', 
        '08.10.2018 12:09 6', 
        '08.10.2018 12:31 2 2 1 1 1 2 4 6', 
        '09.10.2018 17:46 2 3 4 5', 
        '09.10.2018 17:48 2 3 4 5 2 3 4 5 2 3 4', 
        '09.10.2018 17:51 2 3', 
        '09.10.2018 17:52 3 3', 
        '09.10.2018 18:53 1 3', 
        '09.10.2018 19:46 2 1 1 1 1'
        ]

data = {}

for row in rows:
    key, _, value = row.strip().split(maxsplit=2)
    try:
        data[key].append(value)
    except KeyError:
        data[key] = [value]
        
for key, value in data.items():
    print(key, ' '.join(value))

This code will output:

Output:
07.10.2018 3 2 2 1 1 1 2 3 1 2
08.10.2018 6 2 2 1 1 1 2 4 6
09.10.2018 2 3 4 5 2 3 4 5 2 3 4 5 2 3 4 2 3 3 3 1 3 2 1 1 1 1