Pandas dataframe datetime conversion and min/max calculation

user_14624652 0
Viewed 94

I have the following pandas dataframe:

NAME  | 2020-12-03 12:00| 2020-12-03 15:00| 2020-12-03 18:00| 2020-12-03 21:00| etc.
London|        5        |         4       |        3.6      |      1.7        | ...
Berlin|        4        |         4.5     |        2.8      |      0.1        | ...
etc.

It is basically a long table with serveral cities and columns with the °C and the header is the corresponding timestamp. I now want to calculate the min and max temperature aggregated per day per city. The final table is probably going to look as following:

NAME   |Minimum | Maximum |timestamp |
London | 1.7    |    5    |2020-12-03|
Berlin | 4.5    |   0.1   |2020-12-03|

To make things even more complex I want to draw the graphs with matplotlib for each city with the min and max values as bar charts per timestamp. So I am not sure whether the final table should look like the above.

I have already tried transposing the table and grouping by the timestamps (did not work as the column headers couldnt be set to a datetime value). I can print out the values of the first table just fine with the following script, but as mentioned before, I want to get the min and max values.

for i in range(0, fcpanda3.shape[0]):
   plt.rcParams["figure.figsize"] = (15,15)
   ax = fcpanda3.iloc[i].T.plot(kind="bar",  color=(fcpanda3.iloc[i].T > 0).map({True: 'r',False: 'b'}))
   ax.set_xticklabels([t if not i%5 else "" for i,t in enumerate(ax.get_xticklabels())])
   ax.yaxis.set_major_formatter(FormatStrFormatter('%d °C'))
   plt.tight_layout()
   plt.savefig("D:/graph/"+str(i+1)+".png")
   ax = plt.close()
pandas matplotlib python-3.x python
1 Answers

df.melt() serves your purpose by unpivoting the table first. Regular .groupby() aggregation applies subsequently.

Code

df2 = df.melt(id_vars="NAME", var_name="timestamp", value_name="degree")
df2["timestamp"] = pd.to_datetime(df2["timestamp"]).dt.date
df2 = df2.groupby(["NAME", "timestamp"])["degree"].agg(Min=min, Max=max).sort_index().reset_index()

Output

print(df2)
     NAME   timestamp  Min  Max
0  Berlin  2020-12-03  0.1  4.5
1  London  2020-12-03  1.7  5.0
user_32186930
Related