Q&A
前提・実現したいこと
Python itertoolsのgroupbyを使用して、時系列データの分割をしたいと考えています。
時系列データは、規則的に0と50以上の値が羅列されたデータになります。
groupbyを使用することで、数値50を域値とし50以下、50位上のグループ分けはできるのですが、
今回は数値50を域値とし、
① 50以上の値のグループ（ひとつ）と50以下の値のグループ（ふたつ）を連結したグループ
② 50以上の値のグループひとつ
を交互に作成し、出力したいと考えています。※補足情報参照
かなり分かりづらい説明となっていますが、なにかシンプルに作成できるアイディア等ありましたらご教授ください。よろしくお願いします。
該当のソースコード
Python
1import pandas as pd 2from itertools import groupby 3 4x = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 52.923, 109.48, 193.32, 306.67, 448.66, 615.93, 805.2, 1005.5, 1198.2, 1366.9, 1501.6, 1597.9, 1658.3, 1695.4, 1721.3, 1741.4, 1756.5, 1766.2, 1769.7, 1766.8, 1757.7, 1738.8, 1704.1, 1652.3, 1588.0, 1516.5, 1439.9, 1356.1, 1260.0, 1146.5, 1014.8, 869.9, 720.64, 576.81, 444.68, 327.58, 228.36, 150.71, 96.094, 61.576, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 61.815, 150.32, 284.55, 463.5, 676.6, 910.36, 1149.3, 1367.5, 1536.8, 1646.6, 1702.5, 1715.7, 1701.9, 1684.5, 1680.7, 1693.2, 1717.5, 1747.6, 1775.6, 1790.1, 1782.1, 1747.4, 1691.3, 1625.1, 1555.1, 1480.0, 1397.4, 1304.4, 1196.1, 1071.4, 935.89, 797.39, 661.44, 531.25, 409.2, 298.51, 204.19, 130.94, 80.813, 51.863, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 87.921, 160.0, 261.37, 389.34, 540.95, 716.28, 913.48, 1118.5, 1311.8, 1477.8, 1605.3, 1690.7, 1742.1, 1773.0, 1792.3, 1804.5, 1811.8, 1813.3, 1806.2, 1788.4, 1758.8, 1716.1, 1661.4, 1598.8, 1530.3, 1454.7, 1369.4, 1270.0, 1153.1, 1020.2, 877.7, 732.71, 591.88, 459.69, 339.22, 234.95, 152.39, 94.913, 59.815, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 110.49, 210.7, 351.45, 522.97, 709.08, 898.6, 1085.0, 1256.1, 1397.9, 1506.4, 1584.2, 1631.9, 1654.8, 1667.5, 1682.5, 1704.3, 1733.1, 1765.1, 1790.2, 1796.2, 1776.8, 1734.1, 1677.4, 1616.1, 1549.8, 1472.3, 1380.4, 1274.8, 1157.7, 1032.9, 904.5, 775.73, 649.46, 528.59, 415.63, 312.74, 223.34, 151.23, 97.763, 61.738, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 56.276, 105.03, 185.7, 300.25, 443.86, 610.24, 793.59, 983.21, 1163.9, 1323.8, 1455.0, 1552.0, 1616.9, 1661.1, 1696.4, 1728.0, 1756.1, 1779.2, 1795.8, 1803.9, 1800.4, 1781.2, 1743.7, 1691.0, 1630.2, 1566.8, 1501.1, 1428.8, 1341.8, 1234.1, 1106.3, 966.2, 822.81, 681.95, 545.54, 415.18, 296.77, 199.52, 129.75, 85.532, 58.342] 5 6df = pd.DataFrame(x, columns=['X']) 7X = df['X'] 8 9# 分割 10#50より大きいグループ 11def add(Var1,Var2): 12 dst = [ 13 [row[Var1] for _, row in rows] #[Var1]に選択するヘッダー名を指定 14 for k, rows in groupby(df.iterrows(), key=lambda row: row[1][Var2] > 50) #[Var2]を基準として50N以上で分割、上記のヘッダー名が対応し結果を出力 15 if k 16 ] 17 data = pd.DataFrame([dst[0],dst[1],dst[2],dst[3],dst[4] 18 ]) 19 return print(data.T) 20add('X', 'X') 21#50より小さいグループ 22def add(Var3,Var4): 23 dst = [ 24 [row[Var1] for _, row in rows] 25 for k, rows in groupby(df.iterrows(), key=lambda row: row[1][Var2] < 50) 26 if k 27 ] 28 data = pd.DataFrame([dst[0],dst[1],dst[2],dst[3],dst[4] 29 ]) 30 return print(data.T) 31add('X', 'X')
試したこと
一度50以上,50以下の値でグループ分割したのちに、下記（補足情報）のようにつなぎ合わせる
補足情報（FW/ツールのバージョンなど）
#出力結果のイメージ #GroupA, Cは、<50,>50のセットグループ #GroupB, Dは、>50の単一グループ GroupA GroupB GroupC GroupD GroupE 0 0.000 61.815 0.000 110.490 0.000 1 0.000 150.320 0.000 210.700 0.000 2 0.000 284.550 0.000 351.450 0.000 3 0.000 463.500 0.000 522.970 0.000 4 0.000 676.600 0.000 709.080 0.000 5 0.000 910.360 0.000 898.600 0.000 6 0.000 1149.300 0.000 1085.000 0.000 7 0.000 1367.500 0.000 1256.100 0.000 8 0.000 1536.800 0.000 1397.900 0.000 9 0.000 1646.600 0.000 1506.400 0.000 10 0.000 1702.500 0.000 1584.200 0.000 11 0.000 1715.700 0.000 1631.900 0.000 12 0.000 1701.900 0.000 1654.800 0.000 13 0.000 1684.500 0.000 1667.500 0.000 14 52.923 1680.700 0.000 1682.500 0.000 15 109.480 1693.200 0.000 1704.300 0.000 16 193.320 1717.500 0.000 1733.100 0.000 17 306.670 1747.600 0.000 1765.100 0.000 18 448.660 1775.600 0.000 1790.200 0.000 19 615.930 1790.100 0.000 1796.200 56.276 20 805.200 1782.100 0.000 1776.800 105.030 21 1005.500 1747.400 0.000 1734.100 185.700 22 1198.200 1691.300 0.000 1677.400 300.250 23 1366.900 1625.100 0.000 1616.100 443.860 24 1501.600 1555.100 0.000 1549.800 610.240 25 1597.900 1480.000 0.000 1472.300 793.590 26 1658.300 1397.400 87.921 1380.400 983.210 27 1695.400 1304.400 160.000 1274.800 1163.900 28 1721.300 1196.100 261.370 1157.700 1323.800 29 1741.400 1071.400 389.340 1032.900 1455.000 30 1756.500 935.890 540.950 904.500 1552.000 31 1766.200 797.390 716.280 775.730 1616.900 32 1769.700 661.440 913.480 649.460 1661.100 33 1766.800 531.250 1118.500 528.590 1696.400 34 1757.700 409.200 1311.800 415.630 1728.000 35 1738.800 298.510 1477.800 312.740 1756.100 36 1704.100 204.190 1605.300 223.340 1779.200 37 1652.300 130.940 1690.700 151.230 1795.800 38 1588.000 80.813 1742.100 97.763 1803.900 39 1516.500 51.863 1773.000 61.738 1800.400 40 1439.900 NaN 1792.300 NaN 1781.200 41 1356.100 NaN 1804.500 NaN 1743.700 42 1260.000 NaN 1811.800 NaN 1691.000 43 1146.500 NaN 1813.300 NaN 1630.200 44 1014.800 NaN 1806.200 NaN 1566.800 45 869.900 NaN 1788.400 NaN 1501.100 46 720.640 NaN 1758.800 NaN 1428.800 47 576.810 NaN 1716.100 NaN 1341.800 48 444.680 NaN 1661.400 NaN 1234.100 49 327.580 NaN 1598.800 NaN 1106.300 50 228.360 NaN 1530.300 NaN 966.200 51 150.710 NaN 1454.700 NaN 822.810 52 96.094 NaN 1369.400 NaN 681.950 53 61.576 NaN 1270.000 NaN 545.540 54 0.000 NaN 1153.100 NaN 415.180 55 0.000 NaN 1020.200 NaN 296.770 56 0.000 NaN 877.700 NaN 199.520 57 0.000 NaN 732.710 NaN 129.750 58 0.000 NaN 591.880 NaN 85.532 59 0.000 NaN 459.690 NaN 58.342 60 0.000 NaN 339.220 NaN NaN 61 0.000 NaN 234.950 NaN NaN 62 0.000 NaN 152.390 NaN NaN 63 0.000 NaN 94.913 NaN NaN 64 0.000 NaN 59.815 NaN NaN 65 0.000 NaN 0.000 NaN NaN 66 0.000 NaN 0.000 NaN NaN 67 0.000 NaN 0.000 NaN NaN 68 0.000 NaN 0.000 NaN NaN 69 0.000 NaN 0.000 NaN NaN 70 0.000 NaN 0.000 NaN NaN 71 0.000 NaN 0.000 NaN NaN 72 0.000 NaN 0.000 NaN NaN 73 0.000 NaN 0.000 NaN NaN 74 0.000 NaN 0.000 NaN NaN 75 0.000 NaN 0.000 NaN NaN 76 0.000 NaN 0.000 NaN NaN
回答1件
0
ベストアンサー
itertoolsを使わずに、pandas のgroupbyを使用して、こんな感じで書けます。
Python
1import pandas as pd 2pd.set_option('display.max_rows', 100) 3 4x = [0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 52.923, 109.48, 193.32, 306.67, 448.66, 615.93, 805.2, 1005.5, 1198.2, 1366.9, 1501.6, 1597.9, 1658.3, 1695.4, 1721.3, 1741.4, 1756.5, 1766.2, 1769.7, 1766.8, 1757.7, 1738.8, 1704.1, 1652.3, 1588.0, 1516.5, 1439.9, 1356.1, 1260.0, 1146.5, 1014.8, 869.9, 720.64, 576.81, 444.68, 327.58, 228.36, 150.71, 96.094, 61.576, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 61.815, 150.32, 284.55, 463.5, 676.6, 910.36, 1149.3, 1367.5, 1536.8, 1646.6, 1702.5, 1715.7, 1701.9, 1684.5, 1680.7, 1693.2, 1717.5, 1747.6, 1775.6, 1790.1, 1782.1, 1747.4, 1691.3, 1625.1, 1555.1, 1480.0, 1397.4, 1304.4, 1196.1, 1071.4, 935.89, 797.39, 661.44, 531.25, 409.2, 298.51, 204.19, 130.94, 80.813, 51.863, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 87.921, 160.0, 261.37, 389.34, 540.95, 716.28, 913.48, 1118.5, 1311.8, 1477.8, 1605.3, 1690.7, 1742.1, 1773.0, 1792.3, 1804.5, 1811.8, 1813.3, 1806.2, 1788.4, 1758.8, 1716.1, 1661.4, 1598.8, 1530.3, 1454.7, 1369.4, 1270.0, 1153.1, 1020.2, 877.7, 732.71, 591.88, 459.69, 339.22, 234.95, 152.39, 94.913, 59.815, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 110.49, 210.7, 351.45, 522.97, 709.08, 898.6, 1085.0, 1256.1, 1397.9, 1506.4, 1584.2, 1631.9, 1654.8, 1667.5, 1682.5, 1704.3, 1733.1, 1765.1, 1790.2, 1796.2, 1776.8, 1734.1, 1677.4, 1616.1, 1549.8, 1472.3, 1380.4, 1274.8, 1157.7, 1032.9, 904.5, 775.73, 649.46, 528.59, 415.63, 312.74, 223.34, 151.23, 97.763, 61.738, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 0.0, 56.276, 105.03, 185.7, 300.25, 443.86, 610.24, 793.59, 983.21, 1163.9, 1323.8, 1455.0, 1552.0, 1616.9, 1661.1, 1696.4, 1728.0, 1756.1, 1779.2, 1795.8, 1803.9, 1800.4, 1781.2, 1743.7, 1691.0, 1630.2, 1566.8, 1501.1, 1428.8, 1341.8, 1234.1, 1106.3, 966.2, 822.81, 681.95, 545.54, 415.18, 296.77, 199.52, 129.75, 85.532, 58.342] 5 6df = pd.DataFrame(x, columns=['X']) 7# Xが50以上の箇所にフラグを立てる 8df['flag'] = (df['X'] > 50) 9# 上記のフラグの纏まり毎にグループIDを振る 10df['grp'] = (df.shift()['flag'] != df['flag']).cumsum() 11# GroupID 1と2, 6と５、9と10をまとめる（GroupID 3と7は使わないので GroupID 0 に変換しておく） 12df['grp'] = df['grp'].replace({2:1, 3:0, 6:5, 7:0, 10:9}) 13# GroupID毎に列にまとめる 14ret = df.groupby('grp')['X'].apply(lambda d: d.reset_index(drop=True)).unstack().T 15# GroupID 0は削除、他の列はカラム名を変換 16ret = ret.drop(0, axis=1).rename({1:'GroupA', 4:'GroupB', 5:'GroupC', 8:'GroupD', 9:'GroupE'}, axis=1) 17 18print(ret) 19#grp GroupA GroupB GroupC GroupD GroupE 20#0 0.000 61.815 0.000 110.490 0.000 21#1 0.000 150.320 0.000 210.700 0.000 22#2 0.000 284.550 0.000 351.450 0.000 23#3 0.000 463.500 0.000 522.970 0.000 24#4 0.000 676.600 0.000 709.080 0.000 25#5 0.000 910.360 0.000 898.600 0.000 26#6 0.000 1149.300 0.000 1085.000 0.000 27#7 0.000 1367.500 0.000 1256.100 0.000 28#8 0.000 1536.800 0.000 1397.900 0.000 29#9 0.000 1646.600 0.000 1506.400 0.000 30#10 0.000 1702.500 0.000 1584.200 0.000 31#11 0.000 1715.700 0.000 1631.900 0.000 32#12 0.000 1701.900 0.000 1654.800 0.000 33#13 52.923 1684.500 0.000 1667.500 0.000 34#14 109.480 1680.700 0.000 1682.500 0.000 35#15 193.320 1693.200 0.000 1704.300 0.000 36#16 306.670 1717.500 0.000 1733.100 0.000 37#17 448.660 1747.600 0.000 1765.100 0.000 38#18 615.930 1775.600 0.000 1790.200 0.000 39#19 805.200 1790.100 0.000 1796.200 56.276 40#20 1005.500 1782.100 0.000 1776.800 105.030 41#21 1198.200 1747.400 0.000 1734.100 185.700 42#22 1366.900 1691.300 0.000 1677.400 300.250 43#23 1501.600 1625.100 0.000 1616.100 443.860 44#24 1597.900 1555.100 0.000 1549.800 610.240 45#25 1658.300 1480.000 0.000 1472.300 793.590 46#26 1695.400 1397.400 87.921 1380.400 983.210 47#27 1721.300 1304.400 160.000 1274.800 1163.900 48#28 1741.400 1196.100 261.370 1157.700 1323.800 49#29 1756.500 1071.400 389.340 1032.900 1455.000 50#30 1766.200 935.890 540.950 904.500 1552.000 51#31 1769.700 797.390 716.280 775.730 1616.900 52#32 1766.800 661.440 913.480 649.460 1661.100 53#33 1757.700 531.250 1118.500 528.590 1696.400 54#34 1738.800 409.200 1311.800 415.630 1728.000 55#35 1704.100 298.510 1477.800 312.740 1756.100 56#36 1652.300 204.190 1605.300 223.340 1779.200 57#37 1588.000 130.940 1690.700 151.230 1795.800 58#38 1516.500 80.813 1742.100 97.763 1803.900 59#39 1439.900 51.863 1773.000 61.738 1800.400 60#40 1356.100 NaN 1792.300 NaN 1781.200 61#41 1260.000 NaN 1804.500 NaN 1743.700 62#42 1146.500 NaN 1811.800 NaN 1691.000 63#43 1014.800 NaN 1813.300 NaN 1630.200 64#44 869.900 NaN 1806.200 NaN 1566.800 65#45 720.640 NaN 1788.400 NaN 1501.100 66#46 576.810 NaN 1758.800 NaN 1428.800 67#47 444.680 NaN 1716.100 NaN 1341.800 68#48 327.580 NaN 1661.400 NaN 1234.100 69#49 228.360 NaN 1598.800 NaN 1106.300 70#50 150.710 NaN 1530.300 NaN 966.200 71#51 96.094 NaN 1454.700 NaN 822.810 72#52 61.576 NaN 1369.400 NaN 681.950 73#53 NaN NaN 1270.000 NaN 545.540 74#54 NaN NaN 1153.100 NaN 415.180 75#55 NaN NaN 1020.200 NaN 296.770 76#56 NaN NaN 877.700 NaN 199.520 77#57 NaN NaN 732.710 NaN 129.750 78#58 NaN NaN 591.880 NaN 85.532 79#59 NaN NaN 459.690 NaN 58.342 80#60 NaN NaN 339.220 NaN NaN 81#61 NaN NaN 234.950 NaN NaN 82#62 NaN NaN 152.390 NaN NaN 83#63 NaN NaN 94.913 NaN NaN 84#64 NaN NaN 59.815 NaN NaN
一応わかりやすいようにコメントをふっておきましたが、よく解らない場合はコメント欄で質問してください。
** 【追記】**
作成されるDFの列数が不定の場合の処理
Python
1# Xが50以上の箇所にフラグを立てる 2df['flag'] = (df['X'] > 50) 3# 上記のフラグの纏まり毎にグループIDを振る 4df['grp'] = (df.shift()['flag'] != df['flag']).cumsum() 5 6# GroupID を変換 7def conv(d): 8 if d % 4 == 1: 9 ret = d 10 elif d % 4 == 2: 11 ret = d-1 12 elif d % 4 == 3: 13 ret = 0 14 else: 15 ret = d 16 return ret 17 18df['grp'] = df['grp'].map(conv) 19# GroupID 0 を削除 20df = df.loc[df['grp']!=0] 21# GroupID毎に列にまとめる 22ret = df.groupby('grp')['X'].apply(lambda d: d.reset_index(drop=True)).unstack().T 23# カラム名を0から振り直す 24ret.column = range(ret.shape[1])
投稿2020/03/23 04:25
編集2020/03/23 10:23
総合スコア15898
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2020/03/23 06:57
2020/03/23 09:08
2020/03/23 09:41
2020/03/23 10:26
2020/03/24 00:08