Condensing columns in a Pandas Dataframe

user_14538452 0
Viewed 99

I'm working with the data from the Veris project through VerisPy and having some trouble reformatting it for use in other applications.

VerisPy outputs a dataframe containing parsed event information from the JSON files submitted to Veris. Each row in the dataframe corresponds to an event, with details about how/when it occurred. The problem is that it ends up with thousands of columns with boolean flags.

So each event row might have columns like:

event_id action.malware action.hacking action.misuse actor.external.competitor actor.external.crime actor.internal.employee actor.internal.customer target
1 True False False False False True False Microsoft
2 False True False True False False False Bank of America

I'd like to collapse/combine these and use the column values as the actual data. In the example above, that might end us as:

event_id Action Actor target
1 Malware Internal - Employee Microsoft
2 Hacking External - Competitor Bank of America

I'm new to Pandas but have tried a fair amount of things from the documentation with mixed results. Melt seemed promising, but I'm unsure how to get the column names into the actual row-level data. Does anyone have pointers?

pandas dataframe list dictionary python
2 Answers

One Way:

  1. Set ['event_id', 'target'] as index.
  2. split and expand columns to create the hierarchical columns (We'll need this in the dot product).
  3. extract the level 1 values from the hierarchical columns and do the dot product with the dataframe.
  4. Do some string manipulations and rename the columns (I've added 2 ways to rename the columns) to get the desired output.
df1 = df.set_index(['event_id', 'target'])
df1.columns = df1.columns.str.split('.', expand=True, n=1)
df = (
    df1.dot(df1.columns.get_level_values(1) + ',')
    .str.strip(',')
    .str.split(',', expand=True)
    .rename(columns={0: 'Action', 1: 'Actor'})
    .reset_index()
)

OUTPUT:

   event_id           target   Action                Actor
0         1        Microsoft  malware    internal.employee
1         2  Bank of America  hacking  external.competitor
NOTE:

Instead of renaming columns via rename you can also use level=0 column values like this -

df1 = df.set_index(['event_id', 'target'])
df1.columns = df1.columns.str.split('.', expand=True, n=1)
df = (
    df1.dot(df1.columns.get_level_values(1) + ',')
    .str.strip(',')
    .str.split(',',expand=True)
)
df.columns = dict.fromkeys(df1.columns.get_level_values(0)).keys()
df = df.reset_index()

One more alternative is to use mul:

df1 = df.set_index(['event_id', 'target'])
df1.columns = df1.columns.str.split('.', expand=True, n=1)
df = (
    df1.mul(df1.columns.get_level_values(1))
    .replace('', np.NAN)
    .droplevel(1, axis =1)
    .stack()
    .unstack()
)

NOTE:

In case you've a multiple values for actor and agent for 1 target you can use pivot_table instead of stack/unstack:

df1 = df.set_index(['event_id', 'target'])
df1.columns = df1.columns.str.split('.', expand=True, n=1)
(
    df1.mul(df1.columns.get_level_values(1))
    .replace('', np.NAN)
    .droplevel(1, axis =1)
    .stack()
    .reset_index()
    .pivot_table(index = ['event_id', 'target'], columns = 'level_2' , values = 0, aggfunc = ', '.join)
)
user_154380330

You can flip the code from wide to long, filter for only rows where True is, make some modifications to get your output.

 # flip from wide to long
(pd.wide_to_long(df,
                 stubnames=['action','actor'], 
                 i=['event_id','target'],
                 j='subs', 
                 sep='.', 
                 suffix='.+')
  .loc[lambda df: df.any(1)] # keeps only rows that have `True`
   # if any row has `True`, 
   # replace it with values from `subs` index
  .where(lambda df: df.isna(), 
         lambda df: df.index.get_level_values('subs'))
  .fillna('')
  .droplevel('subs') # served its purpose, let it go
  # use the groupby with the agg to reduce to single rows
  .groupby(['event_id', 'target'])
  .agg("".join)
  .reset_index()
)
: 
   event_id           target   action                actor
0         1        Microsoft  malware    internal.employee
1         2  Bank of America  hacking  external.competitor
user_71757130
Related