Pandas squash data frame based on a column

user_140903 0
Viewed 749

I am reading from an API which returns JSON I am using 

from pandas.io.json import json_normalize 
flatten = json_normalize(data['results'])

To flatten the JSON and now the output is like

                                     breakdowns                 metric                  time         value   
0      [{u'key': u'platform', u'value': u'ios'}]      fb_ad_network_imp    2018-08-29T07:00:00+0000  12
1  [{u'key': u'platform', u'value': u'android'}]      fb_ad_network_imp    2018-08-29T07:00:00+0000  32
2      [{u'key': u'platform', u'value': u'ios'}]  fb_ad_network_request    2018-08-29T07:00:00+0000  33    
3  [{u'key': u'platform', u'value': u'android'}]  fb_ad_network_request    2018-08-29T07:00:00+0000  132

now I want to squash these 4 rows into 2 based on the platform, something like this:

           platform    date         clicks     impressions
0          ios         2018-08-29   33         12
1          android     2018-08-29   132        32

I have also mapped these names:

fb_ad_network_request -> clicks
fb_ad_network_imp -> impressions

what's the best way to do that?

pandas python
4 Answers

You can using pivot_table after flatten the dict 

dddd['platform']=pd.concat([pd.DataFrame(x) for x in dddd.breakdowns]).value.values
dddd.pivot_table(index=['platform','time'],columns='metric',values='value',aggfunc=sum).reset_index()
Out[237]: 
metric platform        time  fb_ad_network_imp  fb_ad_network_request
0       android  2018-08-29                 32                    132
1           ios  2018-08-29                 12                     33
user_79645270

Setup

tmp = pd.Series([i[0].get('value', None) for i in df.breakdowns]).rename('platform')

mapping = {
    'columns': {
        'fb_ad_network_request': 'clicks',
        'fb_ad_network_imp': 'impressions',
        'time': 'date',
    }
}

Using groupby and unstack:

(df.join(tmp).groupby(['platform', df.time.dt.date, 'metric'])
    .value.sum().unstack().reset_index().rename(**mapping))


metric platform        date  impressions  clicks
0       android  2018-08-29           32     132
1           ios  2018-08-29           12      33
user_34832030

Setup

df = pd.DataFrame({
    'breakdowns': [[{u'key': u'platform', u'value': u'ios'}],
                   [{u'key': u'platform', u'value': u'android'}],
                   [{u'key': u'platform', u'value': u'ios'}],
                   [{u'key': u'platform', u'value': u'android'}]],
    'metric': ['fb_ad_network_imp'] * 2 + ['fb_ad_network_request'] * 2,
    'time': ['2018-08-29T07:00:00+0000'] * 4,
    'value': [12, 32, 33, 132]
})
df['time'] = pd.DatetimeIndex(df['time'])

Solution

This assumes that the time column holds timestamps, and then uses the dt accessor method to get the dates and assign it to a new column in the chained dataframe.

I used a lambda function to get the platform from the breakdowns column, and then group on those values together with the date and metric. The metric is unstacked so that each will be in separate columns, the index is reset and columns are renamed to the desired format.

result = (
    df
    .assign(date=df['time'].dt.date)
    .groupby([df['breakdowns'].apply(lambda x: x[0].get('value')), 'date', 'metric'])
    ['value']
    .sum()
    .unstack('metric')
    .reset_index()
    .rename(columns={
        'breakdowns': 'platform',
        'fb_ad_network_request': 'clicks',
        'fb_ad_network_imp': 'impressions'
    })
)
result.columns.name = None

>>> result
  platform        date  impressions  clicks
0  android  2018-08-29           32     132
1      ios  2018-08-29           12      33
user_24118020

Create a pandas.Series from a dictionary comprehesion

m0 = dict(fb_ad_network_imp='impressions', fb_ad_network_request='clicks')
flatten.time = pd.to_datetime(flatten.time).dt.floor('D')

s = pd.Series({
    (b[0]['value'], t, m0[m]): v for b, m, t, v in flatten.values
})

s.rename_axis(['platform', 'date', None]).unstack().reset_index()

  platform       date  clicks  impressions
0  android 2018-08-29     132           32
1      ios 2018-08-29      33           12

Similarly

m0 = dict(fb_ad_network_imp='impressions', fb_ad_network_request='clicks')

def f(tup):
  b = tup.breakdowns[0]['value']
  t = pd.to_datetime(tup.time).floor('D')
  m = m0[tup.metric]
  v = tup.value
  return ((b, t, m), v)

s = pd.Series(dict(map(f, flatten.itertuples())))

s.rename_axis(['platform', 'date', None]).unstack().reset_index()

  platform       date  clicks  impressions
0  android 2018-08-29     132           32
1      ios 2018-08-29      33           12
user_23366540
Related