'O365-Rest-Python-Client - Struggling to get the displayname mapped
I am trying to convert a sharepoint list to a pandas dataframe. The Internal name is being returned by default I believe by the client, which isnt very useful - so am trying to map to the display name.
from office365.runtime.auth.user_credential import UserCredential
from office365.sharepoint.client_context import ClientContext
import json
site_url = "https://acme.sharepoint.com/sites/AA483"
ctx = ClientContext(site_url).with_credentials(UserCredential("[email protected]", "mypassword"))
def list_to_dataframeSP(lista):
sp_list = lista
sp_lists = ctx.web.lists
s_list = sp_lists.get_by_title(sp_list)
l_items = s_list.get_items()
ctx.load(l_items)
ctx.execute_query()
fields = s_list.fields.get().execute_query()
fields_list = [field.properties for field in fields if not field.hidden]
renamed_cols = {field['InternalName']: field['Title'] for field in fields_list}
for item_obj in l_items:
item = item_obj.properties
old_keys = item.keys()
keys_to_rename = renamed_cols.keys()
common_keys = [key for key in old_keys if key in keys_to_rename]
for key in common_keys:
new_name = renamed_cols[key]
item[new_name] = item.pop(key)
columnas=list(pd.DataFrame.from_dict(l_items[0].properties.items()).iloc[:,0])
valores=list()
for item in l_items:
data=list(pd.DataFrame.from_dict(item.properties.items()).iloc[:,1])
valores.append(data)
resultdf=pd.DataFrame(valores,columns=columnas)
return resultdf
spol_df = list_to_dataframeSP("MyList")
While in the above example I am mapping InternalName to the Title value, I have also tried mapping EntityPropertyName. EntityPropertyName works better, but I still end up a couple of fields which did not get mapped.
If I map InternalName I get poor results:
Id int64
ServerRedirectedEmbedUri object
ServerRedirectedEmbedUrl object
ContentTypeId object
OData__x0028_KF_x0029__x003a_Initial_x float64
OData__x0028_KF_x0029__x003a_Initial_x0 object
OData__x0028_KF_x0029__x003a_Report_x0 float64
OData__x0028_KF_x0029__x003a_Source float64
OData__x0028_KF_x0029__x003a_AZ_x0020_ float64
OData__x0028_KF_x0029__x003a_Country_x float64
OData__x0028_KF_x0029__x003a_Protocol_ float64
OData__x0028_KF_x0029__x003a_Case_x002 float64
OData__x0028_KF_x0029__x003a_Patient_x float64
OData__x0028_KF_x0029__x003a_Events_x0 float64
OData__x0028_KF_x0029__x003a_Seriousne float64
OData__x0028_KF_x0029__x003a_Reported_ float64
OData__x0028_KF_x0029__x003a_Order_x00 float64
OData__x0028_KF_x0029__x003a_Drug_x002 float64
OData__x0028_KF_x0029__x003a_Product_x float64
OData__x0028_KF_x0029__x003a_Study_x00 float64
OData__x0028_KF_x0029__x003a_Key_x0020 float64
OData__x0028_KF_x0029__x003a_Formulati float64
OData__x0028_KF_x0029__x003a_Associati float64
OData__x0028_KF_x0029__x003a_Case_x0020 float64
OData__x0028_KF_x0029__x003a_PQC_x0020 float64
OData__x0028_KF_x0029__x003a_New_x0020 float64
OData__x0028_KF_x0029__x003a_Pregnant object
OData__x0028_KF_x0029__x003a_Suppress_ float64
OData__x0028_PE_x0029__x003a_Triage_x0 float64
OData__x0028_PE_x0029__x003a_LP_x0020_ float64
OData__x0028_PE_x0029__x003a_GSP_x0020 float64
OData__x0028_PE_x0029__x003a_Action_x0 float64
OData__x0028_PE_x0029__x003a_QRE object
OData__x0028_PE_x0029__x003a_Document_ float64
OData__x0028_PE_x0029__x003a_Document_0 float64
OData__x0028_PE_x0029__x003a_Case_x002 float64
OData__x0028_PE_x0029__x003a_Case_x0020 float64
KPI_x0020_person_x0020__x0028_naId int64
KPI_x0020_person_x0020__x0028_naStringId object
AuthorId int64
EditorId int64
OData__x0028_OTHER_x0029__x003a_Duplic float64
OData__x0028_OTHER_x0029__x003a_Import float64
OData__x0028_KEY_x0029__x003a_Medicall float64
OData__x0028_KEY_x0029__x003a_Combinat float64
OData__x0028_KEY_x0029__x003a_Potentia float64
OData__x0028_KEY_x0029__x003a_Lot_x002 float64
OData__x0028_OTHER_x0029__x003a_Initia float64
OData__x0028_OTHER_x0029__x003a_Critic float64
OData__x0028_OTHER_x0029__x003a_Type_x float64
OData__x0028_OTHER_x0029__x003a_Device float64
OData__x0028_OTHER_x0029__x003a_Pregna float64
SapphireFieldsRequiringCorrectioId object
OData__UIVersionString object
GUID object
Case ID-version object
Compliance Asset Id object
Date case closed object
Case source (code) object
Case category object
CIOMS I Globally Expedited object
If I use EntityPropertyName, I get better results, but still do not get 100% coverage.
renamed_cols = {field['EntityPropertyName']: field['Title'] for field in fields_list}
FileSystemObjectType int64
Id int64
ServerRedirectedEmbedUri object
ServerRedirectedEmbedUrl object
ContentTypeId object
KPI_x0020_person_x0020__x0028_naId int64
KPI_x0020_person_x0020__x0028_naStringId object
AuthorId int64
EditorId int64
SapphireFieldsRequiringCorrectioId object
GUID object
Case ID-version object
Compliance Asset Id object
Date case closed object
Case source (code) object
Case category object
CIOMS I Globally Expedited object
Country of case object
Protocol ID object
License Partner object
Key Ingredient object
Case Owner (PRID) object
Case owner (name) object
QRE check by (name) object
Maybe there is a way to call the API to just get displaynames, or maybe I need to do multiple passes, using InternalName and EntityName?
bject
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|