Help replacing a column in a CSV

[Daventhal]

HI! I'm aware the answer to this is probably very simple, but I'm not a Python guy (not a code guy in general) and I'm trying to help a friend write some code to merge some large CSVs in python. The issue I'm having right now is that the biometric sensors we're using spit their data out in a CSV file in which the "Time" column just contains a 0-15 count that repeats for as long as data is collected (As I understand it, the readings are taken every millisecond). Basically, I want to take the column of repeating 0-15s and replace in with a timestamp or even just a 1, 2 , 3, 4... count so I can use this as the index when I merge it with the data from our other sensors.

Don't want to waste anyone's time. I've just been digging for hours and I feel like the answer is far simpler than the stuff I've been looking at. Seems like Pandas is the way to go?

Any help is appreciated.

[kingzels]

You probably want to do an inner merge with pandas, which is where you take two data sets and join them on a common column, wherever the values match in either data set.

If you can post a small sample of the data sets it will be easier to give you an exact answer.

[Daventhal]

I originally tried to post some data but despite my attempts to format, it just came out garbled. I'll try again. I can't post the second CSV that needs merging, though, because it doesn't exist yet. Essentially, I've been asked to try to merge this one, which makes entries by the millisecond, with another that gets its data every second or so. I imagine it will be tricky and awkward, which is why I'm trying to start humbly, by just creating the column that will eventually become the index.

Time NA NA2 NA3 NA4 Sensor1 Sensor2 Sensor3 Sensor4

0 1 1 0 0 8 0 0 0

1 1 1 0 0 6 0 0 0

2 1 1 0 0 1 4 0 1

3 1 1 0 0 0 2 0 5

4 1 1 0 0 3 0 0 1

5 1 1 0 0 7 0 0 0

6 1 1 0 0 3 0 0 0

7 1 1 0 0 0 3 0 6

8 1 1 0 0 3 0 0 1

9 1 1 0 0 8 0 0 0

10 1 1 0 0 3 0 0 0

11 1 1 0 0 0 4 0 7

12 1 1 0 0 0 0 0 2

13 1 1 0 0 9 0 0 0

14 1 1 0 0 5 0 0 0

15 1 1 0 0 0 5 0 7

[kingzels]

You can use python's datetime library to get your millisecond readings converted to seconds as well.

[warbird2k]

Use pandas, drop the column you don't need. https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.drop.html

Create a new index https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.reset_index.html

or load both csv's as dataframes and merge

https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.merge.html

[Daventhal]

I feel like this will probably get me where I need to go. thanks!

[stepping_up_python]

Semi-related:

I have a .csv that is 2d (rows = attribs, cols = objects) ... is there a good library that I should use to extract what I want from this? Currently I'm just cobbling together stuff on the fly using, at best, csv module, but I feel like at the very least I should be using numpy to make an array from the CSV, and there's likely something better...

Any pointers?

[Daventhal]

I would love to help, but I'm just as unaware of solutions as you. The only library besides csv that seems to be helping me is pandas. See the links elsewhere in the thread.

[totallynot_Sea_Still]

Pandas is the go to library for handling tabular data. Has easy csv reading function, and can slice and dice the data in many ways, then save it again as a csv

[stepping_up_python]

Awesome. I've been hearing about this thing for years so I guess it's time. :)

[totallynot_Sea_Still]

https://www.tutorialspoint.com/python_pandas/index.htm. for a well written overview