In [1]:

    

# Standard Imports.
import numpy as np
import pandas as pd

import csv
import json


    

In [5]:

    

src_df = []
# Reading input file.
with open('C:\\Users\\sh242807\\workarea\\appconfig-data-migration\\round2\\app-migration_2018-09-27 09_43_01.782.csv') as f:
    lines = csv.reader(f, delimiter=",", quotechar='"')
    for line in lines:
        src_df.append(line)


    

In [6]:

    

key_list = set()
for i in range(1, 1675):
    urls = json.loads(src_df[i][8])
    for j in urls:
        key_list.add(j["key"])


    

In [9]:

    

df = pd.read_csv('C:\\Users\\sh242807\\workarea\\appconfig-data-migration\\round2\\app-migration_2018-09-27 09_43_01.782.csv')


    

In [5]:

    

#wrk = df.iloc[:, [0,5,6,7]].copy()


    

In [11]:

    

for i in sorted(list(key_list)):
    df[i] = np.nan


    

In [13]:

    

for i in range(0,1674):
    for j in json.loads(df.loc[i, "urlconfigsList"]):
        df.loc[i, j['key']] = j["value"]


    

In [79]:

    

df.to_csv('final.csv')


    

In [15]:

    

df.head()


    

    
Out[15]:







  

    

      

      
application
      
applicationIndex
      
country
      
createdat
      
envIndex
      
environment
      
locale
      
tagid
      
urlconfigsList
      
modifiedAt
      
...
      
tonguecare
      
userreg.hsdp.userserv
      
userreg.janrain.api
      
userreg.janrain.cdn
      
userreg.janrain.engage
      
userreg.landing.emailverif
      
userreg.landing.myphilips
      
userreg.landing.resetpass
      
userreg.smssupported
      
userreg.urx.verificationsmscode
    
  
  

    

      
0
      
11032
      
GB-11032
      
GB
      
2017-01-27T11:48:45
      
GB-11032-prod
      
prod
      
en-GB
      
apps++env+prod
      
[{"key":"userreg.janrain.cdn","value":"https:/...
      
2017-01-27T11:48:45
      
...
      
NaN
      
NaN
      
https://philips.janraincapture.com
      
https://d1lqe9temigv1p.cloudfront.net
      
NaN
      
https://www.philips.co.uk/c-w/verify-account.html
      
https://www.philips.co.uk/myphilips/login.html
      
https://www.philips.co.uk/myphilips/reset-pass...
      
NaN
      
NaN
    
    

      
1
      
11032
      
RW-11032
      
RW
      
2017-01-27T11:44:24
      
RW-11032-prod
      
prod
      
ar-RW
      
apps++env+prod
      
[{"key":"userreg.janrain.cdn","value":"https:/...
      
2017-01-27T11:44:25
      
...
      
NaN
      
NaN
      
https://philips.janraincapture.com
      
https://d1lqe9temigv1p.cloudfront.net
      
NaN
      
https://www.mea.philips.com/c-w/verify-account...
      
https://www.mea.philips.com/myphilips/login.html
      
https://www.mea.philips.com/myphilips/reset-pa...
      
NaN
      
NaN
    
    

      
2
      
11032
      
ID-11032
      
ID
      
2017-01-27T11:54:09
      
ID-11032-prod
      
prod
      
en-ID
      
apps++env+prod
      
[{"key":"userreg.janrain.cdn","value":"https:/...
      
2017-01-27T11:54:09
      
...
      
NaN
      
NaN
      
https://philips.janraincapture.com
      
https://d1lqe9temigv1p.cloudfront.net
      
NaN
      
https://www.philips.co.id/c-w/verify-account.html
      
https://www.philips.co.id/myphilips/login.html
      
https://www.philips.co.id/myphilips/reset-pass...
      
NaN
      
NaN
    
    

      
3
      
11032
      
BG-11032
      
BG
      
2017-01-27T11:44:37
      
BG-11032-prod
      
prod
      
bg-BG
      
apps++env+prod
      
[{"key":"userreg.janrain.cdn","value":"https:/...
      
2017-01-27T11:44:37
      
...
      
NaN
      
NaN
      
https://philips.janraincapture.com
      
https://d1lqe9temigv1p.cloudfront.net
      
NaN
      
https://www.philips.bg/c-w/verify-account.html
      
https://www.philips.bg/myphilips/login.html
      
https://www.philips.bg/myphilips/reset-passwor...
      
NaN
      
NaN
    
    

      
4
      
11032
      
CN-11032
      
CN
      
2017-01-27T13:32:52
      
CN-11032-prod
      
prod
      
zh-CN
      
apps++env+prod
      
[{"key":"userreg.janrain.cdn","value":"https:/...
      
2017-02-16T13:51:39
      
...
      
NaN
      
NaN
      
https://philips-cn.capture.cn.janrain.com
      
https://janrain-capture-static.cn.janrain.com
      
https://philips-prod.login.cn.janrain.com
      
https://www.philips.com.cn/c-w/user-registrati...
      
https://www.philips.com.cn/c-w/user-registrati...
      
https://www.philips.com.cn/c-w/user-registrati...
      
http://philips.mobile
      
https://www.philips.com.cn/api/v1/user/request...
    
  

5 rows × 96 columns