I'm trying to create an aggregated dataframe of many sensor readings over time to just the sums for each sensor. I have many dataframes but they all have the same schema, with 10 columns, one for each sensor:
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
|sensor_1|sensor_2|sensor_3|sensor_4|sensor_5|sensor_6|sensor_7|sensor_8|sensor_9|sensor_10|
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
| 220.0| 339.0| -336.0| 364.0| null| 492.0| -796.0| -423.0| -582.0| -40.0|
| 178.0| 221.0| -317.0| 366.0| null| 525.0| -754.0| -415.0| -932.0| -305.0|
| 151.0| 42.0| -280.0| 250.0| null| 463.0| -772.0| -229.0| -257.0| -59.0|
| 162.0| -123.0| -243.0| 288.0| null| 303.0| -899.0| 212.0| -295.0| 38.0|
| 158.0| -287.0| -300.0| 372.0| null| 169.0| -769.0| 755.0| 169.0| -239.0|
| 136.0| -302.0| -308.0| 242.0| null| 241.0| -510.0| 888.0| 282.0| -293.0|
| 124.0| -131.0| -292.0| 132.0| null| 234.0| -494.0| 970.0| -326.0| -203.0|
| 127.0| 133.0| -208.0| 14.0| null| 134.0| -748.0| 700.0| 237.0| -278.0|
| 142.0| 374.0| -81.0| -177.0| null| -200.0| -678.0| 402.0| 664.0| -460.0|
| 135.0| 538.0| 52.0| -113.0| null| -440.0| -711.0| 35.0| 877.0| -452.0|
+--------+--------+--------+--------+--------+--------+--------+--------+--------+---------+
Can try recreating a small version of the dataframe with
columns = ['sensor_1','sensor_2','sensor_3','sensor_4','sensor_5','sensor_6','sensor_7','sensor_8','sensor_9','sensor_10']
data = [(220.0, 339.0, -336.0, 364.0, null, 492.0, -796.0, -423.0, -582.0, -40.0),
(178.0, 221.0, -317.0, 366.0, null, 525.0, -754.0, -415.0, -932.0, -305.0),
(151.0, 42.0, -280.0, 250.0, null, 463.0, -772.0, -229.0, -257.0, -59.0)]
spark = SparkSession.builder.appName('Sensors').getOrCreate()
rdd = spark.sparkContext.parallelize(data)
df = rdd.toDF()
AFter I try to create the sums DataFrame with:
exprs = {x: "sum" for x in df.columns}
sum_df = df.agg(exprs)
This gives the following output.
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
|sum(sensor_2)|sum(sensor_9)|sum(sensor_3)|sum(sensor_8)|sum(sensor_4)|sum(sensor_7)|sum(sensor_1)|
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
| 276834.0| 87904.0| 213587.0| 76103.0| 121201.0| 423609.0| -96621.0|
+-------------+-------------+-------------+-------------+-------------+-------------+-------------+
As you can see the columns are out of order to the original. I had to trim the display of the whole df to fit neatly in this post but you get the idea. I'm not sure what logical ordering the spark engine decided to use but it doesn't suit me because I need them to have consistent order. Why does it do this? how can I keep the order consistent?