-3

I have a csv file containing the pointing personal. of this form:

3,23/02/2015,08:27,08:27,12:29,13:52,19:48
3,24/02/2015,08:17,12:36,13:59,19:28
5,23/02/2015,10:53,13:44
5,25/02/2015,09:05,12:34,12:35,13:30,19:08
5,26/02/2015,08:51,12:20,13:46,18:47,18:58

and I want it cleaning. in this way:

ID, DATE, IN,BREAK_OUT, BREAK_IN, OUT, WORK_TIME
3,Monday 23/02/2015,08:27,12:29,13:52,19:48,08:00hours
3,Tuesday 24/02/2015,08:17,12:36,13:59,19:28,08:00hours
5,Monday 23/02/2015,10:53,NAN,13:44,NAN,2houres
5,Wednesday 25/02/2015,09:05,12:34,13:30,19:08,08hours

can you help me please think you

2 Answers2

0

This question might help you out: How to split string into column

First, read the whole file and split the columns. Check if there's data or not and write it back into a new file.

If you need additional help, tell us what you tried, what worked for you and what didn't and so on. We won't write a complete program/script for you.

Community
  • 1
  • 1
Exceen
  • 765
  • 1
  • 4
  • 20
0

I'd suggest you use pandas to import the data from the file

import pandas as pd
pd.read_csv(filepath, sep = ',')

should do the trick, assuming filepath leads to your csv. I'd then suggest that you use the datetime functions to convert your strings to dates you can calculate with (I think you could also use numpys datetime64 types, I'm just not used to them).

import datetime as dt
day = dt.datetime.strptime('23/02/2015', '%d/%m/%Y')
in = dt.datetime.combine(day, dt.datetime.strptime('08:27', '%H:%M').time())

should do the trick. It is necessary, that your in is also a datetime object, not only a time object, otherwise you cannot substract them (which would be the necessary next step to calculate the Worktime.

Is think this should be a bit to get you started, You'll find the pandas documentation here and the datetime documentation here.

If you have further questions, try to ask your question more specific.

MichaelA
  • 1,866
  • 2
  • 23
  • 38
  • can you help me how can i replace empty valy in csv file. think you – Abdellah May 10 '15 at 23:00
  • That is what I meant with "if you have further questions, try to ask your question more specific": You want to replace empty values in the csv-file. Okay, but how are empty values marked in the csv-File, what do you mean by "empty". Solutions can only be as good as the questions that are asked. If you do not put in any effort to ask the question, why should we put any effort in the answer? Please have a look at [How to ask good questions](http://stackoverflow.com/help/how-to-ask) – MichaelA May 11 '15 at 09:37
  • yes I'm really sorry, I have to be more precise in my question. I have a CSV file of the form that I showed you at the beginning. the idea is to create 4 array list (IN, OUT, BREAK_IN, BREAK_OUT) for each ligne or day and if the value is between 7:00 ET 10:00 Morning will be in IN list, if it is from 12:00 to 13:00 is BREAK_OUT and if it is from 14:00 to 15:00 is BREAK_IN and if from 17:00 to 20:00 it's OUT array, if value is empty the the program puts the value NAN (example : 5,23/02/2015, 8:27, NAN, NAN, 18:20). it's all, and thank you for your help . – Abdellah May 12 '15 at 22:39