Btukfyl

I'm trying to aggregate data in a dataframe by specific columns. When I use a dataframe constructor it works:

df = pd.DataFrame([

        ["Firewall-1","outside","tcp","4.4.4.4",53,"1.1.1.1",1025,"outbound","allowed","",2], 

        ["Firewall-1","outside","tcp","4.4.4.4",53,"1.1.1.1",1026,"outbound","allowed","",2], 

        ["Firewall-1","outside","tcp","4.4.4.4",22,"1.1.1.1",1028,"outbound","allowed","",2], 

        ["Firewall-1","outside","tcp","3.3.3.3",22,"2.2.2.2",2200,"outbound", "allowed","",2]

    ], 

    columns=["dvc","src_interface","transport","src_ip","src_port","dest_ip","dest_port","direction", "action", "cause", "count"])



index_cols = df.columns.tolist()

index_cols.remove("dest_port") 

df = df.groupby(index_cols)["dest_port"].apply(list)

df = df.reset_index()

DATAFRAME

          dvc src_interface transport   src_ip  src_port  dest_ip  dest_port direction   action cause  count

0  Firewall-1       outside       tcp  4.4.4.4        53  1.1.1.1       1025  outbound  allowed            2

1  Firewall-1       outside       tcp  4.4.4.4        53  1.1.1.1       1026  outbound  allowed            2

2  Firewall-1       outside       tcp  4.4.4.4        22  1.1.1.1       1028  outbound  allowed            2

3  Firewall-1       outside       tcp  4.4.4.4        22  1.1.1.1       1029  outbound  allowed            2

4  Firewall-1       outside       tcp  3.3.3.3        22  2.2.2.2       2200  outbound  allowed            2

OUTPUT

   dvc         src_interface  transport  src_ip   src_port  dest_ip  direction  action   cause  count

    Firewall-1  outside        tcp        3.3.3.3  22        2.2.2.2  outbound   allowed         2              [2200]

                                          4.4.4.4  22        1.1.1.1  outbound   allowed         2        [1028, 1029]

                                                   53        1.1.1.1  outbound   allowed         2        [1025, 1026]

The issue is when I attempt to import the data from CSV:

fwdata = pd.concat([pd.read_csv(f) for f in glob.glob('*.csv')], ignore_index = True)

df = pd.DataFrame(fwdata)



index_cols = df.columns.tolist()

index_cols.remove("dest_port")

df = df.groupby(index_cols)["dest_port"].apply(list)

df.reset_index()

print(df.head(10))

DATAFRAME
Same as above

OUTPUT

Series(, Name: dest_port, dtype: float64) 

The CSV file has the exact same data as the constructor above but it appears to be treated differently. Any help would be appreciated. Thanks in advance!

CSV

dvc,"src_interface",transport,"src_ip","src_port","dest_ip","dest_port",direction,action,cause,count "Firewall-1",outside,tcp,"4.4.4.4",53,"1.1.1.1",1025,outbound,allowed,"",2 "Firewall-1",outside,tcp,"4.4.4.4",53,"1.1.1.1",1026,outbound,allowed,"",2 "Firewall-1",outside,tcp,"4.4.4.4",22,"1.1.1.1",1028,outbound,allowed,"",2 "Firewall-1",outside,tcp,"3.3.3.3",22,"2.2.2.2",2200,outbound,allowed,"",2

The issue was the empty data in the 'cause' column. Pandas hates this. You can solve this issue with either of the below solutions.

Dropping the column:

df.drop(columns=['column_name'], inplace=True)

Padding out the column with data:

df.column_name.fillna('', inplace=True)

(For these examples column_name = 'cause')