Pandas筑巢

Question

目前，我有一个在熊猫数据帧刮url表。其目的是输出一个嵌套的json输出，通过使用groupby（）和Lambda函数，我几乎得到了我想要的东西。我一直在学习这个，所以可能不是很好的代码。你知道吗

{
"Field (Discovery)": "33/9-6 DELTA",
"NPDID information carrier": 44576,
"MonthlyProduction": [
  {
    "yyyymm": "2009.07.0",
    "Oil - saleable [mill Sm3]": 0.00025,
    "Gas - saleable [bill Sm3]": 0,
    "NGL - saleable [mill Sm3]": -0.00004,
    "Condensate - saleable [mill Sm3]": 0,
    "Oil equivalents - saleable [mill Sm3]": 0.00021,
    "Water - wellbores [mill Sm3]": 0.00051
  }

我要寻找的是如何将JSON的嵌套部分进一步分解，以便在“yyyymm”下面使用列和值，并按如下方式嵌套：

{
"Field (Discovery)": "33/9-6 DELTA",
"NPDID information carrier": 44576,
"MonthlyProduction": [
  {
    "yyyymm": "2009.07.0",
    "Oil – saleable: [
        {
         "Value":0.00025,
         "Unit":  mill Sm3,
        }
       ]
    "Gas - saleable":[
        {
        "Value": 0,
        "Unit":  bill Sm3,
        }
       ]
        "NGL - saleable ": -0.00004, etc
        "Condensate - saleable [mill Sm3]": 0, etc

代码：

import requests
from bs4 import BeautifulSoup
import json
from datetime import datetime as dt
import datetime
import pandas as pd

starttime = dt.now()

#Agent detail to prevent scraping bot detection
user_agent = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_3) 
AppleWebKit/537.36 (KHTML, like Gecko) Chrome/35.0.1916.47 Safari/537.36'
header = {'User-Agent' : user_agent }


# Webpage connection
html ="http://factpages.npd.no/ReportServer?/FactPages/TableView/
field_production_monthly&rs:Command=Render&rc:Toolbar=false&
rc:Parameters=f&Top100=False&IpAddress=108.171.128.174&CultureCode=en"

r=requests.get(html, headers=header)
c=r.content
soup=BeautifulSoup(c,"html.parser")

table = soup.find('table', attrs={'class':'a133'})

#Pandas dataframe 
df = pd.read_html(str(table), header=0)[0]
df['yyyymm'] = df['Year'].map(str)+df['Month'].map(str)
#df['NPDID information carrier'].astype(int)

df.info()

result = (df.groupby(["Field (Discovery)","NPDID information carrier"], 
as_index=False)
         .apply(lambda x: x[[ 'yyyymm','Oil - saleable [mill Sm3]','Gas - 
         saleable [bill Sm3]','NGL - saleable [mill Sm3]','Condensate - 
         saleable [mill Sm3]','Oil equivalents - saleable [mill Sm3]','Water 
         - wellbores [mill Sm3]' ]].to_dict('r'))
         .reset_index()
         .rename(columns={0: 'MonthlyProduction'})
         .to_json(orient='records'))

#print(result)
#print(json.dumps(json.loads(result), indent=2, sort_keys=True))

#Time
runtime = dt.now() - starttime
print(runtime)

Answer 1

我想你需要：

#define columns names
c1 = ["Field (Discovery)","NPDID information carrier"]
c2 = ['Oil - saleable [mill Sm3]', 
      'Gas - saleable [bill Sm3]', 
      'NGL - saleable [mill Sm3]',
      'Condensate - saleable [mill Sm3]', 
      'Oil equivalents - saleable [mill Sm3]', 
      'Water - wellbores [mill Sm3]']

#change values to dictionaries
def f(x):
    a = x.name.split('[')[1].strip(']')
    return list(zip([{'Unit': a}]*len(x),x))

df[c2] = df[c2].applymap(lambda x: {'Value': x}).apply(f)

#rename columns for remove `[]`
d = dict(zip(df[c2].columns, df[c2].columns.str.split('\s+\[').str[0]))
df = df.rename(columns=d)

#a bit improve your solution
j = (df.groupby(c1)
       .apply(lambda x: x[['yyyymm'] + list(d.values())].to_dict('r'))
       .reset_index(name='MonthlyProduction')
       .to_json(orient='records'))

编辑：

def f(x):
    a = x.name.split('[')[1].strip(']')
    return [({'Unit': a, 'Value': i})  for i in x]

df[c2] = df[c2].apply(f)

#rename columns for remove `[]`
d = dict(zip(df[c2].columns, df[c2].columns.str.split('\s+\[').str[0]))
df = df.rename(columns=d)
#print (df.head())


#a bit improve your solution
j = (df.groupby(c1)
       .apply(lambda x: x[['yyyymm'] + list(d.values())].to_dict('r'))
       .reset_index(name='MonthlyProduction')
       .to_json(orient='records'))