Explore all Retail related open source software, libraries, packages, source code, cloud functions and APIs.

The retail industry is a sector of the economy that is comprised of individuals and companies engaged in the selling of finished products to end user consumers. The Methods of Retailing in the Industry are:

Brick-and-Mortar Store Retailers – Those engaged in the sale of products from physical locations which warehouse and display merchandise with the intent of attracting customers to make purchases on site.

Non-Store Retailers – Those engaged in the sale of products using marketing methods which do not include a physical location. Examples of non-store retailing include: Mobile-only retailing (m-commerce), Internet-only e-commerce, Catalogue Sales, Vending Machines, Multi-Level Marketing.

These software components cover functions across Department Stores and Specialty, Food and Grocery, Restaurant areas.

Popular New Releases in Retail

vuex

v4.0.2

airflow

Apache Airflow 2.2.5

vuepress

v1.9.2

prophet

v1.0

saleor

3.0.0-b.19

Popular Libraries in Retail

vuex

by vuejs doticonjavascriptdoticon

star image 27468 doticonMIT

🗃️ Centralized State Management for Vue.js.

airflow

by apache doticonpythondoticon

star image 25547 doticonApache-2.0

Apache Airflow - A platform to programmatically author, schedule, and monitor workflows

ccxt

by ccxt doticonjavascriptdoticon

star image 24082 doticonMIT

A JavaScript / Python / PHP cryptocurrency trading API with support for more than 100 bitcoin/altcoin exchanges

vuepress

by vuejs doticonjavascriptdoticon

star image 20197 doticonMIT

📝 Minimalistic Vue-powered static site generator

Android-Universal-Image-Loader

by nostra13 doticonjavadoticon

star image 16815 doticonApache-2.0

Powerful and flexible library for loading, caching and displaying images on Android.

prophet

by facebook doticonpythondoticon

star image 14280 doticonMIT

Tool for producing high quality forecasts for time series data that has multiple seasonality with linear or non-linear growth.

saleor

by mirumee doticonpythondoticon

star image 13983 doticonNOASSERTION

A modular, high performance, headless e-commerce platform built with Python, GraphQL, Django, and React.

reaction

by reactioncommerce doticonjavascriptdoticon

star image 11662 doticonGPL-3.0

Mailchimp Open Commerce is an API-first, headless commerce platform built using Node.js, React, GraphQL. Deployed via Docker and Kubernetes.

liquid

by Shopify doticonrubydoticon

star image 9461 doticonMIT

Liquid markup language. Safe, customer facing template language for flexible web apps.

Trending New libraries in Retail

Android-Mod-Menu

by LGLTeam doticonc++doticon

star image 332 doticonGPL-3.0

Floating mod menu for Android

NexoPOS-4x

by Blair2004 doticonphpdoticon

star image 261 doticonNOASSERTION

The base version of NexoPOS 4.x, which is a web-Based Point Of Sale (POS) System build with Laravel, TailwindCSS, and Vue.Js.

VarifocalNet

by hyz-xmaster doticonpythondoticon

star image 208 doticonApache-2.0

VarifocalNet: An IoU-aware Dense Object Detector

e-commerce-app-react-native

by anhquan291 doticonjavascriptdoticon

star image 169 doticon

E-commerce App UI. React native, Expo managed flow, React navigation v5, Notification.

KiU-Net-pytorch

by jeya-maria-jose doticonpythondoticon

star image 160 doticonMIT

Official Pytorch Code of KiU-Net for Image Segmentation - MICCAI 2020 (Oral)

amazon-dynamodb-labs

by aws-samples doticonpythondoticon

star image 101 doticonApache-2.0

Hands on workshop and design scenarios for Amazon DynamoDB

react-native-stripe-payments

by Fitpassu doticonjavadoticon

star image 94 doticonMIT

Lightweight, easy to integrate and use React native library for Stripe payments (using Payment Intents) compliant with SCA (strong customer authentication)

lakasir

by lakasir doticonphpdoticon

star image 91 doticonMIT

Point Of Sale system, free and open source

investbook

by spacious-team doticonjavadoticon

star image 87 doticonAGPL-3.0

Оценка эффективности инвестиций с учетом комиссий, налогов (удержанных и ожидающихся), дивидендов и купонов.

Top Authors in Retail

1

mageplaza

28 Libraries

star icon376

2

meanbee

10 Libraries

star icon110

3

mage2pro

9 Libraries

star icon50

4

adobe

8 Libraries

star icon132

5

snipcart

7 Libraries

star icon100

6

launchdarkly

7 Libraries

star icon175

7

aws-samples

7 Libraries

star icon291

8

VirtoCommerce

7 Libraries

star icon86

9

magepow

7 Libraries

star icon63

10

AvePoint Inc.

7 Libraries

star icon0

1

28 Libraries

star icon376

2

10 Libraries

star icon110

3

9 Libraries

star icon50

4

8 Libraries

star icon132

5

7 Libraries

star icon100

6

7 Libraries

star icon175

7

7 Libraries

star icon291

8

7 Libraries

star icon86

9

7 Libraries

star icon63

10

7 Libraries

star icon0

Trending Kits in Retail

Java E-Commerce Libraries help build e-commerce websites. It provides various frameworks, tools, and utilities for developing online shopping websites. 


There are many Java E-Commerce libraries available in the market, which enable you to write your own e-commerce website in easy steps. Java E-Commerce Libraries like shopizer, micro-ecommerce, and keyist-ecommerce are also used for developing an eCommerce website. These libraries are used in e-commerce applications since they provide a platform for making payment gateways, shopping carts, and other functionalities of an e-commerce website.


Shopizer is the most popular Java e-commerce library that allows you to create a customized online store without any coding. It comes with integrated payment gateways, shipping plugins, and many other features, which make it one of the best options for small businesses looking to start an online store. Keyist-Ecommerce is an open-source Java library that allows you to build an online store with minimal effort and time. The library supports multiple languages, including English, Chinese, Japanese and Korean. It also offers integrated payment gateways, shipping plugins, and other features that make it easy for developers to build custom websites without having to write any code themselves. Micro-eCommerce is an open-source Java library that allows you to build an online store without any coding knowledge required by developers. It has over 350 features built in along with integrated payment gateways, shipping plugins, and many other features that make it easy for developers to create custom websites. Some of the most popular Java E-Commerce Libraries among developers are given below

shopizer:  

  • Shopizer is an open-source e-commerce software platform designed for building.  
  • It is a flexible and customizable solution for businesses.  
  • Shopizer is released under the Apache 2.0 license, making it open-source and free to use.  

eCommerce-order-service:  

  • eCommerce-order-service is a Java library typically used in websites and e-commerce applications.  
  • It has a Permissive License, and it has a medium support.  
  • It handles various aspects related to customer orders. 

micro-ecommerce:  

  • Micro-ecommerce might refer to the use of microservices architecture in the context.  
  • This architecture aims to improve scalability, maintainability, and agility in the development.  
  • Micro-ecommerce helps describe smaller or lighter e-commerce businesses.  

Keyist-Ecommerce:  

  • Keyist-Ecommerce is a term used within a specific context or community.  
  • It may be beneficial to seek information from sources associated with that context.  
  • This will provide the most accurate and up-to-date information about its features and functionalities.  

e-commerce-microservice:  

  • e-commerce microservice refers to the application of microservices architecture in the development.  
  • Microservices architecture is an approach where a large and complex application decomposes.  
  • Microservice in the e-commerce system handles a specific business capability.  

ddd-example-ecommerce:  

  • DDD is an approach to software development. It emphasizes understanding the business domain and modeling it in code.  
  • DDD often involves defining bounded contexts to encapsulate specific business domains.  
  • DDD distinguishes between entities and value objects. Entities have an identity and are mutable.  

Android-E-Commerce-Shopping-Application:  

  • Android-E-Commerce-Shopping-Application is a Java library. It is typically used in Retail, Security, Authentication, and Firebase applications.  
  • Android-E-Commerce-Shopping-Application has no bugs or vulnerabilities.  
  • It has a Permissive License, and it has low support.  

Ecommerce-Morningmist-Android:  

  • Ecommerce-Morningmist-Android is a Java library that is typically used in retail websites.  
  • Ecommerce-Morningmist-Android has no vulnerabilities reported, and its dependent libraries have no vulnerabilities reported. 

FAQ

1. What are Java E-commerce Libraries?  

Java E-commerce Libraries are sets of pre-built, reusable code components and functionalities. These libraries often include modules for product management, cart handling, and payment processing.  

  

2. Why use Java for E-commerce Development?  

Java is popular for its portability, scalability, and robustness. It's platform-independent, making it suitable for building enterprise-level applications. Java has a vast ecosystem and community support. It also contributes to its popularity in e-commerce.  

  

3. Which Java E-commerce Libraries are widely used?  

Some widely used Java E-commerce Libraries include:  

  1. Broadleaf Commerce: An open-source, customizable framework for building e-commerce solutions.  
  2. Hybris: A robust and scalable e-commerce platform.  
  3. Apache OFBiz (Open for Business): An open-source framework for building enterprise automation applications.  

  

4.  How can I integrate Payment Gateways with Java E-commerce Libraries?  

Java E-commerce Libraries often provide APIs or modules for integrating with popular payment. The Developers follow the documentation provided by the library in a specific way.  

  

5. Are there Java E-commerce Libraries suitable for small businesses?  

Yes, some Java E-commerce Libraries cater to the needs of small businesses. The Smaller libraries or frameworks may also be suitable for lightweight e-commerce requirements.  

Data scraping is used to gather valuable insights, conduct market research, monitor competitor prices, or build price comparison websites. 


In this digital world, the internet has changed the way we do our shopping. Online shopping is the act of purchasing products or services over the internet. It gathers all the information that will make all your internal and external recommendation processes, sales, and distribution based on the chronology of what you have been buying and bought earlier. Some of the libraries below can help you to achieve this as DIY.

Data Collection

Ecommerce Websites

Ecommerce Services

JavaScript has the accessibility and ideal programming model to build product showcase libraries that can guide users through using an app or viewing a product. Developers can choose elements to focus on and add effects or animation to them on the JS platform as well. These libraries are especially useful for an eCommerce web app and enable you to implement elements like carousels, product gallery, zoomable views, and lots more. You can also add step-by-step functionalities using these libraries to build fully responsive user interfaces and control each element on the app effectively. Adding product showcases, animation, and immersive features makes the website more interactive and user-friendly for viewers and end-users. Below are our 32 best JavaScript products that showcase open source libraries in 2021 that enable data visualization and effective app building like Swiper - modern mobile touch slider with hardware accelerated transitions; slick - the last carousel you'll ever need; react-native-snap-carousel - carousel component for React Native featuring previews.

Health care industries around the globe are growing day by day. Equally pharmacy industry is rising to the same extent. The pharmacy system stores data, systemizes, and controls the use of the medication process with the pharmacies. The pharmacy management system helps the pharmacist maintain their stock and choose the right medicine by using the computer program. The pharmacist makes use of this system to control the pharmacy reliably. Patients sometimes prefer to visit the pharmacy instead of a doctor for minor illnesses, and the system enables the pharmacist to prescribe over-the-counter drugs for these ailments.

Did you know that the value of Online Returns is more than the US Defense Budget? CNBC reported that in 2021 the US online returns were estimated to be at $761 Billion, while the US Defense budget was $741 Billion. Now that you know the scale of this problem or opportunity, it would be surprising to note that few platforms serve this industry. The returns and the used market has been able to soften the supply chain constraints in the recent past in categories like electronics. Today about two-thirds of the returns are processed through reselling, liquidation and donations. About a third ends up in landfills, or its better-sounding alternative of “Energy Recovery,” which sadly means incineration! Given the focus on sustainability, it becomes more critical to ensure that returns are processed through the circular economy more efficiently. This is the idea that we should be reusing and recycling existing materials rather than creating new ones, so that we don't consume our natural resources faster than they can replenish themselves. One important place to implement this concept is in online returns management. The e-commerce industry has grown steadily over the past two decades, and it's projected to keep growing by leaps and bounds over the next few years. As these companies become more efficient at scaling their processes, online shopping will increasingly become the norm—and that means we will have a lot more waste from packaging and discarded returns. In addition, as consumers become more sophisticated in their understanding of sustainable practices, they are becoming less tolerant of wasteful packaging and discarded returns. In fact, many return customers have said that they would not shop with a brand again if it used too much packaging for their order. It's important for companies with big e-commerce operations to find ways to reduce this waste and optimize returns. There are opportunities across the value chain such as better product discovery to minimize incorrect purchases and returns, better demand planning to optimize overstock, fraud management in returns, liquidation marketplaces, and becoming a YouTube influencer unboxing mystery return pallets! For more information on the CNBC report, visit: https://www.cnbc.com/video/2022/02/19/inside-liquidity-services-and-the-644-billion-liquidation-market.html For ideas and libraries to get you jumpstarted on the circular economy use cases use the below libraries.

Supply Chain Optimization Libraries

These libraries help you avoid waste through planning, tracking and optimization solutions.

Simple Retail Storefronts and Auctions

These libraries help you build storefronts and auctions for return, used products returning them to the circular economy.

Trending Discussions on Retail

Stepper functionality using React

Merging data from a separate .csv file using Pandas

How do I show my closest 3 competitors on a map?

Multiple TypeScript discriminations based on different properties

Grabbing certain data from one object to another object

localizing rows from a dataframe

Fetching data in loop next js

how to order json data header while downloading it into xls format using JavaScript

json_normalize a nested database

joining two dataframes on matching values of two common columns R

QUESTION

Stepper functionality using React

Asked 2022-Mar-09 at 16:24

I'm trying to replicate this stepper like functionality using react.

https://www.commbank.com.au/retail/complaints-compliments-form?ei=CTA-MakeComplaint

Below is my stackblitz, How can I achieve this functionality without using any 3rd Party plugins.

https://stackblitz.com/edit/react-ts-9cfjs3

ANSWER

Answered 2022-Mar-09 at 16:24

I set up the basics of the UI on codesandbox.

The main part of how this works is via scrollIntoView using a reference to the div element on each Step. It's important to note that this will work on every modern browser but safari for the smooth scrolling.

Obviously for the actual form parts and moving data around, all of that will still need to be implemented, but this demonstrates nearly all of the navigation/scrolling behaviors as your example.

For reference, here's the main code:

1import { useEffect, useRef, useState } from "react";
2import A from "./A";
3import B from "./B";
4import C from "./C";
5import D from "./D";
6import "./styles.css";
7
8const steps = [A, B, C, D];
9export default function App() {
10  const [step, setStep] = useState(0);
11  /** Set up a ref that refers to an array, this will be used to hold
12   * a reference to each step
13   */
14  const refs = useRef<(HTMLDivElement | null)[]>([]);
15  /** Whenever the step changes, scroll it into view! useEffect needed to wait
16   * until the new component is rendered so that the ref will properly exist
17   */
18  useEffect(() => {
19    refs.current[step]?.scrollIntoView({ behavior: "smooth" });
20  }, [step]);
21
22  return (
23    <div className="App">
24      {steps
25        .filter((_, index) => index <= step)
26        .map((Step, index) => (
27          <Step
28            key={index}
29            /** using `domRef` here to avoid having to set up forwardRef.
30             * Same behavior regardless, but with less hassle as it's an
31             * ordianry prop.
32             */
33            domRef={(ref) => (refs.current[index] = ref)}
34            /** both prev/next handlers for scrolling into view */
35            toPrev={() => {
36              refs.current[index - 1]?.scrollIntoView({ behavior: "smooth" });
37            }}
38            toNext={() => {
39              if (step === index + 1) {
40                refs.current[index + 1]?.scrollIntoView({ behavior: "smooth" });
41              }
42              /** This mimics behavior in the reference. Clicking next sets the next step
43               */
44              setStep(index + 1);
45            }}
46            /** an override to enable reseting the steps as needed in other ways.
47             * I.e. changing the initial radio resets to the 0th step
48             */
49            setStep={setStep}
50            step={index}
51          />
52        ))}
53    </div>
54  );
55}
56
57
58

And component A

1import { useEffect, useRef, useState } from "react";
2import A from "./A";
3import B from "./B";
4import C from "./C";
5import D from "./D";
6import "./styles.css";
7
8const steps = [A, B, C, D];
9export default function App() {
10  const [step, setStep] = useState(0);
11  /** Set up a ref that refers to an array, this will be used to hold
12   * a reference to each step
13   */
14  const refs = useRef<(HTMLDivElement | null)[]>([]);
15  /** Whenever the step changes, scroll it into view! useEffect needed to wait
16   * until the new component is rendered so that the ref will properly exist
17   */
18  useEffect(() => {
19    refs.current[step]?.scrollIntoView({ behavior: "smooth" });
20  }, [step]);
21
22  return (
23    <div className="App">
24      {steps
25        .filter((_, index) => index <= step)
26        .map((Step, index) => (
27          <Step
28            key={index}
29            /** using `domRef` here to avoid having to set up forwardRef.
30             * Same behavior regardless, but with less hassle as it's an
31             * ordianry prop.
32             */
33            domRef={(ref) => (refs.current[index] = ref)}
34            /** both prev/next handlers for scrolling into view */
35            toPrev={() => {
36              refs.current[index - 1]?.scrollIntoView({ behavior: "smooth" });
37            }}
38            toNext={() => {
39              if (step === index + 1) {
40                refs.current[index + 1]?.scrollIntoView({ behavior: "smooth" });
41              }
42              /** This mimics behavior in the reference. Clicking next sets the next step
43               */
44              setStep(index + 1);
45            }}
46            /** an override to enable reseting the steps as needed in other ways.
47             * I.e. changing the initial radio resets to the 0th step
48             */
49            setStep={setStep}
50            step={index}
51          />
52        ))}
53    </div>
54  );
55}
56
57
58import React, { useEffect, useState } from "react";
59import { Step } from "./utils";
60
61interface AProps extends Step {}
62
63function A(props: AProps) {
64  const [value, setValue] = useState("");
65  const values = [
66    { label: "Complaint", value: "complaint" },
67    { label: "Compliment", value: "compliment" }
68  ];
69  const { step, setStep } = props;
70  useEffect(() => {
71    setStep(step);
72  }, [setStep, step, value]);
73  return (
74    <div className="step" ref={props.domRef}>
75      <h1>Component A</h1>
76      <div>
77        {values.map((option) => (
78          <label key={option.value}>
79            {option.label}
80            <input
81              onChange={(ev) => setValue(ev.target.value)}
82              type="radio"
83              name="type"
84              value={option.value}
85            />
86          </label>
87        ))}
88      </div>
89      <button
90        className="next"
91        onClick={() => {
92          if (value) {
93            props.toNext();
94          }
95        }}
96      >
97        NEXT
98      </button>
99    </div>
100  );
101}
102
103export default A;
104

Source https://stackoverflow.com/questions/71400009

QUESTION

Merging data from a separate .csv file using Pandas

Asked 2022-Mar-02 at 08:26

I want to create two new columns in job_transitions_sample.csv and add the wage data from wage_data_sample.csv for both Title 1 and Title 2:

job_transitions_sample.csv:

1                     Title 1                    Title 2  Count
20   administrative assistant             office manager     20
31                 accountant                    cashier      1
42                 accountant          financial analyst     22
54                 accountant          senior accountant     23
66           accounting clerk                 bookkeeper     11
77     accounts payable clerk  accounts receivable clerk      8
88   administrative assistant           accounting clerk      8
99   administrative assistant       administrative clerk     12
10...
11

wage_data_sample.csv

1                     Title 1                    Title 2  Count
20   administrative assistant             office manager     20
31                 accountant                    cashier      1
42                 accountant          financial analyst     22
54                 accountant          senior accountant     23
66           accounting clerk                 bookkeeper     11
77     accounts payable clerk  accounts receivable clerk      8
88   administrative assistant           accounting clerk      8
99   administrative assistant       administrative clerk     12
10...
11                      title   wage
120                   cashier  17.00
131           sandwich artist  18.50
142                dishwasher  20.00
153                babysitter  20.00
164                   barista  21.50
175               housekeeper  21.50
186    retail sales associate  23.00
197                 bartender  23.50
208                   cleaner  23.50
219                 line cook  23.50
2210               pizza cook  23.50
23...
24

I want the end result to look like this:

1                     Title 1                    Title 2  Count
20   administrative assistant             office manager     20
31                 accountant                    cashier      1
42                 accountant          financial analyst     22
54                 accountant          senior accountant     23
66           accounting clerk                 bookkeeper     11
77     accounts payable clerk  accounts receivable clerk      8
88   administrative assistant           accounting clerk      8
99   administrative assistant       administrative clerk     12
10...
11                      title   wage
120                   cashier  17.00
131           sandwich artist  18.50
142                dishwasher  20.00
153                babysitter  20.00
164                   barista  21.50
175               housekeeper  21.50
186    retail sales associate  23.00
197                 bartender  23.50
208                   cleaner  23.50
219                 line cook  23.50
2210               pizza cook  23.50
23...
24                      Title 1             Title 2  Count  Wage of Title 1  Wage of Title 2
250    administrative assistant      office manager     20              NaN              NaN
261                  accountant             cashier      1              NaN              NaN
272                  accountant   financial analyst     22              NaN              NaN
28...
29

I'm thinking of using dictionaries then try to iterate every column but is there a more elegant built in solution? This is my code so far:

1                     Title 1                    Title 2  Count
20   administrative assistant             office manager     20
31                 accountant                    cashier      1
42                 accountant          financial analyst     22
54                 accountant          senior accountant     23
66           accounting clerk                 bookkeeper     11
77     accounts payable clerk  accounts receivable clerk      8
88   administrative assistant           accounting clerk      8
99   administrative assistant       administrative clerk     12
10...
11                      title   wage
120                   cashier  17.00
131           sandwich artist  18.50
142                dishwasher  20.00
153                babysitter  20.00
164                   barista  21.50
175               housekeeper  21.50
186    retail sales associate  23.00
197                 bartender  23.50
208                   cleaner  23.50
219                 line cook  23.50
2210               pizza cook  23.50
23...
24                      Title 1             Title 2  Count  Wage of Title 1  Wage of Title 2
250    administrative assistant      office manager     20              NaN              NaN
261                  accountant             cashier      1              NaN              NaN
272                  accountant   financial analyst     22              NaN              NaN
28...
29wage_data = pd.read_csv('wage_data_sample.csv')
30dict = dict(zip(wage_data.title, wage_data.wage))
31

ANSWER

Answered 2022-Mar-02 at 08:23

You can try with 2 merge con the 2 different Titles subsequentely.

For example, let be

  • df1 : job_transitions_sample.csv

  • df2 : wage_data_sample.csv

    df1.merge(df2, left_on='Title 1', right_on='title',suffixes=('', 'Wage of')).merge(df2, left_on='Title 2', right_on='title',suffixes=('', 'Wage of'))

Source https://stackoverflow.com/questions/71319221

QUESTION

How do I show my closest 3 competitors on a map?

Asked 2022-Jan-27 at 18:19

I work for a retailer and would like to create a map in Power BI for all of our stores and their 3 closest competitors, like the example below (Ideal Output). The blue dot is our store and the red dots are our competitors. Ideally, the map would change automatically when a different store is selected in a drop-down slicer. Please see the example data below (My Data).

Thanks,

Mark

Ideal Output: Ideal Output

My Data My Data

Table 1: All of our stores. Very basic. Every store has its own unique number, along with basic store details including geocodes.

Table 2: All competitors. Every competitor has a unique number along with basic information including geocodes.

Table 3: Our stores and their 3 closest competitors.

ANSWER

Answered 2022-Jan-22 at 21:14

Here's the solution that comes to mind for me. Create a new table that combines information from all 3 of the tables provided. It will be formatted like your Table 1 or Table 2, but with two new columns, Type and Slicer Store.

Type will specify whether the row is your own store or a competitor. Slicer Store will specify which rows should be displayed when that store's name is selected in the dropdown slicer.

Each row from your Table 3 will have 4 rows in the new table, one for each of the columns. The Slicer Store column in your new table will contain the Store Number from each row in Table 3. The Type column in your new table is self-explanatory.

You will end up with something like the following.

Number Name Address Latitude Longitude Type Slicer Store
JL123 ... ... ... ... Store JL123
C1 ... ... ... ... Competitor JL123
C2 ... ... ... ... Competitor JL123
C3 ... ... ... ... Competitor JL123
JL456 ... ... ... ... Store JL456
C2 ... ... ... ... Competitor JL456
C3 ... ... ... ... Competitor JL456
C4 ... ... ... ... Competitor JL456

Now for how to use it in Power BI. Create a single-select dropdown slicer using your new Slicer Store column. Then create your map visual using the rows in your new table. Use your new Type column as the Legend/Category. This allows you to color stores vs competitors differently in the visual.

Bing bang boom, you're done. Note that you may have "duplicate" rows for competitors in your new table. For example, if JL123 and JL456 were physically located right next to each other, then rows for C1, C2, and C3 would each appear twice in your new table. The Slicer Store would be different (JL123 or JL456) for these rows, though.

How you create the new table, either manually or with some sort of script, is the hard part.

Source https://stackoverflow.com/questions/70806570

QUESTION

Multiple TypeScript discriminations based on different properties

Asked 2022-Jan-20 at 09:58

I'm trying to build a complex REACT component which supports different use-cases. In order to simplify its use, I want to implement TypeScript discriminations types to better infer the props.

It's not useful to post the full example, but I can show you a simpler one, which is the following one:

1interface ICategoryDiscriminationGame {
2    category: 'game';
3    gameType: 'AAA' | 'AA' | 'A';
4}
5
6interface ICategoryDiscriminationProgram {
7    category: 'program';
8    programType: 'software' | 'freeware';
9}
10
11type TCategoryDiscrimination = (ICategoryDiscriminationGame | ICategoryDiscriminationProgram);
12
13
14interface ISaleDiscriminationPresale {
15    saleType: 'pre-sale',
16    preSaleCost: number;
17}
18
19interface ISaleDiscriminationRetailSale {
20    saleType: 'retail',
21    retailSaleCost: number;
22}
23
24type TSaleDiscrimination = (ISaleDiscriminationPresale | ISaleDiscriminationRetailSale);
25
26
27type TExampleCompProps = TCategoryDiscrimination & TSaleDiscrimination;
28
29export const ExampleComp = (props: TExampleCompProps) => {
30    if (props.category === 'game') { // In here, intellisense infer only use 'props.category' and 'props.saleType' -> NICE
31        console.log(props.gameType); // In here, intellisense infer also 'props.gameType' -> NICE
32    }
33
34    if (props.saleType === 'pre-sale') { // In here, intellisense infer only use 'props.category' and 'props.saleType' -> NICE
35        console.log(props.preSaleCost); // In here, intellisense infer also 'props.preSaleCost' -> NICE
36    }
37
38    if (props.category === 'game' && props.saleType === 'retail') { // In here, intellisense infer only use 'props.category' and 'props.saleType' -> NICE
39        console.log(props.retailSaleCost); // In here, intellisense infer also 'props.retailSaleCost' and 'props.gameType' -> NICE
40        console.log(props.gameType); // In here, intellisense infer also 'props.retailSaleCost' and 'props.gameType' -> NICE
41    }
42
43    return <p>In example comp</p>
44}
45

As you can see, inside the ExampleComp, the intellisense is brilliant, and works great. The problem is when I try to use the ExampleComp.
What I would expect is that, when I write <ExampleComp, the intellisense allows me only the props category and saleType, since the other ones cannot exist without first defining those two. BUT, instead, it just suggests everything:

enter image description here

So, the question here is: what am I missing that does not make the intellisense works correctly ALSO in the props?

TS Playground link

ANSWER

Answered 2022-Jan-20 at 09:58

That's just the nature of IntelliSense. Once you start to supply some of the combinations of the required props, the suggested ones will be narrowed to only the ones which are applicable to the current possible combination:

Before adding some props: Before adding some props

After adding some props: After adding some props

Source https://stackoverflow.com/questions/70783765

QUESTION

Grabbing certain data from one object to another object

Asked 2022-Jan-08 at 16:38

My goal is to grab certain values from database into curated_database, however I am basically stuck at adding multiple items into an object.

1var curated_database = {};
2
3var database = {
4    0: [{name: 'Micheal'}, 
5        {age: 45},
6        {education: 'BA'},  
7        {income: 245000},  
8        {occupation: 'director'}],
9    1: [{name: 'John'}, 
10        {age: 23},
11        {education: 'BA'},  
12        {income: 60000},  
13        {occupation: 'manager'}],
14    2: [{name: 'Judith'}, 
15        {age: 45},
16        {education: 'PhD'},  
17        {income: 140000},  
18        {occupation: 'professor'}],
19    3: [{name: 'Gill'}, 
20        {age: 28},
21        {education: 'MS'},  
22        {income: 98000},  
23        {occupation: 'scientist'}],
24    4: [{name: 'Dave'}, 
25        {age: 17},
26        {education: 'HS'},  
27        {income: 30000},  
28        {occupation: 'retail associate'}]
29};
30

Goal is to grab similar certain information from the larger object

1var curated_database = {};
2
3var database = {
4    0: [{name: 'Micheal'}, 
5        {age: 45},
6        {education: 'BA'},  
7        {income: 245000},  
8        {occupation: 'director'}],
9    1: [{name: 'John'}, 
10        {age: 23},
11        {education: 'BA'},  
12        {income: 60000},  
13        {occupation: 'manager'}],
14    2: [{name: 'Judith'}, 
15        {age: 45},
16        {education: 'PhD'},  
17        {income: 140000},  
18        {occupation: 'professor'}],
19    3: [{name: 'Gill'}, 
20        {age: 28},
21        {education: 'MS'},  
22        {income: 98000},  
23        {occupation: 'scientist'}],
24    4: [{name: 'Dave'}, 
25        {age: 17},
26        {education: 'HS'},  
27        {income: 30000},  
28        {occupation: 'retail associate'}]
29};
30
31curated_database = { 
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38

My attempt

1var curated_database = {};
2
3var database = {
4    0: [{name: 'Micheal'}, 
5        {age: 45},
6        {education: 'BA'},  
7        {income: 245000},  
8        {occupation: 'director'}],
9    1: [{name: 'John'}, 
10        {age: 23},
11        {education: 'BA'},  
12        {income: 60000},  
13        {occupation: 'manager'}],
14    2: [{name: 'Judith'}, 
15        {age: 45},
16        {education: 'PhD'},  
17        {income: 140000},  
18        {occupation: 'professor'}],
19    3: [{name: 'Gill'}, 
20        {age: 28},
21        {education: 'MS'},  
22        {income: 98000},  
23        {occupation: 'scientist'}],
24    4: [{name: 'Dave'}, 
25        {age: 17},
26        {education: 'HS'},  
27        {income: 30000},  
28        {occupation: 'retail associate'}]
29};
30
31curated_database = { 
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39    desired_contents = [0,3,4]
40    for(contents in desired_contents){
41    console.log(database[data][desired_contents[contents]]);
42    }
43    var k = database[data][0];
44    if (!currated_database[k.key]) {
45        currated_database[k.key] = [];
46    }
47        currated_database[k.key].push(k.val);
48}
49

ANSWER

Answered 2022-Jan-08 at 16:35

you can achieve it this way:

1var curated_database = {};
2
3var database = {
4    0: [{name: 'Micheal'}, 
5        {age: 45},
6        {education: 'BA'},  
7        {income: 245000},  
8        {occupation: 'director'}],
9    1: [{name: 'John'}, 
10        {age: 23},
11        {education: 'BA'},  
12        {income: 60000},  
13        {occupation: 'manager'}],
14    2: [{name: 'Judith'}, 
15        {age: 45},
16        {education: 'PhD'},  
17        {income: 140000},  
18        {occupation: 'professor'}],
19    3: [{name: 'Gill'}, 
20        {age: 28},
21        {education: 'MS'},  
22        {income: 98000},  
23        {occupation: 'scientist'}],
24    4: [{name: 'Dave'}, 
25        {age: 17},
26        {education: 'HS'},  
27        {income: 30000},  
28        {occupation: 'retail associate'}]
29};
30
31curated_database = { 
320 : ['Micheal',245000,'director'],
331: ['John',245000,'manager'],
342: ['Judith',140000,'professor'],
353: ['Gill',98000,'scientist'],
364: ['Dave',30000,'retail associate']
37};
38for(data in database){
39    desired_contents = [0,3,4]
40    for(contents in desired_contents){
41    console.log(database[data][desired_contents[contents]]);
42    }
43    var k = database[data][0];
44    if (!currated_database[k.key]) {
45        currated_database[k.key] = [];
46    }
47        currated_database[k.key].push(k.val);
48}
49var curated_database = {};
50
51var database = {
52    0: [{name: 'Micheal'},
53        {age: 45},
54        {education: 'BA'},
55        {income: 245000},
56        {occupation: 'director'}],
57    1: [{name: 'John'},
58        {age: 23},
59        {education: 'BA'},
60        {income: 60000},
61        {occupation: 'manager'}],
62    2: [{name: 'Judith'},
63        {age: 45},
64        {education: 'PhD'},
65        {income: 140000},
66        {occupation: 'professor'}],
67    3: [{name: 'Gill'},
68        {age: 28},
69        {education: 'MS'},
70        {income: 98000},
71        {occupation: 'scientist'}],
72    4: [{name: 'Dave'},
73        {age: 17},
74        {education: 'HS'},
75        {income: 30000},
76        {occupation: 'retail associate'}]
77};
78
79
80
81var indexToExtract = new Set([0,3,4]);
82
83    var wantedLst = Object.values(database)
84    .map(lst =&gt; lst.filter((ob, idx) =&gt; indexToExtract.has(idx)))
85    .map((lst, idx) =&gt; curated_database[idx] = lst.map(ob =&gt; Object.values(ob)[0]))
86    
87    console.log(curated_database);

Source https://stackoverflow.com/questions/70633937

QUESTION

localizing rows from a dataframe

Asked 2021-Dec-21 at 12:43

I working with a dataframe which has 20 columns but I'm only going to use three of them in my task, which are named "Price","Retail" and "Profit" and are like this:

1cols = ['Price', 'Retail','Profit']
2df3.loc[:,cols]
3
1cols = ['Price', 'Retail','Profit']
2df3.loc[:,cols]
3    Price   Retail  Profit
40   861.5   1315.233051 453.733051
51   901.5   1315.233051 413.733051
62   911.0   1315.233051 404.233051
73   901.5   1315.233051 413.733051
84   901.5   1315.233051 413.733051
9... ... ... ...
102678    14574.0 21546.730769    6972.730769
112679    35708.5 52026.764706    16318.264706
122680    35708.5 52026.764706    16318.264706
132681    163276.5    250882.500000   87606.000000
142682    7369.5  11785.729730    4416.229730
152683 rows × 3 columns
16

My goal is to find the lines where the prices are lower than 5000 and sort by the biggest values of profit. How can I make it?

ANSWER

Answered 2021-Dec-21 at 12:41

You can do this:

1cols = ['Price', 'Retail','Profit']
2df3.loc[:,cols]
3    Price   Retail  Profit
40   861.5   1315.233051 453.733051
51   901.5   1315.233051 413.733051
62   911.0   1315.233051 404.233051
73   901.5   1315.233051 413.733051
84   901.5   1315.233051 413.733051
9... ... ... ...
102678    14574.0 21546.730769    6972.730769
112679    35708.5 52026.764706    16318.264706
122680    35708.5 52026.764706    16318.264706
132681    163276.5    250882.500000   87606.000000
142682    7369.5  11785.729730    4416.229730
152683 rows × 3 columns
16df_pofit_less_5000 = df[df['Price']&lt;5000]
17

Source https://stackoverflow.com/questions/70435605

QUESTION

Fetching data in loop next js

Asked 2021-Dec-08 at 02:35

I am attempting to use the data from 1 endpoint to call another endpoint that is filtered by id. I am planning on fetching both calls using getServerSideProps and passing the data to another component.

The first call will return an array of categories which then I am attempting to loop and fetch articles that is filtered by id.

I am able to successfully get back the array of categories but when I am attempting to loop and fetch articles I am getting a value of undefined How can I achieve this?

Here is an example of my index.js

1import ArticleList from &quot;../../components/ArticleList&quot;;
2
3
4const Index = ({ categories, articles }) =&gt; {
5
6
7  return (
8    &lt;&gt;
9      &lt;ArticleList categories={categories} articles={articles} /&gt;
10    &lt;/&gt;
11  )
12}
13
14export async function getServerSideProps (context) {
15   // console.log('index - getserversideprops() is called')
16   try {
17     let articles = []
18    let response = await fetch('https://example.api/categories')
19    const categories = await response.json()
20
21    for (let i = 0; i &lt; categories.results.length; i++) {
22      response = await fetch (`https://example.api/articleid/` + categories.results[i].id)
23      articles = await response.json()
24    }
25
26    console.log(articles,'33')
27
28
29    if (!categories ) {
30        return {
31            notFound: true,
32        }
33    }
34
35    return { 
36      props: { 
37        categories: categories,
38        articles: artices
39      }
40    }
41  } catch (error) {
42      console.error('runtime error: ', error)
43  }
44}
45
46export default Index
47

Here is an example of my console.log(categories.results) array:

1import ArticleList from &quot;../../components/ArticleList&quot;;
2
3
4const Index = ({ categories, articles }) =&gt; {
5
6
7  return (
8    &lt;&gt;
9      &lt;ArticleList categories={categories} articles={articles} /&gt;
10    &lt;/&gt;
11  )
12}
13
14export async function getServerSideProps (context) {
15   // console.log('index - getserversideprops() is called')
16   try {
17     let articles = []
18    let response = await fetch('https://example.api/categories')
19    const categories = await response.json()
20
21    for (let i = 0; i &lt; categories.results.length; i++) {
22      response = await fetch (`https://example.api/articleid/` + categories.results[i].id)
23      articles = await response.json()
24    }
25
26    console.log(articles,'33')
27
28
29    if (!categories ) {
30        return {
31            notFound: true,
32        }
33    }
34
35    return { 
36      props: { 
37        categories: categories,
38        articles: artices
39      }
40    }
41  } catch (error) {
42      console.error('runtime error: ', error)
43  }
44}
45
46export default Index
47[ {
48&quot;id&quot;: 2,
49&quot;name&quot;: &quot;Online&quot;
50},
51{
52&quot;id&quot;: 11,
53&quot;name&quot;: &quot;Retail&quot;
54},
55{
56&quot;id&quot;: 14,
57&quot;name&quot;: &quot;E-Commerce&quot;
58}]
59

I am expecting articles to be 3 separate arrays of data. Is this something that is possible if I am passing the data to another component? If not what will be a better way of handling this?

ANSWER

Answered 2021-Dec-08 at 02:35
1import ArticleList from &quot;../../components/ArticleList&quot;;
2
3
4const Index = ({ categories, articles }) =&gt; {
5
6
7  return (
8    &lt;&gt;
9      &lt;ArticleList categories={categories} articles={articles} /&gt;
10    &lt;/&gt;
11  )
12}
13
14export async function getServerSideProps (context) {
15   // console.log('index - getserversideprops() is called')
16   try {
17     let articles = []
18    let response = await fetch('https://example.api/categories')
19    const categories = await response.json()
20
21    for (let i = 0; i &lt; categories.results.length; i++) {
22      response = await fetch (`https://example.api/articleid/` + categories.results[i].id)
23      articles = await response.json()
24    }
25
26    console.log(articles,'33')
27
28
29    if (!categories ) {
30        return {
31            notFound: true,
32        }
33    }
34
35    return { 
36      props: { 
37        categories: categories,
38        articles: artices
39      }
40    }
41  } catch (error) {
42      console.error('runtime error: ', error)
43  }
44}
45
46export default Index
47[ {
48&quot;id&quot;: 2,
49&quot;name&quot;: &quot;Online&quot;
50},
51{
52&quot;id&quot;: 11,
53&quot;name&quot;: &quot;Retail&quot;
54},
55{
56&quot;id&quot;: 14,
57&quot;name&quot;: &quot;E-Commerce&quot;
58}]
59export async function getServerSideProps(context) {
60  try {
61    const categories = await fetch('https://example.api/categories').then((response) =&gt; response.json());
62
63    if (!categories) {
64      return { notFound: true };
65    }
66
67    const articles = await Promise.all(
68      categories.results.map((result) =&gt;
69        fetch(`https://example.api/articleid/` + result.id).then((response) =&gt; response.json())
70      )
71    );
72
73    const props = { categories, articles };
74
75    return { props };
76  } catch (error) {
77    console.error('runtime error: ', error);
78  }
79}
80

The code will be clean.

Source https://stackoverflow.com/questions/70269024

QUESTION

how to order json data header while downloading it into xls format using JavaScript

Asked 2021-Dec-05 at 20:41

I have a Json data in which I am generating dynamic keys which is having fiscal year quarter and respective values,I need to download the data into xls format which I am successfully able to do, but the problem is when I download the data the order of the xls header is not same as my json keys.Below is my sample data.

1var input = [
2    {
3        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
4        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
5        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
6        &quot;RTM&quot;: &quot;Retail&quot;,
7        &quot;Account&quot;: &quot;Austria-epos&quot;,
8        &quot;FY202004&quot;: &quot;20%&quot;,
9        &quot;FY202101&quot;: &quot;20%&quot;,
10        &quot;FY202102&quot;: &quot;20%&quot;,
11        &quot;FY202103&quot;: &quot;20%&quot;,
12        &quot;FY202104&quot;: &quot;20%&quot;,
13        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
14        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
15        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
16        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
17    },
18    {
19        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
20        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
21        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
22        &quot;RTM&quot;: &quot;Retail&quot;,
23        &quot;Account&quot;: &quot;Austria-epos&quot;,
24        &quot;FY202004&quot;: &quot;20%&quot;,
25        &quot;FY202101&quot;: &quot;20%&quot;,
26        &quot;FY202102&quot;: &quot;20%&quot;,
27        &quot;FY202103&quot;: &quot;20%&quot;,
28        &quot;FY202104&quot;: &quot;20%&quot;,
29        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
30        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
31        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
32        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
33    },
34    {
35        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
36        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
37        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
38        &quot;RTM&quot;: &quot;Retail&quot;,
39        &quot;Account&quot;: &quot;Austria-epos&quot;,
40        &quot;FY202004&quot;: &quot;20%&quot;,
41        &quot;FY202101&quot;: &quot;20%&quot;,
42        &quot;FY202102&quot;: &quot;20%&quot;,
43        &quot;FY202103&quot;: &quot;20%&quot;,
44        &quot;FY202104&quot;: &quot;20%&quot;,
45        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
46        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
47        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
48        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
49    },
50    {
51        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
52        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
53        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
54        &quot;RTM&quot;: &quot;Retail&quot;,
55        &quot;Account&quot;: &quot;Austria-epos&quot;,
56        &quot;FY202004&quot;: &quot;20%&quot;,
57        &quot;FY202101&quot;: &quot;20%&quot;,
58        &quot;FY202102&quot;: &quot;20%&quot;,
59        &quot;FY202103&quot;: &quot;20%&quot;,
60        &quot;FY202104&quot;: &quot;20%&quot;,
61        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
62        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
63        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
64        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
65    },
66    {
67        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
68        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
69        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
70        &quot;RTM&quot;: &quot;Retail&quot;,
71        &quot;Account&quot;: &quot;Austria-epos&quot;,
72        &quot;FY202004&quot;: &quot;20%&quot;,
73        &quot;FY202101&quot;: &quot;20%&quot;,
74        &quot;FY202102&quot;: &quot;20%&quot;,
75        &quot;FY202103&quot;: &quot;20%&quot;,
76        &quot;FY202104&quot;: &quot;20%&quot;,
77        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
78        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
79        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
80        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
81    },
82]
83

here to snipped code I am working to download the data

1var input = [
2    {
3        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
4        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
5        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
6        &quot;RTM&quot;: &quot;Retail&quot;,
7        &quot;Account&quot;: &quot;Austria-epos&quot;,
8        &quot;FY202004&quot;: &quot;20%&quot;,
9        &quot;FY202101&quot;: &quot;20%&quot;,
10        &quot;FY202102&quot;: &quot;20%&quot;,
11        &quot;FY202103&quot;: &quot;20%&quot;,
12        &quot;FY202104&quot;: &quot;20%&quot;,
13        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
14        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
15        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
16        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
17    },
18    {
19        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
20        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
21        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
22        &quot;RTM&quot;: &quot;Retail&quot;,
23        &quot;Account&quot;: &quot;Austria-epos&quot;,
24        &quot;FY202004&quot;: &quot;20%&quot;,
25        &quot;FY202101&quot;: &quot;20%&quot;,
26        &quot;FY202102&quot;: &quot;20%&quot;,
27        &quot;FY202103&quot;: &quot;20%&quot;,
28        &quot;FY202104&quot;: &quot;20%&quot;,
29        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
30        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
31        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
32        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
33    },
34    {
35        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
36        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
37        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
38        &quot;RTM&quot;: &quot;Retail&quot;,
39        &quot;Account&quot;: &quot;Austria-epos&quot;,
40        &quot;FY202004&quot;: &quot;20%&quot;,
41        &quot;FY202101&quot;: &quot;20%&quot;,
42        &quot;FY202102&quot;: &quot;20%&quot;,
43        &quot;FY202103&quot;: &quot;20%&quot;,
44        &quot;FY202104&quot;: &quot;20%&quot;,
45        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
46        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
47        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
48        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
49    },
50    {
51        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
52        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
53        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
54        &quot;RTM&quot;: &quot;Retail&quot;,
55        &quot;Account&quot;: &quot;Austria-epos&quot;,
56        &quot;FY202004&quot;: &quot;20%&quot;,
57        &quot;FY202101&quot;: &quot;20%&quot;,
58        &quot;FY202102&quot;: &quot;20%&quot;,
59        &quot;FY202103&quot;: &quot;20%&quot;,
60        &quot;FY202104&quot;: &quot;20%&quot;,
61        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
62        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
63        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
64        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
65    },
66    {
67        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
68        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
69        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
70        &quot;RTM&quot;: &quot;Retail&quot;,
71        &quot;Account&quot;: &quot;Austria-epos&quot;,
72        &quot;FY202004&quot;: &quot;20%&quot;,
73        &quot;FY202101&quot;: &quot;20%&quot;,
74        &quot;FY202102&quot;: &quot;20%&quot;,
75        &quot;FY202103&quot;: &quot;20%&quot;,
76        &quot;FY202104&quot;: &quot;20%&quot;,
77        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
78        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
79        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
80        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
81    },
82]
83const xlsData = input
84        const ws = XLSX.utils.json_to_sheet(xlsData);
85        const wb = { Sheets: { 'data': ws }, SheetNames: ['data'] };
86        const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
87        const data = new Blob([excelBuffer], { type: fileType });
88        let fileName = `test`
89        FileSaver.saveAs(data, fileName + fileExtension);
90

result after the converted it into xls the header are like this enter image description here I am excepting the output the be enter image description here

ANSWER

Answered 2021-Dec-05 at 20:33

In your code snippet change the second line to:

1var input = [
2    {
3        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
4        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
5        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
6        &quot;RTM&quot;: &quot;Retail&quot;,
7        &quot;Account&quot;: &quot;Austria-epos&quot;,
8        &quot;FY202004&quot;: &quot;20%&quot;,
9        &quot;FY202101&quot;: &quot;20%&quot;,
10        &quot;FY202102&quot;: &quot;20%&quot;,
11        &quot;FY202103&quot;: &quot;20%&quot;,
12        &quot;FY202104&quot;: &quot;20%&quot;,
13        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
14        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
15        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
16        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
17    },
18    {
19        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
20        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
21        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
22        &quot;RTM&quot;: &quot;Retail&quot;,
23        &quot;Account&quot;: &quot;Austria-epos&quot;,
24        &quot;FY202004&quot;: &quot;20%&quot;,
25        &quot;FY202101&quot;: &quot;20%&quot;,
26        &quot;FY202102&quot;: &quot;20%&quot;,
27        &quot;FY202103&quot;: &quot;20%&quot;,
28        &quot;FY202104&quot;: &quot;20%&quot;,
29        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
30        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
31        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
32        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
33    },
34    {
35        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
36        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
37        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
38        &quot;RTM&quot;: &quot;Retail&quot;,
39        &quot;Account&quot;: &quot;Austria-epos&quot;,
40        &quot;FY202004&quot;: &quot;20%&quot;,
41        &quot;FY202101&quot;: &quot;20%&quot;,
42        &quot;FY202102&quot;: &quot;20%&quot;,
43        &quot;FY202103&quot;: &quot;20%&quot;,
44        &quot;FY202104&quot;: &quot;20%&quot;,
45        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
46        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
47        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
48        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
49    },
50    {
51        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
52        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
53        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
54        &quot;RTM&quot;: &quot;Retail&quot;,
55        &quot;Account&quot;: &quot;Austria-epos&quot;,
56        &quot;FY202004&quot;: &quot;20%&quot;,
57        &quot;FY202101&quot;: &quot;20%&quot;,
58        &quot;FY202102&quot;: &quot;20%&quot;,
59        &quot;FY202103&quot;: &quot;20%&quot;,
60        &quot;FY202104&quot;: &quot;20%&quot;,
61        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
62        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
63        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
64        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
65    },
66    {
67        &quot;FPH Level 1&quot;: &quot;iphone&quot;,
68        &quot;Geo Level 2&quot;: &quot;Austria&quot;,
69        &quot;Geo Level 7&quot;: &quot;DACH&quot;,
70        &quot;RTM&quot;: &quot;Retail&quot;,
71        &quot;Account&quot;: &quot;Austria-epos&quot;,
72        &quot;FY202004&quot;: &quot;20%&quot;,
73        &quot;FY202101&quot;: &quot;20%&quot;,
74        &quot;FY202102&quot;: &quot;20%&quot;,
75        &quot;FY202103&quot;: &quot;20%&quot;,
76        &quot;FY202104&quot;: &quot;20%&quot;,
77        &quot;Y/Y pt Change&quot;: &quot;5%&quot;,
78        &quot;Commentary Y/Y&quot;: &quot;TESTING&quot;,
79        &quot;Q/Q pt Change&quot;: &quot;4%&quot;,
80        &quot;Commentary Q/Q&quot;: &quot;TESTING&quot;
81    },
82]
83const xlsData = input
84        const ws = XLSX.utils.json_to_sheet(xlsData);
85        const wb = { Sheets: { 'data': ws }, SheetNames: ['data'] };
86        const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
87        const data = new Blob([excelBuffer], { type: fileType });
88        let fileName = `test`
89        FileSaver.saveAs(data, fileName + fileExtension);
90const header = [&quot;FPH Level 1&quot;, &quot;Geo Level 2&quot;, &quot;Geo Level 7&quot;, &quot;RTM&quot;, &quot;Account&quot;]
91const fy = Object.keys(input[0]).filter(s =&gt; s.startsWith(&quot;FY&quot;)).sort()
92header.push(...fy)
93header.push(&quot;Y/Y pt Change&quot;, &quot;Commentary Y/Y&quot;, &quot;Q/Q pt Change&quot;, &quot;Commentary Q/Q&quot;)
94const ws = XLSX.utils.json_to_sheet(xlsData, { header })
95

Source https://stackoverflow.com/questions/70237855

QUESTION

json_normalize a nested database

Asked 2021-Nov-10 at 13:56

I'm trying to flatten a json database into pandas dataframe and as it's the first time I'm dealing with json format, I can't do what I want. The database is located here https://mtgjson.com/downloads/all-files/#allprices and according to the model, the structure is like this :

1{
2  &quot;0120a941-9cfb-50b5-b5e4-4e0c7bd32410&quot;: {
3    &quot;mtgo&quot;: {
4      &quot;cardhoarder&quot;: {
5        &quot;currency&quot;: &quot;USD&quot;,
6        &quot;retail&quot;: {
7          &quot;foil&quot;: {
8            ..., // more rows
9            &quot;2020-04-21&quot;: 0.02
10          },
11          &quot;normal&quot;: {
12            ..., // more rows
13            &quot;2020-04-21&quot;: 0.02
14          }
15        }
16      },
17    },
18    &quot;paper&quot;: {
19      &quot;cardkingdom&quot; : {
20        &quot;buylist&quot;: {
21          &quot;foil&quot;: {
22            ..., // more rows
23            &quot;2020-04-21&quot;: 0.6
24          },
25          &quot;normal&quot;: {
26            ..., // more rows
27            &quot;2020-04-21&quot;: 0.01
28          }
29        },
30        &quot;currency&quot;: &quot;USD&quot;,
31        &quot;retail&quot;: {
32          &quot;foil&quot;: {
33            ..., // more rows
34            &quot;2020-04-21&quot;: 0.12
35          },
36          &quot;normal&quot;: {
37            ..., // more rows
38            &quot;2020-04-21&quot;: 0.02
39          }
40        }
41      },
42      &quot;cardmarket&quot;: {
43        &quot;currency&quot;: &quot;EUR&quot;,
44        &quot;retail&quot;: {
45          &quot;foil&quot;: {
46            ..., // more rows
47            &quot;2020-04-21&quot;: 0.12
48          },
49          &quot;normal&quot;: {
50            ..., // more rows
51            &quot;2020-04-21&quot;: 0.02
52          }
53        }
54      },
55      &quot;tcgplayer&quot;: {
56        &quot;currency&quot;: &quot;USD&quot;,
57        &quot;retail&quot;: {
58          &quot;foil&quot;: {
59            ..., // more rows
60            &quot;2020-04-21&quot;: 0.12
61          },
62          &quot;normal&quot;: {
63            ..., // more rows
64            &quot;2020-04-21&quot;: 0.02
65          }
66        }
67      }
68    }
69  }
70}
71

When I look into the json file I have this :

1{
2  &quot;0120a941-9cfb-50b5-b5e4-4e0c7bd32410&quot;: {
3    &quot;mtgo&quot;: {
4      &quot;cardhoarder&quot;: {
5        &quot;currency&quot;: &quot;USD&quot;,
6        &quot;retail&quot;: {
7          &quot;foil&quot;: {
8            ..., // more rows
9            &quot;2020-04-21&quot;: 0.02
10          },
11          &quot;normal&quot;: {
12            ..., // more rows
13            &quot;2020-04-21&quot;: 0.02
14          }
15        }
16      },
17    },
18    &quot;paper&quot;: {
19      &quot;cardkingdom&quot; : {
20        &quot;buylist&quot;: {
21          &quot;foil&quot;: {
22            ..., // more rows
23            &quot;2020-04-21&quot;: 0.6
24          },
25          &quot;normal&quot;: {
26            ..., // more rows
27            &quot;2020-04-21&quot;: 0.01
28          }
29        },
30        &quot;currency&quot;: &quot;USD&quot;,
31        &quot;retail&quot;: {
32          &quot;foil&quot;: {
33            ..., // more rows
34            &quot;2020-04-21&quot;: 0.12
35          },
36          &quot;normal&quot;: {
37            ..., // more rows
38            &quot;2020-04-21&quot;: 0.02
39          }
40        }
41      },
42      &quot;cardmarket&quot;: {
43        &quot;currency&quot;: &quot;EUR&quot;,
44        &quot;retail&quot;: {
45          &quot;foil&quot;: {
46            ..., // more rows
47            &quot;2020-04-21&quot;: 0.12
48          },
49          &quot;normal&quot;: {
50            ..., // more rows
51            &quot;2020-04-21&quot;: 0.02
52          }
53        }
54      },
55      &quot;tcgplayer&quot;: {
56        &quot;currency&quot;: &quot;USD&quot;,
57        &quot;retail&quot;: {
58          &quot;foil&quot;: {
59            ..., // more rows
60            &quot;2020-04-21&quot;: 0.12
61          },
62          &quot;normal&quot;: {
63            ..., // more rows
64            &quot;2020-04-21&quot;: 0.02
65          }
66        }
67      }
68    }
69  }
70}
71{&quot;meta&quot;: {&quot;date&quot;: &quot;2021-11-07&quot;, &quot;version&quot;: &quot;5.1.0+20211107&quot;}, &quot;data&quot;: {&quot;00010d56-fe38-5e35-8aed-518019aa36a5&quot;: {&quot;paper&quot;: {&quot;cardkingdom&quot;: {&quot;buylist&quot;: {&quot;foil.....
72

When I do the basic pd.read_json('AllPrices.json') I got this

meta data
date 2021-11-07 NaN
version 5.1.0+20211107 NaN
00010d56-fe38-5e35-8aed-518019aa36a5 NaN {'paper': {'cardkingdom': {'buylist': {'foil':...
0001e0d0-2dcd-5640-aadc-a84765cf5fc9 NaN {'paper': {'cardkingdom': {'buylist': {'normal...

So I did some research and found the json_normalize and wrote this piece of code :

1{
2  &quot;0120a941-9cfb-50b5-b5e4-4e0c7bd32410&quot;: {
3    &quot;mtgo&quot;: {
4      &quot;cardhoarder&quot;: {
5        &quot;currency&quot;: &quot;USD&quot;,
6        &quot;retail&quot;: {
7          &quot;foil&quot;: {
8            ..., // more rows
9            &quot;2020-04-21&quot;: 0.02
10          },
11          &quot;normal&quot;: {
12            ..., // more rows
13            &quot;2020-04-21&quot;: 0.02
14          }
15        }
16      },
17    },
18    &quot;paper&quot;: {
19      &quot;cardkingdom&quot; : {
20        &quot;buylist&quot;: {
21          &quot;foil&quot;: {
22            ..., // more rows
23            &quot;2020-04-21&quot;: 0.6
24          },
25          &quot;normal&quot;: {
26            ..., // more rows
27            &quot;2020-04-21&quot;: 0.01
28          }
29        },
30        &quot;currency&quot;: &quot;USD&quot;,
31        &quot;retail&quot;: {
32          &quot;foil&quot;: {
33            ..., // more rows
34            &quot;2020-04-21&quot;: 0.12
35          },
36          &quot;normal&quot;: {
37            ..., // more rows
38            &quot;2020-04-21&quot;: 0.02
39          }
40        }
41      },
42      &quot;cardmarket&quot;: {
43        &quot;currency&quot;: &quot;EUR&quot;,
44        &quot;retail&quot;: {
45          &quot;foil&quot;: {
46            ..., // more rows
47            &quot;2020-04-21&quot;: 0.12
48          },
49          &quot;normal&quot;: {
50            ..., // more rows
51            &quot;2020-04-21&quot;: 0.02
52          }
53        }
54      },
55      &quot;tcgplayer&quot;: {
56        &quot;currency&quot;: &quot;USD&quot;,
57        &quot;retail&quot;: {
58          &quot;foil&quot;: {
59            ..., // more rows
60            &quot;2020-04-21&quot;: 0.12
61          },
62          &quot;normal&quot;: {
63            ..., // more rows
64            &quot;2020-04-21&quot;: 0.02
65          }
66        }
67      }
68    }
69  }
70}
71{&quot;meta&quot;: {&quot;date&quot;: &quot;2021-11-07&quot;, &quot;version&quot;: &quot;5.1.0+20211107&quot;}, &quot;data&quot;: {&quot;00010d56-fe38-5e35-8aed-518019aa36a5&quot;: {&quot;paper&quot;: {&quot;cardkingdom&quot;: {&quot;buylist&quot;: {&quot;foil.....
72with open('AllPrices.json','r') as f:
73    data = json.loads(f.read())
74pd.json_normalize(data, errors='ignore')
75

This did the job by flattening the json database but I ended with one row and 31 millions columns. What I want is only one information in this database that is the uuid and the cardmarket price of a normal paper card on the date I want like this :

uuid paper.cardmarket.retail.normal.2021-11-07
00010d56-fe38-5e35-8aed-518019aa36a5 0.5
0001e0d0-2dcd-5640-aadc-a84765cf5fc9 0.25

I played with the record_path = parameter and the meta = parameter but the best I did was not my expected table. I tried record_path = ['data'] that give me only the uuid in one column.

Thanks for your help

ANSWER

Answered 2021-Nov-10 at 11:03

In your case, use json_normalize for each uuid record then extract the desired information 'paper.cardmarket.retail.normal':

1{
2  &quot;0120a941-9cfb-50b5-b5e4-4e0c7bd32410&quot;: {
3    &quot;mtgo&quot;: {
4      &quot;cardhoarder&quot;: {
5        &quot;currency&quot;: &quot;USD&quot;,
6        &quot;retail&quot;: {
7          &quot;foil&quot;: {
8            ..., // more rows
9            &quot;2020-04-21&quot;: 0.02
10          },
11          &quot;normal&quot;: {
12            ..., // more rows
13            &quot;2020-04-21&quot;: 0.02
14          }
15        }
16      },
17    },
18    &quot;paper&quot;: {
19      &quot;cardkingdom&quot; : {
20        &quot;buylist&quot;: {
21          &quot;foil&quot;: {
22            ..., // more rows
23            &quot;2020-04-21&quot;: 0.6
24          },
25          &quot;normal&quot;: {
26            ..., // more rows
27            &quot;2020-04-21&quot;: 0.01
28          }
29        },
30        &quot;currency&quot;: &quot;USD&quot;,
31        &quot;retail&quot;: {
32          &quot;foil&quot;: {
33            ..., // more rows
34            &quot;2020-04-21&quot;: 0.12
35          },
36          &quot;normal&quot;: {
37            ..., // more rows
38            &quot;2020-04-21&quot;: 0.02
39          }
40        }
41      },
42      &quot;cardmarket&quot;: {
43        &quot;currency&quot;: &quot;EUR&quot;,
44        &quot;retail&quot;: {
45          &quot;foil&quot;: {
46            ..., // more rows
47            &quot;2020-04-21&quot;: 0.12
48          },
49          &quot;normal&quot;: {
50            ..., // more rows
51            &quot;2020-04-21&quot;: 0.02
52          }
53        }
54      },
55      &quot;tcgplayer&quot;: {
56        &quot;currency&quot;: &quot;USD&quot;,
57        &quot;retail&quot;: {
58          &quot;foil&quot;: {
59            ..., // more rows
60            &quot;2020-04-21&quot;: 0.12
61          },
62          &quot;normal&quot;: {
63            ..., // more rows
64            &quot;2020-04-21&quot;: 0.02
65          }
66        }
67      }
68    }
69  }
70}
71{&quot;meta&quot;: {&quot;date&quot;: &quot;2021-11-07&quot;, &quot;version&quot;: &quot;5.1.0+20211107&quot;}, &quot;data&quot;: {&quot;00010d56-fe38-5e35-8aed-518019aa36a5&quot;: {&quot;paper&quot;: {&quot;cardkingdom&quot;: {&quot;buylist&quot;: {&quot;foil.....
72with open('AllPrices.json','r') as f:
73    data = json.loads(f.read())
74pd.json_normalize(data, errors='ignore')
75with open('AllPrices.json') as fp:
76    prices = json.load(fp)
77
78    data = []
79    for uuid in prices['data']:
80        df = pd.json_normalize(prices['data'][uuid]) \
81               .filter(like='paper.cardmarket.retail.normal')
82        if df.empty:
83            continue
84        df.columns = df.columns.str.rsplit('.', 1).str[-1]
85        df.index = [uuid]
86        data.append(df)
87    df = pd.concat(data)
88

Output: (tested on AllPrices.json file)

1{
2  &quot;0120a941-9cfb-50b5-b5e4-4e0c7bd32410&quot;: {
3    &quot;mtgo&quot;: {
4      &quot;cardhoarder&quot;: {
5        &quot;currency&quot;: &quot;USD&quot;,
6        &quot;retail&quot;: {
7          &quot;foil&quot;: {
8            ..., // more rows
9            &quot;2020-04-21&quot;: 0.02
10          },
11          &quot;normal&quot;: {
12            ..., // more rows
13            &quot;2020-04-21&quot;: 0.02
14          }
15        }
16      },
17    },
18    &quot;paper&quot;: {
19      &quot;cardkingdom&quot; : {
20        &quot;buylist&quot;: {
21          &quot;foil&quot;: {
22            ..., // more rows
23            &quot;2020-04-21&quot;: 0.6
24          },
25          &quot;normal&quot;: {
26            ..., // more rows
27            &quot;2020-04-21&quot;: 0.01
28          }
29        },
30        &quot;currency&quot;: &quot;USD&quot;,
31        &quot;retail&quot;: {
32          &quot;foil&quot;: {
33            ..., // more rows
34            &quot;2020-04-21&quot;: 0.12
35          },
36          &quot;normal&quot;: {
37            ..., // more rows
38            &quot;2020-04-21&quot;: 0.02
39          }
40        }
41      },
42      &quot;cardmarket&quot;: {
43        &quot;currency&quot;: &quot;EUR&quot;,
44        &quot;retail&quot;: {
45          &quot;foil&quot;: {
46            ..., // more rows
47            &quot;2020-04-21&quot;: 0.12
48          },
49          &quot;normal&quot;: {
50            ..., // more rows
51            &quot;2020-04-21&quot;: 0.02
52          }
53        }
54      },
55      &quot;tcgplayer&quot;: {
56        &quot;currency&quot;: &quot;USD&quot;,
57        &quot;retail&quot;: {
58          &quot;foil&quot;: {
59            ..., // more rows
60            &quot;2020-04-21&quot;: 0.12
61          },
62          &quot;normal&quot;: {
63            ..., // more rows
64            &quot;2020-04-21&quot;: 0.02
65          }
66        }
67      }
68    }
69  }
70}
71{&quot;meta&quot;: {&quot;date&quot;: &quot;2021-11-07&quot;, &quot;version&quot;: &quot;5.1.0+20211107&quot;}, &quot;data&quot;: {&quot;00010d56-fe38-5e35-8aed-518019aa36a5&quot;: {&quot;paper&quot;: {&quot;cardkingdom&quot;: {&quot;buylist&quot;: {&quot;foil.....
72with open('AllPrices.json','r') as f:
73    data = json.loads(f.read())
74pd.json_normalize(data, errors='ignore')
75with open('AllPrices.json') as fp:
76    prices = json.load(fp)
77
78    data = []
79    for uuid in prices['data']:
80        df = pd.json_normalize(prices['data'][uuid]) \
81               .filter(like='paper.cardmarket.retail.normal')
82        if df.empty:
83            continue
84        df.columns = df.columns.str.rsplit('.', 1).str[-1]
85        df.index = [uuid]
86        data.append(df)
87    df = pd.concat(data)
88                                      2021-08-09  2021-08-11  2021-08-12  2021-08-13  ...  2021-10-27  2021-10-28  2021-11-02  2021-11-09
8900010d56-fe38-5e35-8aed-518019aa36a5        4.35        4.35        4.35        4.35  ...        4.35        4.35        4.35        4.35
900001e0d0-2dcd-5640-aadc-a84765cf5fc9        4.95        4.95        3.45        3.45  ...        7.99        6.81        5.42        6.77
910003caab-9ff5-5d1a-bc06-976dd0457f19        0.24        0.27        0.07        0.38  ...        0.25        0.04        0.13        0.36
920003d249-25d9-5223-af1e-1130f09622a7        0.30        0.30        0.75        0.75  ...        0.15        0.20        0.04        0.25
930004a4fb-92c6-59b2-bdbe-ceb584a9e401        0.27        0.14        0.19        0.10  ...        0.10        0.05        0.19        0.13
94...                                          ...         ...         ...         ...  ...         ...         ...         ...         ...
95fffa4ccf-733e-513a-98f9-181b9549de62        0.23        0.15        0.21        0.21  ...        0.10        0.21        0.15        0.20
96fffb659e-b3fa-5cd8-9423-fe5ac74248b5        0.49        0.49        0.49        0.49  ...        0.35        0.35        0.35        0.20
97fffbc95a-c4d1-56aa-8653-8a7c71fe19ce        6.95        6.95        6.95        6.95  ...       10.26       10.26       10.26        6.43
98fffc1305-a118-559b-9504-3d7b56ca0bde        0.18        0.18        0.18        0.18  ...        0.04        0.04        0.04        0.04
99fffdd333-3789-5104-a8be-37be199a2cb1        0.87        0.73        0.99        0.99  ...        0.49        0.45        0.45        0.15
100
101[50568 rows x 75 columns]
102

Source https://stackoverflow.com/questions/69910864

QUESTION

joining two dataframes on matching values of two common columns R

Asked 2021-Oct-07 at 16:35

I have a two dataframes A and B that both have multiple columns. They share the common columns "week" and "store". I would like to join these two dataframes on the matching values of the common columns.

For example this is a small subset of the data that I have:

1A = data.frame(retailer = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
2store = c(5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6), 
3week = c(2021100301, 2021092601, 2021091901, 2021091201, 2021082901, 2021082201, 2021081501, 2021080801,
4          2021080101, 2021072501, 2021071801, 2021071101, 2021070401, 2021062701, 2021062001, 2021061301),
5dollars = c(121817.9, 367566.7, 507674.5, 421257.8, 453330.3, 607551.4, 462674.8,
6  464329.1, 339342.3, 549271.5, 496720.1, 554858.7, 382675.5,
7  373210.9, 422534.2, 381668.6))
8

and

1A = data.frame(retailer = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
2store = c(5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6), 
3week = c(2021100301, 2021092601, 2021091901, 2021091201, 2021082901, 2021082201, 2021081501, 2021080801,
4          2021080101, 2021072501, 2021071801, 2021071101, 2021070401, 2021062701, 2021062001, 2021061301),
5dollars = c(121817.9, 367566.7, 507674.5, 421257.8, 453330.3, 607551.4, 462674.8,
6  464329.1, 339342.3, 549271.5, 496720.1, 554858.7, 382675.5,
7  373210.9, 422534.2, 381668.6))
8B = data.frame(
9  week = c(&quot;2020080901&quot;, &quot;2017111101&quot;, &quot;2017061801&quot;, &quot;2020090701&quot;, &quot;2020090701&quot;, &quot;2020090701&quot;,
10           &quot;2020091201&quot;,&quot;2020082301&quot;, &quot;2019122201&quot;, &quot;2017102901&quot;),
11  store = c(14071, 11468, 2428, 17777, 14821, 10935,  5127, 14772, 14772, 14772),
12  fill = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
13)
14

I would like to join these two tables on the matching week AND store values in order to incorporate the "fill" column from B into A. Where the values don't match, I would like to have a label "0" in the fill column, instead of a 1. Is there a way I can do this? I am not sure which join to use as well, or if "merge" would be better for this? Essentially I am NOT trying to get rid of any rows that do not have the matching values for the two common columns. Thanks for any help!

ANSWER

Answered 2021-Oct-07 at 16:35

We may do a left_join

1A = data.frame(retailer = c(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
2store = c(5, 5, 5, 5, 5, 5, 5, 5, 6, 6, 6, 6, 6, 6, 6, 6), 
3week = c(2021100301, 2021092601, 2021091901, 2021091201, 2021082901, 2021082201, 2021081501, 2021080801,
4          2021080101, 2021072501, 2021071801, 2021071101, 2021070401, 2021062701, 2021062001, 2021061301),
5dollars = c(121817.9, 367566.7, 507674.5, 421257.8, 453330.3, 607551.4, 462674.8,
6  464329.1, 339342.3, 549271.5, 496720.1, 554858.7, 382675.5,
7  373210.9, 422534.2, 381668.6))
8B = data.frame(
9  week = c(&quot;2020080901&quot;, &quot;2017111101&quot;, &quot;2017061801&quot;, &quot;2020090701&quot;, &quot;2020090701&quot;, &quot;2020090701&quot;,
10           &quot;2020091201&quot;,&quot;2020082301&quot;, &quot;2019122201&quot;, &quot;2017102901&quot;),
11  store = c(14071, 11468, 2428, 17777, 14821, 10935,  5127, 14772, 14772, 14772),
12  fill = c(1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
13)
14library(dplyr)
15library(tidyr)
16A %&gt;%
17    mutate(week = as.character(week)) %&gt;% 
18    left_join(B) %&gt;% 
19    mutate(fill = replace_na(fill, 0))
20

Source https://stackoverflow.com/questions/69484758

Community Discussions contain sources that include Stack Exchange Network

Tutorials and Learning Resources in Retail

Tutorials and Learning Resources are not available at this moment for Retail

Share this Page

share link

Get latest updates on Retail