I have a data set in this format:
data = { 'sensor1': {'units': 'x', 'values': [{'time': 17:00, 'value': 10},
{'time': 17:10, 'value': 12},
{'time': 17:20, 'value' :7}, ...]}
'sensor2': {'units': 'x', 'values': [{'time': 17:00, 'value': 9},
{'time': 17:20, 'value': 11}, ...]}
}
And I want to collect the data to put into a csv like:
time, sensor1, sensor2
17:00, 10, 9,
17:10, 12, ,
17:20, 7, 11,
...
I need to use the csv module so I require a list of dictionaries like so:
[{'time': 17:00, 'sensor1': 10, 'sensor2': 9}, ... ]
I know that
fields = list(data.keys())
Will go into csv write as the header. It's just the rows I can't format properly. Especially since the times don't always exist in both sensors. e.g. 17:10 has a value in sensor 1 but does not exist in sensor 2.
CodePudding user response:
You can use pandas
to create a dataframe from your data and save it as CSV:
import pandas as pd
data = {
"sensor1": {
"units": "x",
"values": [
{"time": "17:00", "value": "10"},
{"time": "17:10", "value": "12"},
{"time": "17:20", "value": "7"},
],
},
"sensor2": {
"units": "x",
"values": [
{"time": "17:00", "value": "9"},
{"time": "17:20", "value": "11"},
],
},
}
df = pd.DataFrame(
[
{"time": vv["time"], "column": k, "value": vv["value"]}
for k, v in data.items()
for vv in v["values"]
],
)
df = df.pivot(index="time", columns="column", values="value").reset_index()
df.to_csv("data.csv", index=False)
saves data.csv
:
time,sensor1,sensor2
17:00,10,9
17:10,12,
17:20,7,11
CodePudding user response:
You can do it in 2 steps.
data = { 'sensor1': {'units': 'x', 'values': [{'time': '17:00', 'value': 10},
{'time': '17:10', 'value': 12},
{'time': '17:20', 'value' :7},
]},
'sensor2': {'units': 'x', 'values': [{'time': '17:00', 'value': 9},
{'time': '17:20', 'value': 11},
]},
}
# first step: dictionary keyed by time {time:t,{'sensorx':v,}}
dic = {}
for sensor in data:
for sample in data[sensor]['values']:
if not dic.get( sample['time']):
dic[ sample['time']] = {} # create the record
dic[ sample[ 'time']][sensor] = sample['value']
# second step: generate CSV
columns=['time', 'sensor1', 'sensor2']
import csv
f = open( 'zz.csv', 'w', newline='')
writer = csv.DictWriter( f, columns)
writer.writeheader()
for key in dic:
writer.writerow( dict( [('time',key)] list( dic[key].items())))
The result will be:
time,sensor1,sensor2
17:00,10,9
17:10,12,
17:20,7,11