从行中提取每日值并使用日期创建新行

我有一个从 csv 文件字节字符串编译而来的天气数据 DataFrame。每行包含一个元素一个月的数据，即 1949 年 6 月的最高温度位于年份 = 1949、月份 = 06、元素 = TMAX 的行中。 6 月 1 日的临时值为 value1，6 月 2 日的临时值为 value2，依此类推。

我已将 df 的片段复制到剪贴板并粘贴在下面，您应该能够复制和使用pd.read_clipboard(sep=',')重新创建这个 df.

我需要做的是提取该月每一天的值（现阶段我不太担心 mflag、sflag、qflag），附加值引用的元素并创建一个如下所示的新数据框:

,ID,year,month,element,value1,mflag1,qflag1,sflag1,value2,mflag2,qflag2,sflag2,value3,mflag3,qflag3,sflag3,value4,mflag4,qflag4,sflag4,value5,mflag5,qflag5,sflag5,value6,mflag6,qflag6,sflag6,value7,mflag7,qflag7,sflag7,value8,mflag8,qflag8,sflag8,value9,mflag9,qflag9,sflag9,value10,mflag10,qflag10,sflag10,value11,mflag11,qflag11,sflag11,value12,mflag12,qflag12,sflag12,value13,mflag13,qflag13,sflag13,value14,mflag14,qflag14,sflag14,value15,mflag15,qflag15,sflag15,value16,mflag16,qflag16,sflag16,value17,mflag17,qflag17,sflag17,value18,mflag18,qflag18,sflag18,value19,mflag19,qflag19,sflag19,value20,mflag20,qflag20,sflag20,value21,mflag21,qflag21,sflag21,value22,mflag22,qflag22,sflag22,value23,mflag23,qflag23,sflag23,value24,mflag24,qflag24,sflag24,value25,mflag25,qflag25,sflag25,value26,mflag26,qflag26,sflag26,value27,mflag27,qflag27,sflag27,value28,mflag28,qflag28,sflag28,value29,mflag29,qflag29,sflag29,value30,mflag30,qflag30,sflag30,value31,mflag31,qflag31,sflag31
14,40223,1945,12,TAVG,  244,H, ,S,  228,H, ,S,  239,H, ,S,  231,H, ,S,  195,H, ,S,  216,H, ,S,  232,H, ,S,  230,H, ,S,  253,H, ,S,  260,H, ,S,  251,H, ,S,  260,H, ,S,  256,H, ,S,  257,H, ,S,  252,H, ,S,  256,H, ,S,  253,H, ,S,  241,H, ,S,  209,H, ,S,  226,H, ,S,  229,H, ,S,  239,H, ,S,  239,H, ,S,  234,H, ,S,  233,H, ,S,  232,H, ,S,  234,H, ,S,  231,H, ,S,  232,H, ,S,  232,H, ,S,  241,H, ,S
15,40223,1946,01,TAVG,  249,H, ,S,  259,H, ,S,  256,H, ,S,  271,H, ,S,  277,H, ,S,  264,H, ,S,  231,H, ,S,  236,H, ,S,  235,H, ,S,  249,H, ,S,  257,H, ,S,  256,H, ,S,  250,H, ,S,  253,H, ,S,  261,H, ,S,  262,H, ,S,  257,H, ,S,  264,H, ,S,  264,H, ,S,  264,H, ,S,  272,H, ,S,  274,H, ,S,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , ,-9999, , , 
16,40223,1949,06,TMAX,-9999, , , ,-9999, , , ,  210, , ,a,  146, , ,a,  189, , ,a,  216, , ,a,  211, , ,a,  210, , ,a,  214, , ,a,  174, , ,a,  225, , ,a,  187, , ,a,  168, , ,a,  207, , ,a,  218, , ,a,  205, , ,a,  174, , ,a,  192, , ,a,  186, , ,a,  199, , ,a,  176, , ,a,  141, , ,a,  173, , ,a,  190, , ,a,  189, , ,a,  197, , ,a,  207, , ,a,  216, , ,a,  202, , ,a,  204, , ,a,-9999, , , 
17,40223,1949,06,TMIN,-9999, , , ,-9999, , , ,  136, , ,a,   60, , ,a,  113, , ,a,   84, , ,a,   91, , ,a,   88, , ,a,  126, , ,a,  131, , ,a,  129, , ,a,   84, , ,a,   81, , ,a,  113, , ,a,  110, , ,a,  133, , ,a,  136, , ,a,  108, , ,a,  111, , ,a,  122, , ,a,  107, , ,a,   81, , ,a,   59, , ,a,   57, , ,a,   44, , ,a,   42, , ,a,   66, , ,a,   77, , ,a,   60, , ,a,   68, , ,a,-9999, , , 
18,40223,1949,06,PRCP,  277, , ,a,  239, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    3, , ,a,   53, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    8, , ,a,    3, , ,a,    3, , ,a,   71, , ,a,  107, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,    0, , ,a,-9999, , , 
19,40223,1949,06,TAVG,  156,H, ,S,  174,H, ,S,  136,H, ,S,  129,H, ,S,  146,H, ,S,  153,H, ,S,  146,H, ,S,  165,H, ,S,  164,H, ,S,  154,H, ,S,  156,H, ,S,  126,H, ,S,  144,H, ,S,  153,H, ,S,  169,H, ,S,  167,H, ,S,  151,H, ,S,  154,H, ,S,  153,H, ,S,  150,H, ,S,  132,H, ,S,  102,H, ,S,  106,H, ,S,  109,H, ,S,  113,H, ,S,  121,H, ,S,  133,H, ,S,  132,H, ,S,  132,H, ,S,  129,H, ,S,-9999, , , 

提前致谢

可能有一种更简单的方法可以完成您想要的操作，但是此代码可以工作，并且可以逐步执行。

注意，我将数据保存在名为weather_data.csv- 使用剪贴板一段时间后就不太起作用了。:)

import pandas as pd

# df = pd.read_clipboard(sep=',')
# df.to_csv('weather_data.csv')

df = pd.read_csv('weather_data.csv')

keep_columns = [col for col in df.columns[2:] if not 'flag' in col]
df = df[keep_columns]

df_melt = pd.melt(df, id_vars=['year', 'month', 'element'], value_vars=keep_columns[3:])

df_melt['day'] = df_melt['variable'].str[5:].astype(int)

df_melt = df_melt.query('value!=-9999')

df_melt['date'] = pd.to_datetime(df_melt[['year', 'month', 'day']])
                              
df_final = df_melt[['date', 'element', 'value']].pivot(index='date', columns='element')

print(df_final)

示例输出：

          value
element     PRCP   TAVG   TMAX  TMIN
date
1945-12-01   NaN  244.0    NaN   NaN
1945-12-02   NaN  228.0    NaN   NaN
1945-12-03   NaN  239.0    NaN   NaN
1945-12-04   NaN  231.0    NaN   NaN
1945-12-05   NaN  195.0    NaN   NaN
...          ...    ...    ...   ...
1949-06-26   0.0  121.0  197.0  42.0
1949-06-27   0.0  133.0  207.0  66.0
1949-06-28   0.0  132.0  216.0  77.0
1949-06-29   0.0  132.0  202.0  60.0
1949-06-30   0.0  129.0  204.0  68.0