How to convert a single timeseries in Pandas to multiple series

user_393639 0
Viewed 56

I have a Pandas DataFrame which looks like following

timestamp metric labels value
12345 cpu [region=usa, az=az1] 1
12345 cpu [region=eu, az=az2] 2
12355 cpu [region=usa, az=az1] 3
12355 cpu [region=eu, az=az2] 4

I want to build a timeseries per each label combination. So the result i'm looking for is

timetsamp metric [region=usa, az=az1] [region=eu, az=az2]
12345 cpu 1 2
12355 cpu 3 4

I'm super new to Pandas, and can some one provide me some pointers on how to get there. Thank you.

pandas dataframe python-3.x python
3 Answers

Try .pivot:

print(
    df.pivot(["timestamp", "metric"], "labels", "value")
    .reset_index()
    .rename_axis("", axis=1)
)

Prints:

   timestamp metric  [region=eu, az=az2]  [region=usa, az=az1]
0      12345    cpu                    2                     1
1      12355    cpu                    4                     3

EDIT: If your values in column labels are of type list:

df["labels"] = df["labels"].astype(str)

print(
    df.pivot(["timestamp", "metric"], "labels", "value")
    .reset_index()
    .rename_axis("", axis=1)
)
user_100359850

Try via pivot_table():

out=(df.astype({'labels':'str'}).pivot_table('value',['timestamp','metric'],'labels')
       .rename_axis(columns=None).reset_index())

output of out:

  timestamp     metric  [region=eu, az=az2]     [region=usa, az=az1]
0   12345       cpu         2                         1
1   12355       cpu         4                         3
user_142898920

Try using pivot_table:

df['labels'] = df['labels'].astype(str)
print(df.pivot_table("value", ["timestamp", "metric"], "labels").reset_index())

Output:

labels  timestamp metric  [region=eu, az=az2]  [region=usa, az=az1]
0           12345    cpu                    2                     1
1           12355    cpu                    4                     3
user_87083640
Related