Pandas Dataframe - Group by column value and lookup values from other columns
Crovish
I have this dataframe :
label | time_0 | value_0 | time_1 | value_1 | time_2 | value_2
value_0 column corresponds to time_0, value_1 to time_1...
time_i is unique, which means for all rows time_0 has the same value, same for time_1, time_2..
I want to achieve 2 things :
1- first group_by label, then time_i
The result should look like :
Label0 time_0(0) value_0(0)
value_0(1)
...
time_1(0) value_1(0)
value_1(1)
..
...
Label1 time_0(0) value_0(0)
value_0(1)
...
time_1(0) value_1(0)
value_1(1)
..
...
Label2 time_0(0) value_0(0)
value_0(1)
...
time_1(0) value_1(0)
value_1(1)
..
...
2- first group_by label, then time_i and sum all values_i
The result should look like
Label1 time_0(0) sum(value_0)
time_1(1) sum(value_1)
time_2(2) sum(value_2)
Label2 time_0(0) sum(value_0)
time_1(1) sum(value_1)
time_2(2) sum(value_2)
...
I tried different combinations with pd.merge, group_by, but without a success
here is an example with values 
jezrael
Use wide_to_long with aggregate sum:
df = (pd.wide_to_long(df.reset_index(),
stubnames=['value','time'],
i=['index','Label'], j='tmp', sep='_')
.groupby(['Label','time'])['value']
.sum()
.reset_index())
print (df)
Label time value
0 EUR/CHF 2020-12-04 -51.260248
1 EUR/CHF 2020-12-10 98.202053
2 USD/CHF 2020-12-04 0.134488
3 USD/CHF 2020-12-10 4.510396
4 USD/NOK 2020-12-04 0.395785
5 USD/NOK 2020-12-10 -0.801768
EDIT:
Use:
print (df)
Label veg__0_time veg__0_value veg__1_time veg__1_value
0 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
1 USD/CHF 2020-12-04 -0.439058 2020-12-10 1.392752
2 USD/CHF 2020-12-04 -0.012020 2020-12-10 0.043742
3 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
4 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
5 USD/CHF 2020-12-04 -0.525791 2020-12-10 1.273146
6 USD/CHF 2020-12-04 1.306578 2020-12-10 1.115313
7 USD/CHF 2020-12-04 -0.195221 2020-12-10 0.685444
8 USD/NOK 2020-12-04 0.395785 2020-12-10 -0.801768
9 EUR/CHF 2020-12-04 -29.385792 2020-12-10 45.951600
10 EUR/CHF 2020-12-04 -21.874456 2020-12-10 52.250453
df = df.set_index('Label')
df.columns = df.columns.str.split('_', expand=True).droplevel([0,1])
print (df)
0 1
time value time value
Label
USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
USD/CHF 2020-12-04 -0.439058 2020-12-10 1.392752
USD/CHF 2020-12-04 -0.012020 2020-12-10 0.043742
USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
USD/CHF 2020-12-04 -0.525791 2020-12-10 1.273146
USD/CHF 2020-12-04 1.306578 2020-12-10 1.115313
USD/CHF 2020-12-04 -0.195221 2020-12-10 0.685444
USD/NOK 2020-12-04 0.395785 2020-12-10 -0.801768
EUR/CHF 2020-12-04 -29.385792 2020-12-10 45.951600
EUR/CHF 2020-12-04 -21.874456 2020-12-10 52.250453
df = df.stack(0).groupby(['Label','time'])['value'].sum().reset_index()
print (df)
Label time value
0 EUR/CHF 2020-12-04 -51.260248
1 EUR/CHF 2020-12-10 98.202053
2 USD/CHF 2020-12-04 0.134488
3 USD/CHF 2020-12-10 4.510396
4 USD/NOK 2020-12-04 0.395785
5 USD/NOK 2020-12-10 -0.801768
EDIT1: Solution with rename:
def f(x):
splitted = x.split('_')
if len(splitted) > 1:
return f'{splitted[-1]}_{splitted[-2]}'
else:
return x
df = df.rename(columns=f)
print (df)
Label time_0 value_0 time_1 value_1
0 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
1 USD/CHF 2020-12-04 -0.439058 2020-12-10 1.392752
2 USD/CHF 2020-12-04 -0.012020 2020-12-10 0.043742
3 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
4 USD/CHF 2020-12-04 0.000000 2020-12-10 0.000000
5 USD/CHF 2020-12-04 -0.525791 2020-12-10 1.273146
6 USD/CHF 2020-12-04 1.306578 2020-12-10 1.115313
7 USD/CHF 2020-12-04 -0.195221 2020-12-10 0.685444
8 USD/NOK 2020-12-04 0.395785 2020-12-10 -0.801768
9 EUR/CHF 2020-12-04 -29.385792 2020-12-10 45.951600
10 EUR/CHF 2020-12-04 -21.874456 2020-12-10 52.250453
df = (pd.wide_to_long(df.reset_index(),
stubnames=['value','time'],
i=['index','Label'], j='tmp', sep='_')
.groupby(['Label','time'])['value']
.sum()
.reset_index())
print (df)
Label time value
0 EUR/CHF 2020-12-04 -51.260248
1 EUR/CHF 2020-12-10 98.202053
2 USD/CHF 2020-12-04 0.134488
3 USD/CHF 2020-12-10 4.510396
4 USD/NOK 2020-12-04 0.395785
5 USD/NOK 2020-12-10 -0.801768
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Insert data in different sheets in Excel
- Next: Force match on filename*= over filename= in regular expression
Related
TOP Ranking
- 1
pump.io port in URL
- 2
How to import an asset in swift using Bundle.main.path() in a react-native native module
- 3
Failed to listen on localhost:8000 (reason: Cannot assign requested address)
- 4
Inner Loop design for webscrapping
- 5
Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background
- 6
mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)
- 7
Removed zsh, but forgot to change shell back to bash, and now Ubuntu crashes (wsl)
- 8
ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements
- 9
How to run blender on webserver?
- 10
Resetting Value of <input type="time"> in Firefox
- 11
Converting a class method to a property with a backing field
- 12
Ambiguous use of 'init' with CFStringTransform and Swift 3
- 13
Execute ./script.sh with a crontab
- 14
How to set tab order for array of cluster,where cluster elements have different data types in LabVIEW?
- 15
How to pass data to the ng2-bs3-modal?
- 16
Retrieve Element Tag Value XML Using Bash
- 17
Spring Boot JPA PostgreSQL Web App - Internal Authentication Error
- 18
SQL Server : need add a dot before two last character
- 19
Making Array From Page Elements in jQuery
- 20
Laravel's ORM sync with timestamps doesn't update timestamps
- 21
Do animations stop css changes after animation completion?
Comments