divide group data base on select columns values?

Viewed 232

df

   ts_code    type  close

  0 861001.TI   1   648.399
  1 861001.TI   20  588.574
  2 861001.TI   30  621.926
  3 861001.TI   60  760.623
  4 861001.TI   90  682.313
  ...   ... ... ...
  8328  885933.TI   5   1083.141
  8329  885934.TI   1   951.493
  8330  885934.TI   5   1011.346
  8331  885935.TI   1   1086.558
  8332  885935.TI   5   1028.449

Goal

ts_code    l5d_close l20d_close …… l90d_close
861001.TI   NaN       1.10          0.95
……           ……       ……            ……

I want to groupby ts_code to calculate the close of type(1)/the close of type(N:5,20,30……). Take 861001.TI for example, l5d_close is nan because there is no value when the type is 5. l20d_close equals 648.399/588.574=1.10, l90d_close equals 648.399/682.313=0.95. And the result is rounded.

Try

df.groupby('ts_code')\
  .pipe(lambda x: x[x.type==1].close/x[x.type==10].close)

Got: KeyError: 'Column not found: False'

The type values is: 1,5,20,30,60,90,180,200

Notice: There is one value of type columns for each ts_code

4 Answers

Use sort_values to make sure type == 1 is the first row per group and extract them with groupby.transform('first'):

df = df.sort_values(['ts_code', 'type'])
close1 = df.groupby('ts_code')['close'].transform('first')
df['close'] = close1 / df['close']

#         ts_code  type     close
# 0     861001.TI     1  1.000000
# 1     861001.TI    20  1.101644
# 2     861001.TI    30  1.042566
# 3     861001.TI    60  0.852458
# ...         ...   ...       ...

Then pivot the type column into column headers:

out = (df.pivot(index='ts_code', columns='type', values='close')
         .drop(columns=1)
         .add_prefix('l')
         .add_suffix('d_close'))

# type       l5d_close  l20d_close  l30d_close  l60d_close  l90d_close
# ts_code
# 861001.TI        NaN    1.101644    1.042566    0.852458    0.950296
# ...              ...         ...         ...         ...         ...

To chain together, assign a ratio column before the pivot:

(df.assign(ratio=df.groupby('ts_code').close.transform('first').div(df.close))
   .pivot(index='ts_code', columns='type', values='ratio')
   .drop(columns=1)
   .add_prefix('l')
   .add_suffix('d_close'))

# type       l5d_close  l20d_close  l30d_close  l60d_close  l90d_close
# ts_code
# 861001.TI        NaN    1.101644    1.042566    0.852458    0.950296
# ...              ...         ...         ...         ...         ...

well, I don't think you need group by here as you aren't really grouping or using any aggregative function.
I think it will be easier to just create a new DF using a function

ts_codes = df.ts_code.unique()
types = [5,20,30,60,90,180,200]
ts_results = []
for ts_code in ts_codes:
    ts_result = [ts_code]
    temp = df.loc[df.tscode == ts_code]
    val_1 = temp.loc[df.type == 1]['close'].iloc[0] # to get the actual value 
    for type in types:
        val = temp.loc[df.type == type]
        if len(val) > 0:
            ts_result.append(val_1/val)
        else:
            ts_result.append(None)
    ts_results.append(ts_result)
results_df = pd.DataFrame(ts_results,coluns=['ts_code','l5d_close', 'l20d_close' …… 'l90d_close']

I didn't run the code to get the results as you didn't provide an easy way to generate your data hope this help

and sorry but sometimes the easy solution is the best (I would have move it to a function)

You can do this with pandas.DataFrame.pivot_table() (docs). As long as there's some data of each type, that column will be created.

pivoted = (
    df.pivot_table(values=["close"], index="ts_code", columns="type")
    # get rid of the first MultiIndex level
    .droplevel(0, axis=1)
    # divide type == 1 column values by every other column
    .pipe(lambda f: f[[1]].values / f.iloc[:, 1:])
    .round(2)
)

# format column names
pivoted.columns = "l" + pivoted.columns.astype(str) + "d_close"
pivoted

This returns:

type       l5d_close  l20d_close  l30d_close  l60d_close  l90d_close
ts_code                                                             
861001.TI        NaN    1.101644    1.042566    0.852458    0.950296
885933.TI        NaN         NaN         NaN         NaN         NaN
885934.TI   0.940818         NaN         NaN         NaN         NaN
885935.TI   1.056502         NaN         NaN         NaN         NaN
def get_sum_by_type(ts_code,type):
    num = df.query(f"ts_code=='{ts_code}' and type==1")['close'].sum()
    den = df.query(f"ts_code=='{ts_code}' and type=={type}")['close'].sum()
    return 

types = [5,20,30,60,90,180,200]
results = []
for tscode, type in zip(df.ts_code.uniques(), types):
    template = dict(ts_code=tscode)
    template.update({
        f"l{type_}d_close": get_sum_by_type(tscode, type ) for type_ in types
    })
    results.append(template)
goal = pd.DataFrame(results)

The only thing I see in the code is that you will need to replace the zeros for nan, because when there are no type_1 for a given ts_0, the function get_sum_by_type will return 0 due the num will be 0. You can manage the 0/0 operations inside get_sum_by_type if there are no close for a given ts_code and type

Related