2023年7月5日 00:30:34go评论66阅读模式

英文:

How to use pd.json_normalize to retrieve the data I need from 2 parts (I'm almost there already, only need last piece of data)

问题

I've extracted the relevant part of your JSON and DataFrame. Here's the DataFrame you provided:

        identifier  period   value  ... archiveDate  shortCode datumUnit
0     TD3$BALMO  Jul 23  14.400  ...  2023-06-30   TD3$-FFA      $/mt
1    TD3$CURMON  Jul 23  14.400  ...  2023-06-30   TD3$-FFA      $/mt
2      TD3$+1_M  Aug 23  13.662  ...  2023-06-30   TD3$-FFA      $/mt
3      TD3$+2_M  Sep 23  13.716  ...  2023-06-30   TD3$-FFA      $/mt
4      TD3$+3_M  Oct 23  13.830  ...  2023-06-30   TD3$-FFA      $/mt
5      TD3$+4_M  Nov 23  14.619  ...  2023-06-30   TD3$-FFA      $/mt
6      TD3$+5_M  Dec 23  16.389  ...  2023-06-30   TD3$-FFA      $/mt
7      TD3$CURQ   Q3 23  13.926  ...  2023-06-30   TD3$-FFA      $/mt
8       TD3$+1Q   Q4 23  14.946  ...  2023-06-30   TD3$-FFA      $/mt
9       TD3$+2Q   Q1 24  13.056  ...  2023-06-30   TD3$-FFA      $/mt
10      TD3$+3Q   Q2 24  11.818  ...  2023-06-30   TD3$-FFA      $/mt
11      TD3$+4Q   Q3 24  11.407  ...  2023-06-30   TD3$-FFA      $/mt
12    TD3$+1CAL  Cal 24  12.693  ...  2023-06-30   TD3$-FFA      $/mt
13    TD3$+2CAL  Cal 25  12.057  ...  2023-06-30   TD3$-FFA      $/mt
14    TD3$+3CAL  Cal 26  11.756  ...  2023-06-30   TD3$-FFA      $/mt
15    TD3$+4CAL  Cal 27  11.683  ...  2023-06-30   TD3$-FFA      $/mt
16   TD20$BALMO  Jul 23  19.093  ...  2023-06-30  TD20$-FFA      $/mt
17  TD20$CURMON  Jul 23  19.093  ...  2023-06-30  TD20$-FFA      $/mt
18    TD20$+1_M  Aug 23  17.896  ...  2023-06-30  TD20$-FFA      $/mt
19    TD20$+2_M  Sep 23  17.832  ...  2023-06-30  TD20$-FFA      $/mt
20    TD20$+3_M  Oct 23  18.610  ...  2023-06-30  TD20$-FFA      $/mt
21    TD20$+4_M  Nov 23  19.417  ...  2023-06-30  TD20$-FFA      $/mt
22    TD20$+5_M  Dec 23  20.272  ...  2023-06-30  TD20$-FFA      $/mt
23    TD20$CURQ   Q3 23  18.274  ...  2023-06-30  TD20$-FFA      $/mt
24     TD20$+1Q   Q4 23  19.433  ...  2023-06-30  TD20$-FFA      $/mt
25     TD20$+2Q   Q1 24  17.142  ...  2023-06-30  TD20$-FFA      $/mt
26     TD20$+3Q   Q
<details>
<summary>英文:</summary>
I have this JSON list in Python:
[{&#39;id&#39;: &#39;TD3$-FFA&#39;,
&#39;shortCode&#39;: &#39;TD3$-FFA&#39;,
&#39;dataSet&#39;: {&#39;id&#39;: &#39;TD3C&#39;,
&#39;shortCode&#39;: &#39;TD3C&#39;,
&#39;shortDescription&#39;: &#39;Dirty Middle East Gulf to China&#39;,
&#39;displayGroup&#39;: &#39;BDTI&#39;,
&#39;datumUnit&#39;: &#39;Worldscale&#39;,
&#39;datumPrecision&#39;: 2,
&#39;data&#39;: [{&#39;value&#39;: 56.67, &#39;date&#39;: &#39;2023-06-30&#39;}],
&#39;apiIdentifier&#39;: &#39;RDSSYGSJBFEV9P2FLSCXGQC3510G2EGE&#39;},
&#39;datumUnit&#39;: &#39;$/mt&#39;,
&#39;datumPrecision&#39;: 3,
&#39;projectionStartOn&#39;: &#39;2010-05-10T00:00:00&#39;,
&#39;projectionEndOn&#39;: &#39;2023-06-30T00:00:00&#39;,
&#39;groupings&#39;: [{&#39;date&#39;: &#39;2023-06-30T00:00:00&#39;,
&#39;groups&#39;: [{&#39;periodType&#39;: &#39;m&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD3$BALMO&#39;,
&#39;period&#39;: &#39;Jul 23&#39;,
&#39;value&#39;: 14.4,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-07-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$CURMON&#39;,
&#39;period&#39;: &#39;Jul 23&#39;,
&#39;value&#39;: 14.4,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-07-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+1_M&#39;,
&#39;period&#39;: &#39;Aug 23&#39;,
&#39;value&#39;: 13.662,
&#39;validFrom&#39;: &#39;2023-08-01&#39;,
&#39;validTo&#39;: &#39;2023-08-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+2_M&#39;,
&#39;period&#39;: &#39;Sep 23&#39;,
&#39;value&#39;: 13.716,
&#39;validFrom&#39;: &#39;2023-09-01&#39;,
&#39;validTo&#39;: &#39;2023-09-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+3_M&#39;,
&#39;period&#39;: &#39;Oct 23&#39;,
&#39;value&#39;: 13.83,
&#39;validFrom&#39;: &#39;2023-10-01&#39;,
&#39;validTo&#39;: &#39;2023-10-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+4_M&#39;,
&#39;period&#39;: &#39;Nov 23&#39;,
&#39;value&#39;: 14.619,
&#39;validFrom&#39;: &#39;2023-11-01&#39;,
&#39;validTo&#39;: &#39;2023-11-30&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+5_M&#39;,
&#39;period&#39;: &#39;Dec 23&#39;,
&#39;value&#39;: 16.389,
&#39;validFrom&#39;: &#39;2023-12-01&#39;,
&#39;validTo&#39;: &#39;2023-12-22&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]},
{&#39;periodType&#39;: &#39;q&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD3$CURQ&#39;,
&#39;period&#39;: &#39;Q3 23&#39;,
&#39;value&#39;: 13.926,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-09-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+1Q&#39;,
&#39;period&#39;: &#39;Q4 23&#39;,
&#39;value&#39;: 14.946,
&#39;validFrom&#39;: &#39;2023-10-01&#39;,
&#39;validTo&#39;: &#39;2023-12-22&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+2Q&#39;,
&#39;period&#39;: &#39;Q1 24&#39;,
&#39;value&#39;: 13.056,
&#39;validFrom&#39;: &#39;2024-01-01&#39;,
&#39;validTo&#39;: &#39;2024-03-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+3Q&#39;,
&#39;period&#39;: &#39;Q2 24&#39;,
&#39;value&#39;: 11.818,
&#39;validFrom&#39;: &#39;2024-04-01&#39;,
&#39;validTo&#39;: &#39;2024-06-28&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+4Q&#39;,
&#39;period&#39;: &#39;Q3 24&#39;,
&#39;value&#39;: 11.407,
&#39;validFrom&#39;: &#39;2024-07-01&#39;,
&#39;validTo&#39;: &#39;2024-09-30&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]},
{&#39;periodType&#39;: &#39;y&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD3$+1CAL&#39;,
&#39;period&#39;: &#39;Cal 24&#39;,
&#39;value&#39;: 12.693,
&#39;validFrom&#39;: &#39;2024-01-01&#39;,
&#39;validTo&#39;: &#39;2024-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+2CAL&#39;,
&#39;period&#39;: &#39;Cal 25&#39;,
&#39;value&#39;: 12.057,
&#39;validFrom&#39;: &#39;2025-01-01&#39;,
&#39;validTo&#39;: &#39;2025-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+3CAL&#39;,
&#39;period&#39;: &#39;Cal 26&#39;,
&#39;value&#39;: 11.756,
&#39;validFrom&#39;: &#39;2026-01-01&#39;,
&#39;validTo&#39;: &#39;2026-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD3$+4CAL&#39;,
&#39;period&#39;: &#39;Cal 27&#39;,
&#39;value&#39;: 11.683,
&#39;validFrom&#39;: &#39;2027-01-01&#39;,
&#39;validTo&#39;: &#39;2027-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]}]}],
&#39;apiIdentifier&#39;: &#39;RPSVJJTJBXBCAF2FAG2PQAVYN4UGQ9LN&#39;},
{&#39;id&#39;: &#39;TD20$-FFA&#39;,
&#39;shortCode&#39;: &#39;TD20$-FFA&#39;,
&#39;dataSet&#39;: {&#39;id&#39;: &#39;TD20&#39;,
&#39;shortCode&#39;: &#39;TD20&#39;,
&#39;shortDescription&#39;: &#39;Dirty West Africa to UK-Continent&#39;,
&#39;displayGroup&#39;: &#39;BDTI&#39;,
&#39;datumUnit&#39;: &#39;Worldscale&#39;,
&#39;datumPrecision&#39;: 2,
&#39;data&#39;: [{&#39;value&#39;: 101.14, &#39;date&#39;: &#39;2023-06-30&#39;}],
&#39;apiIdentifier&#39;: &#39;RDSU23QH0OX6DZZDC5BYZTQIZ9TXHUQR&#39;},
&#39;datumUnit&#39;: &#39;$/mt&#39;,
&#39;datumPrecision&#39;: 3,
&#39;projectionStartOn&#39;: &#39;2014-08-01T00:00:00&#39;,
&#39;projectionEndOn&#39;: &#39;2023-06-30T00:00:00&#39;,
&#39;groupings&#39;: [{&#39;date&#39;: &#39;2023-06-30T00:00:00&#39;,
&#39;groups&#39;: [{&#39;periodType&#39;: &#39;m&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD20$BALMO&#39;,
&#39;period&#39;: &#39;Jul 23&#39;,
&#39;value&#39;: 19.093,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-07-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$CURMON&#39;,
&#39;period&#39;: &#39;Jul 23&#39;,
&#39;value&#39;: 19.093,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-07-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+1_M&#39;,
&#39;period&#39;: &#39;Aug 23&#39;,
&#39;value&#39;: 17.896,
&#39;validFrom&#39;: &#39;2023-08-01&#39;,
&#39;validTo&#39;: &#39;2023-08-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+2_M&#39;,
&#39;period&#39;: &#39;Sep 23&#39;,
&#39;value&#39;: 17.832,
&#39;validFrom&#39;: &#39;2023-09-01&#39;,
&#39;validTo&#39;: &#39;2023-09-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+3_M&#39;,
&#39;period&#39;: &#39;Oct 23&#39;,
&#39;value&#39;: 18.61,
&#39;validFrom&#39;: &#39;2023-10-01&#39;,
&#39;validTo&#39;: &#39;2023-10-31&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+4_M&#39;,
&#39;period&#39;: &#39;Nov 23&#39;,
&#39;value&#39;: 19.417,
&#39;validFrom&#39;: &#39;2023-11-01&#39;,
&#39;validTo&#39;: &#39;2023-11-30&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+5_M&#39;,
&#39;period&#39;: &#39;Dec 23&#39;,
&#39;value&#39;: 20.272,
&#39;validFrom&#39;: &#39;2023-12-01&#39;,
&#39;validTo&#39;: &#39;2023-12-22&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-07-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]},
{&#39;periodType&#39;: &#39;q&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD20$CURQ&#39;,
&#39;period&#39;: &#39;Q3 23&#39;,
&#39;value&#39;: 18.274,
&#39;validFrom&#39;: &#39;2023-07-01&#39;,
&#39;validTo&#39;: &#39;2023-09-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+1Q&#39;,
&#39;period&#39;: &#39;Q4 23&#39;,
&#39;value&#39;: 19.433,
&#39;validFrom&#39;: &#39;2023-10-01&#39;,
&#39;validTo&#39;: &#39;2023-12-22&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+2Q&#39;,
&#39;period&#39;: &#39;Q1 24&#39;,
&#39;value&#39;: 17.142,
&#39;validFrom&#39;: &#39;2024-01-01&#39;,
&#39;validTo&#39;: &#39;2024-03-29&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+3Q&#39;,
&#39;period&#39;: &#39;Q2 24&#39;,
&#39;value&#39;: 14.091,
&#39;validFrom&#39;: &#39;2024-04-01&#39;,
&#39;validTo&#39;: &#39;2024-06-28&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+4Q&#39;,
&#39;period&#39;: &#39;Q3 24&#39;,
&#39;value&#39;: 12.478,
&#39;validFrom&#39;: &#39;2024-07-01&#39;,
&#39;validTo&#39;: &#39;2024-09-30&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-09-29&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]},
{&#39;periodType&#39;: &#39;y&#39;,
&#39;projections&#39;: [{&#39;identifier&#39;: &#39;TD20$+1CAL&#39;,
&#39;period&#39;: &#39;Cal 24&#39;,
&#39;value&#39;: 14.904,
&#39;validFrom&#39;: &#39;2024-01-01&#39;,
&#39;validTo&#39;: &#39;2024-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+2CAL&#39;,
&#39;period&#39;: &#39;Cal 25&#39;,
&#39;value&#39;: 14.184,
&#39;validFrom&#39;: &#39;2025-01-01&#39;,
&#39;validTo&#39;: &#39;2025-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;},
{&#39;identifier&#39;: &#39;TD20$+3CAL&#39;,
&#39;period&#39;: &#39;Cal 26&#39;,
&#39;value&#39;: 13.831,
&#39;validFrom&#39;: &#39;2026-01-01&#39;,
&#39;validTo&#39;: &#39;2026-12-24&#39;,
&#39;nextRolloverDate&#39;: &#39;2023-12-22&#39;,
&#39;archiveDate&#39;: &#39;2023-06-30&#39;}]}]}],
&#39;apiIdentifier&#39;: &#39;RPSRTIFJYJVDT9TFWIYQMLXN2ZN7RRK1&#39;}]
Now I use `df_usd_mt = pd.json_normalize(response_usd_mt, record_path=[&#39;groupings&#39;, &#39;groups&#39;, &#39;projections&#39;], meta=[&#39;shortCode&#39;, &#39;datumUnit&#39;])` to ALMOST get everything I need.
my current dataframe looks like this
identifier  period   value  ... archiveDate  shortCode datumUnit
0     TD3$BALMO  Jul 23  14.400  ...  2023-06-30   TD3$-FFA      $/mt
1    TD3$CURMON  Jul 23  14.400  ...  2023-06-30   TD3$-FFA      $/mt
2      TD3$+1_M  Aug 23  13.662  ...  2023-06-30   TD3$-FFA      $/mt
3      TD3$+2_M  Sep 23  13.716  ...  2023-06-30   TD3$-FFA      $/mt
4      TD3$+3_M  Oct 23  13.830  ...  2023-06-30   TD3$-FFA      $/mt
5      TD3$+4_M  Nov 23  14.619  ...  2023-06-30   TD3$-FFA      $/mt
6      TD3$+5_M  Dec 23  16.389  ...  2023-06-30   TD3$-FFA      $/mt
7      TD3$CURQ   Q3 23  13.926  ...  2023-06-30   TD3$-FFA      $/mt
8       TD3$+1Q   Q4 23  14.946  ...  2023-06-30   TD3$-FFA      $/mt
9       TD3$+2Q   Q1 24  13.056  ...  2023-06-30   TD3$-FFA      $/mt
10      TD3$+3Q   Q2 24  11.818  ...  2023-06-30   TD3$-FFA      $/mt
11      TD3$+4Q   Q3 24  11.407  ...  2023-06-30   TD3$-FFA      $/mt
12    TD3$+1CAL  Cal 24  12.693  ...  2023-06-30   TD3$-FFA      $/mt
13    TD3$+2CAL  Cal 25  12.057  ...  2023-06-30   TD3$-FFA      $/mt
14    TD3$+3CAL  Cal 26  11.756  ...  2023-06-30   TD3$-FFA      $/mt
15    TD3$+4CAL  Cal 27  11.683  ...  2023-06-30   TD3$-FFA      $/mt
16   TD20$BALMO  Jul 23  19.093  ...  2023-06-30  TD20$-FFA      $/mt
17  TD20$CURMON  Jul 23  19.093  ...  2023-06-30  TD20$-FFA      $/mt
18    TD20$+1_M  Aug 23  17.896  ...  2023-06-30  TD20$-FFA      $/mt
19    TD20$+2_M  Sep 23  17.832  ...  2023-06-30  TD20$-FFA      $/mt
20    TD20$+3_M  Oct 23  18.610  ...  2023-06-30  TD20$-FFA      $/mt
21    TD20$+4_M  Nov 23  19.417  ...  2023-06-30  TD20$-FFA      $/mt
22    TD20$+5_M  Dec 23  20.272  ...  2023-06-30  TD20$-FFA      $/mt
23    TD20$CURQ   Q3 23  18.274  ...  2023-06-30  TD20$-FFA      $/mt
24     TD20$+1Q   Q4 23  19.433  ...  2023-06-30  TD20$-FFA      $/mt
25     TD20$+2Q   Q1 24  17.142  ...  2023-06-30  TD20$-FFA      $/mt
26     TD20$+3Q   Q2 24  14.091  ...  2023-06-30  TD20$-FFA      $/mt
27     TD20$+4Q   Q3 24  12.478  ...  2023-06-30  TD20$-FFA      $/mt
28   TD20$+1CAL  Cal 24  14.904  ...  2023-06-30  TD20$-FFA      $/mt
29   TD20$+2CAL  Cal 25  14.184  ...  2023-06-30  TD20$-FFA      $/mt
30   TD20$+3CAL  Cal 26  13.831  ...  2023-06-30  TD20$-FFA      $/mt
I only wish to have one additional column with the information under &#39;dataSet&#39;&gt;&gt;&#39;id&#39; for example for the first 16 rows, I need &#39;TD3C&#39; as values of this additional column, this info could be seen at the top of the Json list. to be accurate, the 3rd row of the data, for the following rows I need value &quot;TD20&quot;
I really can&#39;t figure it out and asked Chatgpt, it gave me this code which looks correct but just generate Traceback
df_usd_mt = pd.json_normalize(response_usd_mt, 
record_path=[&#39;groupings&#39;, &#39;groups&#39;, &#39;projections&#39;], 
meta=[&#39;shortCode&#39;, &#39;datumUnit&#39;, [&#39;dataSet&#39;, &#39;id&#39;]])
</details>
# 答案1
**得分**: 1
你可以将 dataSet 添加到 meta 中，然后解析出字典。您将从 dataSet 键中获取所有列，但可以筛选出您想要的列。
```python
df = pd.json_normalize(
data=data,
meta_prefix="meta.",
meta=["dataSet", "shortCode", "datumUnit"],
record_path=["groupings", "groups", "projections"]
)
df = df.join(pd.DataFrame(df.pop("meta.dataSet").tolist()))
df = df[["identifier", "period", "value", "validFrom", "validTo", "nextRolloverDate",
"archiveDate", "meta.shortCode", "meta.datumUnit", "id"]]
df.columns = df.columns.str.split(".").str[-1]
print(df)

输出：

    identifier  period  value   validFrom     validTo nextRolloverDate archiveDate  shortCode datumUnit    id
0     TD3$BALMO  Jul 23   14.4  2023-07-01  2023-07-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
1    TD3$CURMON  Jul 23   14.4  2023-07-01  2023-07-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
2      TD3$+1_M  Aug 23 13.662  2023-08-01  2023-08-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
3      TD3$+2_M  Sep 23 13.716  2023-09-01  2023-09-29       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
4      TD3$+3_M  Oct 23  13.83  2023-10-01  2023-10-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
5      TD3$+4_M  Nov 23 14.619  2023-11-01  2023-11-30       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
6      TD3$+5_M  Dec 23 16.389  2023-12-01  2023-12-22       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
7      TD3$CURQ   Q3 23 13.926  2023-07-01  2023-09-29       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
8       TD3$+1Q   Q4 23 14.946  2023-10-01  2023-12-22       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
9       TD3$+2Q   Q1 24 13.056  2024-01-01  2024-03-29       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
10      TD3$+3Q   Q2 24 11.818  2024-04-01  2024-06-28       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
11      TD3$+4Q   Q3 24 11.407  2024-07-01  2024-09-30       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
12    TD3$+1CAL  Cal 24 12.693  2024-01-01  2024-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
13    TD3$+2CAL  Cal 25 12.057  2025-01-01  2025-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
14    TD3$+3CAL  Cal 26 11.756  2026-01-01  2026-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
15    TD3$+4CAL  Cal 27 11.683  2027-01-01  2027-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
16   TD20$BALMO  Jul 23 19.093  2023-07-01  2023-07-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
17  TD20$CURMON  Jul 23 19.093  2023-07-01  2023-07-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
18    TD20$+1_M  Aug 23 17.896  2023-08-01  2023-08-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
19    TD20$+2_M  Sep 23 17.832  2023-09-01  2023-09-29       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
20    TD20$+3_M  Oct 23  18.61  2023-10-01  2023-10-31       2023-07-29  2023-06-30  TD20$-FFA
<details>
<summary>英文:</summary>
You can add dataSet to meta and then parse out the dict. You will get all the columns from dataSet keys but can filter out only the columns you want.
df = pd.json_normalize(
data=data,
meta_prefix=&quot;meta.&quot;,
meta=[&quot;dataSet&quot;, &quot;shortCode&quot;, &quot;datumUnit&quot;],
record_path=[&quot;groupings&quot;, &quot;groups&quot;, &quot;projections&quot;]
)
df = df.join(pd.DataFrame(df.pop(&quot;meta.dataSet&quot;).tolist()))
df = df[[&quot;identifier&quot;, &quot;period&quot;, &quot;value&quot;, &quot;validFrom&quot;, &quot;validTo&quot;, &quot;nextRolloverDate&quot;,
&quot;archiveDate&quot;, &quot;meta.shortCode&quot;, &quot;meta.datumUnit&quot;, &quot;id&quot;]]
df.columns = df.columns.str.split(&quot;.&quot;).str[-1]
print(df)
Output:
identifier  period  value   validFrom     validTo nextRolloverDate archiveDate  shortCode datumUnit    id
0     TD3$BALMO  Jul 23   14.4  2023-07-01  2023-07-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
1    TD3$CURMON  Jul 23   14.4  2023-07-01  2023-07-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
2      TD3$+1_M  Aug 23 13.662  2023-08-01  2023-08-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
3      TD3$+2_M  Sep 23 13.716  2023-09-01  2023-09-29       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
4      TD3$+3_M  Oct 23  13.83  2023-10-01  2023-10-31       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
5      TD3$+4_M  Nov 23 14.619  2023-11-01  2023-11-30       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
6      TD3$+5_M  Dec 23 16.389  2023-12-01  2023-12-22       2023-07-29  2023-06-30   TD3$-FFA      $/mt  TD3C
7      TD3$CURQ   Q3 23 13.926  2023-07-01  2023-09-29       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
8       TD3$+1Q   Q4 23 14.946  2023-10-01  2023-12-22       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
9       TD3$+2Q   Q1 24 13.056  2024-01-01  2024-03-29       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
10      TD3$+3Q   Q2 24 11.818  2024-04-01  2024-06-28       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
11      TD3$+4Q   Q3 24 11.407  2024-07-01  2024-09-30       2023-09-29  2023-06-30   TD3$-FFA      $/mt  TD3C
12    TD3$+1CAL  Cal 24 12.693  2024-01-01  2024-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
13    TD3$+2CAL  Cal 25 12.057  2025-01-01  2025-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
14    TD3$+3CAL  Cal 26 11.756  2026-01-01  2026-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
15    TD3$+4CAL  Cal 27 11.683  2027-01-01  2027-12-24       2023-12-22  2023-06-30   TD3$-FFA      $/mt  TD3C
16   TD20$BALMO  Jul 23 19.093  2023-07-01  2023-07-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
17  TD20$CURMON  Jul 23 19.093  2023-07-01  2023-07-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
18    TD20$+1_M  Aug 23 17.896  2023-08-01  2023-08-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
19    TD20$+2_M  Sep 23 17.832  2023-09-01  2023-09-29       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
20    TD20$+3_M  Oct 23  18.61  2023-10-01  2023-10-31       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
21    TD20$+4_M  Nov 23 19.417  2023-11-01  2023-11-30       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
22    TD20$+5_M  Dec 23 20.272  2023-12-01  2023-12-22       2023-07-29  2023-06-30  TD20$-FFA      $/mt  TD20
23    TD20$CURQ   Q3 23 18.274  2023-07-01  2023-09-29       2023-09-29  2023-06-30  TD20$-FFA      $/mt  TD20
24     TD20$+1Q   Q4 23 19.433  2023-10-01  2023-12-22       2023-09-29  2023-06-30  TD20$-FFA      $/mt  TD20
25     TD20$+2Q   Q1 24 17.142  2024-01-01  2024-03-29       2023-09-29  2023-06-30  TD20$-FFA      $/mt  TD20
26     TD20$+3Q   Q2 24 14.091  2024-04-01  2024-06-28       2023-09-29  2023-06-30  TD20$-FFA      $/mt  TD20
27     TD20$+4Q   Q3 24 12.478  2024-07-01  2024-09-30       2023-09-29  2023-06-30  TD20$-FFA      $/mt  TD20
28   TD20$+1CAL  Cal 24 14.904  2024-01-01  2024-12-24       2023-12-22  2023-06-30  TD20$-FFA      $/mt  TD20
29   TD20$+2CAL  Cal 25 14.184  2025-01-01  2025-12-24       2023-12-22  2023-06-30  TD20$-FFA      $/mt  TD20
30   TD20$+3CAL  Cal 26 13.831  2026-01-01  2026-12-24       2023-12-22  2023-06-30  TD20$-FFA      $/mt  TD20
</details>

通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。

How to use pd.json_normalize to retrieve the data I need from 2 parts (I'm almost there already, only need last piece of data)

问题

无法在 Discord 机器人的功能中发送图片。

应用 libcst codemod 并跳过测试文件。

Scrape href from td – can’t get it to work correctly.

将格式 ‘year-month-dayT11:04:11.373Z’ 在 Python 中转换为只有 y/m/d。

What's the correct way to type hint an empty list as a literal in python?

如何在Highcharts Gantt中更改本地化的星期名称

如何在同一个流中使用多个过滤器和映射函数？

如何使用Map/Set来将代码优化到O(n)？

.NET MAUI Android在GitHub Actions上构建失败，错误代码为1。

如何在Playwright视觉比较中屏蔽多个定位器？

在C++中，可以使用可变模板参数来检索类型的内部类型。

selenium.common.exceptions.StaleElementReferenceException: Message: stale element reference: stale element not found

Creating and opening a URL to log in to Website via Basic Auth with Robot Framework/Selenium (Python)

AG Grid 在上下文菜单中以大文本形式打开

发表评论