I am new to Python and I am trying to work out a problem with two different sets of data. I have created two data frames, s and c using pandas.read_csv
S
Spread per1 per2 per3 acc
0 4.50% 1.26% 0.35% 0.50% 0.60%
1 4.45% 1.29% 0.35% 0.50% 0.60%
2 4.40% 1.31% 0.35% 0.50% 0.60%
3 4.35% 1.34% 0.35% 0.50% 0.60%
4 4.30% 1.37% 0.35% 0.50% 0.60%
c
Cer OO
0 1.00% 0.65%
1 1.05% 0.68%
2 1.10% 0.71%
3 1.15% 0.73%
4 1.20% 0.76%
So what I would want to do is, for every combination of Cer and OO from c, I would want to populate all the records from s. (Not sure if that is a good way to explain it). Please see below for example
Spread per1 per2 per3 acc Cer OO
0 4.50% 1.26% 0.35% 0.50% 0.60% 1.00% 0.65%
1 4.45% 1.29% 0.35% 0.50% 0.60% 1.00% 0.65%
2 4.40% 1.31% 0.35% 0.50% 0.60% 1.00% 0.65%
3 4.35% 1.34% 0.35% 0.50% 0.60% 1.00% 0.65%
4 4.30% 1.37% 0.35% 0.50% 0.60% 1.00% 0.65%
5 4.50% 1.26% 0.35% 0.50% 0.60% 1.05% 0.68%
6 4.45% 1.29% 0.35% 0.50% 0.60% 1.05% 0.68%
7 4.40% 1.31% 0.35% 0.50% 0.60% 1.05% 0.68%
8 4.35% 1.34% 0.35% 0.50% 0.60% 1.05% 0.68%
9 4.30% 1.37% 0.35% 0.50% 0.60% 1.05% 0.68%
I read that pandas and/or itertools could help me do this kind of analysis? The confusion is when trying to create all of the records from s for each combination of the two fields from dataframe c.
Again, any help with documentation and/or insight would be great. Thank you
Use Cartesian join with merge and a temporary key:
S.assign(key=1).merge(c.assign(key=1), on='key').drop('key', axis=1)
Output:
Spread per1 per2 per3 acc Cer OO
0 4.50% 1.26% 0.35% 0.50% 0.60% 1.00% 0.65%
1 4.50% 1.26% 0.35% 0.50% 0.60% 1.05% 0.68%
2 4.50% 1.26% 0.35% 0.50% 0.60% 1.10% 0.71%
3 4.50% 1.26% 0.35% 0.50% 0.60% 1.15% 0.73%
4 4.50% 1.26% 0.35% 0.50% 0.60% 1.20% 0.76%
5 4.45% 1.29% 0.35% 0.50% 0.60% 1.00% 0.65%
6 4.45% 1.29% 0.35% 0.50% 0.60% 1.05% 0.68%
7 4.45% 1.29% 0.35% 0.50% 0.60% 1.10% 0.71%
8 4.45% 1.29% 0.35% 0.50% 0.60% 1.15% 0.73%
9 4.45% 1.29% 0.35% 0.50% 0.60% 1.20% 0.76%
10 4.40% 1.31% 0.35% 0.50% 0.60% 1.00% 0.65%
11 4.40% 1.31% 0.35% 0.50% 0.60% 1.05% 0.68%
12 4.40% 1.31% 0.35% 0.50% 0.60% 1.10% 0.71%
13 4.40% 1.31% 0.35% 0.50% 0.60% 1.15% 0.73%
14 4.40% 1.31% 0.35% 0.50% 0.60% 1.20% 0.76%
15 4.35% 1.34% 0.35% 0.50% 0.60% 1.00% 0.65%
16 4.35% 1.34% 0.35% 0.50% 0.60% 1.05% 0.68%
17 4.35% 1.34% 0.35% 0.50% 0.60% 1.10% 0.71%
18 4.35% 1.34% 0.35% 0.50% 0.60% 1.15% 0.73%
19 4.35% 1.34% 0.35% 0.50% 0.60% 1.20% 0.76%
20 4.30% 1.37% 0.35% 0.50% 0.60% 1.00% 0.65%
21 4.30% 1.37% 0.35% 0.50% 0.60% 1.05% 0.68%
22 4.30% 1.37% 0.35% 0.50% 0.60% 1.10% 0.71%
23 4.30% 1.37% 0.35% 0.50% 0.60% 1.15% 0.73%
24 4.30% 1.37% 0.35% 0.50% 0.60% 1.20% 0.76%