CODEWITHSUNDEEP
pythonpandas
Jason
Jason

Reputation: 33

Pandas - Pivot, Stack, Unstack?

I'm wondering how to have the keys be the column headers (Result, IP, time)?

Data in CSV looks like this:

"Result":"Success","IP":"0.0.0.0","time":"2018-08-20T12:00:00.000Z"
"Result":"Failure","IP":"1.1.1.1","time":"2018-08-20T12:01:00.000Z"

I'd like to format it like this:

Result        IP        time
Success    0.0.0.0    2018-08-20T12:00:00.000Z
Failure    1.1.1.1    2018-08-20T12:01:00.000Z

My code so far:

import pandas as pd
file = pd.read_csv("path_to.csv", sep='\n', names = ["Result","IP","time"])
df = pd.DataFrame(file)
print(df.head(1))

Upvotes: 2

Views: 75

Answers (2)

harpan
harpan

Reputation: 8641

You need:

import csv
file = pd.read_csv("foo.csv", sep=',', header=None, quoting=csv.QUOTE_ALL, names=["Result", "IP", "time"])
df = pd.DataFrame(file)
df = df.applymap(lambda x: (''.join(str(x).split(":")[1:]))[1:-1])

Output:

    Result       IP                    time
0  Success  0.0.0.0  2018-08-20T120000.000Z
1  Failure  1.1.1.1  2018-08-20T120100.000Z

Upvotes: 2

sacuL
sacuL

Reputation: 51425

Here is a similar approach, also using str.split, but which retains your timezone information by splitting on a regular expression that is more selective than just splitting on : (it splits on : only if preceded by 2 or more letters):

df = pd.read_csv('data.csv', header=None, sep=',', names=['Result', 'IP', 'Time'])

new_df = df.apply(lambda x: x.str.split('[A-Za-z]{2,}:').str[1].str.strip('\"'))

>>> new_df
    Result       IP                      Time
0  Success  0.0.0.0  2018-08-20T12:00:00.000Z
1  Failure  1.1.1.1  2018-08-20T12:01:00.000Z

Upvotes: 1

Related Questions