Python Forum
Reading an Unconventional CSV file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading an Unconventional CSV file
#1
Hi Everyone,

I'm killing myself to find a way to read a CSV file and convert to a format I can import to InfluxDB.

The CSV has a very unconventional format and it grows horizontally. I tried to make sense of reading it with the CSV module and PANDAS but no luck in making sense of it. The file is an export from the Xiaomi Mi Flower Care sensor (example below). And I want to load the date into a InfluxDB while I learn how to do that using Phyton.

An example of he file is:
And the format I need the output is:
Output:
Device Column 1 (L) Column 2(S) Column 3 (T) Column 4 (E) Time finger light=74,moisture=61,temperature=11.3,fertility=812 1557784799999750000 finger light=244,moisture=61,temperature=12.3,fertility=798 1557788399999750000 finger light=325,moisture=62,temperature=14.2,fertility=773 1557791999999750000 finger light=379,moisture=62,temperature=16.2,fertility=753 1557795599999740000 finger light=495,moisture=63,temperature=18.2,fertility=739 1557799199999740000 finger light=436,moisture=63,temperature=18.3,fertility=750 1557802799999740000 finger light=264,moisture=63,temperature=17.8,fertility=772 1557806399999740000 finger light=323,moisture=63,temperature=17.7,fertility=785 1557809999999740000 finger light=217,moisture=63,temperature=17.5,fertility=796 1557813599999740000 finger light=37,moisture=63,temperature=16.6,fertility=815 1557817199999740000 finger light=4,moisture=63,temperature=15.6,fertility=829 1557820799999740000 finger light=5,moisture=62,temperature=14.9,fertility=839 1557824399999740000 finger light=5,moisture=62,temperature=13.6,fertility=854 1557827999999740000 finger light=5,moisture=61,temperature=11.7,fertility=872 1557831599999740000 finger light=5,moisture=61,temperature=10.9,fertility=873 1557835199999740000 finger light=5,moisture=61,temperature=10.7,fertility=861 1557838799999740000 finger light=5,moisture=61,temperature=11.1,fertility=839 1557842399999740000 finger light=5,moisture=60,temperature=11.5,fertility=825 1557845999999740000 finger light=6,moisture=60,temperature=11.9,fertility=810 1557849599999740000 finger light=5,moisture=60,temperature=11.9,fertility=805 1557853199999740000 finger light=5,moisture=60,temperature=11.5,fertility=805 1557856799999740000 finger light=6,moisture=60,temperature=12.2,fertility=789 1557860399999740000 finger light=5,moisture=60,temperature=12.3,fertility=783 1557863999999740000 finger light=7,moisture=60,temperature=12.2,fertility=782 1557867599999740000
O tried the CSV module but couldn't find a way to read the file that would make sense.

Any help?

Thanks
Reply
#2
This is not csv file. Luckily for you, you need to write parser yourself. I say luckily because IMHO the best way to learn (and you say you want to learn) is working on real project.

Can you provide actual file you work with? I have added output tags and it looks a bit differently now. So having the actual file will help us to help you.

For the parser - each plant has 27 lines. So you will need to read the file in chunks of 27 lines (probably after first skipping few "header" lines) to get info for each plant and then parse these lines accordingly and combine the information. As I said it would be useful to have the actual file in order to help you better, but in any case it would not be difficult to parse the lines/chunks

For the output - you need to explain how you derive the information (e.g. what is finger? how do you combine all info from input into that output, etc.)
If you can't explain it to a six year old, you don't understand it yourself, Albert Einstein
How to Ask Questions The Smart Way: link and another link
Create MCV example
Debug small programs

Reply
#3
Note: This is just one general processing way to get to the data. Its not dealing with storing this data in a easy reusable data-container (dictionary, class or otherwise). Which depends a bit on further processing needs (ie: the database exporting part (InfluxDB unknown to me)).

Looking at the example source file. I would:
1) Read/process the file per line. (text mode)
1.1) Using/setting TAB as field / line-data delimiter.
2) Skip empty lines. (<string>.strip())
3) Skip line that starts with "Name" (alternatively it could be used to identify this file as a correct data file. If its encountered as fist data entry (none empty line))
4) On "Flower Care" encounter switch mode / start a new plant. (ignoring MAC-type data here)
4.1) Next/First "Flower Care" data line: Split line into list(array) on TAB character, and separate out the name-part from the date-list. (date-list size could be used to preset and/or as check-up for reading next data-part, although, as it size if kinda fixed (ie: (multiple of 4) + 1)), that part can be skipped)
4.2) Second data-line can be skipped, as it contains no real variable data. (could be verified to be correct line to skip by checking the leading N characters on the line-string)
4.3) Now we switch into time-records Mode. Ie: next 24 lines which can be directly processes in sequence.
4.3.1) Split line into list on TAB character.
4.3.2) Separate time-part. Potentially check if its as expected.
4.3.3) Split rest of record/line-data into sets of 4, while keeping them ordered as they are (ie: lists in list) so its in the same order as the date-data from 4.1.


Not related to task at hand, but potentially useful for alternative solutions for dealing with the Xiaomi Mi Flower Care sensor.
Xiaomi Mi Flora [Temp/Light/Moisture] BLE Sensor" topic at domoticz fora (domoticz: Open source home automation)
Going beyond the idiomatic Python (Sensible Idiomatic usage - Blog post - Sep 11th 2017)
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  Excel File reading vanjoe198 1 2,005 Mar-31-2021, 11:53 AM
Last Post: snippsat
  reading from a file looseCannon101 14 4,752 Jul-18-2020, 11:29 AM
Last Post: GOTO10
  Weird problem with reading from file and performing calculations pineapple999 1 2,956 Jul-25-2019, 01:30 AM
Last Post: ichabod801
  Handling IO Error / Reading from file Expel 10 4,715 Jul-18-2019, 01:21 PM
Last Post: snippsat
  reading text file and writing to an output file precedded by line numbers kannan 7 10,245 Dec-11-2018, 02:19 PM
Last Post: ichabod801
  Reading of structured .mat (matlab) file sumit 2 3,369 May-24-2018, 12:12 PM
Last Post: sumit
  File Reading toxicxarrow 9 5,102 May-07-2018, 04:12 PM
Last Post: toxicxarrow
  reading all lines from a text file seadoofanatic 2 2,886 Mar-13-2018, 06:05 PM
Last Post: Narsimhachary
  Reading a text file fivestar 7 5,504 Oct-13-2017, 07:25 AM
Last Post: gruntfutuk
  Dictionary + File Reading palmtrees 2 4,816 Nov-15-2016, 05:16 PM
Last Post: Ofnuts

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020